Recent

Author Topic: TMSSQLConnection + SQL Server  (Read 11556 times)

httpal

  • New Member
  • *
  • Posts: 14
TMSSQLConnection + SQL Server
« on: July 09, 2016, 12:18:21 am »
Hi everyone!

I use Lazarus with SQL Server 2008, and my app works fine on most computers.
But I ran into a problem. If, for example, windows 7 recently installed on a computer, then my app will generate an error:
MSSQLConnection1 : Error : 20002 :
Adaptive Server Connection Failed (ip:port)

So, I installed on new system:
Microsoft Visual C++ 2005 Redistributable
Microsoft Visual C++ 2008 Redistributable
Microsoft SQL Server 2008 Client
and other...

But it did not help.

I use:
Lazarus 1.6 (32 bit)
FPC 3.0
dblib.dll (0.95 win32)

All machines on the same subnet and ping the server.
Maybe I need something else to install?

Thanks in advance.

Zath

  • Sr. Member
  • ****
  • Posts: 327
Re: TMSSQLConnection + SQL Server
« Reply #1 on: July 09, 2016, 12:29:54 pm »
I'd be interested to know what combination of components and connection details you used to successfully connect.
I've had lots of problems try to connect to MS SQL 2012 or 2014.
Admittedly I'm using Win 7 64bit too.

httpal

  • New Member
  • *
  • Posts: 14
Re: TMSSQLConnection + SQL Server
« Reply #2 on: July 10, 2016, 04:45:14 pm »
I use windows 7 64 Bit, windows 7 32 bit, windows xp, windows server 2008 64 bit (as client), windows 10, 64 bits, windows server 2003 (as client) ! And it all works fine through TMSSQLConnection with SQL Server 2008 64. But some windows 7 32 bit do not work. And I can not figure out ...

I installed a new windows 7 professional 32 bit without activation, set up a network and install Lazarus. I create a connection to a database, just like on a different machine and it does not work ...
« Last Edit: July 11, 2016, 03:40:08 am by httpal »

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: TMSSQLConnection + SQL Server
« Reply #3 on: July 10, 2016, 08:39:20 pm »
And can you connect from those machines where you have problems with TMSSQLConnection with any other database client tool ?
(probably firewall is blocking trafic?)

httpal

  • New Member
  • *
  • Posts: 14
Re: TMSSQLConnection + SQL Server
« Reply #4 on: July 11, 2016, 03:13:38 am »
Yes, from ms clients (FoxPro) I connect with no problems. Firewall is completely disabled.

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: TMSSQLConnection + SQL Server
« Reply #5 on: July 11, 2016, 07:31:51 am »
Hm, error 20002 is something new to me.
Please look at FreeTDS.org web site how to generate dump file: http://www.freetds.org/userguide/freetdsconf.htm
Then we can lok inside dump and see what is going wrong at which point ...


httpal

  • New Member
  • *
  • Posts: 14
Re: TMSSQLConnection + SQL Server
« Reply #6 on: July 12, 2016, 10:46:06 am »
Ok, please help, how to connect the freetds.conf to the MSSQLConnection1 (dblib.dll) to get dump? If I correctly understand the process...

I create file freetds.conf:
[global]
tds version = 7.3
               
[myserver]
host = [server ip]
port = 1433
tds version = 7.3

Native freetds works fine (without lazarus).

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: TMSSQLConnection + SQL Server
« Reply #7 on: July 12, 2016, 08:57:06 pm »
Create plain text file named "freetds.conf"with this content:
Code: [Select]
[global]
tds version = 7.3
client charset=UTF-8
dump file=freetds.log
and place it somewhere for example: D:\freetds.conf

Then set environment variable:
FREETDS=D:\freetds.conf

That is all. When you run your application log will be created in application directory.

httpal

  • New Member
  • *
  • Posts: 14
Re: TMSSQLConnection + SQL Server
« Reply #8 on: July 14, 2016, 06:59:56 am »
Thanks for help, I appreciate it.
I did as you wrote, but it does not work. the log file is empty.
I configured odbc connection, I try to work with it.
Thanks.

eny

  • Hero Member
  • *****
  • Posts: 1587
