TMSSQLConnection - sqlDB component for accessing MS SQL Server

[Lacak2]

At the same time I don't really understand this part of the code:

Code: [Select]

procedure TMSSQLConnection.DoInternalConnect;
...
  if Ftds = DBTDS_50 then //Sybase
    dbsetlversion(FDBLogin, DBVERSION_100) //ATM not implemented by FreeTDS 0.91; use freetds.conf to set "tds version=5.0"
  else
    dbsetlversion(FDBLogin, DBVER60);
... won't you have the same problem for MS SQL server: you can't go to a lower version than DBVERSION71 by specifying your own freetds.conf or TDSVER environment variable??

Yes it is true. I am not going support MS SQL Server versions before 2000
(I do not expect, that there will be demand from users support some versions older than 10 years ... same for Sybase)

This seems even better, but probably not every dblib.dll has that (I presume you compiled your own version, or is there a newer release available?)

Yes I compiled my own version and I also submit request to FreeTDS developement team to add (3 lines of code) into base sources.

May be, that not all is perfect, but let's wait if there will be real feedback from users ... if there will be, then we can do more on compatibility, usability etc.

[BigChimp]

May be, that not all is perfect, but let's wait if there will be real feedback from users ... if there will be, then we can do more on compatibility, usability etc.

Totally agreed, let's move on 

[Lacak2]

I updated UpdateIndexDefs with help of Google 
Now it works for me as I expect.
See attached files .
(I will be here on monday again)

Correction!

[ludob]

Using the latest version I still have a problem with identifier length on sybase. I created a field with an identifier length of 30. No problem using it with jdbc (com.sybase.jdbc3.jdbc.SybDriver) but when using with freetds I'm getting a "The identifier that starts with '...' is too long.  Maximum length is 28." When I run "execute sp_mda 1,1" over the freetds connection the value for MAXNAMELENGTHS is 30,30,30,30,30 which seems to ctradict the max 28. The identifier contains spaces and mixed case character and is therefor quoted (32 chars including quotes). Checked the queries sent by jdbc and freetds and they are the same.
The error message is coming from the server, not freetds. Verified with wireshark. It seems the selected protocol level isn't optimal for this sybase server. I'll try to figure out what jdbc is doing differently. If somebody has an idea in the mean time, don't hesitate.

[ludob]

According to Sybase docs, identifier lengths are reduced to 28 when QUOTED_IDENTIFIER is ON. Mssqlconn does this in DoInternalConnect. However, when I change QUOTED_IDENTIFIER  to OFF in DoInternalConnect, I have to put certain non-standard fields between brackets (= expected) but the max length is still 28...
I also looked at wireshark traces for jdbc (jconnect) and they also send a SET QUOTED_IDENTIFIER ON. The mystery remains...

[BigChimp]

Hi ludo,

The sybase version is the latest, 15.7 iirc.

If you enable freetds logging, what tds version is reported? I agree with you that the version might still be a problem.

Not at computer right now, when i am (today or tomorrow) i will get back to you.

Btw, seems that sybase ase 15+ has an increased max length of 255.... But freetds probably does not support it http://www.petersap.nl/SybaseWiki/index.php?title=Version_15_client_compatibility

[BigChimp]

Tds 5 reference, might explain what capabilities are negotiated after login which might influence long names support....
http://www.sybase.com/content/1040983/Sybase-tds38-102306.pdf

Maybe look at Tds_req_Largeident item in Tds_cap_request capabilities negotiations....

[ludob]

http://www.sybase.com/content/1040983/Sybase-tds38-102306.pdf

That is the same document I've been working with to decrypt the packages. What I have found is the following:
- Client to server TDS_CAPABILITY packets are different
jconnect (starts with 0xe2):

Code: [Select]

0070  00 00 01 35 31 32 00 00  00 03 00 00 00 00 e2 00   ...512.. ........
0080  18 01 0c 07 cd ff 85 ee  ef 65 7f ff ff ff d6 02   ........ .e......
0090  08 00 06 80 06 48 00 00  00                        .....H.. .       
dblib:

Code: [Select]

