Recent

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

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #45 on: November 14, 2011, 12:02:12 pm »
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.

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #46 on: November 14, 2011, 12:31:45 pm »
Ludo your readme.txt is identical to my original ?
Please attach modified version. :)

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #47 on: November 14, 2011, 12:47:35 pm »
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?

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 #48 on: November 14, 2011, 12:51:19 pm »
Code: [Select]
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.
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 #49 on: November 14, 2011, 01:06:52 pm »
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.
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

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #50 on: November 14, 2011, 01:34:21 pm »
Quote
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)

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 #51 on: November 14, 2011, 01:55:12 pm »
PICNIC once again :)

Understood.
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 #52 on: November 14, 2011, 03:22:41 pm »
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.

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #53 on: November 14, 2011, 03:48:12 pm »
One more correction in detecting IDENTITY columns

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 #54 on: November 15, 2011, 09:53:00 am »
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!
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 #55 on: November 15, 2011, 10:24:36 am »
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"

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 #56 on: November 15, 2011, 11:30:27 am »
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
Code: [Select]
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:
Code: [Select]
      '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.
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 #57 on: November 15, 2011, 11:49:58 am »
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, SQL folder.

Thanks for the help!
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

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #58 on: November 15, 2011, 12:10:12 pm »
Quote
Quote
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.

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #59 on: November 15, 2011, 01:12:48 pm »
Quote
Quote
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.
« Last Edit: November 15, 2011, 01:20:39 pm by Lacak2 »

 

TinyPortal © 2005-2018