ADO stored procedure question 
Author Message
 ADO stored procedure question

I've asked this question before, and got a partial solution, but one
problem still persists.

I have a stored procedure running on a MS SQL server.

The Storedproc has one in param and several out params, which I have
managed to pass and receive quite successfully.

Unfortunately stored procedures also have a return value, and integer
(so I guess they should really be called stored functions) and I don't
know how to read this.

  adostoredproc1.ProcedureName:='myprocedure';
  adostoredproc1.Parameters.Clear;

me
input data');

l);

l);

l);

l);

  adostoredproc1.execproc;

  // from this point I can access all the parameters by using

How can I get to the return value which is used in my case to indicate
if the stored procedure has actually worked or not?

Can anyone help?

Thanks,

Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES



Mon, 14 Nov 2005 21:24:41 GMT  
 ADO stored procedure question

Quote:

>I have a stored procedure running on a MS SQL server.
>The Storedproc has one in param and several out params, which I have
>managed to pass and receive quite successfully.
>Unfortunately stored procedures also have a return value, and integer
>(so I guess they should really be called stored functions) and I don't
>know how to read this.


Groetjes,
Maarten Wiltink



Tue, 15 Nov 2005 03:42:02 GMT  
 ADO stored procedure question
On Thu, 29 May 2003 21:42:02 +0200, "Maarten Wiltink"

Quote:

>>I have a stored procedure running on a MS SQL server.

>>The Storedproc has one in param and several out params, which I have
>>managed to pass and receive quite successfully.

>>Unfortunately stored procedures also have a return value, and integer
>>(so I guess they should really be called stored functions) and I don't
>>know how to read this.


>Groetjes,
>Maarten Wiltink

But how do I read it?

If I try to do


I've tried

  for l:=0 to adostoredproc1.parameters.Count-1 do
  begin
    memo1.lines.add(adostoredproc1.parameters.Items[l].name);
  end;

But all that shows up are the parameters I defined at the beginning.

number of params for the stored procedure do not match.

Any ideas?

Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES



Tue, 15 Nov 2005 04:02:59 GMT  
 ADO stored procedure question

Quote:

> How can I get to the return value which is used in my case to indicate
> if the stored procedure has actually worked or not?

> Can anyone help?

This isn't exactly answering your question, but normally
if an error occurs in an SP an exception is raised, and
you handle it that way (and / or iterate the ADO Errors
collection).

--
jc



Tue, 15 Nov 2005 16:08:36 GMT  
 ADO stored procedure question
On Fri, 30 May 2003 09:08:36 +0100, Jeremy Collins

Quote:

>> How can I get to the return value which is used in my case to indicate
>> if the stored procedure has actually worked or not?

>> Can anyone help?

>This isn't exactly answering your question, but normally
>if an error occurs in an SP an exception is raised, and
>you handle it that way (and / or iterate the ADO Errors
>collection).

Hehe, well at least you were honest...

Unfortunately the SP in question catches various errors such as
invalid calling param values and uses this illusive return value to
pass back various numerical error code.

Well it would if I could work out how to read the bl**dy thing!

Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES



Tue, 15 Nov 2005 19:26:10 GMT  
 ADO stored procedure question

Quote:

> On Fri, 30 May 2003 09:08:36 +0100, Jeremy Collins

>>This isn't exactly answering your question, but normally
>>if an error occurs in an SP an exception is raised, and
>>you handle it that way (and / or iterate the ADO Errors
>>collection).

> Hehe, well at least you were honest...

> Unfortunately the SP in question catches various errors such as
> invalid calling param values and uses this illusive return value to
> pass back various numerical error code.

> Well it would if I could work out how to read the bl**dy thing!

Sounds like you can create your own "return value" by just
having another out parameter, then...?

--
jc



Tue, 15 Nov 2005 20:23:12 GMT  
 ADO stored procedure question
On Fri, 30 May 2003 13:23:12 +0100, Jeremy Collins

Quote:

>> On Fri, 30 May 2003 09:08:36 +0100, Jeremy Collins

>>>This isn't exactly answering your question, but normally
>>>if an error occurs in an SP an exception is raised, and
>>>you handle it that way (and / or iterate the ADO Errors
>>>collection).

>> Hehe, well at least you were honest...

>> Unfortunately the SP in question catches various errors such as
>> invalid calling param values and uses this illusive return value to
>> pass back various numerical error code.

>> Well it would if I could work out how to read the bl**dy thing!

>Sounds like you can create your own "return value" by just
>having another out parameter, then...?

The simple solution... Sounds obvious doesn't it... Unfortunately the
SP is provided by the company who's database we are interfacing to...
It's standard routine used by 101 other things, so can't be changed
for me, and trying to get them to create a uniquely named SP just for
me, is like banging my head against a wall!

Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES



Tue, 15 Nov 2005 22:03:20 GMT  
 ADO stored procedure question

Quote:

> The simple solution... Sounds obvious doesn't it... Unfortunately the
> SP is provided by the company who's database we are interfacing to...
> It's standard routine used by 101 other things, so can't be changed
> for me, and trying to get them to create a uniquely named SP just for
> me, is like banging my head against a wall!

