Recent

Author Topic: TMSSQLConnection - sqlDB component for accessing MS SQL Server  (Read 117993 times)

Lacak2

  • Guest

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #1 on: November 03, 2011, 02:33:14 pm »
I attached a modified version to the issue. I prefer to support also the last -1 version (0.82) since 0.91 is only about 6 months old.
The DBDATEREC version detection isn't very nice but support for dbdatecrack(nil,..) was introduced around the same time as the new DBDATEREC and crashed with a SIGSEGV before that.



BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #2 on: November 03, 2011, 02:58:48 pm »
Ludo,

Sounds like a good idea to support older versions as well... I suppose (guess) the older version might be in use in stable CentOS/Debian etc...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #3 on: November 03, 2011, 03:45:47 pm »
I am not very happy with supporting 0.82, as there may be also other bugs (0.82 was released in 2008) and also as you wrote hack "is not very nice"  :D
I will merge some of your changes with mine and I will upload here modified files so you can review/test changes.

Another question is if this MSSQLConnection will be accepted by fpc team and included into fcl-db sqlDB. If it will be leaved into bug tracker as is for next 1-2 years, then we do not need care about 0.82 at all  :) and things will be easier.

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #4 on: November 03, 2011, 04:38:16 pm »
Well, if freetds takes 3 years to release a new version and six months after its release only some rpm packages are available (http://pkgs.org/download/freetds), I'm afraid not supporting 0.82 is going to be difficult.
What scares me off most is the support for ntwdblib.dll. There are so many versions around that support different versions of servers and its use is deprecated http://msdn.microsoft.com/en-us/library/ms143729.aspx.
On my system I have different dll versions and I couldn't any to work with SQLEXPRESS 2008  :(

Let's hope the linux support will bring us a step closer to getting the msssql implemented by the fpc team. In the mean time, it won't stop you from using the unit, will it?

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #5 on: November 04, 2011, 10:51:51 am »
Well, if freetds takes 3 years to release a new version and six months after its release only some rpm packages are available (http://pkgs.org/download/freetds), I'm afraid not supporting 0.82 is going to be difficult.
I hope, that packages will be updated as time increases
(I am Windows only developer, so my knowledge of *nix world is near zero  :( )

What scares me off most is the support for ntwdblib.dll. There are so many versions around that support different versions of servers and its use is deprecated http://msdn.microsoft.com/en-us/library/ms143729.aspx.
On my system I have different dll versions and I couldn't any to work with SQLEXPRESS 2008  :(
Yes, because of it is ntwdblib by default commented and prefered FreeTDS.
(advantage of ntwdblib is that there is precompiled binary library; FreeTDS project does not provide precompiled binaries (for Windows), ... and build dll from sources using C/C++ compiler is for some users (specially Pascal) "hard work")

Let's hope the linux support will bring us a step closer to getting the msssql implemented by the fpc team. In the mean time, it won't stop you from using the unit, will it?
I agree with you, but I am unhappy with your hack. My basic phylosophy is "make things as simple as possible". So I prefer less functionality if price is so high.  :'(
So let's find some other simple solution or postpone decision into future (waiting for users feedback)
My idea: can we use for example dbiscount() to detect/guess version of client library?
(if not present, then we assume, that it is old 0.82)

See attached zip with my changes.

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #6 on: November 04, 2011, 12:15:09 pm »
Quote
My idea: can we use for example dbiscount() to detect/guess version of client library?
That was also my initial idea but the downside is that dbiscount dates 9/2010 and new DBDATEREC 2/2009. Like you wrote before, freetds dll's for windows aren't official and several versions are around, including ones build between these 2 dates.

Another, less risky, way of detecting the DBDATEREC version is to initialize DBDATEREC.millisecond to -1 before calling dbdatecrack. If DBDATEREC.millisecond is -1 after the call, we have the old version. I checked several dblib.c source
revisions back to 2002 and that should work fine.

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #7 on: November 04, 2011, 03:10:24 pm »
Quote
My idea: can we use for example dbiscount() to detect/guess version of client library?
That was also my initial idea but the downside is that dbiscount dates 9/2010 and new DBDATEREC 2/2009. Like you wrote before, freetds dll's for windows aren't official and several versions are around, including ones build between these 2 dates.
Hm, my guess was, that this is *nix only problem. I am not aware of any precompiled versions of FreeTDS for windows. Can you give me some links where are these binaries ?
In case of *nix do you mean, that there are also packages, which was build using some intermediate ("snapshot" - post 0.82) version of FreeTDS between 0.82 and 0.91 ?
(my original guess was, that we must deal only with 0.82 version from 2008 year, where was old DBDATE REC and no dbiscount())

Another, less risky, way of detecting the DBDATEREC version is to initialize DBDATEREC.millisecond to -1 before calling dbdatecrack. If DBDATEREC.millisecond is -1 after the call, we have the old version. I checked several dblib.c source
revisions back to 2002 and that should work fine.
If it helps avoid using GetDBDATERECVersion() at all then ok  ::)

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #8 on: November 04, 2011, 03:37:13 pm »
Quote
I am not aware of any precompiled versions of FreeTDS for windows. Can you give me some links where are these binaries ?
Google for "freetds windows" and you get as first link http://docs.moodle.org/20/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Windows. As the text explains, the dll's are renamed to php_dblib.dll but they are the same.

Another, less risky, way of detecting the DBDATEREC version is to initialize DBDATEREC.millisecond to -1 before calling dbdatecrack. If DBDATEREC.millisecond is -1 after the call, we have the old version. I checked several dblib.c source
revisions back to 2002 and that should work fine.
If it helps avoid using GetDBDATERECVersion() at all then ok  ::)
No more GetDBDATERECVersion()   O:-) It still requires the variable DBDATEREC record declaration. I'll give it some more testing and upload here the modified version. I don't have a running ntwdblib.dll config here and I can't validate the solution for that config. AFAIK MS hasn't changed the DBDATEREC format.

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #9 on: November 04, 2011, 04:53:51 pm »
Google for "freetds windows" and you get as first link http://docs.moodle.org/20/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Windows. As the text explains, the dll's are renamed to php_dblib.dll but they are the same.
Forget the link. php_dblib.dll is not just a rename of dblib.dll. The exports are different.

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #10 on: November 04, 2011, 05:38:37 pm »
Attached the changed DBDATEREC version detection.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #11 on: November 05, 2011, 01:17:58 pm »
I tried to add Sybase support but I'm doing something wrong as I seem to be able to connect but then get this in my test program:
Code: [Select]
select name from master..syslogins;
An unhandled exception occurred at $0042F429 :
sysindexkeys not found. Specify owner.objectname or use sp_help to check whether
 the object exists (sp_help may produce lots of output).