0070  00 04 01 35 31 32 00 00  00 03 00 00 00 00 e2 16   ...512.. ........
0080  00 01 09 00 08 0e 6d 7f  ff ff ff fe 02 09 00 00   ......m. ........
0090  00 00 02 68 00 00 00                               ...h...         
Note the big vs little endianness. (00 18 vs 16 00 ). If decrypted correctly, TDS_REQ_LARGEIDENT bit (83) is set for jconnect and not transmitted for dblib.

To verify this I tried creating a column with more than 35 characters with jconnect and that works. So large identifiers is supported by the database and by jconnect.

Still not an explanation why identifiers are limited to 28 chars when quoted identifier off  Since this is a server issue I'll leave it there. And large identifier support for freetds, that is something for freetds.

[BigChimp]

I updated UpdateIndexDefs with help of Google 
Now it works for me as I expect.
See attached files .
(I will be here on monday again)

Correction!

Then you must have different code than mine?!?! I downloaded yours and still got this:

Code: [Select]

Going to run:
select name from master..syslogins
==============================================================
An unhandled exception occurred at $0041441F :
EListError : List index (1) out of bounds
  $0041441F  TFPLIST__RAISEINDEXERROR,  line 48 of C:/Development/Fpc/Source/rtl
/objpas/classes/lists.inc
(with or without a SET FREETDS=5.0)

@Ludo: thanks for your investigations, sad that freetds doesn't seem to support it...

[BigChimp]

Yes I compiled my own version and I also submit request to FreeTDS developement team to add (3 lines of code) into base sources.

Hi Lacak2 (& everybody else),

Thanks for your efforts in writing the freetds wrapper. I've been playing around with building freetds. As you may have guessed, I almost certainly know less about C than you

I've got FreeTDS nightly build compiling on Debian but am running into trouble with a Windows build.
After some fiddling:

Code: [Select]

edit
win32/config.h
/* Define to 1 if you have the <inttypes.h> header file. */
#define HAVE_INTTYPES_H 1
into
/* BigChimp: I don't seem to have inttypes.h here, so undefine it:
#undef HAVE_INTTYPES_H 1
*/
nmake -fNmakefile -nologo apps PLATFORM=Win32 CONFIGURATION=Debug

I managed to get a dblib .lib/.obj with Visual Studio and the nmakefile, but couldn't get a dll built:

Code: [Select]

cd /d D:\Cop\freetds\src\dblib\win32\debug
rem Link into dblib.dll; specify all required .obj files to resolve symbols etc
link /dll *.obj ..\..\..\..\*.obj ..\..\..\tds\win32\debug\*.obj ..\..\..\replacements\win32\debug\*.obj /OUT:dblib.dll /implib:db-lib.lib
Still get 25 unresolved symbols, mostly winsock
If I add:

Code: [Select]

"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Lib\WS2_32.Lib"
to the command line, I still get 3 unresolved symbols (SHGetMalloc, SHGetPathFromIDListA, SHGetSpecialFolderLocation).

Rather than keeping Googling and trying (and failing) perhaps one of the kind readers here have a solution 

Thanks!

[Lacak2]

1. According to length of of indetifiers, Ludo can you post such request/info into FreeTDS mailing list ?
2. According to compiling FreeTDS under Windows, see readme.txt in zip for some informations , which I collected, when I compiled FreeTDS using Visual Studio Express.

[BigChimp]

Thanks, I've managed to compile dblib.dll including iconv.dll; it seems to work.

Updated the readme for Visual Studio 2010, and clarified some file/download locations etc:

Code: [Select]

