TMSSQLConnection + SQL Server
lazarus / 16-Databases

----------------------------------------------------------------------
httpal · 2016-07-08 22:18

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 · 2016-07-09 10:29

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 · 2016-07-10 14:45

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

----------------------------------------------------------------------
LacaK · 2016-07-10 18:39

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 · 2016-07-11 01:13

Yes, from ms clients (FoxPro) I connect with no problems. Firewall is
completely disabled.

----------------------------------------------------------------------
LacaK · 2016-07-11 05:31

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 · 2016-07-12 08:46

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 · 2016-07-12 18:57

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 · 2016-07-14 04:59

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 · 2016-07-15 21:29

Quote from: httpal on July 14, 2016, 06:59:56 amThanks 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.

----------------------------------------------------------------------
LacaK · 2016-07-19 12:01

Quote from: eny on July 15, 2016, 11:29:41 pmNote 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 · 2016-07-21 16:12

Quote from: LacaK on July 19, 2016, 02:01:02 pmCan 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.

----------------------------------------------------------------------
LacaK · 2016-07-22 06:25

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 · 2016-07-23 21:02

Quote from: LacaK on July 22, 2016, 08:25:09 amWhich datetime data
types do you use in database ? (DATETIME2 or old DATETIME) ?datetime2
indeed (including milliseconds).

Quote from: LacaK on July 22, 2016, 08:25:09 amI 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.

Quote from: LacaK on July 22, 2016, 08:25:09 amIn 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.

Quote from: LacaK on July 22, 2016, 08:25:09 amHere you should use
AsDateTime always.If it would work that would be great.
But it does not.

----------------------------------------------------------------------
LacaK · 2016-07-24 13:10

Quote from: eny on July 23, 2016, 11:02:35 pmAsString 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

Quote from: eny on July 23, 2016, 11:02:35 pmQuote from: LacaK on July
22, 2016, 08:25:09 amHere 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 ...

----------------------------------------------------------------------
eny · 2016-07-24 20:49

Quote from: LacaK on July 24, 2016, 03:10:38 pmQuote from: eny on July
23, 2016, 11:02:35 pmAsString 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

Quote from: eny on July 23, 2016, 11:02:35 pmQuote from: LacaK on July
22, 2016, 08:25:09 amHere 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 ...
Ah sorry there seems to be a misunderstanding.
I'll try to be more clear: the existence of this bug is not open for
debate!
It is a quite serious bug that is very difficult to find a workaround
for. The way the ODBC component handles datetime(2) fields is sloppy
at best. Hence my warning for TS.
If one does not use the datetime2 SQL Server data type at all or not
do mass updates with the default 'SQL Server' driver, the problem will
most likely not present itself.

----------------------------------------------------------------------
LacaK · 2016-07-25 05:58

If it is bug, then it is bug of ODBC driver.
What should FPC do if ODBC driver reports, that column is VARCHAR type
... simply create TStringField ...

As a workaround you can define type helper for TField f.e. something
like this:

type
 TFieldHelper = class helper for TField
   function GetAsDateTime2: TDateTime;
 end;

function TFieldHelper.GetAsDateTime2: TDateTime;
begin
  if Self.DataType = ftString then
   Result := StrToDateTime(Self.AsDateTime, DefaultSQLFormatSettings);
  else
   Result := Self.AsDateTime;

----------------------------------------------------------------------
httpal · 2016-08-11 14:20

Tell me, please, who is the author of TMSSQLConnection component?

Thanks!!!

----------------------------------------------------------------------
LacaK · 2016-08-12 05:16

It is stated in the begining of source file ...