* * *

Author Topic: TMSSQLConnection + SQL Server 2016 (v13)  (Read 821 times)

Sniper

  • New member
  • *
  • Posts: 23
TMSSQLConnection + SQL Server 2016 (v13)
« on: May 11, 2018, 10:50:34 am »
Anyone did the connection to latest SQL database?
I could not do it with FreeTDS(dblib.dll) on Win64 with Lazarus 32-bit. Is it possible?

Thaddy

  • Hero Member
  • *****
  • Posts: 6101
Re: TMSSQLConnection + SQL Server 2016 (v13)
« Reply #1 on: May 11, 2018, 11:22:28 am »
Afaik, FreeTDS hasn't been updated (stable) since 2015, so unless you build it yourself from git https://github.com/FreeTDS/freetds.....
Alternatively you can use ODBC.
I might not give the answer that you want me to.. Peter Green 1969

hmprof

  • New member
  • *
  • Posts: 8
Re: TMSSQLConnection + SQL Server 2016 (v13)
« Reply #2 on: May 11, 2018, 10:28:27 pm »
Personally I use
Lazarus 1.8.0 r56594 FPC 3.0.4 i386-win32-win32/win64
+ SQL Server 2014
+ TMSSQLConnection

It's working really well.

Proceed as follows:
1 - You have to download the dll from the link below :
ftp://ftp.freepascal.org/fpc/contrib/windows/dblib_1.00.zip
2 - Decompress the file dblib_1.00.zip
3 - Retrieve the 2 dll files, below, from the subdirectory
..\dblib_1.00.zip\Win32\
    dblib_2008.dll
    libiconv2.dll
4 - Copy the 2 dll files (dblib_2008.dll and libiconv2.dll) in the same directory of your executable program
5 - and finally, Rename the dblib_2008.dll file to dblib.dll (this last point, not very intuitive, is important !)

A+

hmprof

  • New member
  • *
  • Posts: 8
Re: TMSSQLConnection + SQL Server 2016 (v13)
« Reply #3 on: May 11, 2018, 11:08:11 pm »
Below is a simple example for connecting to a SQL Server instance and opening a query in a DBGrid

Code: Pascal  [Select]
  1. procedure TForm1.BtnConnexionExample01Click(Sender: TObject);
  2. begin
  3.   with MSSQLConnectionExample01 do
  4.   begin
  5.     if Connected then
  6.       Connected := False;
  7.     HostName := 'MACHINE01\INSTANCE01';  // The name of your instance SQL Server
  8.     DatabaseName := 'master';
  9.     UserName := 'sa';          // Authentication SQL Server : User name
  10.     Password := '*********';   // Authentication SQL Server : Password
  11. // CharSet :
  12. // - if you use Microsoft DB-Lib and set to 'UTF-8' then char/varchar fields will be UTF8Encoded/Decoded
  13. // - if you use FreeTDS DB-Lib then you must compile with iconv support (requires libiconv2.dll)
  14. //    or cast char/varchar to nchar/nvarchar in SELECTs
  15.     CharSet := 'UTF-8';
  16. // Params :
  17. // "AutoCommit=true" - if you don't want explicitly commit/rollback transactions
  18. // "TextSize=16777216" - set maximum size of text/image data returned
  19. // "ApplicationName=YourAppName" - Set the app name for the connection.
  20.     Params.Clear;
  21.     Params.Add('AutoCommit=true');
  22.     Params.Add('TextSize=16777216');
  23.     Params.Add('ApplicationName=YourAppName');
  24.     // Connected := True;  // See below
  25.   end;
  26.  
  27.   with SQLTransactionExample01 do
  28.   begin
  29.     if Active then
  30.       Active := False;
  31.     DataBase := MSSQLConnectionExample01;
  32.   end;
  33.  
  34.   { use Database NameOfMyDatabase (other than master) }
  35.   MSSQLConnectionExample01.DatabaseName := 'NameOfMyDatabase';
  36.  
  37.   with  SQLQueryExample01 do
  38.   begin
  39.     if Active then
  40.       Close;
  41.     DataBase := MSSQLConnectionExample01;
  42.     Transaction := SQLTransactionExample01;
  43.     SQL.Clear;
  44.     SQL.Add('SELECT  Id, Column1, Column2, Column3... ');
  45.     SQL.Add('FROM dbo.NameOfMyTable ');
  46.     SQL.Add('WHERE Id = 100');
  47.     // Open;  // See below
  48.   end;
  49.  
  50.   DataSourceExample01.DataSet := SQLQueryExample01;
  51.   DBGridExample01.DataSource := DataSourceExample01;
  52.  
  53.   { Connection }
  54.   with MSSQLConnectionExample01 do
  55.   begin
  56.     if Connected then
  57.       Connected := False;
  58.     Connected := True;
  59.   end;
  60.  
  61.   { Open SQLTransaction }
  62.   with SQLTransactionExample01 do
  63.   begin
  64.     if Active then
  65.       Active := False;
  66.     Active := True;
  67.   end;
  68.  
  69.   { Open query }
  70.   with SQLQueryExample01 do
  71.   begin
  72.     if Active then
  73.       Close;
  74.     Open;
  75.   end;
  76. end;
  77.  

