I don't know the ns_db sp_* functions, but you can get return values
in other ways.
First of all, there is nothing stopping you from executing a big chunk
of T-SQL in "ns_db select". You could easily execute the stored proc
using exec and the trick to getting the output values is to not use
output variables, but simply using "select". If the last statement in
your proc is a select, you will get those rows returned. But they
don't need to be from a table, you could also do "select @varA as foo,
@varB as bar".
And if you don't want to write your procs that way, you can just use
execute a chunk of T-SQL which declares the output variables and then
at then end of your "ns_db select $db "..."" block use the "select
@varA as ..." trick again.
Bas.
On 17/12/2007, at 10:30 PM, Rajesh Nair wrote:
Hi,
I am trying to use the ns_db sp_* API in AOLServer 4.0 with
nsfreetds driver
against MS SQL Server 2005.
I can tell that the driver are all set fine as I can execute Select
SQLs
correctly from ADP
But I am unable to call a stored procedure passing in a paramater and
retrieveing the result from the stored procedure.
myproc is a simple stored procedure which accepts a varchar
parameter and
insert the parameter in a table.
Here is the simple code snippet
<%
if {[catch {
#Select the database
set db [ns_db gethandle];
set sql1 "use rajesh";
#ns_db sp_start
set ret1 [ns_db sp_start $db "myproc"];
#Set an input varchar parameter to the stored procedure
#This code generates error
set ret2 [ns_db sp_setparam $db "@param" varchar in "980"];
ns_puts "ret2:$ret2<br>";
#Execute the stored procedure
set ret3 [ns_db sp_exec $db ];
ns_puts "<br>ret3:$ret3\n ";
} exception] != 0 } {
global errorInfo;
set savedInfo $errorInfo;
ns_puts "<P> EXCEPTION GENERATED<BR> $exception<BR/>"
ns_puts "<P> EXCEPTION GENERATED<BR> $savedInfo <BR/>"
}
%>
When I run this adp, I get the following error
EXCEPTION GENRATED
Database operation "sp_setparam" failed
EXCEPTION GENRATED
Database operation "sp_setparam" failed while executing "ns_db
sp_setparam
$db "@param" varchar in "980""
I had expected the ns_db sp_setparam to be the API used to set in
and out
parameters for Stored procedures but it is not working as such for me
Is it that I have missed anything or my expectation is wrong?
Any alternate way of doing this?
I did find that I was able to execute a stored procedure via
ns_db exec $dbhandle "exec myproc @param='980'"
but in this case I am unable to get the return value from the stored
procedure.
Also not sure if this is the right way to execute a stored procedure
as this
is very much db specific
--Rajesh Nair
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]
> with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the
Subject: field of your email blank.
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]>
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject:
field of your email blank.
