I am investigating Lazarus' capabilities for database work so I have made a very simple test application using ODBC and the SQLdb components.
I am doing this on a Windows XP platform.

The code is a very slightly modified example that I got from the WIKI here:
http://wiki.lazarus.freepascal.org/Lazarus_Database_Tutorial#Lazarus_and_MSSQL

My problem is that I get an exception on the query.Open line when the SQL text is something like this:
'EXEC SelectSomeData @Parameter=1234'

This is a stored procedure in an MSSQL Server database, which will return a recordset similar to a SELECT statement only the actual selection mechanism may be very complex and is located in a stored procedure for performance reasons.
We build all our database manipulations this way to gain speed and ease support.

However the TSQLQuery object seems unable to return these data for some obscure reason.

I have tried to read up but not found a solution, for example here: http://wiki.lazarus.freepascal.org/SQLdb_Package
A bit down the page it says:

TSQLQuery
This is a descendant of TDataset, and provides the data as a table from the SQL query that you submit. But can also be used to execute SQL-queries that don't return any data.

Does this mean that the query MUST be table oriented and only use SELECT statements if data are to be returned???

What I have here are executable procedures that DO return data just like any SELECT statement....

What to do?

Hm. Weird MS syntax.

Why could they not use SELECT * FROM SelectSomeData(1234) like everyone else ?

Can you please give the error message ?

Michael

Not really, this is standard syntax for calling a stored procedure with a parameter value. I simplified the call for this post to have only one parameter and gave it a valid name, which is not the actual name...

Why could they not use SELECT * FROM SelectSomeData(1234) like everyone else ?

Because the SelectSomeData is not a table, it is a stored procedure and must be called with the EXEC verb.
And there are possibly many different input arguments so the syntax allows for giving these by name. Parameters not supplied by the call will be replaced by defaults if the procedure is so programmed. But it is also possible to use this call syntax:

EXEC SelectSomeData 1234

In this case it is assumed that the supplied parameter value belongs to the first argument of the stored procedure.

Stored procedures are of two kinds (at least):
- Action procedures perform an action like updating tables according to the procedure code or such but return nothing.
- Select procedures select data according to a complex scheme built into the procedure and return them in a dataset similar to what is returned by a table select sentence.

Both are called with the EXEC verb.

Can you please give the error message ?

Not now, I am at work and the program is located on my home PC.

I understand that SelectSomeData is not a table.

But in other engines "SELECT * FROM SelectSomeData(1234)" does work on a stored procedure. I assumed it was standard.

Michael.

Op 2011-01-20 12:38, het geskryf:

But in other engines "SELECT * FROM SelectSomeData(1234)" does work on a stored procedure.

That will only work if the stored procedure returns a table-type dataset.

I used to use MS SQL Server years ago, but can't remember if we used that format (from original poster) to pass parameters. I'll see if I can find some old delphi code to confirm.

Regards,
- Graeme -

En/na ha escrit:

I never used it (my database needs are quite simple) so I don't know how it works, but zeos has a TZStoredProc component, probably meant to encapsulate the different way each database has to execute stored procedures.

Bye

???? 20/1/2011 12:38 ??, ?/? ??????:

Bo, one trick you can use is :

"SELECT * FROM OPENROWSET('SQLOLEDB', 'SERVER=(LOCAL);TRUSTED CONNECTION=YES;','SET FMTONLY OFF EXEC MASTER.DBO.SelectSomeData"

but because under SQL Server 2005, Ad Hoc Distributed Queries must be replaced all sp under MS SQL with User Defined Functions, which return a RowSet and can be used in
the SQL statements.

regards,

but because under SQL Server 2005, Ad Hoc Distributed Queries must be replaced all sp under MS SQL with User Defined Functions, which return a RowSet and can be used in the SQL statements.

"but because under SQL Server 2005, Ad Hoc Distributed Queries must be on, i replaced all sp under"

i meant to write, sorry

regards,

I might as well use something else than SQLdb even though it would be a pity since SQLdb seems to be part of the lazarus file set while zeos is not.

I located this zeos wiki page:
http://wiki.lazarus.freepascal.org/Zeos_tutorial
but it seems VERY old since it refers to the "latset stable lazarus" as 0.9.12 and dates are 2005-2006....

Of course I am on lazarus 0.9.30_fixes and FPC 2.4.2

Is there some more up-to-date lazarus tutorial for zeos?

Bo Berglund wrote

Is there some more up-to-date lazarus tutorial for zeos?

I might add that I followed the link on the wiki page to the Zeoslib own webpage: http://zeos.firmos.at/

Here is a download link in the left panel that goes to: http://zeos.firmos.at/downloads.php

Finally on this page is a link to Releases that ends up at an EMPTY PAGE, only the site headers are shown...

But thanks to Google:
If one wants to test the Zeoslib 6.6.6 stable release (pre unicode) from 2009, then here is where it can be downloaded: http://sourceforge.net/projects/zeoslib/