Call Stored Procedures via ADO

[Chris]

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

[Remove the obvious for replies]

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

[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

[Remove the obvious for replies]

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
>
>
>