Hello everyone
I want to call a DB2 7.2 stored procedure via ADO in VC++ 7.1
It has 4 parameters, the 1st and the 4th are OUTPUT and the others are INPUT
params.
My code looks like this:
(spCMD is a Command, spCON a Connection, spRS a Resultset, and spPARAM
are Parameters)
spCMD->Name = "mySPname";
spCMD->ActiveConnection = spCON;
spCMD->CommandText = "CALL SYSPROC.mySPname(?, char('a string',8),
char('31.10.2003',10), ?)";
spPARAM1 = spCMD->CreateParameter("RETCODE", adSmallInt,
adParamOutput, 2, NULL);
spCMD->Parameters->Append(spPARAM1);
spPARAM2 = spCMD->CreateParameter("RETCHAR", adChar,
adParamOutput, 2, NULL);
spCMD->Parameters->Append(spPARAM2);
spRS = spCMD->Execute(NULL, NULL, adCmdText);
When I execute I have the following error message:
[IBM][CLI Driver][DB2] SQL0440N No function by the name "mySPname"
having compatible arguments was found in the function path SQLSTATE=42884
The following SQL command works in DB2 Command Center:
CALL SYSPROC.MYSPNAME(?, 'a string', '31.10.2003', ?)
It displays the 2 output params correctly.
I've checked in DB2: the SP prototype is (OUT SmallInt, IN Char 8, IN Char
10, OUT Char 2).
So I guess my problem has to do with using ADO correctly... With a simple
SELECT statement it works fine.
So what did I do wrong with a parameterized stored procedure?
Regards,
Chris 3 7683
Here's a chunk of code I use to call a stored proc with in and out
variables :
One of the key things - CLOSE the recordset BEFORE using the return
value(s)
Public Function GetNewKeyValue(sTableName As String) As Long
Dim rstSequence As ADODB.Recordset
Dim cTmp As New ADODB.Connection
Dim lNewKey As Long
Dim sSeq As String
Dim cmd As New ADODB.Command
Dim parm1 As New ADODB.Parameter
Dim parm2 As New ADODB.Parameter
Dim nCount As Integer
lNewKey = 0
nCount = 0
On Error GoTo Err_GetNewKeyValue
repeat_call:
nCount = nCount + 1
cTmp.ConnectionString = sConnectString
cTmp.CursorLocation = adUseClient
cTmp.Open
cmd.ActiveConnection = cTmp
cmd.CommandText = "KWOOD.GETNEWKEY"
cmd.CommandType = adCmdStoredProc
Set parm1 = cmd.CreateParameter("Sseq", adVarChar, adParamInput,
30, sTableName)
cmd.Parameters.Append parm1
Set parm2 = cmd.CreateParameter("lKeyValue", adBigInt,
adParamOutput, 8)
cmd.Parameters.Append parm2
Set rstSequence = cmd.Execute
Set rstSequence = Nothing
lNewKey = cmd(1).Value
On Thu, 6 Nov 2003 08:40:01 +0100, "Chris"
<ch****************@swisscom.com> wrote: Hello everyone
I want to call a DB2 7.2 stored procedure via ADO in VC++ 7.1 It has 4 parameters, the 1st and the 4th are OUTPUT and the others are INPUT params.
My code looks like this: (spCMD is a Command, spCON a Connection, spRS a Resultset, and spPARAM are Parameters) spCMD->Name = "mySPname"; spCMD->ActiveConnection = spCON; spCMD->CommandText = "CALL SYSPROC.mySPname(?, char('a string',8), char('31.10.2003',10), ?)"; spPARAM1 = spCMD->CreateParameter("RETCODE", adSmallInt, adParamOutput, 2, NULL); spCMD->Parameters->Append(spPARAM1); spPARAM2 = spCMD->CreateParameter("RETCHAR", adChar, adParamOutput, 2, NULL); spCMD->Parameters->Append(spPARAM2); spRS = spCMD->Execute(NULL, NULL, adCmdText);
When I execute I have the following error message: [IBM][CLI Driver][DB2] SQL0440N No function by the name "mySPname" having compatible arguments was found in the function path SQLSTATE=42884
The following SQL command works in DB2 Command Center: CALL SYSPROC.MYSPNAME(?, 'a string', '31.10.2003', ?) It displays the 2 output params correctly. I've checked in DB2: the SP prototype is (OUT SmallInt, IN Char 8, IN Char 10, OUT Char 2). So I guess my problem has to do with using ADO correctly... With a simple SELECT statement it works fine. So what did I do wrong with a parameterized stored procedure?
Regards, Chris
Thanks for your reply!
I've found out that I can execute the stored procedure if I set every
parameter
spCMD->CommandText = "CALL SYSPROC.ST1RTCHECKBEZMM(?, ?, ?, ?)";
But there is another problem when I try to get the return values of the
parameters...
After I've called Execute the recordset is in a closed state already:
spRS = spCMD->Execute(NULL, NULL, adCmdText);
(spRS->State == adStateClosed)
In C++ it seems I cannot access the parameters collection as easily as you
do in VB.
I tried the get_item method but I always get a NULL pointer :-(
Regards
Chris
"Remove the obvious for replies" <kurt.wood@NO**@M.state.mn.us> a écrit dans
le message de news: 3f**************@news.state.mn.us... Here's a chunk of code I use to call a stored proc with in and out variables : One of the key things - CLOSE the recordset BEFORE using the return value(s)
Public Function GetNewKeyValue(sTableName As String) As Long
Dim rstSequence As ADODB.Recordset Dim cTmp As New ADODB.Connection Dim lNewKey As Long Dim sSeq As String Dim cmd As New ADODB.Command Dim parm1 As New ADODB.Parameter Dim parm2 As New ADODB.Parameter Dim nCount As Integer
lNewKey = 0 nCount = 0 On Error GoTo Err_GetNewKeyValue
repeat_call: nCount = nCount + 1 cTmp.ConnectionString = sConnectString cTmp.CursorLocation = adUseClient cTmp.Open
cmd.ActiveConnection = cTmp cmd.CommandText = "KWOOD.GETNEWKEY" cmd.CommandType = adCmdStoredProc
Set parm1 = cmd.CreateParameter("Sseq", adVarChar, adParamInput, 30, sTableName) cmd.Parameters.Append parm1 Set parm2 = cmd.CreateParameter("lKeyValue", adBigInt, adParamOutput, 8) cmd.Parameters.Append parm2
Set rstSequence = cmd.Execute Set rstSequence = Nothing
lNewKey = cmd(1).Value
On Thu, 6 Nov 2003 08:40:01 +0100, "Chris" <ch****************@swisscom.com> wrote:
Hello everyone
I want to call a DB2 7.2 stored procedure via ADO in VC++ 7.1 It has 4 parameters, the 1st and the 4th are OUTPUT and the others are
INPUTparams.
My code looks like this: (spCMD is a Command, spCON a Connection, spRS a Resultset, and
spPARAMare Parameters) spCMD->Name = "mySPname"; spCMD->ActiveConnection = spCON; spCMD->CommandText = "CALL SYSPROC.mySPname(?, char('a
string',8),char('31.10.2003',10), ?)"; spPARAM1 = spCMD->CreateParameter("RETCODE", adSmallInt, adParamOutput, 2, NULL); spCMD->Parameters->Append(spPARAM1); spPARAM2 = spCMD->CreateParameter("RETCHAR", adChar, adParamOutput, 2, NULL); spCMD->Parameters->Append(spPARAM2); spRS = spCMD->Execute(NULL, NULL, adCmdText);
When I execute I have the following error message: [IBM][CLI Driver][DB2] SQL0440N No function by the name
"mySPname"having compatible arguments was found in the function path SQLSTATE=42884
The following SQL command works in DB2 Command Center: CALL SYSPROC.MYSPNAME(?, 'a string', '31.10.2003', ?) It displays the 2 output params correctly. I've checked in DB2: the SP prototype is (OUT SmallInt, IN Char 8, IN
Char10, OUT Char 2). So I guess my problem has to do with using ADO correctly... With a simple SELECT statement it works fine. So what did I do wrong with a parameterized stored procedure?
Regards, Chris
I can't help you with C++, I know there certainly was some putz work
in getting VB to work...
On Mon, 10 Nov 2003 17:02:20 +0100, "Chris"
<ch****************@swisscom.com> wrote: Thanks for your reply!
I've found out that I can execute the stored procedure if I set every parameter spCMD->CommandText = "CALL SYSPROC.ST1RTCHECKBEZMM(?, ?, ?, ?)";
But there is another problem when I try to get the return values of the parameters...
After I've called Execute the recordset is in a closed state already: spRS = spCMD->Execute(NULL, NULL, adCmdText); (spRS->State == adStateClosed)
In C++ it seems I cannot access the parameters collection as easily as you do in VB. I tried the get_item method but I always get a NULL pointer :-(
Regards Chris
"Remove the obvious for replies" <kurt.wood@NO**@M.state.mn.us> a écrit dans le message de news: 3f**************@news.state.mn.us... Here's a chunk of code I use to call a stored proc with in and out variables : One of the key things - CLOSE the recordset BEFORE using the return value(s)
Public Function GetNewKeyValue(sTableName As String) As Long
Dim rstSequence As ADODB.Recordset Dim cTmp As New ADODB.Connection Dim lNewKey As Long Dim sSeq As String Dim cmd As New ADODB.Command Dim parm1 As New ADODB.Parameter Dim parm2 As New ADODB.Parameter Dim nCount As Integer
lNewKey = 0 nCount = 0 On Error GoTo Err_GetNewKeyValue
repeat_call: nCount = nCount + 1 cTmp.ConnectionString = sConnectString cTmp.CursorLocation = adUseClient cTmp.Open
cmd.ActiveConnection = cTmp cmd.CommandText = "KWOOD.GETNEWKEY" cmd.CommandType = adCmdStoredProc
Set parm1 = cmd.CreateParameter("Sseq", adVarChar, adParamInput, 30, sTableName) cmd.Parameters.Append parm1 Set parm2 = cmd.CreateParameter("lKeyValue", adBigInt, adParamOutput, 8) cmd.Parameters.Append parm2
Set rstSequence = cmd.Execute Set rstSequence = Nothing
lNewKey = cmd(1).Value
On Thu, 6 Nov 2003 08:40:01 +0100, "Chris" <ch****************@swisscom.com> wrote:
>Hello everyone > >I want to call a DB2 7.2 stored procedure via ADO in VC++ 7.1 >It has 4 parameters, the 1st and the 4th are OUTPUT and the others areINPUT >params. > >My code looks like this: > (spCMD is a Command, spCON a Connection, spRS a Resultset, andspPARAM >are Parameters) > spCMD->Name = "mySPname"; > spCMD->ActiveConnection = spCON; > spCMD->CommandText = "CALL SYSPROC.mySPname(?, char('astring',8), >char('31.10.2003',10), ?)"; > spPARAM1 = spCMD->CreateParameter("RETCODE", adSmallInt, >adParamOutput, 2, NULL); > spCMD->Parameters->Append(spPARAM1); > spPARAM2 = spCMD->CreateParameter("RETCHAR", adChar, >adParamOutput, 2, NULL); > spCMD->Parameters->Append(spPARAM2); > spRS = spCMD->Execute(NULL, NULL, adCmdText); > >When I execute I have the following error message: > [IBM][CLI Driver][DB2] SQL0440N No function by the name"mySPname" >having compatible arguments was found in the function path SQLSTATE=42884 > >The following SQL command works in DB2 Command Center: > CALL SYSPROC.MYSPNAME(?, 'a string', '31.10.2003', ?) >It displays the 2 output params correctly. >I've checked in DB2: the SP prototype is (OUT SmallInt, IN Char 8, INChar >10, OUT Char 2). >So I guess my problem has to do with using ADO correctly... With a simple >SELECT statement it works fine. >So what did I do wrong with a parameterized stored procedure? > >Regards, >Chris > > >
This discussion thread is closed Replies have been disabled for this discussion. Similar topics
3 posts
views
Thread by Mariusz |
last post: by
|
1 post
views
Thread by bughunter |
last post: by
|
5 posts
views
Thread by Tim Marshall |
last post: by
|
2 posts
views
Thread by mike |
last post: by
|
45 posts
views
Thread by John |
last post: by
|
13 posts
views
Thread by Larry Menard |
last post: by
|
28 posts
views
Thread by mooreit |
last post: by
| |
3 posts
views
Thread by mandible |
last post: by
|
11 posts
views
Thread by peter |
last post: by
| | | | | | | | | | |