Recent

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

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #15 on: November 07, 2011, 01:15:25 pm »
Hi Lacak2,

I've sort of figured out a possible fix for the indexkeys stuff.
The UpdateIndexDefs procedure needs some changes:
Code: [Select]
   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
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

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

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

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #16 on: November 07, 2011, 01:45:31 pm »
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 or the Express Edition (production use permitted, but with size/connection limits).
Thanks for info, but download file size is too big for me  :(

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #17 on: November 07, 2011, 02:10:31 pm »
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  :)
« Last Edit: April 04, 2013, 09:22:35 am by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

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

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

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #18 on: November 07, 2011, 02:52:55 pm »

... 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+''')'+

BigChimp

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

 ::)

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
Code: [Select]
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?
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

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

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

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #20 on: November 08, 2011, 08:01:21 am »
Must say I'm not that happy with
Code: [Select]
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.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #21 on: November 08, 2011, 11:37:25 am »
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:
Code: [Select]
  //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.
« Last Edit: November 08, 2011, 11:55:19 am by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

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

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

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #22 on: November 08, 2011, 02:26:29 pm »
I updated dblib.pp to include constants for the connection flavour:
Code: [Select]
  //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
Code: [Select]
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!)
« Last Edit: November 08, 2011, 02:30:11 pm by Lacak2 »

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #23 on: November 09, 2011, 03:21:02 pm »
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
Code: [Select]
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:
Code: [Select]
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:
Code: [Select]
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:
Code: [Select]
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.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

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

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

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #24 on: November 10, 2011, 12:53:50 pm »
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)
« Last Edit: November 10, 2011, 01:04:41 pm by Lacak2 »

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #25 on: November 10, 2011, 04:19:26 pm »
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  :).
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

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

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

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #26 on: November 11, 2011, 06:13:08 am »
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

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
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

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

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

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #27 on: November 11, 2011, 08:02:57 am »
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

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #28 on: November 11, 2011, 08:09:50 am »
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()

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #29 on: November 11, 2011, 08:44:03 am »
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:
Code: [Select]
procedure TMSSQLConnection.DoInternalConnect;
...
  if Ftds = DBTDS_50 then //Sybase
    dbsetlversion(FDBLogin, DBVERSION_100) //ATM not implemented by FreeTDS 0.91; use freetds.conf to set "tds version=5.0"
  else
    dbsetlversion(FDBLogin, DBVER60);
... won't you have the same problem for MS SQL server: you can't go to a lower version than DBVERSION71 by specifying your own freetds.conf or TDSVER environment variable??

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...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

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

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

 

TinyPortal © 2005-2018