TMSSQLConnection + SQL Server 2016 (v13)
lazarus / 16-Databases

----------------------------------------------------------------------
Sniper · 2018-05-11 08:50

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 · 2018-05-11 09:22

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.

----------------------------------------------------------------------
hmprof · 2018-05-11 20:28

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 · 2018-05-11 21:08

Below is a simple example for connecting to a SQL Server instance and
opening a query in a DBGrid

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.BtnConnexionExample01Click(Sender: TObject);begin  with
MSSQLConnectionExample01 do  begin    if Connected then      Connected
:= False;    HostName := 'MACHINE01\INSTANCE01';  // The name of your
instance SQL Server     DatabaseName := 'master';    UserName := 'sa';
         // Authentication SQL Server : User name     Password :=
'*********';   // Authentication SQL Server : Password // CharSet ://
- if you use Microsoft DB-Lib and set to 'UTF-8' then char/varchar
fields will be UTF8Encoded/Decoded// - if you use FreeTDS DB-Lib then
you must compile with iconv support (requires libiconv2.dll)//    or
cast char/varchar to nchar/nvarchar in SELECTs     CharSet :=
'UTF-8';// Params :// "AutoCommit=true" - if you don't want explicitly
commit/rollback transactions// "TextSize=16777216" - set maximum size
of text/image data returned// "ApplicationName=YourAppName" - Set the
app name for the connection.     Params.Clear;   
Params.Add('AutoCommit=true');    Params.Add('TextSize=16777216');   
Params.Add('ApplicationName=YourAppName');    // Connected := True;
 // See below   end;   with SQLTransactionExample01 do  begin    if
Active then      Active := False;    DataBase :=
MSSQLConnectionExample01;  end;   { use Database NameOfMyDatabase
(other than master) }  MSSQLConnectionExample01.DatabaseName :=
'NameOfMyDatabase';   with  SQLQueryExample01 do  begin    if Active
then      Close;    DataBase := MSSQLConnectionExample01;   
Transaction := SQLTransactionExample01;    SQL.Clear;   
SQL.Add('SELECT  Id, Column1, Column2, Column3... ');    SQL.Add('FROM
dbo.NameOfMyTable ');    SQL.Add('WHERE Id = 100');    // Open;  //
See below  end;   DataSourceExample01.DataSet := SQLQueryExample01; 
DBGridExample01.DataSource := DataSourceExample01;   { Connection } 
with MSSQLConnectionExample01 do  begin    if Connected then     
Connected := False;    Connected := True;  end;    { Open
SQLTransaction }  with SQLTransactionExample01 do  begin    if Active
then      Active := False;    Active := True;  end;   { Open query } 
with SQLQueryExample01 do  begin    if Active then      Close;   
Open;  end;end; 
A+

----------------------------------------------------------------------
Thaddy · 2018-05-12 06:12

@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.

----------------------------------------------------------------------
hmprof · 2018-05-12 08:55

@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 · 2018-05-14 08:54

Quote from: Thaddy on May 12, 2018, 08:12:34 amNote 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?

----------------------------------------------------------------------
Thaddy · 2018-05-14 09:14

Quote from: hmprof 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+
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   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.

----------------------------------------------------------------------
ccrause · 2018-05-22 09:50

Quote from: Sniper on May 14, 2018, 10:54:55 amCould 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var 
ODBCConnection: TODBCConnection;  SqlCommand: TSQLQuery; 
SqlTransaction: TSQLTransaction;  s: string;begin  ODBCConnection :=
TODBCConnection.Create(self);  SqlCommand := TSQLQuery.Create(self); 
SqlTransaction := TSQLTransaction.Create(self); 
ODBCConnection.Transaction := SqlTransaction;  SqlCommand.DataBase :=
ODBCConnection;      ODBCConnection.Driver := 'SQL Server Native
Client 11.0'; //   ODBCConnection.Params.Add('Integrated
Security=SSPI');  // No need for user name / password 
ODBCConnection.Params.Add('Persist Security Info=False'); 
ODBCConnection.Params.Add('Initial Catalog=master'); 
ODBCConnection.Params.Add('Server=(localdb)\' + LocalInstanceStr);  //
specify database server string 
ODBCConnection.Params.Add('AUTOCOMMIT=0');   ODBCConnection.Connected
:= true;   Memo1.Lines.Add('List of registered databases:');  try   
SqlCommand.SQL.Text := 'select name, filename, crdate from
sysdatabases order by crdate';    SqlCommand.Open;    s := '';    for
i := 0 to SqlCommand.FieldDefs.Count-1 do      s := s +
SqlCommand.FieldDefs[i].Name + #9;        Memo1.Lines.Add(s);    
while not SqlCommand.Eof do    begin      s := '';      for i := 0 to
SqlCommand.FieldCount-1 do        s := s +
SqlCommand.Fields[i].AsString + #9;        Memo1.Lines.Add(s);       
SqlCommand.Next;    end;  finally    SqlCommand.Close;  end;...

----------------------------------------------------------------------
badmintonfan · 2018-05-31 03:29

Quote from: hmprof on May 11, 2018, 10:28:27 pmPersonally 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 ?