Hi Marcin,
Passing parameters as text helps --- it really works. Thanks a lot.
Yet Microsoft claims that using Refresh gives better performance when
calling same procedure many times in a loop (refresh stays outside the loop
and all parameters are initialized before the loop). In this situation you
alter only selected parameters before next call.
However the performance is not that crucial in this moment. My current doubt
concerns returning value in parameter in this situation. Lets assume dbproc
which returns recordset and a number indicating some status:
CREATE DBPROC TESTFN2( IN AGE INTEGER, OUT NUMBER INTEGER)
RETURNS CURSOR AS
$CURSOR = 'ACUR';
DECLARE :$CURSOR CURSOR FOR SELECT NAME FROM SA.PEOPLE;
SET NUMBER = 45;
On my ASP page I would like to get both the recordset and the NUMBER value.
I tried
to create parameters and append them to the Parameters collection:
set param = Server.CreateObject("ADODB.Parameter")
param.Name = "AGE"
param.Direction = adParamInput
param.Type = adInteger
param.Value = 47
cmd.Parameters.Append
param set param = Server.CreateObject("ADODB.Parameter")
param.Name = "NUMBER"
param.Direction = adParamOutput
*****
param.Type = adInteger
cmd.Parameters.Append param
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "TESTFN2"
set rs = cmd.Execute
....
I get the following error from Microsoft OLEDB driver for ODBC:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[SAP AG][SQLOD32 DLL][SAP DB]General error;-4024 .
and points at line marked with *****
Thank you for help and I am looking forward for more ;-), please.
Best regards,
Tomek
----- Original Message -----
From: "Marcin P" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 12:16 PM
Subject: Re: DBPROC called from ADO/ASP
| Hi Tomek,
| I use SAP DB Stored Procedures with ADO Objects and have no problem with
| this.
| I always use USER.PROC_NAME, and I append my parameters of command
| in code, so the refresh method isn't neccesary (because it slows down your
| application).
| Did you grant EXECUTE privilege to your application user??
| And give us some more detailed errors descriptions (and no. errors) !!
| (it means - "give us the chance to help you")
| Regards,
| Marcin Pytel
| Talex S.A.
|
| Użytkownik "Tomasz Kantecki" <[EMAIL PROTECTED]> napisał w wiadomości
| 003b01c2b7cb$afa7d090$1801a8c0@leo">news:003b01c2b7cb$afa7d090$1801a8c0@leo...
| Hi,
|
| I searched through the archive but I could not find similar issue. I hope
I
| am not duplicating the case.
| I am working on web application working on Win2K/IIS/ASP + SAPDB. I get
| access to SAPDB via ADO objects and ODBC driver for SAPDB. As far as I use
| SQL commands in CmdText mode it is OK. Yet I cannot invoke DBPROC as
| CmdStoredProc via ADO interface at all. I get strange errors that tell me
| nothing. Here is the code fragment:
|
| dim conn, rs, cmd
| set conn = Server.CreateObject("ADODB.Connection")
| set cmd = Server.CreateObject("ADODB.Command")
| set rs = Server.CreateObject("ADODB.RecordSet")
|
| conn.Open "DSN=sapdb2;uid=xx;pwd=xx;"
| set cmd.ActiveConnection = conn
| cmd.CommandType = adCmdStoredProc
| cmd.CommandText = "TESTFN" <--- adding "USER.TESTFN"
| does not change anything
| cmd.Parameters.Refresh
| cmd.Parameters.Item(0) = 47
| cmd.Execute adExecuteNoRecords
| conn.Close
|
| If I remove the call to Refresh then error message changes to other ...
| The above code works with MSSQL
|
| Another question concerns DBPROC:
| - is it possible to return recordset from DBPROC? how shall I do it?
| - I guess the return data is retrieveable via Parameters collection?
|
| I am frustrated with this stored procedure issue, please help.
| Thanks in advance and best regards,
| Tomasz Kantecki
|
|
|
| ************r-e-k-l-a-m-a***************
| Chcesz oszczędzić na kosztach obsługi bankowej ?
| mBIZNES - konto dla firm
|
|
|
| _______________________________________________
| sapdb.general mailing list
| [EMAIL PROTECTED]
| http://listserv.sap.com/mailman/listinfo/sapdb.general
***************r-e-k-l-a-m-a**************
Chcesz oszczędzić na kosztach obsługi bankowej ?
mBIZNES - konto dla firm
http://epieniadze.onet.pl/mbiznes
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
