|
Home > Archive > AOL Webserver > December 2007 > ns_db sp_* API not working with MS SQL server 2005
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
ns_db sp_* API not working with MS SQL server 2005
|
|
| Rajesh Nair 2007-12-17, 7:11 am |
| 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
| |
| Bas Scheffers 2007-12-18, 1:11 am |
| 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 <listserv@listserv.aol.com
> body of "SIGNOFF AOLSERVER" in the email message. You can leave the
> Subject: field of your email blank.
| |
| Rajesh nair 2007-12-18, 1:11 pm |
| Thanks Bas,
Two Queries here :
1. Do you mean a code like this ?
<%
if {[catch {
set db [ns_db gethandle];
set sql1 "use rajesh";
ns_puts "RESULT1[ns_db exec $db $sql1]<br> ";
set sql2 "EXEC myprocddd @param =7777";
########################################
####
## THE AOLSERVER CRASHES IN THE NEXT LINE###
set row [ns_db select $db $sql2];
set size [ns_set size $row];
ns_puts "<br>SIZE:$size";
while {[ns_db getrow $db $row]} {
for {set i 0} {$i < $size} {incr i} {
ns_puts "[ns_set value $row $i]"
}
}
} exception] != 0 } {
global errorInfo;
set savedInfo $errorInfo;
ns_puts "<P> EXCEPTION GENRATED<BR> $savedinfo<BR/>"
}
%>
This code crashes when I do a ns_db select with SQL = "Exec <stored
procedure> @params".
I can do a ns_db exec "exec <stored procedure> @param=value".
But don't know how to get the value returned from stored procedure in that
case.
2. Does that mean ns_db sp_start API (and all the rest of the ns_db sp_*
APIs will not work for MS SQL Server 2005).
I did notice that nsfreetds wiki
<http://panoptic.com/wiki/aolserver/Nsfreetds_FAQ> mentions that ns_db sp_*
API is incomplete.
Btw, I am using AOLServer 4.5 and the nsfreetds version is the 0.4
--- Rajesh Nair
-----Original Message-----
From: AOLserver Discussion [mailto:AOLSERVER@LISTSERV.AOL.COM] On Behalf Of
Bas Scheffers
Sent: Tuesday, December 18, 2007 8:26 AM
To: AOLSERVER@LISTSERV.AOL.COM
Subject: Re: [AOLSERVER] ns_db sp_* API not working with MS SQL server 2005
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
<listserv@listserv.aol.com
[vbcol=seagreen]
> 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
<listserv@listserv.aol.com> 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 <listserv@listserv.aol.com> with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
| |
| Bas Scheffers 2007-12-18, 7:11 pm |
| On 19/12/2007, at 12:59 AM, Rajesh nair wrote:
> This code crashes when I do a ns_db select with SQL =3D =93Exec <stored=
=20
> procedure> @params=94.
Crash is such a harsh word, don't you mean "throws an error"? I bet =20
the error is "... is not a statement returning rows" or similar.
> I can do a ns_db exec =93exec <stored procedure> @param=3Dvalue=94.
> But don=92t know how to get the value returned from stored procedure =20
> in that case.
Try this:
ns_db 1row $db "
declare @foo int
declare @bar int
exec my_stored_proc @paramA=3D@foo, @paramB=3D@bar OUTPUT
select @bar as bar
"
Now you should get back one row with the output of the procedure in =20
the column "bar".
I am pretty sure this works in Sybase - I used this years ago. Not =20
sure about the ODBC driver and SQL2005, though.
> 2. Does that mean ns_db sp_start API (and all the rest of the ns_db =20
> sp_* APIs will not work for MS SQL Server 2005).
Never tried that, so I wouldn't know.
Hope that helps,
Bas.
|
|
|
|
|