Compiling FreeTDS DB-Lib with MS Visual C++ 2005:
=================================================
1.   Download sources from www.freetds.org
2.   Open FreeTDS.dsw from /win32/msvc6 source directory
2.1  in libTDS / Header Files edit config.h and comment "HAVE_INTTYPES_H":
     /* #undef HAVE_INTTYPES_H */
     (http://www.freetds.org/userguide/osissues.htm#WINDOWS)
2.2  Right-click on project "dblib_dll" and select "Properties"
     In Configuration Manager select "Release"
     C/C++ / Preprocesor / Preprocessor Definitions add "MSDBLIB" (optionally default TDS version "TDS71")
     Linker / Input / Additional Dependencies add "$(ProjectDir)\tds_Release\libTDS.lib"
     Linker / General / Output File change from ".\dbdll_Release\dblib_dll.dll" to "..\..\dblib.dll"
3.   Build "dblib_dll"
4.   The dblib.dll will appear in the Release (or Debug, depending on configuration) subdirectory

Compiling FreeTDS DB-Lib with MS Visual C++ 2010:
=================================================
Differences compared to Visual C++ 2005 seem to be confined to option naming.
2.2. Click on the FreeTDS Solution, in Build / Configuration Manager select "Release"
Right-click on project "dblib_dll" and select "Properties"     
     Configuration Properties / C/C++ / Preprocesor / Preprocessor Definitions add "MSDBLIB" (optionally default TDS version "TDS71")
     Linker / Input / Additional Dependencies add "$(ProjectDir)\tds_Release\libTDS.lib"
Linker / General / Output File change from ".\dbdll_Release\dblib_dll.dll" to "..\..\dblib.dll"


Compiling FreeTDS with iconv support:
=====================================
(not required when you don't use char/varchar/text datatypes or if you use character set (SBCS) ISO-8859-1 (Latin1) for your char/varchar/text columns)
1.  Download libiconv source, binaries and developer libraries for Windows
    http://gnuwin32.sourceforge.net/packages/libiconv.htm
e.g.:
http://gnuwin32.sourceforge.net/downlinks/libiconv-src-zip.php
and
http://gnuwin32.sourceforge.net/downlinks/libiconv-bin-zip.php
and
http://gnuwin32.sourceforge.net/downlinks/libiconv-lib-zip.php
or via
http://www.gnu.org/s/libiconv/
    and extract them to a directory, e.g. the directory iconv below your root FreeTDS folder
2.  in libTDS / Header Files edit config.h and uncomment /* #undef HAVE_ICONV */:
    #define HAVE_ICONV 1
3.  in Project properties:
    libTDS: C/C++ / General / Additional Include Directories add path to "include/iconv.h" (e.g. "..\..\iconv\src\libiconv\1.9.2\libiconv-1.9.2\include"
    dblib_dll: Linker / Input / Additional Dependencies add "lib/libiconv.lib" (e.g. "..\..\iconv\lib\libiconv.lib"
4.  Follow regular compilation instructions above
5.  Distribute libiconv2.dll with your dblib.dll


Using in Lazarus:
=================
1. Put on the form TSQLConnector and set property ConnectorType=MSSQLServer
2. Put into uses clause mssqlconn unit


Known problems:
===============
- CHAR/VARCHAR data truncated to column length when encoding to UTF-8 (use NCHAR/NVARCHAR instead or CAST char/varchar to nchar/nvarchar)
- Multiple result sets (for example when SP returns more than 1 result set only 1st is processed)
- DB-Library error 10038 "Results Pending" - set TSQLQuery.PacketRecords=-1 to fetch all pendings rows
- BLOB data (IMAGE/TEXT columns) larger than 16MB are truncated to 16MB - (set TMSSQLConnection.Params: 'TEXTSIZE=2147483647' or execute 'SET TEXTSIZE 2147483647')
  (create temporary stored procedures for prepared statements)

Manuals for DB-Library API:
===========================
http://msdn.microsoft.com/en-us/library/aa936988(v=sql.80).aspx
http://manuals.sybase.com/onlinebooks/group-cnarc/cng1110e/dblib/


[Lacak2]

Thanks, I've managed to compile dblib.dll including iconv.dll; it seems to work.

Please attach readme.txt 

[Lacak2]

Then you must have different code than mine?!?! I downloaded yours and still got this:

Code: [Select]

Going to run:
select name from master..syslogins
==============================================================
An unhandled exception occurred at $0041441F :
EListError : List index (1) out of bounds
  $0041441F  TFPLIST__RAISEINDEXERROR,  line 48 of C:/Development/Fpc/Source/rtl
/objpas/classes/lists.inc
(with or without a SET FREETDS=5.0)

select name from master..syslogins
for me works without exception.
Of course indexes are not returned in case of cross database queries 

[BigChimp]

Lacak, I think you might still have attached an old version of the code on Friday... Could you attach the latest version, please?

As for copy/pasting readme.txt from my post, sure, if you want to avoid that 
Attached to this post.....