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

[Dimitrios Chr. Ioannidis]

Στις 20/1/2011 12:38 μμ, ο/η michael.vancanneyt at wisa.be έγραψε:
>
>
> On Thu, 20 Jan 2011, Bo Berglund wrote:
>
>> 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.
>
> 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.


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,

-- 
Dimitrios Chr. Ioannidis