Recent

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

ludob

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

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #61 on: November 16, 2011, 08:24:36 am »
I did basic testing with fcl-db test suite. Results seems good.
See attached file.

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 #62 on: November 16, 2011, 11:23:35 am »
Very basic testing with my command line program.  :D
Works.
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 #63 on: November 16, 2011, 11:37:10 am »
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)
Code: [Select]
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
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 #64 on: November 16, 2011, 12:10:17 pm »
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 ?

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 #65 on: November 16, 2011, 12:35:57 pm »
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?
« Last Edit: November 16, 2011, 12:37:46 pm 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

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #66 on: November 16, 2011, 12:50:26 pm »
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 ;)

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 #67 on: November 16, 2011, 01:10:15 pm »
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):
Code: [Select]
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...
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 #68 on: November 16, 2011, 01:15:47 pm »
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

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #69 on: November 16, 2011, 01:26:20 pm »
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)

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 #70 on: November 16, 2011, 01:33:20 pm »
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.
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 #71 on: November 16, 2011, 01:38:59 pm »
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)
« Last Edit: November 16, 2011, 01:41:57 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 #72 on: November 16, 2011, 01:49:31 pm »
... 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.
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 #73 on: November 16, 2011, 04:36:19 pm »
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?

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 #74 on: November 16, 2011, 04:42:31 pm »
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>
« Last Edit: November 16, 2011, 04:58:20 pm 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

 

TinyPortal © 2005-2018