#319 Post Data into MsSQL

[MmVisual]

Hello,
I have 2 TZConnection, one is connected to "SQLite-3", seccond is connected to "mssql". The function copy all data from SQLite into a MSSQl Database for restore a backup. The fields ftString are right, but the fields ftMemo has wrong characters.
See screenshot from "SQL Server 2014 Management Studio"
The Source from SQLite database ist correct, I have write a log in my EXE, only the post into mssql goes wrong.
Thank you very much.

PS: If I compile the EXE with Zeos 7.2.5rc, the database do not open.

[marsupilami79]

Hello MmVisual,

I assume, you use the ntwdblib.dll and the mssql protocol? These beasts are hard to support and would rather not invest time in fixing things for ntwdblib.dll beacue it has reached end of life for more than 10 years now. Could you please try with Zeos 7.2.5 from SVN and freetds? Some older versions of FreeTDS are included in the Zeos SVN. Also there is an (older) description in our wiki that describes how to build freetds in case you can't find a suitable dll file: https://sourceforge.net/p/zeoslib/wiki/Building%20FreeTDS%20with%20libiconv%20on%20Windows%2032%20Bits/

Best regards,

Jan

[MmVisual]

The FreeTDS driver DLL from SVN do not work, the compillation is to complex, there must done 50 steps, it is not for using for everybody. But I write in future a other bug report, and I write in future a bug report with PostgreSQL. (I use may years Zeos with SQlite, MySQL, MariaDB, MsSQL and PostgreSQL. with EleLa). Is there a solution to post a newer FreeTDS DLL into the SVN? - Thx.

But now, I have update the SVN and install 7.2.5-rc. Then compile the EleLa project again and activate the SQL monitor. There in the log all version informations and the error "TABLE_QUALIFIER". The last version from zeos where working good was 7.1.x.
See attachment picture.

[MmVisual]

The attachment...

[marsupilami79]

Hello MmVisual,

hmm - which error do you get if you try to use the freetds library from SVN? I use it for testing connections to SQL Server 2014 from Delphi and Lazarus without problems.

"TABLE_QUALIFIER" is a column in the meta data of SQL server. It is used in Zeos in six locations. All of them execute a stored procedure that returns a column with the name "TABLE_QUALIFIER". What happens if you execute the following statements in your database?

exec sp_tables

exec sp_columns null, null

exec sp_column_privileges null, null, null

exec sp_table_privileges null, null, null

exec sp_pkeys null, null

exec sp_statistics null, null, null, null

Each of them should generate a result set where the first column is called "TABLE_QUALIFIER" (Tested on SQL Server 2014 Express).

[MmVisual]

I have execute all 6 SQL statements. All gives a table as result with first column "TABLE_QUALIFIER". But this tables has a no data (empty table):
- exec sp_column_privileges null, null, null
- exec sp_pkeys null, null
- exec sp_statistics null, null, null, null

Tested with "Microsoft SQL Server Management Studio 2014"

If I open the EXE with the driver for FreeTDS, see screenshot:
(You see the read from parameter file, the section [DB] in this log)

[MmVisual]

This is when the EXE starts with SQLite:

[MmVisual]

This with MySQL:

[marsupilami79]

Hmmm - I tried to create a test case for your problems with freetds. The following works like a charm for me on Lazarus 2.0RC2 / FPC 3.0.4 32 Bits / Zeos 7.2 / SQL Server 2017:

procedure TForm1.Button1Click(Sender: TObject);
begin
  ZConnection.Protocol := 'FreeTDS_MsSQL>=2005';
  ZConnection.HostName := 'localhost';
  ZConnection.Port := 1434;
  ZConnection.Database := 'master';
  ZConnection.User := 'sa';
  ZConnection.Password := 'password';
  ZConnection.LibraryLocation := 'C:\Komponenten\Zeos-7.2\lib\freetds\32 Bits\sybdb.dll';
  ZConnection.Connect;
  ShowMessage('Connected!');
end;

Do you do anything else?

The "TABLE_QUALIFIER"-Problem is even more of a mystery for me. Could you try to create a small example database and an example project that demonstarates the issue so I can debug it?

Bets regards,

Jan

[MmVisual]

I need some days to create a sample application. Thank you very much.

[MmVisual]

Here a example. MsSQL2014 as local server. Test with Zeos 7.1.4 works good, test with zeos 7.2.5 crashes, Lazarus 32Bit 1.8.4 with mssql driver and ntwdblib.dll. In attachmet the SQL Script for the table and a Screenshot file ans all other files. I hope it help to find the problem. Thank you very much.

[MmVisual]

Hint: If I use a TZSQLMonitor, then there is the function good, but when I delete the TZSQLMonitor the 7.2.5 crashes again.

[MmVisual]

Here the source with checkbox for activation/ deactivation from TZSQLMonitor

[EgonHugeist]

Hello Markus,

i've fixed all regression in SVN (7.3,trunk,7.2)
But the problem with FreeTDS i can't reproduce. I've used the lib from SVN btw. Plz don't use the ntwdblib anymore. The FreeTDS-lib offers a global connection charset UTF-8 to resolve your encoding issues without my "GUESS THE ENCODING IS X after fetching ton's of rows" as i made it in 7.1.

PLZ test and report you findings.
Mery christmas, Michael

[MmVisual]

Hello Michael,

Mery christmas, too.

Thank you very much for the fix, now the "mssql" works fine with 32Bit 7.2.5 and 7.3

I have found the problem for FreeTDS, the read from inifile do this:
DB.Protocol := LowerCase(ini.ReadString('DB', 'Protocol', 'sqlite-3'));
But a lower case driver name do not work, now I have fix my problem.

With 7.3.alpha is a problem, the Protocol "mysql" do not work with MariaDB 10.3, the EXE crash. But 7.2.5 works.

Now I check PostgreSQL.

Markus

[EgonHugeist]

Thanks for confirmation, Markus.