By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,009 Members | 2,866 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,009 IT Pros & Developers. It's quick & easy.

Call Stored Procedures via ADO

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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


Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
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
>
>
>



Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.