Um, forgive another possibly obvious thought, but have you
tried "pdReturnValue" as your parameter direction instead
of "pdoutput"?

--
jc



Tue, 15 Nov 2005 22:45:51 GMT  
 ADO stored procedure question
On Fri, 30 May 2003 15:45:51 +0100, Jeremy Collins

Quote:

>> The simple solution... Sounds obvious doesn't it... Unfortunately the
>> SP is provided by the company who's database we are interfacing to...
>> It's standard routine used by 101 other things, so can't be changed
>> for me, and trying to get them to create a uniquely named SP just for
>> me, is like banging my head against a wall!

>Um, forgive another possibly obvious thought, but have you
>tried "pdReturnValue" as your parameter direction instead
>of "pdoutput"?

Yeap...

I can only create params for the true input and output params. If I
try and add an extra one for the return value, I get an error telling
me that the number of parameters in the SP don't match the call.

I must admit I did wonder what the pdReturnValue was for, but although
it looks like just what I want, I can't find how to assign it to my
call!

Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES



Tue, 15 Nov 2005 23:46:19 GMT  
 ADO stored procedure question
On Fri, 30 May 2003 16:46:19 +0100, Dodgy

Quote:
>On Fri, 30 May 2003 15:45:51 +0100, Jeremy Collins


>>> The simple solution... Sounds obvious doesn't it... Unfortunately the
>>> SP is provided by the company who's database we are interfacing to...
>>> It's standard routine used by 101 other things, so can't be changed
>>> for me, and trying to get them to create a uniquely named SP just for
>>> me, is like banging my head against a wall!

>>Um, forgive another possibly obvious thought, but have you
>>tried "pdReturnValue" as your parameter direction instead
>>of "pdoutput"?

>Yeap...

>I can only create params for the true input and output params. If I
>try and add an extra one for the return value, I get an error telling
>me that the number of parameters in the SP don't match the call.

>I must admit I did wonder what the pdReturnValue was for, but although
>it looks like just what I want, I can't find how to assign it to my
>call!

>Dodgy.

I assume from the death of this thread that nobody else had any idea
either... :'-(

Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES



Mon, 21 Nov 2005 16:31:34 GMT  
 ADO stored procedure question

Quote:

> I assume from the death of this thread that nobody else had any idea
> either... :'-(

Looks like it - a pity - as I often do SQL server stuff and was curious
too!

A final desperate attempt - look at this:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=%23b...

and this:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=aa72...

--
jc



Mon, 21 Nov 2005 17:00:59 GMT  
 ADO stored procedure question
On Thu, 05 Jun 2003 10:00:59 +0100, Jeremy Collins

Quote:

>> I assume from the death of this thread that nobody else had any idea
>> either... :'-(

>Looks like it - a pity - as I often do SQL server stuff and was curious
>too!

>A final desperate attempt - look at this:

>http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=%23b...

>and this:

>http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=aa72...

Excuse my language, but Jeremy you are a f*cking star!

Problem solved.

The return value definition *MUST* be the first one when you define
the params. If you do it anywhere else you'll get told that the number
of parameters to the Stored Procedure do not match.

I have no idea how you managed to google those two out, I thought I'd
googled every possible page that mentioned stored procedures!

Once again thanks you thank you thank you...

Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES



Mon, 21 Nov 2005 22:31:30 GMT  
 ADO stored procedure question
Have you tried using the pdResult kind of the stored procedure.  This
should be the first parameter created in the collection.

Good Luck

Quote:

> I've asked this question before, and got a partial solution, but one
> problem still persists.

> I have a stored procedure running on a MS SQL server.

> The Storedproc has one in param and several out params, which I have
> managed to pass and receive quite successfully.

> Unfortunately stored procedures also have a return value, and integer
> (so I guess they should really be called stored functions) and I don't
> know how to read this.

>   adostoredproc1.ProcedureName:='myprocedure';
>   adostoredproc1.Parameters.Clear;

me
> input data');

l);

l);

l);

l);

>   adostoredproc1.execproc;

>   // from this point I can access all the parameters by using

> How can I get to the return value which is used in my case to indicate
> if the stored procedure has actually worked or not?

> Can anyone help?

> Thanks,

> Dodgy.



Wed, 14 Dec 2005 03:28:04 GMT  
 
 [ 13 post ] 

 Relevant Pages 

1. Returning resultsets from Oracle stored procedures via ADO?

2. ADO, stored proc and MSSQL question.

3. Simple Stored Procedure question

4. Stored Procedure question

5. Stored procedure question - Interbase NLM.

6. Question involving updating data behind stored procedures with TClientDataSet

7. A simple question for Stored Procedure!!

8. **PLEASE HELP** Oracle Stored Procedures question

9. Simple Stored Procedure question

10. Oracle Stored Procedures - Procedures in Packages

11. Question on passing pointer to procedure - question.zip (0/1)

12. Question on passing pointer to procedure - question.zip (0/1)

 

 
Powered by phpBB® Forum Software