Re: TMSSQLConnection + SQL Server
« Reply #9 on: July 15, 2016, 11:29:41 pm »
Thanks for help, I appreciate it.
I did as you wrote, but it does not work. the log file is empty.
I configured odbc connection, I try to work with it.
Thanks.
Some info on that: M$ has a new ODBC driver that is not installed by default on Win10.
Win10 can give you update problems when not using the latest ODBC driver, which can be downloaded from the M$ site.
Note however that there is a bug in FPC's ODBC handling in case of datetime fields with this new driver.
If you don't use datetime fields with milliseconds then you will probably be fine.
All posts based on: Win10 (Win64); Lazarus 1.8.0 'stable' (#56594 win64) unless specified otherwise...

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: TMSSQLConnection + SQL Server
« Reply #10 on: July 19, 2016, 02:01:02 pm »
Note however that there is a bug in FPC's ODBC handling in case of datetime fields with this new driver.
If you don't use datetime fields with milliseconds then you will probably be fine.
Can you give us more details, which values expected, which returned in which case with which driver / SQL server version ?

eny

  • Hero Member
  • *****
  • Posts: 1587
Re: TMSSQLConnection + SQL Server
« Reply #11 on: July 21, 2016, 06:12:27 pm »
Can you give us more details, which values expected, which returned in which case with which driver / SQL server version ?
Windows10: 2 ODBC driver versions possible.
  • The default 'SQL Server' ODBC driver that is available by default; this one works for example on Windows 8 and older (and also on Windows 10). But on Windows 10 it no longer supports bulk insert operations.
  • An updated driver for Windows 10 that is a recommended install and free download, but not available by default: 'ODBC Driver 11 for SQL Server'
The problem is with datetime fields: with the default driver ('SQL Server') fpc/lazarus correctly recturns values in the format 'yyyy-mm-dd hh:mi:ss.<msec>' (when using the AsString field type).
With the new driver fpc/lazarus erroneously starts using locale settings for returning a datetime value. So for example values can be returned as 'dd-mm-yyyy hh:mi:ss'. Note that the year and day are swapped and the milliseconds are missing.
The bug is that fpc/lazarus uses locale settings to internally translate datetime values back and forth to string representations, even if one just requests the AsDateTime field type value.

DateTime values should just be returned as is, without internal translations to and from string values.
And the AsString field type value should consistently return values in the format 'yyyy-mm-dd hh:mm:ss.nnn' without using locale settings.
All posts based on: Win10 (Win64); Lazarus 1.8.0 'stable' (#56594 win64) unless specified otherwise...

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: TMSSQLConnection + SQL Server
« Reply #12 on: July 22, 2016, 08:25:09 am »
Which datetime data types do you use in database ? (DATETIME2 or old DATETIME) ?

I guess, that in case of old "SQL Server" ODBC driver, just SQL Servers returns new date-time values (of new data types introduced in SQL Server 2008) as string (check data type of field created for this columns. It is TStringField?). So it explains why, when you use AsString, you get date time value formated as "SQL Server"

In case of "ODBC Driver 11" SQL Server sends DATETIME2 values as native date time, so you must see on client side TDateTimeField, right ? Then it is okay, that when you use AsString you get "locale dependant" formatted string. Here you should use AsDateTime always.

eny

  • Hero Member
  • *****
  • Posts: 1587
Re: TMSSQLConnection + SQL Server
« Reply #13 on: July 23, 2016, 11:02:35 pm »
Which datetime data types do you use in database ? (DATETIME2 or old DATETIME) ?
datetime2 indeed (including milliseconds).

I guess, that in case of old "SQL Server" ODBC driver, just SQL Servers returns new date-time values (of new data types introduced in SQL Server 2008) as string (check data type of field created for this columns. It is TStringField?). So it explains why, when you use AsString, you get date time value formated as "SQL Server"
Maybe.

In case of "ODBC Driver 11" SQL Server sends DATETIME2 values as native date time, so you must see on client side TDateTimeField, right ? Then it is okay, that when you use AsString you get "locale dependant" formatted string.
No.
AsString is a data exchange function and should report dtm values in a consistent format and not use spurious locale settings.

Here you should use AsDateTime always.
If it would work that would be great.
But it does not.
All posts based on: Win10 (Win64); Lazarus 1.8.0 'stable' (#56594 win64) unless specified otherwise...

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: TMSSQLConnection + SQL Server
« Reply #14 on: July 24, 2016, 03:10:38 pm »
AsString is a data exchange function and should report dtm values in a consistent format and not use spurious locale settings.
TDateTieField.AsString returns its value formated using FormatDateTime() function, which uses ShortDateFormat and LongTimeFormat global variables for formating. It is so from begining (also in Delphi) and it won't be changed

Here you should use AsDateTime always.
If it would work that would be great.
But it does not.
It does not work, because in 1st case you have datetime value stored in TStringField and using AsDateTime raises exception , because FPC is trying convert string representation using locale settings to TDateTime representation.

There is no wrong on FPC side, but these two ODBC drivers are just incompatible.
So if you want support both drivers with your application with DATETIME2 columns, you must handle it separately on your application side ...