SEO

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

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"
<chrisNOSPAM.monnet@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@NOSP@M.state.mn.us> a écrit dans
le message de news: 3faa4b2c.1801406@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"
> <chrisNOSPAM.monnet@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
> >
> >
> >
>

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"
<chrisNOSPAM.monnet@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@NOSP@M.state.mn.us> a écrit dans
>le message de news: 3faa4b2c.1801406@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"
>> <chrisNOSPAM.monnet@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
>> >
>> >
>> >
>>
>
>