A+
« Last Edit: May 11, 2018, 11:10:20 pm by hmprof »

Thaddy

  • Hero Member
  • *****
  • Posts: 6101
Re: TMSSQLConnection + SQL Server 2016 (v13)
« Reply #4 on: May 12, 2018, 08:12:34 am »
@hmprof
The question is about mssqlserver 2016, not mssqlserver 2008..... Yes, that works, but 2016 does not!
This is a valid question. Alas, the third-party software has no stable release for it, since it is from 2015.
You need to build it from source to get it working.
See the link I gave. Note the ODBC path I suggested will also work, but is partly limiting functionality.
« Last Edit: May 12, 2018, 08:15:17 am by Thaddy »
I might not give the answer that you want me to.. Peter Green 1969

hmprof

  • New member
  • *
  • Posts: 8
Re: TMSSQLConnection + SQL Server 2016 (v13)
« Reply #5 on: May 12, 2018, 10:55:49 am »
@Thaddy
OK. Thank you for that clarification.
I thought there wouldn't be much difference between SQL Server 2014 and SQL Server 2016
Indeed, I used TMSSQLConnection with SQL Server 2008 R2, SQL Server 2012 and SQL Server 14 and it works very well
But I haven't had a chance to test with SQL Server 2016
If TMSSQLConnection does not work under SQL server 2016, it is still worrisome! I hope that an update of the TMSSQLConnection components and dll files will be published soon (?).

A+

Sniper

  • New member
  • *
  • Posts: 23
Re: TMSSQLConnection + SQL Server 2016 (v13)
« Reply #6 on: May 14, 2018, 10:54:55 am »
Note the ODBC path I suggested will also work, but is partly limiting functionality.

Could you show a right example with ODBC or some advice what to write in Driver and FileDSN fields...? Is it enouth?
« Last Edit: May 14, 2018, 10:56:44 am by Sniper »

Thaddy

  • Hero Member
  • *****
  • Posts: 6101
Re: TMSSQLConnection + SQL Server 2016 (v13)
« Reply #7 on: May 14, 2018, 11:14:42 am »
@Thaddy
OK. Thank you for that clarification.
I thought there wouldn't be much difference between SQL Server 2014 and SQL Server 2016
Indeed, I used TMSSQLConnection with SQL Server 2008 R2, SQL Server 2012 and SQL Server 14 and it works very well
But I haven't had a chance to test with SQL Server 2016
If TMSSQLConnection does not work under SQL server 2016, it is still worrisome! I hope that an update of the TMSSQLConnection components and dll files will be published soon (?).

