[Lazarus] SQLdb not able to retrieve data from stored procedures?

[Bo Berglund]

On Thu, 20 Jan 2011 09:31:44 +0100 (CET), michael.vancanneyt at wisa.be
wrote:

>
>
>On Wed, 19 Jan 2011, Bo Berglund wrote:
>
>> 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'
>
>Hm. Weird MS syntax.

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.

-- 

Bo Berglund
Developer in Sweden