On second thoughts, I don't really want to check whether my SQL is buggy (seems to work in another query tool) - which might very well be the case or whether I've used FreeTDS in the wrong way.
There's an ODBC driver already  :)

If anybody wants to use it, go ahead. The code is based on Ludo's latest post above, with test programs for SQL Server and Sybase (tested on Sybase ASE).

I've added a server type property to the connection object. Based on server type, the tds version is set. Also, I've tried fiddling with the sybase/sql server "dialect"/interpretation flavour.
« Last Edit: November 05, 2011, 01:20:29 pm by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #12 on: November 07, 2011, 07:28:41 am »
Attached the changed DBDATEREC version detection.
Ludo, now is it perfect! No more objections from me side  :)
I only exchanged in definition of DBDATEREC "true" and "false" cases to "false" and "true"

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #13 on: November 07, 2011, 07:38:49 am »
I tried to add Sybase support but I'm doing something wrong as I seem to be able to connect but then get this in my test program:
Code: [Select]
select name from master..syslogins;
An unhandled exception occurred at $0042F429 :
sysindexkeys not found. Specify owner.objectname or use sp_help to check whether
 the object exists (sp_help may produce lots of output).
If we want add Sybase support, then I will go another way.
Create descendant class from TMSSQLConnection and name it TSybaseConnection and internaly preset TDS version etc. (or rename current TMSSQLConnection to TSQLServerConnection and create specialized TMSSQLConnection and TSybaseConnection)
If you agree, I can prepare such scenario. But as far as I have no Sybase server, I can not test any such changes.

Regarding exception you mention, it may be caused by TMSSQLConnection.UpdateIndexDefs ... it seems, that Sybase does not have system table "sysindexkeys" now used to determine indexes on given table.
So for Sybase we must chose another approach how to obtain indexes.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #14 on: November 07, 2011, 08:59:56 am »
Lacak2, good idea regarding creating a descendant - as FreeTDS itself is suited to both Sybase & SQL Server, the second solution seems better.

Yep, the UpdateIndexDefs seems like a likely culprit.
This: http://www.pdf-word.net/Tutorial-Database-MsSQL/MS-SQL-Server-7-To-Sybase-ASE-11-9-Migration-Guide.html says:
Quote
The MSSQL sysforeignkeys and sysindexkeys tables are equivalent to
ASE’s syskeys table.
(The newest version of Sybase ASE is at least 15, but I couldn't quickly find any newer reference)

Couldn't quickly fix the query, as syskeys is a bit cryptic. When I have more time (and feel like it  :) ), I'll get back to it...

As for getting Sybase: if you want, you can download e.g. the free developer's edition at http://www.sybase.com/ase_1500devel or the Express Edition (production use permitted, but with size/connection limits).
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified