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->ActiveConnecti on = spCON;
spCMD->CommandText = "CALL SYSPROC.mySPnam e(?, char('a string',8),
char('31.10.200 3',10), ?)";
spPARAM1 = spCMD->CreateParamete r("RETCODE", adSmallInt,
adParamOutput, 2, NULL);
spCMD->Parameters->Append(spPARAM 1);
spPARAM2 = spCMD->CreateParamete r("RETCHAR", adChar,
adParamOutput, 2, NULL);
spCMD->Parameters->Append(spPARAM 2);
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.MYSPNAM E(?, '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.Connectio n
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_GetNewKeyVa lue

repeat_call:
nCount = nCount + 1
cTmp.Connection String = sConnectString
cTmp.CursorLoca tion = adUseClient
cTmp.Open

cmd.ActiveConne ction = cTmp
cmd.CommandText = "KWOOD.GETNEWKE Y"
cmd.CommandType = adCmdStoredProc

Set parm1 = cmd.CreateParam eter("Sseq", adVarChar, adParamInput,
30, sTableName)
cmd.Parameters. Append parm1
Set parm2 = cmd.CreateParam eter("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.c om> 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->ActiveConnecti on = spCON;
spCMD->CommandText = "CALL SYSPROC.mySPnam e(?, char('a string',8),
char('31.10.20 03',10), ?)";
spPARAM1 = spCMD->CreateParamete r("RETCODE", adSmallInt,
adParamOutpu t, 2, NULL);
spCMD->Parameters->Append(spPARAM 1);
spPARAM2 = spCMD->CreateParamete r("RETCHAR", adChar,
adParamOutpu t, 2, NULL);
spCMD->Parameters->Append(spPARAM 2);
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.MYSPNAM E(?, '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.ST1RTCH ECKBEZMM(?, ?, ?, ?)";

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.Connectio n
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_GetNewKeyVa lue

repeat_call:
nCount = nCount + 1
cTmp.Connection String = sConnectString
cTmp.CursorLoca tion = adUseClient
cTmp.Open

cmd.ActiveConne ction = cTmp
cmd.CommandText = "KWOOD.GETNEWKE Y"
cmd.CommandType = adCmdStoredProc

Set parm1 = cmd.CreateParam eter("Sseq", adVarChar, adParamInput,
30, sTableName)
cmd.Parameters. Append parm1
Set parm2 = cmd.CreateParam eter("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.c om> 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->ActiveConnecti on = spCON;
spCMD->CommandText = "CALL SYSPROC.mySPnam e(?, char('a string',8),char('31.10.20 03',10), ?)";
spPARAM1 = spCMD->CreateParamete r("RETCODE", adSmallInt,
adParamOutpu t, 2, NULL);
spCMD->Parameters->Append(spPARAM 1);
spPARAM2 = spCMD->CreateParamete r("RETCHAR", adChar,
adParamOutpu t, 2, NULL);
spCMD->Parameters->Append(spPARAM 2);
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.MYSPNAM E(?, '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.c om> 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.ST1RTCH ECKBEZMM(?, ?, ?, ?)";

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.Connectio n
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_GetNewKeyVa lue

repeat_call:
nCount = nCount + 1
cTmp.Connection String = sConnectString
cTmp.CursorLoca tion = adUseClient
cTmp.Open

cmd.ActiveConne ction = cTmp
cmd.CommandText = "KWOOD.GETNEWKE Y"
cmd.CommandType = adCmdStoredProc

Set parm1 = cmd.CreateParam eter("Sseq", adVarChar, adParamInput,
30, sTableName)
cmd.Parameters. Append parm1
Set parm2 = cmd.CreateParam eter("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.c om> 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->ActiveConnecti on = spCON;
> spCMD->CommandText = "CALL SYSPROC.mySPnam e(?, char('astring',8), >char('31.10.20 03',10), ?)";
> spPARAM1 = spCMD->CreateParamete r("RETCODE", adSmallInt,
>adParamOutpu t, 2, NULL);
> spCMD->Parameters->Append(spPARAM 1);
> spPARAM2 = spCMD->CreateParamete r("RETCHAR", adChar,
>adParamOutpu t, 2, NULL);
> spCMD->Parameters->Append(spPARAM 2);
> 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.MYSPNAM E(?, '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
>
>
>