Recent

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

mdbs99

  • Full Member
  • ***
  • Posts: 143
    • Github
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #180 on: August 10, 2012, 01:59:09 pm »
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.

taazz

  • Hero Member
  • *****
  • Posts: 5365
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #181 on: August 10, 2012, 02:05:53 pm »
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.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

mdbs99

  • Full Member
  • ***
  • Posts: 143
    • Github
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #182 on: August 11, 2012, 11:39:21 pm »
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.

Lacak2

  • Guest
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #183 on: August 14, 2012, 08:20:19 am »
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 :
Code: [Select]
1396   if FCursor.FStatementType in [stSelect,stExecProcedure] thenTry alter it to:
Code: [Select]
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:
Code: [Select]
  else if s = '--proc' then
    Result:=stExecProcedure

All these solutions are not ideal but can help you to avoid rewriting all queries

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #184 on: August 14, 2012, 10:52:32 am »
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:
Code: [Select]
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.

mdbs99

  • Full Member
  • ***
  • Posts: 143
    • Github
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #185 on: August 14, 2012, 04:15:47 pm »
@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...

taazz

  • Hero Member
  • *****
  • Posts: 5365
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #186 on: August 14, 2012, 09:34:43 pm »
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.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

mdbs99

  • Full Member
  • ***
  • Posts: 143
    • Github
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #187 on: August 15, 2012, 03:31:29 am »
(...)
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:
Code: [Select]
  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
« Last Edit: August 15, 2012, 03:35:08 am by md »

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 #188 on: August 15, 2012, 11:09:59 am »
Good news, Marcos!

Could you perhaps share that code/some hints on http://wiki.lazarus.freepascal.org/Lazarus_Database_Tutorial#Lazarus_and_MSSQL.2FSybase
That'll probably help others in your situation...

Thanks,
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

mdbs99

  • Full Member
  • ***
  • Posts: 143
    • Github
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #189 on: August 15, 2012, 02:23:15 pm »
These tips are from Lacak and Ludo. I just implemented the ideas.  :)

[off]
I use my own lib to work with database called 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]

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 #190 on: August 15, 2012, 03:24:13 pm »
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 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
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

mdbs99

  • Full Member
  • ***
  • Posts: 143
    • Github
Re: TMSSQLConnection - sqlDB component for accessing MS SQL Server
« Reply #191 on: August 15, 2012, 03:35:44 pm »
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 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.

Quote
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.

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 #192 on: August 15, 2012, 04:22:42 pm »
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.

Quote
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.
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