SqlDB and MS SQL Server support lazarus / 16-Databases ---------------------------------------------------------------------- Graeme · 2014-08-13 06:37 Hi, Looking at the FPC 2.6.4 fcl-db source folder it seems that SqlDB does have MS SQL Server support. What confuses me is that the readme.txt files mentions that I must download FreeTDS sources and compile it with MS Visual C++ (a compiler I don't have and have never used). Could somebody explain the situation with SqlDB and MS SQL Server support? * Must I have a C++ compiler? * Is the MS SQL Server support any good? * What version of MS SQL Server is supported? The program I am writing is going to run on the server which hosts SQL Server, so client libraries should all be there. Do I still need this FreeTDS library mentioned? Is FPC 2.7.1 support any better - I could use that if I must. While I'm at it, has anybody implemented MS SQL Server support in tiOPF? If so, do you mind sharing? Though it normally takes 30-60 minutes to add a new persistence layer, but that is 30-60 I could save. ;-) Regards,   - Graeme - ---------------------------------------------------------------------- Zoran · 2014-08-13 10:23 You might be interested to take a look at: http://bugs.freepascal.org/view.php?id=17303 and http://forum.lazarus.freepascal.org/index.php/topic,15135.0.html I have no idea about this, just remembered there was that forum topic, so I found the above links. Sorry if they don't help. Quote from: Graeme Geldenhuys on August 13, 2014, 08:37:38 amWhat confuses me is that the readme.txt files mentions that I must download FreeTDS sources and compile it with MS Visual C++ (a compiler I don't have and have never used). But it is a Windows only compiler! So, not much useful to FPC. I doubt you need that, though I admit that I do not know actually. ---------------------------------------------------------------------- marcov · 2014-08-13 10:37 See FPC ftp site. Note these binaries might be old, IIRC Lacak or Reinier asked me to upload them a while back: ftp://ftp.freepascal.org/fpc/contrib/windows/ ---------------------------------------------------------------------- Graeme · 2014-08-13 10:55 Thanks for all the replies. In the mean time I delved deaper into the SqlDB code and noticed that the SQL Server support can use 2 libraries to connect to the server. The Microsoft Client DB-Library or the open source FreeDTS library. So it seems I don't need the C++ compiler and FreeDTS after all. ---------------------------------------------------------------------- Zoran · 2014-08-13 10:59 Also, Zeos has FreeTDS_... protocols (take a look at TZConnection.Protocol in object inspector, there you will see these listed). Here is a user who says he uses it in both Windows and Linux: http://zeoslib.sourceforge.net/viewtopic.php?p=15772#p15772 On the bottom of the same topic, EgonHugeist says Quoteand we have a compiled FreeTDS msdblibr.dll (release) in our lib folderPerhaps it's the file you need. ---------------------------------------------------------------------- marcov · 2014-08-13 11:04 Quote from: Graeme Geldenhuys on August 13, 2014, 12:55:54 pmIn the mean time I delved deaper into the SqlDB code and noticed that the SQL Server support can use 2 libraries to connect to the server. The Microsoft Client DB-Library or the open source FreeDTS library. So it seems I don't need the C++ compiler and FreeDTS after all. Cool. I didn't know it could use the native ones too, since afaik the oledb/ado headers are not translated in FPC. (which is why MSSQL doesn't work with ZEOS either, I pointed out that sqldb used freetds to egon, Zoran's mail seems to indicate he fixed that). Is there more info about this somewhere? ---------------------------------------------------------------------- Graeme · 2014-08-13 15:35 I've successfully connected to one of our SQL Servers and managed to insert data without problems. The tiOPF project now has a SqlDB -> MS SQL Server persistence layer as well. For now I've used the dblib.dll connection - thanks for the download link Marco.  This was simply because FPC's dblib.pp unit is compiled with dblib.dll support by default. To use ntwdblib.dll I have to enable the other compiler define and recompile that unit. I'll do that as soon as my application is going to run on our actual SQL Server box. Many thanks for everybody's help. ---------------------------------------------------------------------- EgonHugeist · 2014-08-15 18:08 @Marco Long ago we did add the FreeTDS protocols(about two years ago inbetween?). But native access using the TDS "tabular data stream" protocol was possible with the knwon "sybase" and "mssql" protocols as long as i know Zeos. Just a hint hint our compiled dll: The "dbret_____" functions are missing there. Don't know about other complied lib's. But on our side the StoredProcedures are broken... We do access the Results and params by CAPI. supported protocols are: 'FreeTDS_MsSQL<=6.5' 'FreeTDS_Sybase<10' 'FreeTDS_Sybase-10+' 'FreeTDS_MsSQL-7.0' 'FreeTDS_MsSQL-2000' 'FreeTDS_MsSQL>=2005' More or less these are namespaces to set the tds-protocols for FreeTDS ---------------------------------------------------------------------- marcov · 2014-08-15 18:31 What do you need for native access? I found a python project that converted the IDL from the platform SDK to a TLB. I ran that through our importtl, and that generates a  compile pascal file that could be a basis for the FPC version of that unit. It is three time smaller than the Delphi one, but that is partially also the due to {$externalsym} statements in the Delphi source. ---------------------------------------------------------------------- LacaK · 2014-08-15 19:53 Quote from: Graeme Geldenhuys on August 13, 2014, 12:55:54 pmThe Microsoft Client DB-Library This was original library which I have used when I start work on mssqlconnection. Of course it is no more developed by Microsoft and freezes sometime in MS SQL 6.0 time (so it supports only TDS v. 4.2 IIRC) Quote from: Graeme Geldenhuys on August 13, 2014, 12:55:54 pm or the open source FreeDTS library. So it seems I don't need the C++ compiler and FreeDTS after all. It is better solution as FreeTDS project is alive and supports also new features of TDS protocol added in version 2008 + FreeTDS is cross platform. There are also available pre-compiled DLL for Win32/64 so you do not need build own version when you do not care. ---------------------------------------------------------------------- EgonHugeist · 2014-08-15 21:09 @Marco Hmm we already can access MSSQL using the protocols. But there are some issues: NTEXT columns need to be converter to TEXT columns or we get an Exception from MSSQL pffff. DBLIB doen't return a unique identifier type for NCHAR/CHAR or NVARCHAR/VARCHAR columns -> result is we need a encoding detection for the strings. (this is what freetds + the slow libiconv is doing too) OleDB is prefered for MSSQL until Ver2012(new 2014 broke with OleDB again AFAIK). So having such a wrapper unit for Windows only would be the cherry on the cake. Marco after playing with your link i was able to download your oledb.pas -> it's a bit confusing all types have a weird name. Anyway a good start. Hint: http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=10110 Quote from: sternasOh, in lab CT ver 4.7 we add pl_Win_OleDB for ADO support on Windows. Merry Christmas and a happy New Year... Sternas is maintainer of CodeTyphoon and it looks like they already have a wrapper unit?! OleDB provides some more little advantages: Microsoft JET Engine is not ODBC compatible but can do some tricks: Add a header destription file to the settings and you can use SQL syntax for *.csv/xls/txt etc. files. ---------------------------------------------------------------------- eny · 2014-08-15 22:11 Quote from: EgonHugeist on August 15, 2014, 11:09:59 pmOleDB is prefered for MSSQL until Ver2012(new 2014 broke with OleDB again AFAIK)MS is abandoning OleDB in favor of ODBC. ---------------------------------------------------------------------- EgonHugeist · 2014-08-15 22:46 Quote from: eny on August 16, 2014, 12:11:50 amQuote from: EgonHugeist on August 15, 2014, 11:09:59 pmOleDB is prefered for MSSQL until Ver2012(new 2014 broke with OleDB again AFAIK)MS is abandoning OleDB in favor of ODBC. Yes you are right: http://msdn.microsoft.com/en- us/library/hh967418.aspx Well Oracle and IBM-DB2 did provide new OleDB drivers too. Why?.. Oh: ODBC simply is horrable slow. I don't know any RDBM which performce very well with ODBC access. It might be MSSQL will be first than? (: See: http://blog.synopse.info/post/2014/03/07/Support-of- MySQL,-DB2-and-PostgreSQL Note the current Synopse access api isn't 100% ready for our Zeos while ODBC access is ready. Now compare ODBC access <> native client access... ---------------------------------------------------------------------- eny · 2014-08-16 08:17 Quote from: EgonHugeist on August 16, 2014, 12:46:02 amOh: ODBC simply is horrable slow. I don't know any RDBM which performce very well with ODBC access. It might be MSSQL will be first than? (:MS are 'cheating' a bit. Thye support bulk inserts through ODBC.