A+
Well, yes, but there is no issue with the FPC/Lazarus side of the code. TMSSQLConnection depends on the drivers. If these are not up-to-date there is little we can do.
If you compile the driver from its repository (note that is C, not Pascal) and install it, the Pascal side of the code works.
The worrysommy  :D part is the fact that there has been no stable release for the driver since 2015....Nothing to do with our side.... Good news is that there are still many recent commits for that driver. Just no release. Suggest to contact the developer(s). Because TMSSQLConnection needs no updates (maybe for some new features, but it works) if you are able to build the driver from source. Most professionals can.
« Last Edit: May 14, 2018, 11:21:00 am by Thaddy »
I might not give the answer that you want me to.. Peter Green 1969

ccrause

  • Full Member
  • ***
  • Posts: 117
Re: TMSSQLConnection + SQL Server 2016 (v13)
« Reply #8 on: May 22, 2018, 11:50:43 am »
Could you show a right example with ODBC or some advice what to write in Driver and FileDSN fields...? Is it enouth?
I have an ODBC connection working to a MS Sql localdb instance.  Probably not exactly what you are looking for, but here it is:
Code: Pascal  [Select]
  1. var
  2.   ODBCConnection: TODBCConnection;
  3.   SqlCommand: TSQLQuery;
  4.   SqlTransaction: TSQLTransaction;
  5.   s: string;
  6. begin
  7.   ODBCConnection := TODBCConnection.Create(self);
  8.   SqlCommand := TSQLQuery.Create(self);
  9.   SqlTransaction := TSQLTransaction.Create(self);
  10.   ODBCConnection.Transaction := SqlTransaction;
  11.   SqlCommand.DataBase := ODBCConnection;    
  12.   ODBCConnection.Driver := 'SQL Server Native Client 11.0'; //
  13.   ODBCConnection.Params.Add('Integrated Security=SSPI');  // No need for user name / password
  14.   ODBCConnection.Params.Add('Persist Security Info=False');
  15.   ODBCConnection.Params.Add('Initial Catalog=master');
  16.   ODBCConnection.Params.Add('Server=(localdb)\' + LocalInstanceStr);  // specify database server string
  17.   ODBCConnection.Params.Add('AUTOCOMMIT=0');
  18.   ODBCConnection.Connected := true;
  19.  
  20.   Memo1.Lines.Add('List of registered databases:');
  21.   try
  22.     SqlCommand.SQL.Text := 'select name, filename, crdate from sysdatabases order by crdate';
  23.     SqlCommand.Open;
  24.     s := '';
  25.     for i := 0 to SqlCommand.FieldDefs.Count-1 do
  26.       s := s + SqlCommand.FieldDefs[i].Name + #9;
  27.    
  28.     Memo1.Lines.Add(s);
  29.  
  30.     while not SqlCommand.Eof do
  31.     begin
  32.       s := '';
  33.       for i := 0 to SqlCommand.FieldCount-1 do
  34.         s := s + SqlCommand.Fields[i].AsString + #9;
  35.         Memo1.Lines.Add(s);
  36.  
  37.         SqlCommand.Next;
  38.     end;
  39.   finally
  40.     SqlCommand.Close;
  41.   end;
  42. ...

badmintonfan

  • New member
  • *
  • Posts: 8
Re: TMSSQLConnection + SQL Server 2016 (v13)
« Reply #9 on: May 31, 2018, 05:29:01 am »
Personally I use
Lazarus 1.8.0 r56594 FPC 3.0.4 i386-win32-win32/win64
+ SQL Server 2014
+ TMSSQLConnection

It's working really well.

Proceed as follows:
1 - You have to download the dll from the link below :
ftp://ftp.freepascal.org/fpc/contrib/windows/dblib_1.00.zip
2 - Decompress the file dblib_1.00.zip
3 - Retrieve the 2 dll files, below, from the subdirectory
..\dblib_1.00.zip\Win32\
    dblib_2008.dll
    libiconv2.dll
4 - Copy the 2 dll files (dblib_2008.dll and libiconv2.dll) in the same directory of your executable program
5 - and finally, Rename the dblib_2008.dll file to dblib.dll (this last point, not very intuitive, is important !)

A+
My question is,the dll files can not work within the same folder of executable program ,I have to put them in c:\windows\system32 ,but I hope they can work within the same folder,anyone know the way to fix it ?

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus