Lazarus
Programming => Databases => Topic started by: Lacak2 on November 03, 2011, 11:07:00 am
-
Discussion for http://bugs.freepascal.org/view.php?id=17303
-
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.
-
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...
-
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.
-
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 (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?
-
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 (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.
-
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.
-
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 ::)
-
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 (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.
-
Google for "freetds windows" and you get as first link http://docs.moodle.org/20/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Windows (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.
-
Attached the changed DBDATEREC version detection.
-
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:
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.
-
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"
-
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:
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.
-
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 (http://www.pdf-word.net/Tutorial-Database-MsSQL/MS-SQL-Server-7-To-Sybase-ASE-11-9-Migration-Guide.html) says:
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 (http://www.sybase.com/ase_1500devel) or the Express Edition (production use permitted, but with size/connection limits).
-
Hi Lacak2,
I've sort of figured out a possible fix for the indexkeys stuff.
The UpdateIndexDefs procedure needs some changes:
with qry do
begin
ReadOnly := True;
if FServerType = Sybase then
begin
// Apparently indid 0 is the table name in Sybase
// http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.help.ase.15.7/title.htm
SQL.Text:='select si.name as idx_name, ' +
'si.indid, ' +
'index_col(object_name(si.id),indid,1) as col_name, ' +
{ status: flag 0x2/2: unique index}
'(si.status & 2)/2 as IsUnique, ' +
{ status: 0x800/2048: primary key}
'(si.status & 2048)/2048 as IsPrimaryKey ' +
{ status2: flag 0x1/1: index supports foreign key
(si.status2 & 1) as foreignkey}
'from ' +
'sysindexes si ' +
'where ' +
{ indid 0: table
indid 255: text, image, text chain, or Java off-row structure (large object—or LOB—structure).}
'si.indid > 0 ' +
'and ' +
'si.indid < 255 ' +
'and ' +
{Tablename may have multiple dots etc so we have to normalize }
'si.id = object_id('''+TableName+''')'+
';';
writeln('debug sql: ');
writeln(sql.text);
writeln('Still get errors; incorrect syntax near '';'');
end
else
begin
//MS SQL Server
//TODO: we can use "execute dbo.sp_helpindex 'TableName'" when Open on execute will fully work
SQL.Text:='select i.name as idx_name, i.indid, c.name as col_name'+
',indexproperty(i.id, i.name, ''IsUnique'')'+
',objectproperty(o.id, ''IsPrimaryKey'') '+
'from sysindexes i '+
' join sysindexkeys k on i.id=k.id and i.indid=k.indid '+
' join syscolumns c on k.id=c.id and k.colid=c.colid '+
' left join sysobjects o on i.name=o.name and i.id=o.parent_obj '+
'where i.id=object_id('''+TableName+''')'+
' and used>0 '+
'order by k.indid, k.keyno';
end;
Open;
end;
... but there are still errors - I'd have to check if they occur in the actual select statement or the one above (which runs fine in a query tool)
If you want to split out the sybase stuff as you suggested, I can wait for that and incorporate my changes in that - might make it easier to keep it up to date.
Thanks
-
Lacak2, good idea regarding creating a descendant - as FreeTDS itself is suited to both Sybase & SQL Server, the second solution seems better.
I created direct descendant of TMSSQLConnection for simplicity - TSybaseConnection (I am not sure with name "Sybase" because it is name of vendor (company) and there are many database products "ASE", "Sql Anywhere", "Advantage Database Server" ... so I do not know how to name them ...) .
Please see attached zip
Yep, the UpdateIndexDefs seems like a likely culprit.
...
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...
You can use for Sybase another approach (may be, that there are other tables in system catalog, which can be used.
I created in UpdateIndexDef separate if ... then so replace SQL.Text there
As for getting Sybase: if you want, you can download e.g. the free developer's edition at http://www.sybase.com/ase_1500devel (http://www.sybase.com/ase_1500devel) or the Express Edition (production use permitted, but with size/connection limits).
Thanks for info, but download file size is too big for me :(
-
I created direct descendant of TMSSQLConnection for simplicity - TSybaseConnection (I am not sure with name "Sybase" because it is name of vendor (company) and there are many database products "ASE", "Sql Anywhere", "Advantage Database Server" ... so I do not know how to name them ...) .
Neither do I: Sybase Adaptive Server Enterprise =Sybase ASAE, SQL Anywhere = Sybase ASA. Apparently they all talk the same protocol, so Sybase is fine by me.
Edit: corrected ASA=>ASE hoping not to confuse ppl further
Please see attached zip
Thanks.
You can use for Sybase another approach (may be, that there are other tables in system catalog, which can be used.
I created in UpdateIndexDef separate if ... then so replace SQL.Text there
Yep, i'm actually using another system catalog table (sysindexes). I'll replace the stuff..
Thanks for info, but download file size is too big for me :(
No problem :)
-
... but there are still errors - I'd have to check if they occur in the actual select statement or the one above (which runs fine in a query tool)
Simply remove
' ;';
after:
'si.id = object_id('''+TableName+''')'+
-
>:(
::)
Would that be it? Seems too stupid to be true - then again, that wouldn't be a surprise considering my coding skills :D
I'll get back to you - doing other things right now - and I'll merge my stuff in your files.
Must say I'm not that happy with
if Ftds = DBTDS_50 then //Sybasein UpdateIndexDefs, seems a bit hackish (no offence intended, I know my code is a lot worse).
Can't we specify the specific SQL to be run in the TMSSQLConnectionDef/TSybaseConnectionDef or maybe override the entire procedure?
-
Must say I'm not that happy with
if Ftds = DBTDS_50 then //Sybasein UpdateIndexDefs, seems a bit hackish (no offence intended, I know my code is a lot worse).
Can't we specify the specific SQL to be run in the TMSSQLConnectionDef/TSybaseConnectionDef or maybe override the entire procedure?
May be, that there are also other ways, but why complicate things, when we can do it relative easy
I did some changes, I remove things, which are IMO not needed.
So please check with attached version.
-
Hi all,
Thanks for the update, makes it clearer!
The latest code ran worse on Sybase; the connection "flavour" was not set, or the TDS version (those were present in my earlier version).
I updated dblib.pp to include constants for the connection flavour:
//Used in defining which database mode/dialect to use when opening connection:
SYBDBLIB = 0;
MSDBLIB = 1;
... and added a variable in the main class (FDBLibrary). Then set this in the respective connection creators. I then use tdsdbopen instead of dbopen in DoInternalConnect so the relevant flavour is specified.
The advantage is also that the indexkeys procedure can now use the FDBLibrary variable instead of depending on the TDS version, which is a little cleaner...
Likewise I set FTDS to the required value for each version in the relevant constructor (DBVER60 or DB_TDS_50). FTDS is now used to set the requested TDS level instead of the previously hardcoded DBVER60.
And Lacak, you were right, seems like Sybase doesn't want to run statement ended by semicolons. Stupid but we'll have to live with it...
Moved my test programs to a subdirectory tests and converted them to Lazarus.
SQL Server test still seems to work; Sybase is a little further along; I just thought I'd upload the changes in case someone wanted to work on it.
-
I updated dblib.pp to include constants for the connection flavour:
//Used in defining which database mode/dialect to use when opening connection:
SYBDBLIB = 0;
MSDBLIB = 1;
... and added a variable in the main class (FDBLibrary). Then set this in the respective connection creators. I then use tdsdbopen instead of dbopen in DoInternalConnect so the relevant flavour is specified.
IMO meaning of these constants is different as you expect. IMO they affect only how client library behaves.
The advantage is also that the indexkeys procedure can now use the FDBLibrary variable instead of depending on the TDS version, which is a little cleaner...
We can add private
function IsSybase: boolean;
begin
Result := Ftds=DBTDS_50;
end;
and use it in tests if you think, that it is more clear :)
Likewise I set FTDS to the required value for each version in the relevant constructor (DBVER60 or DB_TDS_50). FTDS is now used to set the requested TDS level instead of the previously hardcoded DBVER60.
1. You can not mix DBVERSION_* and DBTDS_* it is wrong. (you can/must use DBVERSION_100 instead of DBTDS_50)
2. dbsetlversion (expects DBVERSION_*) does not support DBVERSION_100, so step 1 will have no effect. You MUST use only freetds.conf to set required TDS version
SQL Server test still seems to work; Sybase is a little further along; I just thought I'd upload the changes in case someone wanted to work on it.
Please test attached version. I did small change to better support Sybase. (I still remove parts which IMO are not required)
What is with UpdateIndexDefs does it work now ? What if index is build from more than one column ? (it seems to me, that only 1st column is returned, which is wrong!)
-
IMO meaning of these constants is different as you expect. IMO they affect only how client library behaves.
We're talking here about using client libraries, right? (I'm probably missing something obvious, again).
Still, you're right, it works without specifying this.
We can add private
function IsSybase: boolean;
begin
Result := Ftds=DBTDS_50;
end;
and use it in tests if you think, that it is more clear :)
Ummmh yes, but isn't DBTDS_50 just some TDS version?
My FreeTDS log for Sybase shows:
token.c:373:server reports TDS version 4.2.0.0
Which probably is not DBTDS_50...
I have another solution below which relies on the connection code explicitly having some kind of variable without relying on a TDS version...
1. You can not mix DBVERSION_* and DBTDS_* it is wrong. (you can/must use DBVERSION_100 instead of DBTDS_50)
You're right, sorry.
2. dbsetlversion (expects DBVERSION_*) does not support DBVERSION_100, so step 1 will have no effect. You MUST use only freetds.conf to set required TDS version
Yep, the code seems to reject DBVERSION_100; setting it to DBVER42 would make sense as the other options are TDS version 6 and further, useless for Sybase.
So I did that.
Please test attached version. I did small change to better support Sybase. (I still remove parts which IMO are not required)
What is with UpdateIndexDefs does it work now ? What if index is build from more than one column ? (it seems to me, that only 1st column is returned, which is wrong!)
Doesn't work because Sybase apparently is not detected, therefore tries the SQL Server indexkeys stuff:
select name from master..syslogins
An unhandled exception occurred at $0042C523 :
sysindexkeys not found. Specify owner.objectname or use sp_help to check whether
the object exists (sp_help may produce lots of output).
I've changed your code to add a servertype property which is used when doing the indexkeys stuff. I still think it would be cleaner to override the entire procedure within the Sybase specific code if possible.
Hope you can live with that ;)
Code now bombs with following error:
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
I'm willing to investigate further once we have a version that works for both of us :)
Attached my changes as well as the freetds log.
-
Hm, you used for Sybase DBVERSION_42 which is very old.
(so I do not think, that force this version is good idea ... may be, that it works, but with limitations (for example does not support all datatypes etc) and support of this very old version by client libraries and servers may be poor ... so rely on this very old version may lead to problems!)
My intention was not use hardcoded OLD version, but leave it up to users set "tds version=5.0" using freetds.conf
TDS 5.0 is only meaningful version for current Sybase SQL Servers.
Can you test my last sources (from my previous post) without hardcoded DBVERSION_42 and use freetds.conf to set tds version = 5.0 ?
If it will not work (but I expect, that it will), then use this attached sources. (I still want avoid using FServerType ... :D I hope, that tds version is good enought to distinguish between Sybase and Microsoft)
-
Lacak2, I don't really understand why you want to avoid using servertype.
Sybase and MSSQL speak different dialects of the same language. If you don't know in advance if you're going to connect to Sybase or MSSQL there will be other gotchas (different stored procedure names, functionality, etc).
So yes, simple SQL stuff might work, but anything more difficult will fail.
I'll send PM to you (and Ludo) with a proposal to streamline development :).
-
Can you test my last sources (from my previous post) without hardcoded DBVERSION_42 and use freetds.conf to set tds version = 5.0 ?
Works; I used a TDSVER environment variable set to 5.0 though, was easier.
Otherwise I had to set environment variable FREETDS to set the location of freetds.conf (it was looking in c:\freetds.conf by default, seems)
http://www.freetds.org/userguide/envvar.htm (http://www.freetds.org/userguide/envvar.htm)
Still think it's a bit inconvenient to require the user/developer to set environment variables/freetds.conf when connecting to Sybase; we could just use TDS 4.2 in the code and allow the user to override that with 5.0, or better yet, set the variable ourselves in the connection code.
But at least this part works, so I'll stop complaining & we can focus on getting actual functionality :D
-
Lacak2, I don't really understand why you want to avoid using servertype.
:) hard to explain. I feel it so. IMO it follows freetds logic, where there is no option to set if we connect to MS or Sybase. We set only "tds version" and it is sufficient to freetds talk to SQL Server
Sybase and MSSQL speak different dialects of the same language. If you don't know in advance if you're going to connect to Sybase or MSSQL there will be other gotchas (different stored procedure names, functionality, etc).
So yes, simple SQL stuff might work, but anything more difficult will fail.
True, but task of sqlconnector is only send SQL commands and get back results. So sqlconnector must not care about SQL dialect (with only exception in UpdateIndexDefs)
I'll send PM to you (and Ludo) with a proposal to streamline development :).
Thanks I am able connect.
Ludo if you are also working on it, please let me know, to avoid duplicate work
-
Still think it's a bit inconvenient to require the user/developer to set environment variables/freetds.conf when connecting to Sybase; we could just use TDS 4.2 in the code and allow the user to override that with 5.0, or better yet, set the variable ourselves in the connection code.
But setting in code (using dbsetlversion()) overrides setting in freetds.conf, so if we set TDS 4.2 in our connector, users will not be able to set it to 5.0
If you use windows I have updated dblib.dll, where is DBVERSION_100 handled correctly in dbsetlversion()
-
But setting in code (using dbsetlversion()) overrides setting in freetds.conf, so if we set TDS 4.2 in our connector, users will not be able to set it to 5.0
Ok. We could set it to 5.0 using environment variables for Sybase within the connector code, right? That should override freetds.conf etc. but that is fine because 5.0 is the latest supported for Sybase.
(We could even check if the variable is set to a lower value and if so, leave it as-is)
At the same time I don't really understand this part of the code:
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??
If you use windows I have updated dblib.dll, where is DBVERSION_100 handled correctly in dbsetlversion()
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?)
Why don't you do what you think is best - in the worst case I can always set the TDSVER environment variable from within my client Pascal code, if I understand the system correctly...
-
At the same time I don't really understand this part of the code:
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.
-
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 :D
-
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!
-
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.
-
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...
-
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 (http://www.petersap.nl/SybaseWiki/index.php?title=Version_15_client_compatibility)
-
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....
-
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):
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:
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.
-
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:
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...
-
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:
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:
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:
"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 :D
Thanks!
-
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.
-
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:
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/
-
Thanks, I've managed to compile dblib.dll including iconv.dll; it seems to work.
Please attach readme.txt ;)
-
Then you must have different code than mine?!?! I downloaded yours and still got this:
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 :(
-
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 :D
Attached to this post.....
-
A few minor improvements:
-$(ProjectDir) can contain spaces. Replaced $(ProjectDir)\tds_Release\libTDS.lib with .\tds_Release\libTDS.lib. That corresponds with the Output Directory setting of libTDS.
- replaced ..\..\dblib.dll with .\dbdll_Release\dblib.dll to have the dll appear in the standard VC output directory. ..\.. is the freetds source root.
-
Ludo your readme.txt is identical to my original ?
Please attach modified version. :)
-
Wrong directory :(
I don't have vc2010. Tested with 2008 but I think the same modifications would apply to 2010. @BigChimp could you verify this?
-
select name from master..syslogins
select name from master..syslogins
for me works without exception.
Of course indexes are not returned in case of cross database queries :(
Mmmmm.... only now do I see what you mean. Can I raise a bug report :D Maybe we can do something with the function for getting an object ID or something that includes the db reference... I'll think about it.
After all, if JDBC query tools can do it, so should we :D
@Ludo, going to have a look.
-
Wrong directory :(
I don't have vc2010. Tested with 2008 but I think the same modifications would apply to 2010. @BigChimp could you verify this?
Seems the various compilers are not so different... :D
There is one level higher in a solution properties form, namely Configuration Properties. I've added that to the readme.
Unified instructions for VC2005...2010.
In 2.2: Configuration Manager => Build / Configuration manager for clarity. Suspect it's the same name in 2005 and 2008, if not you can remove it again.
In 2.2: is there a difference between $(ProjectDir)\tds_Release\libTDS.lib and .\tds_Release\libTDS.lib? Either seems to work so I don't care which one is used...
Attached new version.
-
n 2.2: is there a difference between $(ProjectDir)\tds_Release\libTDS.lib and .\tds_Release\libTDS.lib?
No, They are the same in this case. But the problem is that the $(ProjectDir) path can contain spaces (fe. c:\Documents and settings\User\My Projects) and then the path needs to be quoted to avoid linking errors (c:\documents.obj not found)
-
PICNIC once again :)
Understood.
-
Here is my current development version.
I am now playing with fcl-db test suite.
Some bugs fixed, some remains.
So you can test also.
-
One more correction in detecting IDENTITY columns
-
First: I've updated the test program to:
- run on both MS SQL Server and Sybase
- take parameter options so you don't need to type until your fingers bleed
- includes limited SQL console functionality: running queries, executing sps
select name from master..syslogins
for me works without exception.
Of course indexes are not returned in case of cross database queries :(
I've run the test program, and executed some queries in it (attached output in zip with test program).
Results on Sybase:
- select name from master..syslogins doesn't seem to work for me, or select <singlecolumn> from <localdatabasetable>
Coding error in test program?
- select * from master..syslogins works now, same for other cross-database queries. Nice!
-
Coding error in test program?
Yes, see at:
Trim(FQuery.Fields[0].AsString) , ',' ,
Trim(FQuery.Fields[1].AsString) , ',' ,
Trim(FQuery.Fields[2].AsString)
When you select only 1 column, there are no Fields[1] and Fields[2] ... so this causes exception
(use loop and for example Fields.Count or FieldsCount)
select name from master..syslogins
for me works without exception.
Of course indexes are not returned in case of cross database queries
I tested also EXEC sp_helpindex 'master..syslogins' and also it returns, that "Object must be in current database"
-
Coding error in test program?
Yes, see at:
Trim(FQuery.Fields[0].AsString) , ',' ,
Trim(FQuery.Fields[1].AsString) , ',' ,
Trim(FQuery.Fields[2].AsString)
When you select only 1 column, there are no Fields[1] and Fields[2] ... so this causes exception
(use loop and for example Fields.Count or FieldsCount)
Of course.
Thanks Lacak, seems I'm getting dumber and dumber. I should have just gone through the code, it stands out like a sore thumb.
Think it's time for a coding break now... so I'll just fiddle with my own stuff instead of bothering you :D
I tested also EXEC sp_helpindex 'master..syslogins' and also it returns, that "Object must be in current database"
Yep, same as in my JDBC query GUI.
Maybe switch to right database with "go <whatever db>" then execute the sp & back to current database with "go <currentdb"
... but the problem is probably to find out the right db?? Don't know if the tablename in
procedure UpdateIndexDefs(IndexDefs:TIndexDefs; TableName:string); override;
... or some other variable contains the db name as well!?!
Once we have the db name, we could of course also prefix the tables:
'select i.name, i.indid,' +
'index_col(object_name(i.id),i.indid,c.colid) as col_name,' +
'(i.status & 2)/2 as IsUnique,' +
'(i.status & 2048)/2048 as IsPrimaryKey ' +
'from ' + dbname + '.' + ownername +'.sysindexes i '+
' join ' + dbname + '.' + ownername +'.syscolumns c on c.id=i.id and c.colid<=i.keycnt-case i.indid when 1 then 0 else 1 end ' +
'where i.id=object_id(''%s'') '+
' and i.indid between 1 and 254 '+ // indid 0 is the table name, 255 is TEXT,IMAGE
'order by i.indid, c.colid'
or something...
But I'll just stop here, I'm afraid I'm starting to make less & less sense.
-
Coding error in test program?
Yes, [...]
Updated the program. I'm using a mercurial repository, so newest source can (at least while playing with it) be downloaded via:
https://bitbucket.org/reiniero/fpc_laz_patch_playground/src (https://bitbucket.org/reiniero/fpc_laz_patch_playground/src), SQL folder.
Thanks for the help!
-
I tested also EXEC sp_helpindex 'master..syslogins' and also it returns, that "Object must be in current database"
Yep, same as in my JDBC query GUI.
Maybe switch to right database with "go <whatever db>" then execute the sp & back to current database with "go <currentdb"
... but the problem is probably to find out the right db??
EXEC master..sp_helpindex 'master..syslogins' seems to work from all databases.
-
I tested also EXEC sp_helpindex 'master..syslogins' and also it returns, that "Object must be in current database"
Yep, same as in my JDBC query GUI.
Maybe switch to right database with "go <whatever db>" then execute the sp & back to current database with "go <currentdb"
... but the problem is probably to find out the right db??
EXEC master..sp_helpindex 'master..syslogins' seems to work from all databases.
May be, but it will require parse TableName, check if there is also databasename (+username) and then use it as prefix either in "sys" table names or "sp_helpindex stored procedure".
I am sure, that it is doable, but IMO in real life very rare used (if user want open table from other database and make it automatic updatable ... based on primary key)
I think, that we can also this postpone for further real user feedback ... what do you think ? It is worth to play with it ? IMO Also other connectors does not support such complexity.
-
IMO Also other connectors does not support such complexity.
That is indeed one of the weak points in sql-db: poor catalog and schema support (I'm using odbc terminolgy).
-
I did basic testing with fcl-db test suite. Results seems good.
See attached file.
-
Very basic testing with my command line program. :D
Works.
-
I did basic testing with fcl-db test suite. Results seems good.
See attached file.
I'd like to spell things out a bit more :D
Could you apply this patch, please? (attached)
diff -r 89c1009d05a2 -r 1b1450a27a8f SQL/mssqlconn.pp
--- a/SQL/mssqlconn.pp Wed Nov 16 11:34:22 2011 +0100
+++ b/SQL/mssqlconn.pp Wed Nov 16 10:36:54 2011 +0100
@@ -311,12 +311,7 @@
FDBLogin:=dblogin();
if FDBLogin=nil then DatabaseError('dblogin() failed!');
- // DBVERSION_100 is ATM not implemented by FreeTDS 0.91;
- // set environment variable TDSVER to 5.0:
- // - Windows: SET TDSVER=5.0
- // - Unix/Linux: TDSVER=5.0
- // or
- // freetds.conf: include "tds version=5.0"
+ // DBVERSION_100 is ATM not implemented by FreeTDS 0.91; use env.var. TDSVER or freetds.conf to set "tds version=5.0"}
dbsetlversion(FDBLogin, DBVERSION[IsSybase]);
if UserName = '' then
-
Could you apply this patch, please? (attached)
I guess, that you want do reverse e.g. add what is "-" and remove what is "+"
I added your comments, with minor formating changes
May be, that also some comments about Sybase in the begining of file would be useful ?
I am thinking about adding support of some connection specific settings like:
ANSI_PADDING, ANSI_WARNINGS, ANSI_DEFAULTS
using TMSSQLConnection.Params property. So users can put there for example:
ANSI_PADDING=ON which will execute in DoInternalConnect : "SET ANSI_PADDING ON"
but I am not sure if they are realy useful (Same can be do by using TMSSQLConnection.ExecuteDirect('SET ANSI_PADDING ON')) What do you think ?
-
Re reversal: yep, sorry. Seems my mercurial tool diff generator can go backwards in time as well as forwards, which I had assumed...
I think exposing these options as connection parameters is a great idea as it gives more transparency/clarity to the user on what mode is actually used.
As far as I gather, these options have an effect on how a user should format his SQL, so this is very relevant info.
I'd suggest adding them to the connection params and setting them to the defaults of the relevant db...
Naughty suggestion: you could also have a TDS version in the db params with choices auto, 4.2, 5.0, 7... Select auto by default.
This can let you change the TDSVER environment variable within the program so users don't have to :D )
If auto selected:
- leave code as is for SQL server
- set TDSVER environment variable to 5.0 before connecting if on Sybase. That should elminate the need for a patched dblib.
If non-auto value selected: set it at beginning of code.
If you decide to implement stuff in params, I could have a look at implementing something like that afterwards and you can (dis)approve it... What do you think?
-
Downside of params is that they have to be well documented. Otherwise you have to look in the code to figure out what params key value pairs are implemented and how. Using TMSSQLConnection.ExecuteDirect doesn't require documentation, other than the DB docs that is ;)
-
Upside is that - if there are defaults for the params that you can see - you can see what's going on without having to go through the mssqlconn.pp dblib.pp and possibly freetds code to see what the defaults are.
Would it be easier/clearer to create actual properties for the connection object, such as done in e.g. Firebird (from the IBConnection code):
const
DEFDIALECT = 3;
private
FDialect : integer;
function GetDialect: integer;
published
property Dialect : integer read GetDialect write FDialect stored IsDialectStored default DEFDIALECT;
... such a property will also show up nicely in a Lazarus object inspector...
-
Downside of params is that they have to be well documented. Otherwise you have to look in the code to figure out what params key value pairs are implemented and how. Using TMSSQLConnection.ExecuteDirect doesn't require documentation, other than the DB docs that is ;)
Yes, I am aware of that ... and this is reason, why I have doubts and why I asked for your opinion.
I think, that we can leave it as is for now
-
Naughty suggestion: you could also have a TDS version in the db params with choices auto, 4.2, 5.0, 7... Select auto by default.
This can let you change the TDSVER environment variable within the program so users don't have to :D )
I was also thinking about that. (specially in the future if there will be supported also TDS 7.2, 7.3 etc. by FreeTDS db-lib)
Only drawback is that, overwriting TDSVER (if exists) is IMO not very good solution. It is up to user to have full control over environment.
From my point of view, I think, that there is no big community of FreePascal <-> Sybase users, so they can live with explicitly preset TDSVER (until there is no support of pre-setting TDS 5.0 in db-lib)
-
Naughty suggestion: you could also have a TDS version in the db params with choices auto, 4.2, 5.0, 7... Select auto by default.
This can let you change the TDSVER environment variable within the program so users don't have to :D )
I was also thinking about that. (specially in the future if there will be supported also TDS 7.2, 7.3 etc. by FreeTDS db-lib)
Only drawback is that, overwriting TDSVER (if exists) is IMO not very good solution. It is up to user to have full control over environment.
Totally agreed fiddling with variables can be problem. We can test in the code if the environment variable is already set. If so, just don't change the variable. Result: programmer can specify TDS version in code/property; system admin can override with explicit TDS version environment variable.
-
Would it be easier/clearer to create actual properties for the connection object, such as done in e.g. Firebird
Simple answer - Yes it will be clearer :D
But I have fear, that we will end with "tons" of rare used properies.
"Advantage" of Params is, that we can add unlimited number of options:
Option1=Value1
Option2=Value2
etc.
Some of such options may be "expert only" options, so it is good if they are not so visible to common users :)
Also other connectors use this "approach" PQconnection, MySQLConnection (so they do not publish standalone properties)
-
... whichever you like best. The advantage of properties I like is that you can set defaults. If people rarely use them and may screw up their systems, so what :D They can do that with params as well...
And yes, you can have a lot of properties. So does a visual control on a form...
Params are more difficult because you have to go though documentation to find out what they should be, have chances for typos etc while properties are more self-documenting.
Maybe the mysql and postgresql conection implementers were just a bit lazy :D
But of course you knew I was going to say something like that :) Whichever you're comfortable with, I'm happy with.
-
After 10-15 minutes of inactivity I'm getting a time-out on springbok. "write to the server failed" is returned by the server. The freetds log doesn't provide more info. This happens on both windows and linux clients. A local network sql server doesn't have these timeouts. Is there something special about the springbok configuration?
-
Not that I'm aware of... maybe it's a default setting on Sybase ASE (or perhaps it's my firewall that drops the connection> ?
I'll open up my test program and let it idle for a while then see if I see the same locally. I'll also have a look at the firewall logs..
I can send you the notes I took during setup if you want to...
<edited: firewall>
-
I searched for sybase session timeouts but other than the tcp keepalive timeout, which is 2 hours per default, I didn't find anything. I traced the output of the GUI that I use (based on jconnect) : it sends a "select getdate()" query about every 400 secs. This is, considering the order of magnitude, just below the 10-15 minutes timeout I'm experiencing. I also noticed that I'm not receiving TCP Keep-Alive packages from springbok. That could be a router configuration thing anywhere between client and server.
-
Local connection (test program using mssqlconn/freetds) is fine after about 20 minutes.
So it is probably a network thing, yes.
-
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:
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*
How did you open FreeTDS.dsw in VC++ 2008/2010 (Express or not ?) ?
When I try open /win32/msvc6/FreeTDS.dsw (like stated above in readme.txt) in VS2008 then I get error "Project file ... has been corrupted and cannot be opened", when in VC++ 2010 Express I get error "Project upgrade failed" (it seems be a known issue: http://connect.microsoft.com/VisualStudio/feedback/details/595054/visual-c-2010-express-cannot-open-visual-c-6-0-projects )
Of course in VC++ 2005 Express it can be opened :)
-
<<edited>>
Freetds stable source.
Regular file open (via explorer)/VS2010Pro Service pack 1. Then it goes through the upgrade process.
I'll have a look again & I could post the .sln file (and whatever else it generates, if it's reasonable)
<<edit: mmm, posting the sln file won't help, 2010 has a new format>>
-
Opening FreeTDS.dsw from explorer with VS2008Pro -> prompt to convert project to the new format. No problems encountered.
-
Thanks, then problem must be related to Express versions, which I have used.
In case of 2010 it is known problem, which seems be fixed by SP1 in VS2010
In case of 2008 I do not known.
But now I have downloaded VC++ 2008 Express SP1 and all is ok (so I guess, that it was fixed in SP1)
I expect same story with VS2010 (there is also SP1 available)
-
Hi where can i find the latest sources of the TMSSQLConnection ?
-
Hi,
LacaK, good good job!
I'm very impressed with this project and how it progressed!
I should make some performance tests to LacaK, but I didn't do. Very sorry because that. :'(
Well, how I can help now? :-\
Do you have a SVN to get the improvements?
Thanks for share.
-
Hi where can i find the latest sources of the TMSSQLConnection ?
Attached ZIP (GNU libiconv2.dll is required see readme.txt for download location)
If there will be no big objections, I plan post RC2 also into bug report
-
LacaK, good good job!
I'm very impressed with this project and how it progressed!
Also thanks to others like Reinier and Ludo
I should make some performance tests to LacaK, but I didn't do. Very sorry because that. :'(
It is OK, IMO most of performance critical code is in FreeTDS (and of course in SQL Server), which we do not develop. So we have only little chance to affect overal performance on freepascal side
Do you have a SVN to get the improvements?
No, until sources will not be merged into standard freepascal fcl-db SVN ... you can send diffs to me and I will do merging manualy (If I will agree with changes :D )
-
If there will be no big objections, I plan post RC2 also into bug report
No objections at all!
Hope it gets included into fcl-db; seems like a good way to work with SQL Server/Sybase...
-
Hi
i get EDatabaseError 'transaction not set' if i open, close and reopen the connection.
Used a TSQLConnector, TSQLTransaction, TSQLQuery and a TButton. The TButton OnClick event have just
SQLConnector1.Connected := not SQLConnector1.Connected
Debian Squeeze amd64
FreeTDS 0.82-7
Lazarus rev. 33816 from branches/fixes_0_9_30 rev. 33816
Free Pascal from branches/fixes_2_6 rev. 19693
MS SQL Server 2005 i386
-
i get EDatabaseError 'transaction not set' if i open, close and reopen the connection.
IMO it is now fixed in trunk http://bugs.freepascal.org/view.php?id=20694
-
Hi where can i find the latest sources of the TMSSQLConnection ?
Attached ZIP (GNU libiconv2.dll is required see readme.txt for download location)
If there will be no big objections, I plan post RC2 also into bug report
I tested that new version and everything worked. :D
-
I think I found a possible bug:
If you call StartTransaction, the DataSets on the Form are closed.
The connection still open, but you should Close/Open the DataSets to use them.
-
DataSets are closed when you use Commit/Rollback (not CommitRetaining/RollbackRetaining)
May be, that it is your case ?
-
I call Commit before StartTransaction (because the "SQLdb design" is so).
See attachment, please. I'm using the last version from dblib.dll and libiconv2.dll posted by you.
-
Yes, when I press button 4-Transaction : dataset is closed.
But as I wrote this is task performed by sqlDB - TSQLTransaction.Commit calls CloseDataSets (see in sqldb.pp)
So this behavior I can not alter in TMSSQLConnection.
You can use instead of:
FTran.Commit; // Commit before transaction... design horrible!
FTran.StartTransaction;
FTran.CommitRetaining;
-
Yes, I saw in sqldb.pp. I do not understood why that, but... %)
if I change this:
FTran.Commit;
FTran.StartTransaction;
For this:
FTran.CommitRetaining;
So, when can I call StartTransaction? I mean, a real begin tran - commit/rollback
EDIT:
Ok, I saw:
procedure TMSSQLConnection.CommitRetaining(trans: TSQLHandle);
begin
if Commit(trans) then
DBExecute(SBeginTransaction);
end;
But we will always keep the transaction open all the time?!
-
Yes, I saw in sqldb.pp. I do not understood why that, but... %)
Hard to answer. I am not designer of sqlDB.
But AFAIK sql standard defines, that COMMIT also closes all open cursors (associated with current transaction).
So you can imagine, that sqlDB performs some similar ;-)
But we will always keep the transaction open all the time?!
Yes it seems so. If you want for example see data in DBgrid then you must have active transaction, because if DBgrid shows data from TSQLQuery, which must be associated with any transaction object. If you close transaction then all associated datasets are also closed.
May be, that this "general feature" can be changed ... but this is another question (you can try ask it in fpc-users or devel mailing list).
-
But we will always keep the transaction open all the time?!
Yes it seems so. If you want for example see data in DBgrid then you must have active transaction, because if DBgrid shows data from TSQLQuery, which must be associated with any transaction object. If you close transaction then all associated datasets are also closed.
May be, that this "general feature" can be changed ... but this is another question (you can try ask it in fpc-users or devel mailing list).
[/quote]
I asked them here:
http://lists.freepascal.org/lists/fpc-pascal/2011-November/031276.html
http://lists.freepascal.org/lists/fpc-pascal/2011-December/031280.html
You can be right, using BEGIN TRAN all the time can work but...
I did more tests using TMSSQLConnection vs ADO (Delphi 7) using SQL Server Profiler 2008:
I did:
1. CONNECT
2. Execute a SELECT;
3. Start a transaction;
4. UPDATE;
5. ROLLBACK;
6. Start a transaction;
7. UPDATE again;
8. COMMIT;
ADO trace:
SET NO_BROWSETABLE ON
go
select * from do
go
set implicit_transactions on SET NO_BROWSETABLE OFF
go
update do set cd_UF= 'TA' where id_DO=1
go
IF @@TRANCOUNT > 0 ROLLBACK TRAN
go
SET NO_BROWSETABLE ON
go
SET FMTONLY ON update do set cd_UF= 'TA' where id_DO=1
SET FMTONLY OFF
go
SET NO_BROWSETABLE OFF
go
update do set cd_UF= 'TA' where id_DO=1
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
Conclusion(?)
As you can see, using ADO, the transactions are called using [implicit_transactions on] and SELECTs are called using [SET NO_BROWSETABLE ON].
I don't know if has some difference, but they have different commands ;-)
-
Marcos, yes, apparently the way ADO and SQLDB handle transactions are different :D
I think it might make more sense to:
1. Compare TMSSQLConnection with a server accessed through SQLDB's ODBC connector. If TMSSQLConnection fails where the ODBC connection works, that's a problem with TMSSQLConnection
2. Compare Delphi ADO with FPC SQLDB in general. Apparently ADO issues implicit transaction. In SQLDB you need to explicitly set a transaction object and deal with it, but you can use CommitRetaining/RollbackRetaining.
Unless there's a huge performance or functionality difference, I'd just accept that the two components use different levels of abstraction.
Of course, the documentation of the SQLDB components could be improved a lot...
-
BigChimp,
I'm not considering performance yet, but I will compare...
For now, I only want work with transactions but I still can not.
One question: The AUTOCOMMIT parameter should not be set to Transaction too?
I'm trying to use only one connection with 2 transactions: one to show data and another to commit/rollback. The problem is: the component ALWAYS starts a transaction and does not close. At the end of the application, I have an exception because the "ROLLBACK not correspond... bla bla bla".
How do you works using RAD (DBware) + SQLdb + transactions + ONLY one connection?
-
(...) you can use CommitRetaining/RollbackRetaining.
I understand now. Use CommitRetaining is the same that:
Eg.:
Query.Open;
Trans.CommitRetaining;
[...]
Query.ExecSQL;
Trans.CommitRetaining;
For each excetution... call the Trans.CommitRetaining! Is it right?
-
I already replied on the mailing list but I'll repeat it here briefly:
- transactions don't cost anything and can stay open as long as you want as long as you don't require repeatable read or higher isolation. SQLServer uses per default autocommit and suspend this with an explicit BEGIN TRANSACTION. All other databases start an implicit transaction with the first executable statement (SELECT, ...). This is also specified in the SQL92/99 standard.
- sql-db matches the standard
- ADO matches ... MS
- if you have still cold feet regarding transactions, split your datasets over different SQLConnections with their own SQLTransactions and set autocommit=true for those that you don't want inside a transaction.
-
I already replied on the mailing list but I'll repeat it here briefly:
- transactions don't cost anything and can stay open as long as you want as long as you don't require repeatable read or higher isolation. SQLServer uses per default autocommit and suspend this with an explicit BEGIN TRANSACTION. All other databases start an implicit transaction with the first executable statement (SELECT, ...). This is also specified in the SQL92/99 standard.
- sql-db matches the standard
- ADO matches ... MS
You're right, I was learning.
- if you have still cold feet regarding transactions, split your datasets over different SQLConnections with their own SQLTransactions and set autocommit=true for those that you don't want inside a transaction.
Here we have a bug because the Conn.StartTransaction ignores the Transaction's Params, see:
function TMSSQLConnection.StartDBTransaction(trans: TSQLHandle; AParams: string): boolean;
begin
Result := not AutoCommit;
if Result then
DBExecute(SBeginTransaction);
end;
Because that I can't use 2 transaction: 1º default (autocommit); 2º begintrans/commit/rollback manually.
See TIBConnection.StartDBTransaction...
-
Here we have a bug because the Conn.StartTransaction ignores the Transaction's Params, see:
Result := not AutoCommit;
if Result then
DBExecute(SBeginTransaction);
AutoCommit tests the connection params and returns true if AutoCommit=true. You are confusing with transaction Params.
Because that I can't use 2 transaction: 1º default (autocommit); 2º begintrans/commit/rollback manually.
That is what I explained in the mail. When AutoCommit=true, explicit transactions don't work. The DBExecute(SBeginTransaction) is never executed. You can set AutoCommit=false, start transaction, AutoCommit=true, make your changes, commitretaining. Note that I haven't tested this but looking at the code, this should be a working hack.
As said in the mailing list, for mssql, we'll need probably an easier way of doing this.
I'll propose a patch.
-
Here we have a bug because the Conn.StartTransaction ignores the Transaction's Params, see:
Result := not AutoCommit;
if Result then
DBExecute(SBeginTransaction);
AutoCommit tests the connection params and returns true if AutoCommit=true. You are confusing with transaction Params.
Yes, for the default parameters. But, AFAIK I could modify using Transaction.Params (did you see the TIBConnection.StartDBTransaction implementation?)
Because that I can't use 2 transaction: 1º default (autocommit); 2º begintrans/commit/rollback manually.
That is what I explained in the mail. When AutoCommit=true, explicit transactions don't work. The DBExecute(SBeginTransaction) is never executed. You can set AutoCommit=false, start transaction, AutoCommit=true, make your changes, commitretaining. Note that I haven't tested this but looking at the code, this should be a working hack.
As said in the mailing list, for mssql, we'll need probably an easier way of doing this.
I'll propose a patch.
If we use aParams (StartDBTransaction) we can do this very easy. Again, see TIBConnection...
I can't modify Connection.Params (a global variable) many times. This is not thread-safe. IMHO, should be for each Transaction.
-
Yes, for the default parameters. But, AFAIK I could modify using Transaction.Params (did you see the TIBConnection.StartDBTransaction implementation?)
The Autocommit behavior in SQLserver is defined on the connection level, not on the transaction level. See the doc for SET IMPLICIT_TRANSACTIONS.
Interbase can set most of the transaction parameters on a per transaction level and the api does support this with isc_start_transaction. SQL Server defines most on the connection level. For example, see the doc for SET TRANSACTION ISOLATION LEVEL: only one level can be specified per connection. AFAIK the freetds and db-lib api don't have an equivalent of isc_start_transaction.
If we use aParams (StartDBTransaction) we can do this very easy. Again, see TIBConnection...
I can't modify Connection.Params (a global variable) many times. This is not thread-safe. IMHO, should be for each Transaction.
See above.
Connections are not thread safe. Especially SQLServer that supports only one active result set per connection.
-
So, if SQLServer is so different from Interbase, eg., we do not need to work like Interbase does (like isc_start_transactio option) if we don't have advantages, right? ;)
In that case, we can use the ADO's approach. :D
You know something is strange... as you said: "...for mssql, we'll need probably an easier way of doing this.
I'll propose a patch."
-
You know something is strange... as you said: "...for mssql, we'll need probably an easier way of doing this.
I'll propose a patch."
Meaning: I'll make a patch that I propose to Lacak2, Big Chimp and whoever wishes to comment, for inclusion. This is cooperative development ;) I won't have time tonight, though.
The idea is indeed to have a mode that emulates the ADO behavior: autocommit per default, suspend autocommit when a transaction is started, resume autocommit when commit or commitretaining. No change to commit = close datasets since that is not on the mssql level. But, commit retaining doesn't start a new transaction.
-
You know something is strange... as you said: "...for mssql, we'll need probably an easier way of doing this.
I'll propose a patch."
Meaning: I'll make a patch that I propose to Lacak2, Big Chimp and whoever wishes to comment, for inclusion. This is cooperative development ;) I won't have time tonight, though.
The idea is indeed to have a mode that emulates the ADO behavior: autocommit per default, suspend autocommit when a transaction is started, resume autocommit when commit or commitretaining. No change to commit = close datasets since that is not on the mssql level.
I think is very easy, but this will be in disagreement with the standard SQLdb way. :-[
Well, I resolved my problem. Was easy, becaus I always have a layer (my own Connection, Query, StoredProc components. I have these layers for Zeos, SQLdb, etc) to others tecnologies, so:
1. After Query.Open or Query.ExecSQL, I call ExecCommitRetaining;
2.: protected ExecCommitRetaining
begin
if not FInTransaction then
FTran.CommitRetaining;
end
3. New Transaction:
begin
FInTransaction := True; // just it ;-)
end
4. Commit:
begin
FTran.CommitRetaining;
FInTransation := True;
end
5. Rollback
begin
FTran.RollbackRetaining;
FInTransation := True;
end
Ah, I have counters for nested transactions (simulate), but this is other thing.
But, commit retaining doesn't start a new transaction.
I just do not know how do this... but you said it does not matter!! :o
-
If you are using your own layer why not simply call SQLConnector.ExecuteDirect('BEGIN TRANSACTION'); to start a transaction and SQLConnector.ExecuteDirect('COMMIT); or SQLConnector.ExecuteDirect('ROLLBACK'); instead of using the SQLTransaction.commit, etc. ? Using SQLConnector.Params 'Autocommit=TRUE' and above ExecuteDirect's, your program will behave the same as ADO, without closing your data sets.
-
WT... That is right! :-[
Very much more simple!
I did not have more the problem with datasets closed, as I said before, but the transaction continued open.
Now I can use as ADO, using transactions only if necessary. Thank you! :)
So, anyway, do you will give up to propose the patch to use AUTOCOMMIT more simple that we have today?
-
I've been looking at a patch. The idea was to maintain a transaction nesting level (FTransactionNest) and only send a BEGIN TRANSACTION if not(AutoCommit) or (FTransactionNest>0);
Problem is that sqldb doesn't support nested transactions. It raises an exception when starting a transaction when there is already one active. Cheating and return false in StartDBTransaction when no BEGIN TRANSACTION is sent (as is down now when Autocommit) won't work if multiple queries are attached to the same TSQLTransaction since every TSqlquery.open will check if a transaction is active and start one if not. No way to keep track of the correct transaction nesting level :( So, I'm at a dead end in finding a solution that only affects mssql.pas and is transparent for the user.
The other possible route is to add new methods to TMSSQLConnection: StartExplicitTransaction(name:string), CommitExplicit(name:string) and RollBackExplicit(name:string) that would wrap the SQLConnector.ExecuteDirect('BEGIN TRANSACTION name');, etc. A nesting level counter in these routines would be maintained and an exception raised if the counter drops below zero. Published as property trancount.
A SavePointExplicit(savepoint_name:string) would be another useful add-on. Your opinion?
-
I've been looking at a patch. The idea was to maintain a transaction nesting level (FTransactionNest) and only send a BEGIN TRANSACTION if not(AutoCommit) or (FTransactionNest>0);
Problem is that sqldb doesn't support nested transactions. It raises an exception when starting a transaction when there is already one active. Cheating and return false in StartDBTransaction when no BEGIN TRANSACTION is sent (as is down now when Autocommit) won't work if multiple queries are attached to the same TSQLTransaction since every TSqlquery.open will check if a transaction is active and start one if not. No way to keep track of the correct transaction nesting level :( So, I'm at a dead end in finding a solution that only affects mssql.pas and is transparent for the user.
I think you're on track.
In my own layer, I have a simulate of "transaction nesting" (FTranCount) that I use even on Delphi + ADO. This is not only a "problem" of SQLdb.
Eg:
procedure FooA:
begin
conn.StartTransaction;
[...]
conn.Commit;
end;
procedure FooB:
begin
conn.StartTransaction;
[...]
conn.Commit;
end;
procedure FooExec;
begin
conn.StartTransaction;
FooA;
FooB;
conn.Commit;
end;
FooA and FooB doesn't know if a transaction was started.
My layer only starts the first transaction (first StartTransaction) and sum the others. When I call Commit, I need to see if FTranCount = 1 to call the real Commit, like you said.
This is transparent for the user and, using AutoCommit=True, it is very easy to use for mostly Delphi programmers. ;)
The other possible route is to add new methods to TMSSQLConnection: StartExplicitTransaction(name:string), CommitExplicit(name:string) and RollBackExplicit(name:string) that would wrap the SQLConnector.ExecuteDirect('BEGIN TRANSACTION name');, etc. A nesting level counter in these routines would be maintained and an exception raised if the counter drops below zero. Published as property trancount.
A SavePointExplicit(savepoint_name:string) would be another useful add-on. Your opinion?
IMHO create new methods is not good way. The SQLdb was created to be one generical solution for many DBMS, right? If you put new methods will complicate the use of TSQLConnector, e.g.
-
Or another route is change how sqlDB works ;)
Make it more compatible with Delphi's DB-Express.
I am not expert for DBX, but IMO DBX works like this:
1. transactions are not started implicitly
2. if transaction is not explicitly started then when ApplyUpdates, transaction is started and if no errors occurc during applying updates transaction is commited after applyupdates (something like batch-autocommit mode)
3. if transaction is started explicitly (by calling StartTransaction) then transaction is under control of user (something like explicit transactions) and user must Commit or Rollback
Plus:
- Signal if connection support multiple transactions per connection
- Count transactions (StartTransaction +1, Commit,Rollback -1)
Edit:
But AFAIU DBX does not support "virtual nested" transactions.
Transactions can be nested only if driver support it (property MultipleTransactionsSupported)
-
IMHO create new methods is not good way. The SQLdb was created to be one generical solution for many DBMS, right? If you put new methods will complicate the use of TSQLConnector, e.g.
I agree here.
-
Or another route is change how sqlDB works ;)
Make it more compatible with Delphi's DB-Express.
You're right but I doubt that change will be made by FPC team! :P
I am not expert for DBX, but IMO DBX works like this:
1. transactions are not started implicitly
2. if transaction is not explicitly started then when ApplyUpdates, transaction is started and if no errors occurc during applying updates transaction is commited after applyupdates (something like batch-autocommit mode)
3. if transaction is started explicitly (by calling StartTransaction) then transaction is under control of user (something like explicit transactions) and user must Commit or Rollback
Plus:
- Signal if connection support multiple transactions per connection
- Count transactions (StartTransaction +1, Commit,Rollback -1)
Perfect. 8-)
-
Or another route is change how sqlDB works ;)
Make it more compatible with Delphi's DB-Express.
You're right but I doubt that change will be made by FPC team! :P
Yes I think so also :D
But if there will be good arguments and good proposal ... But personaly I do not want go into this "war"
-
They will talk about backward compatibility... but no cost to try! :D
-
I think you're on track.
In my own layer, I have a simulate of "transaction nesting" (FTranCount) that I use even on Delphi + ADO. This is not only a "problem" of SQLdb.
The difference is that you build a layer on top of sqldb and I'm trying (and fail so far) to find a solution below sqldb (in mssql).
As explained before, there is no way for mssql to make a distinction between an explicit start transaction and one triggered by a TSQLQuery.Open.
Or another route is change how sqlDB works ;)
Make it more compatible with Delphi's DB-Express.
1. transactions are not started implicitly
See a few posts before. That matches better MS but not the SQL standard nor most other databases.
But if there will be good arguments and good proposal ... But personaly I do not want go into this "war"
Me neither ;) IMHO create new methods is not good way. The SQLdb was created to be one generical solution for many DBMS, right? If you put new methods will complicate the use of TSQLConnector, e.g.
I agree here.
There are precedents. A few properties in TODBCConnection, TMySQLxxConnection and method GetInsertID in TSQLite3Connection.
Personally I'm perfectly happy with calling SQLConnector.ExecuteDirect. So, I guess we'll better leave it as is. A working solution is documented in this thread for posterity :D
-
The difference is that you build a layer on top of sqldb and I'm trying (and fail so far) to find a solution below sqldb (in mssql).
As explained before, there is no way for mssql to make a distinction between an explicit start transaction and one triggered by a TSQLQuery.Open.
But we can do the same in SQLdb!
We can have a parameter to work in "SQL standard" or "MSSQL standard". The first is it as is. The second we can implement using the techniques presented here: autocommit is default; counter transactions; etc.
The methods are same; interface same; no backward compatibility problems. Just a different mode to use. Your opinion?
-
The difference is that you build a layer on top of sqldb and I'm trying (and fail so far) to find a solution below sqldb (in mssql).
As explained before, there is no way for mssql to make a distinction between an explicit start transaction and one triggered by a TSQLQuery.Open.
But we can do the same in SQLdb!
We can have a parameter to work in "SQL standard" or "MSSQL standard". The first is it as is. The second we can implement using the techniques presented here: autocommit is default; counter transactions; etc.
The methods are same; interface same; no backward compatibility problems. Just a different mode to use. Your opinion?
I hear you saying. I'm trying to explain that at the mssql layer there is no way to count the number of transactions the user requests. sqldb will begin a transaction as soon as a TSQLQuery is prepared and TSQLTransaction allows only one active transaction.
If we change at the sqldb layer, everything can be done while remaining backward compatible. Unfortunately I don't see a volunteer here to get that sold to the FPC core team. Or are you suggesting we make an sqldb branch...
-
We do not to count the number of real transactions, just simule that! ;D
See the Lacak's comment: "- Count transactions (StartTransaction +1, Commit,Rollback -1)". That I was talked about.
We do not need a SQLdb branch for implement only the TMSSQLConnection. But, if the new features we want to TMSSQLConnection get better than original SQLdb... yes, maybe a branch, why not? 8)
-
We do not to count the number of real transactions, just simule that! ;D
See the Lacak's comment: "- Count transactions (StartTransaction +1, Commit,Rollback -1)". That I was talked about.
Sigh... Read xx posts back where I called that the transaction nesting level. And it is oversimplified. Commit decrements one level but rollback sets to 0, instead of decrementing one level. A rollback rolls back to the first begin transaction (ignoring save points) on SQLServer.
On the TMSSQLConnection level StartTransaction is called as soon as a query is prepared (open). What do you want to simulate with that?
Currently, with autocommit StartDBTransaction returns false to sqldb and the transaction is never set to active. sqldb will continue calling StartTransaction for every open though. What do you want to simulate with that when you can have x SQLQueries for one connection?
-
Calm down, I just want to help. :)
I understood your point. Ok, if we have one only StartTransaction method, that is call by Querys when they open, how can you differentiate one from another call? We didn't (not using only this method) ... you're right, sorry. :-[
So, we can back to your comment...
The other possible route is to add new methods to TMSSQLConnection: StartExplicitTransaction(name:string), CommitExplicit(name:string) and RollBackExplicit(name:string) that would wrap the SQLConnector.ExecuteDirect('BEGIN TRANSACTION name');, etc. A nesting level counter in these routines would be maintained and an exception raised if the counter drops below zero. Published as property trancount.
A SavePointExplicit(savepoint_name:string) would be another useful add-on. Your opinion?
... and implement these new methods only in TMSSQLConnection and, after, propose these new methods to FPC team.
-
... and implement these new methods only in TMSSQLConnection and, after, propose these new methods to FPC team.
In the current state (no TMSSQLConnection component registered) the additional methods would only be accessible for manually created TMSSQLConnection objects and not for the TSQLConnector objects dropped on the form or data module. That is a serious limitation for a (simple) wrapper method.
-
While we're thinking about...
I will talk about other things:
We can do to the component to run more than one SQL instruction in one call?
I'm trying to run:
q.SQL.Add(update t set data=123);
q.SQL.Add(select * from t);
q.Open;The query can't open the last SELECT.
-
We can do to the component to run more than one SQL instruction in one call?
I'm trying to run:
q.SQL.Add(update t set data=123);
q.SQL.Add(select * from t);
q.Open;The query can't open the last SELECT.
I am afraid, that this is also limitation of how sqlDB parses SQL.Text
See in sqldb.pp function TCustomSQLQuery.SQLParser
ATM you must workaround it in your code:
q.SQL.Text:='update t set data=123; update t2 set data2=123';
q.ExecSQL;
q.SQL.Text:='select * from t';
q.Open;
Separate non-select statements (here IMO you can batch more statements into single call to ExecSQL) and select statement
-
If we change at the sqldb layer, everything can be done while remaining backward compatible. Unfortunately I don't see a volunteer here to get that sold to the FPC core team.
It is also my impression.
But if there will be
1. good arguments (may be comparasion to Delphi DBX or Zeos)
2. good simple/smart/clear proposal
3. preasure/discussion in fpc-devel list
then IMO there is chance ;)
Bad thing is, that main fcl-db maintainer Joost is busy ATM ... so all this activity can end with no effect
-
I am afraid, that this is also limitation of how sqlDB parses SQL.Text
See in sqldb.pp function TCustomSQLQuery.SQLParser
Yeah... I saw after send this message.
ATM you must workaround it in your code:
q.SQL.Text:='update t set data=123; update t2 set data2=123';
q.ExecSQL;
q.SQL.Text:='select * from t';
q.Open;
Separate non-select statements (here IMO you can batch more statements into single call to ExecSQL) and select statement
Yep. But the problem is send many statements to the server... but if do not exists a solution, that is OK.
Well, I am migrating one application to TMSSQLConnection. My business objects still using ntwdblib.dll because they have a lot of call of non-select statements. But the new part (GUI to show data) are using the new connector.
It is worthwhile to create our own version of this connector, without using SQLdb?
-
If we change at the sqldb layer, everything can be done while remaining backward compatible. Unfortunately I don't see a volunteer here to get that sold to the FPC core team.
It is also my impression.
But if there will be
1. good arguments (may be comparasion to Delphi DBX or Zeos)
2. good simple/smart/clear proposal
3. preasure/discussion in fpc-devel list
then IMO there is chance ;)
Bad thing is, that main fcl-db maintainer Joost is busy ATM ... so all this activity can end with no effect
It is also going to be very big job. sqldb itself isn't the biggest problem. The weak part, IMHO, is bufdataset: mem leaks, sequential search, mixing of pchar and string, bugs...
-
I didn't know the TBufDataSet have so many problems...
-
And if we had the API TMSSQLConnection being used in another layer, would be good?
I posted about the Pascal FreeTDS wrapper on Zeos' list:
http://zeos.firmos.at/viewtopic.php?p=13883#13883
-
The zeos abstraction layers are quite different from sqldb. Their intermediate layer is very much jdbc inspired. The result is a lot of layers and interfaces.
Zeos has already support for ntwdblib.dll and libsybdb.dll/so. It's probably less work to adapt these implementations to freetds than to adapt TMSSQLConnection to zeos. Zeos also supports stored procedures through the dprpcinit, etc functions which is missing in TMSSQLConnection. Also the metadata support is well developed (jdbc influence).
-
I know that Zeos has already support for ntwdblib.dll (as I said in Zeos' forum).
When I said "API TMSSQLConnection" I mean FreeTDS API used by TMSSQLConnection... sorry my English.
So, would be better if Zeos use the new API (FreeTDS) instead of old ntwdblib.dll. And as you said, it's probably less work to adapt these implementations of FreeTDS vs ntwdblib.dll.
Maybe we can't change the way of SQLdb works so, would be better have one more option of layer, don't?
-
Hi,
we have something new?
-
From me side no news.
Tomorow I will post into bug report RC2 (very similar to last posted here in forum)
-
Ok, thanks.
I will wait.
-
From me side no news.
Tomorow I will post into bug report RC2 (very similar to last posted here in forum)
Hi,
Did you posted?
-
Yes as you can see http://bugs.freepascal.org/view.php?id=17303 (mssqlconn_rc2.zip http://bugs.freepascal.org/file_download.php?file_id=15134&type=bug )
-
Some news about it? Nobody answered on bugtraq.
-
Some news about it? Nobody answered on bugtraq.
:( yes.
We must only wait if somebody of FPC team will (adopt it and ) merge it.
Or if nothing will happens in 2-3 months, then you can write to fpc-devel (or fpc-users) mailling list.
-
I think we can write to fpc-devel or fpc-users) mailling list right now. >:D
-
Hi,
No news about this project?
Somebody talked with FPC core about it?
-
Somebody talked with FPC core about it?
No from me side :) (from my POV source are stable and ready for addition to SVN sqldb/mssql)
We are waiting for you :D
(as I expected and wrote, there will be no progress until there will be significant users demand)
If TMSSQLConnection will be accepted by FPC then next step should be adding it into sqlDB component pallete into Lazarus (picture for component etc.)
-
OK, here is:
http://lists.lazarus.freepascal.org/pipermail/lazarus/2012-February/071120.html
I send a copy to fpc-list too.
-
Good initiative, but I don't see why sending it to the Lazarus list would be relevant... before we have a nice and shiny package with components for the Lazarus tool palette for MS SQL Server and Sybase <hint, hint> ;)
-
For disclosure! :-X
Do you think I must send to fpc-devel too? :)
-
I think people may have gotten the hint by now ;)
-
Seriously now. This connector is great and should be available in SVN since it has nothing similar.
-
Seriously: as I said, I would suggest you write a component for Lazarus and submit it as a feature in Mantis. This will probably increase interest in using it.
In the end, we can talk in this thread about how nice it is, but it is not us who need to be convinced to sacrifice their free time to implement the patch, it's the FPC and Lazarus devs ;)
Of course, it not being in SVN doesn't prevent you from using it...
-
IMO first step is on fpc side. Because fcl-db package is primary maintained by FPC team, not Lazarus team.
Adding to SVN is question of minute or two, so there is no free-time related problem :)
When it will be part of fcl-db then we can talk to Lazarus to add it on sqlDB pallete.
I think, that you shoul post message / question also into fpc-devel mailing list
-
I agree with LacaK.
So, here is the link in devel-list:
http://lists.freepascal.org/lists/fpc-devel/2012-February/028417.html
PS: Sorry my late, I was enjoying the Carnaval here in Rio de Janeiro/Brazil. :D
-
The TMSSQLConnection component was committed in rev 20522! 8-)
-
Thanks for getting it into FPC.
I've created (ugly) Sybase and MS SQL Server connectors for the Lazarus component palette.
See
http://bugs.freepascal.org/view.php?id=21578 (http://bugs.freepascal.org/view.php?id=21578)
-
Heads up: started on fpdoc documentation for Sybase & MS SQL Server connectors.
See https://bitbucket.org/reiniero/fpc_laz_patch_playground/src (https://bitbucket.org/reiniero/fpc_laz_patch_playground/src), directory docs, mssqlconn.xml
When I'm done, I'll upload the result here for review before submitting a patch....
Thanks,
BigChimp
-
First stab (basically copied readme contents).
Attached resulting .chm; source is in repository mentioned earlier.
@Lacak2, Ludo: the readme has this under Known problems:
(create temporary stored procedures for prepared statements)
Can't remember what that one is. Does it mean that preparing a parameterized query won't work? Or won't give any performance benefits?
Next up: writing up the various ways you can connect to sql server: in other words: explain that you can specify instance names in the database field (e.g. .\SQLEXPRESS, IIRC - can't remember if that works with freetds)... and remembering/looking up where the port number goes.
Also, I'll try and add references/links as appropriate.
Note that the help is very short; most of it is inherited from the parent class.
As usual, comments welcome...
Thanks,
Bigchimp
Thanks.
-
(create temporary stored procedures for prepared statements)
Can't remember what that one is. Does it mean that preparing a parameterized query won't work? Or won't give any performance benefits?
Ignore it ATM, it was only my comment to some way which I have thought about
Next up: writing up the various ways you can connect to sql server: in other words: explain that you can specify instance names in the database field (e.g. .\SQLEXPRESS, IIRC - can't remember if that works with freetds)... and remembering/looking up where the port number goes.
Look at begining of mssqlconn.pp:
TMSSQLConnection properties:
HostName - can be specified also as 'servername:port' or 'servername\instance'
And: http://www.freetds.org/userguide/portoverride.htm
-
Thanks Lacak!
-
Submitted as http://bugs.freepascal.org/view.php?id=21643 (http://bugs.freepascal.org/view.php?id=21643)
FPC trunk currently won't allow building documents (bug 21621) so no chm file.
However, you can copy the .xml file in one of the directories defined in Lazarus Tools/Optionis/FPDoc Editor/"FPDoc Settings"... this should show at least context-sensitive help (e.g. hovering your mouse over a TMSSQLConnection should show something).
-
Hi guys,
Can I install this component now in the current version of Lazarus? I didn't see download link, but I might have missed it.
Thanks,
-
Yes, it should work on current SVN/trunk version.
Edit: A recent snapshot will also work. the snapshots are based on 2.6/2.6.1 - sorry, I don't think the mssqlconn connector is already in there...
-
hi all
I think it's a very interesting component and I'd like to test it
I'm using a snapshot
lazarus 0.9.30-2build1 (2011-07-06)
FPC 2.4.4
revision 29738
i386-linux-gtk 2
on ubuntu 32 bit
donwoaded source from
https://bitbucket.org/reiniero/fpc_laz_patch_playground/src
during pasql.lpr project compilation I receive following error:
mssqlconn.pp(756,57) Error: Identifier not found "FSQLFormatSettings"
missing some code?
thanks
-
FPC 2.4.4
...
during pasql.lpr project compilation I receive following error:
mssqlconn.pp(756,57) Error: Identifier not found "FSQLFormatSettings"
missing some code?
I think FSQLFormatsetinngs must be introduced in a later version of FPC... that's why it won't work...
-
forgive me, I mistakenly thought it was a property introduced in donwloaded project :)
-
Ya, I am getting the same error. Do we need FCP 2.6 minimum?
-
One more question, is this a visual component that goes in the pallet or is it just a library?
-
There is an FPC connection (i.e. non-visual). That's been added to FPC 2.7.1 (SVN trunk) but will quite probably work on FPC 2.6.0/fixes 2.6/2.6.1 (and won't work without change on 2.4.4 - see nomorelogic's post).
You can look at the bug report mentioned to download the code, or get the code from the FPC trunk repository:
http://svn.freepascal.org/svn/fpc/trunk/packages/fcl-db/src/sqldb/mssql/ (http://svn.freepascal.org/svn/fpc/trunk/packages/fcl-db/src/sqldb/mssql/)
Edit: You'd also have to get a new registersqldb.pas
(You'd have to fiddle with the make files, perhaps at http://svn.freepascal.org/svn/fpc/trunk/packages/fcl-db/src/sqldb/ (http://svn.freepascal.org/svn/fpc/trunk/packages/fcl-db/src/sqldb/))
Then there's a visual/GUI component in the Lazarus pallette that requires the FPC component - so it needs FPC trunk/2.7.1 or a patched FPC 2.6.x.
See my post earlier with the bug report and associated code. Once again, you can download the code from the patch or via the repository.
Edit: In other words, if you have Lazarus trunk with FPC trunk, both the visual and non-visual components are there.
Regards,
BigChimp
-
Just a FYI, I got the connector working great with the version of Lazarus that was released last month. Works great, looking forward to it being included by default :-)
-
I made some small changes to pasql.lpr to fix some problems I had testing it in Linux:
- SetEnvironmentVariable is now compiled only in windows (as in other place in same source)
- select ... from RDB$DATABASE doesn't work in MSSQL
-
Thanks a lot, logic!
I'm incorporating the changes. The TDSVER thing makes sense as AFAIU, you can't change environment variables in a running process on POSIX systems..
Some questions:
- why did you disable odbcconn? Isn't that available on Linux or just not on your system?
- I noticed you updated a test query I wrote for Firebird; good fix you gave but instead of accepting your code I just removed it (it was meant to demonstrate something)...
-
I'm incorporating the changes. The TDSVER thing makes sense as AFAIU, you can't change environment variables in a running process on POSIX systems..
I think you're rigth
- why did you disable odbcconn? Isn't that available on Linux or just not on your system?
not for unavaiability, simply to test without this unit
I did not understand the presence, then I forgot to uncomment :P
I made some test with MsSql syntax (ie: exec stored proc that return a recordset) and all goes well.
Really good work
-
Hi,
Guys, do you have some update about the TMSSQLConnection class and, more important, about the libiconv2.dll and dblib.dll updates?
Thanks.
-
- TMSSQLConnection class is now part of FCL sqlDB, so all updates goes into SVN
- dblib.dll is compiled from FreeTDS db-lib . Latest stable release is 0.91 and precompiled DLL you can find:
ftp://ftp.freepascal.org/fpc/contrib/windows/
- libiconv2.dll is 3rd party so updates you can check f.e. at:
http://gnuwin32.sourceforge.net/packages/libiconv.htm
-
Hi Lacak, some time...
Well, yesterday I was back to a project (in production) that uses TMSSQLConnection to update some things so, I remember I use the same dblib.dll that you sent me a long time ago.
You said, If I remember well, that you compiled the dblib.dll yourself and had to change something...
- TMSSQLConnection class is now part of FCL sqlDB, so all updates goes into SVN
Yeah, I know. I use FPC 2.6.1 and I got the updates but I ask to know about new features or problems...
Yesterday I was very happy when the autoinc worked perfectly using SQLdb and mssql, very good!
- dblib.dll is compiled from FreeTDS db-lib . Latest stable release is 0.91 and precompiled DLL you can find:
ftp://ftp.freepascal.org/fpc/contrib/windows/
Hm... I did not know about this link. I will update the dblib.dll. Thanks!
- libiconv2.dll is 3rd party so updates you can check f.e. at:
http://gnuwin32.sourceforge.net/packages/libiconv.htm
I can update any time or depends the version of dblib.dll?
Best regards.
-
You said, If I remember well, that you compiled the dblib.dll yourself and had to change something...
AFAIR all my private changes was also applied later into FreeTDS project sources, so you can now compile direct FreeTDS without need of any special customization
Yeah, I know. I use FPC 2.6.1 and I got the updates but I ask to know about new features or problems...
I am not aware of any new problems and also AFAIK no new features were added ...
- libiconv2.dll is 3rd party so updates you can check f.e. at:
http://gnuwin32.sourceforge.net/packages/libiconv.htm
I can update any time or depends the version of dblib.dll?
here I am not sure ... it depends ... FreeTDS db-lib is compiled using libiconv - iconv.h, libiconv.lib which exports some functions. When signatures of these function do not change, then I would say, that you can upgrade libiconv2.dll without need to recompile dblib.dll
-
AFAIR all my private changes was also applied later into FreeTDS project sources, so you can now compile direct FreeTDS without need of any special customization
That is amazing, I did not know, congratulations!
I am not aware of any new problems and also AFAIK no new features were added ...
OK
here I am not sure ... it depends ... FreeTDS db-lib is compiled using libiconv - iconv.h, libiconv.lib which exports some functions. When signatures of these function do not change, then I would say, that you can upgrade libiconv2.dll without need to recompile dblib.dll
Any way, I saw that you put the libiconv in the same zip, on the FTP link so, no problems.
But I saw that exists a dblib_x64.zip too. The TMSSQLConnection already working on 64bit too?
Why the zip file do not contain the libiconv?
-
But I saw that exists a dblib_x64.zip too. The TMSSQLConnection already working on 64bit too?
Yes.
Why the zip file do not contain the libiconv?
Seems very difficult to find. I'm trying to build one with VS2010.
-
But I saw that exists a dblib_x64.zip too. The TMSSQLConnection already working on 64bit too?
Yes.
Why the zip file do not contain the libiconv?
Seems very difficult to find. I'm trying to build one with VS2010.
Ok Ludo!
Please, tell us if you can (here and/or on the list).
-
Why the zip file do not contain the libiconv?
As Ludo mentioned, I was not able find libiconv for Win64. For Win32 there is http://gnuwin32.sourceforge.net/packages/libiconv.htm. For Win64 there exists http://sourceforge.net/projects/gnuwin64/ but no files are there published ATM.
So if Ludo will compile own libiconv from sources then also db-lib can be recompiled and updated in ftp://ftp.freepascal.org/fpc/contrib/windows/
(then I would recommend put Win32 and 64 versions into one ZIP with 2 directories Win32 and Win64)
-
Ok Lacak, very good.
Thanks for the info.
-
Hi,
Imagine this query:
declare @id int
update f set @id = f.id from foo f
How I can get the @id parameter using SQLdb?
If I use q.Open (adding select @id as id) I got an exception. If I use q.ExecSQL I do not access to the parameter.
Thanks.
-
Which exception ? "Cannot open non-select statement"?
Problem is in fact that batch SQL statements parsing is not supported. So if you set SQL.Text to something like:
'declare @id int; select @id=f.ident_field from pokus1 f; select @id;'
(multiple SQL statements)
Then SQLParse does not describe this statement type as stSelect and when you try open such statement you will get above mentioned exception.
So ATM I do not know how to help you :(
... Only put your statements into stored procedure and use EXEC ...
-
Which exception ? "Cannot open non-select statement"?
Yes.
Problem is in fact that batch SQL statements parsing is not supported. So if you set SQL.Text to something like:
'declare @id int; select @id=f.ident_field from pokus1 f; select @id;'
(multiple SQL statements)
Then SQLParse does not describe this statement type as stSelect and when you try open such statement you will get above mentioned exception.
I did not do (still) an internal investigation in SQLdb to know how it works, but I understand if we call q.Open the statement type should be stSelect... but I think this isn't not so easy.
So ATM I do not know how to help you :(
... Only put your statements into stored procedure and use EXEC ...
Yes, I did that but only in some parts. I won't like to change all querys... :(
This project was made in Delphi 7 using ADO. I'm rewriting for FPC... many many querys.
-
Yes, I did that but only in some parts. I won't like to change all querys... :(
This project was made in Delphi 7 using ADO. I'm rewriting for FPC... many many querys.
Have you try to set SQLParse to false for those queries that have multiple commands and see if that helps to get back the results?
As far as I understand (and I'm way to new on this components mind you) is that this should allow you to execute queries like this but I don't know if those queries would be read only or not.
-
Have you try to set SQLParse to false for those queries that have multiple commands and see if that helps to get back the results?
Yes, and I got the same error.
As far as I understand (and I'm way to new on this components mind you) is that this should allow you to execute queries like this but I don't know if those queries would be read only or not.
Would be no problem for me if the resultset was read only.
-
Problem is in fact that batch SQL statements parsing is not supported. So if you set SQL.Text to something like:
'declare @id int; select @id=f.ident_field from pokus1 f; select @id;'
(multiple SQL statements)
Then SQLParse does not describe this statement type as stSelect and when you try open such statement you will get above mentioned exception.
I did not do (still) an internal investigation in SQLdb to know how it works, but I understand if we call q.Open the statement type should be stSelect... but I think this isn't not so easy.
Marcos you can also modify sqldb.pp ... look there at procedure TCustomSQLQuery.InternalOpen;
There is :
1396 if FCursor.FStatementType in [stSelect,stExecProcedure] thenTry alter it to:
1396 if FCursor.FStatementType in [stSelect,stExecProcedure,stUnknown] then
(it is not good as general solution, but if you use ONLY TMSSQLConnection then you can work-around it by this way)
or another solution is use hack like this:
as a first line in your batch use something like (comment):
--proc
and then in mssqlconn.pp in function TMSSQLConnection.StrToStatementType(s: string): TStatementType;
add something like:
else if s = '--proc' then
Result:=stExecProcedure
All these solutions are not ideal but can help you to avoid rewriting all queries
-
If you don't want to modify sqldb and don't want to rewrite the queries you can put the following in the beginning of your queries:
select 1 as a into #dummy; drop table #dummy;
What it does is make the query start with a select that doesn't produce a result set. The drop is needed to avoid an existing table error the second time you run this in the same session. There is a small overhead to this. So for queries that are used a lot, converting them to stored procedures would be recommended.
-
@Lacak2
Maybe a patch to choice the correct StatementType would be better.
I won't modify the sqldb.
@ludo
Many queries I'm already converting in Stored Procecedures but your hack is "better" than Lacak's hack because I don't need to modify the sqldb (sorry Lacak!).
--
Is it possible create a patch? I will see...
-
My experience with MSSQLServer 2000 was that using stored procedures could actually slow down the execution of statements. So in the end it might not be as easy to turn everything in to a stored procedure instead of a dynamically created sql at run time for instance.
As much as I appreciate smart code that tries to make it easy for me to handle things I hate when that smart code gets in the way of using the server capabilities because the developer did not knew or foreseen that a server could support what he thought is not supported. If it was on my hands I would have made sure that the "smart code" did not get in the way of any ones work and this makes a big difference.
I don't have the TMSSQLConnection installed my self so I don't know how well it behaves but the TSQLQuery that I can see it behaves a lot like a 14 year old that thinks it knows everything about the world around him.
-
(...)
or another solution is use hack like this:
as a first line in your batch use something like (comment):
--proc
and then in mssqlconn.pp in function TMSSQLConnection.StrToStatementType(s: string): TStatementType;
add something like:
else if s = '--proc' then
Result:=stExecProcedure
All these solutions are not ideal but can help you to avoid rewriting all queries
I take back what I said, the Lacak's hack worked very well! ;D
Well, not so good. I could not capture the '--proc' string. I think the sqldb do not process the SQL comments. But I made my own TSQLConnector class and override the StrToStatementType method, created a boolean property and voilá, worked! :D
-
Good news, Marcos!
Could you perhaps share that code/some hints on http://wiki.lazarus.freepascal.org/Lazarus_Database_Tutorial#Lazarus_and_MSSQL.2FSybase (http://wiki.lazarus.freepascal.org/Lazarus_Database_Tutorial#Lazarus_and_MSSQL.2FSybase)
That'll probably help others in your situation...
Thanks,
BigChimp
-
These tips are from Lacak and Ludo. I just implemented the ideas. :)
[off]
I use my own lib to work with database called Greyhound (http://github.com/mdbs99/Greyhound). So, create subclasses is normal to me but I don't know if somebody wants to create sub classes to SQLdb too -- many people just drop a component on a form...
[/off]
-
These tips are from Lacak and Ludo. I just implemented the ideas. :)
That's fine. I implement other people's ideas a lot, too ;)
See e.g. http://wiki.lazarus.freepascal.org/SAPI (http://wiki.lazarus.freepascal.org/SAPI) which I created with ludob's forum post info.
I just find that adding stuff to the wiki makes the information more structured accessible and attracts people that add to it. You can also refer people to it on the forum.
Regards,
BigChimp
-
These tips are from Lacak and Ludo. I just implemented the ideas. :)
That's fine. I implement other people's ideas a lot, too ;)
See e.g. http://wiki.lazarus.freepascal.org/SAPI (http://wiki.lazarus.freepascal.org/SAPI) which I created with ludob's forum post info.
Would be better create a patch to SQLdb. My implementation (or Lacak's implementation) is a hack.
I will study more the SQLdb and, if is possible, propose a patch.
I just find that adding stuff to the wiki makes the information more structured accessible and attracts people that add to it. You can also refer people to it on the forum.
Yes, you're right. I will do this.
-
Would be better create a patch to SQLdb. My implementation (or Lacak's implementation) is a hack.
I will study more the SQLdb and, if is possible, propose a patch.
I know. However, sqldb patches are often not quite *cough*immediately*cough* implemented.... So a workaround is nice to have - also for older versions if the patch doesn't get backported.
I just find that adding stuff to the wiki makes the information more structured accessible and attracts people that add to it. You can also refer people to it on the forum.
Yes, you're right. I will do this.
Thanks.