stored proc error - ADO and DB2

[ColdCanuck]

Greetings!

I am VERY new to DB2 but not Orable, Sybase and SQL Server.

I am trying to call a stored procedure via VB 6 and ADO/OLEDB. But when I
try to execute

[ColdCanuck]

"ColdCanuck" <co**@canuck.ca> wrote in message
news:gY6mc.6987$F04.5734@clgrps13...

Greetings!

I am VERY new to DB2 but not Orable, Sybase and SQL Server.

I am trying to call a stored procedure via VB 6 and ADO/OLEDB. But when I
try to execute

Sorry, I accidently sent this before finishing...

Please excuse the cross-post but I am unsure which group my problem pertains
to.

As I was saying, when I try to execute my stored proc, I get the following
error:
"Parameter type cannot be determined for at least one variant
parameter."

My proc is quite simple:

CREATE PROCEDURE NORM.npn_test2
( IN pContactId character(3),
OUT oName character(20))
LANGUAGE SQL
BEGIN ATOMIC

DECLARE sName CHAR(20);

SET sName = (SELECT ADADTX || ', ' || ADAETX FROM MAILIB.UUADCPP WHERE
ADAANB = pContactId);
SET oName = sName;

END
My VB code looks like:

Public Function Call_sp_test(con As ADODB.Connection, ByVal sContactId As
String) As String
On Error GoTo ErrHandler
Dim sName As String

Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

Dim P1 As New ADODB.Parameter
Dim P2 As New ADODB.Parameter

Set cmd.ActiveConnection = con

cmd.CommandText = "{CALL NORM.npn_test2(?,?)}"

cmd.CommandType = adCmdText
'cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh
'cmd.Parameters("pContactId").Value = sContactId
cmd.Prepared = True

sName = " " '40 spaces
P1.Direction = adParamInput
P1.Type = adChar
P1.Size = 6
P1.Name = "ContactId"
P1.Value = "000006"

P1.Direction = adParamReturnValue
P2.Type = adChar
P2.Name = "Name"
P2.Size = 40
P2.Value = sName

cmd.Parameters.Append P1
cmd.Parameters.Append P2

'cmd.Execute
rst.Open cmd <- error occurs here

sName = cmd.Parameters("oName").Value

Call_sp_test = cmd.Parameters("oName").Value

rst.Close
Set rst = Nothing
Set cmd = Nothing

ErrHandler:
MsgBox "Error Code: " & Err.Number & vbNewLine & _
"Description: " & Err.Description & vbNewLine & _
"Source: " & Err.Source, vbOKOnly + vbCritical
Err.Clear
'Set Call_sp_test = Nothing

End Function
Has anyone run into this problem before? What am I missing here?

Thanks for all replies!

cheers,
CC

[ColdCanuck]

"ColdCanuck" <co**@canuck.ca> wrote in message
news:gY6mc.6987$F04.5734@clgrps13...

Greetings!

I am VERY new to DB2 but not Orable, Sybase and SQL Server.

I am trying to call a stored procedure via VB 6 and ADO/OLEDB. But when I
try to execute

Sorry, I accidently sent this before finishing...

Please excuse the cross-post but I am unsure which group my problem pertains
to.

As I was saying, when I try to execute my stored proc, I get the following
error:
"Parameter type cannot be determined for at least one variant
parameter."

My proc is quite simple:

CREATE PROCEDURE NORM.npn_test2
( IN pContactId character(3),
OUT oName character(20))
LANGUAGE SQL
BEGIN ATOMIC

DECLARE sName CHAR(20);

SET sName = (SELECT ADADTX || ', ' || ADAETX FROM MAILIB.UUADCPP WHERE
ADAANB = pContactId);
SET oName = sName;

END
My VB code looks like:

Public Function Call_sp_test(con As ADODB.Connection, ByVal sContactId As
String) As String
On Error GoTo ErrHandler
Dim sName As String

Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

Dim P1 As New ADODB.Parameter
Dim P2 As New ADODB.Parameter

Set cmd.ActiveConnection = con

cmd.CommandText = "{CALL NORM.npn_test2(?,?)}"

cmd.CommandType = adCmdText
'cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh
'cmd.Parameters("pContactId").Value = sContactId
cmd.Prepared = True

sName = " " '40 spaces
P1.Direction = adParamInput
P1.Type = adChar
P1.Size = 6
P1.Name = "ContactId"
P1.Value = "000006"

P1.Direction = adParamReturnValue
P2.Type = adChar
P2.Name = "Name"
P2.Size = 40
P2.Value = sName

cmd.Parameters.Append P1
cmd.Parameters.Append P2

'cmd.Execute
rst.Open cmd <- error occurs here

sName = cmd.Parameters("oName").Value

Call_sp_test = cmd.Parameters("oName").Value

rst.Close
Set rst = Nothing
Set cmd = Nothing

ErrHandler:
MsgBox "Error Code: " & Err.Number & vbNewLine & _
"Description: " & Err.Description & vbNewLine & _
"Source: " & Err.Source, vbOKOnly + vbCritical
Err.Clear
'Set Call_sp_test = Nothing

End Function
Has anyone run into this problem before? What am I missing here?

Thanks for all replies!

cheers,
CC

[Philip Nelson]

ColdCanuck wrote:

"ColdCanuck" <co**@canuck.ca> wrote in message
news:gY6mc.6987$F04.5734@clgrps13...

Greetings!

I am VERY new to DB2 but not Orable, Sybase and SQL Server.

I am trying to call a stored procedure via VB 6 and ADO/OLEDB. But when I
try to execute

Sorry, I accidently sent this before finishing...

Please excuse the cross-post but I am unsure which group my problem
pertains to.

As I was saying, when I try to execute my stored proc, I get the following
error:
"Parameter type cannot be determined for at least one variant
parameter."

My proc is quite simple:

CREATE PROCEDURE NORM.npn_test2
( IN pContactId character(3),
OUT oName character(20))
LANGUAGE SQL
BEGIN ATOMIC

DECLARE sName CHAR(20);

SET sName = (SELECT ADADTX || ', ' || ADAETX FROM MAILIB.UUADCPP WHERE
ADAANB = pContactId);
SET oName = sName;

END
My VB code looks like:

Public Function Call_sp_test(con As ADODB.Connection, ByVal sContactId As
String) As String
On Error GoTo ErrHandler
Dim sName As String

Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

Dim P1 As New ADODB.Parameter
Dim P2 As New ADODB.Parameter

Set cmd.ActiveConnection = con

cmd.CommandText = "{CALL NORM.npn_test2(?,?)}"

cmd.CommandType = adCmdText
'cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh
'cmd.Parameters("pContactId").Value = sContactId
cmd.Prepared = True

sName = " " '40 spaces
P1.Direction = adParamInput
P1.Type = adChar
P1.Size = 6
P1.Name = "ContactId"
P1.Value = "000006"

P1.Direction = adParamReturnValue
P2.Type = adChar
P2.Name = "Name"
P2.Size = 40
P2.Value = sName

cmd.Parameters.Append P1
cmd.Parameters.Append P2

'cmd.Execute
rst.Open cmd <- error occurs here

sName = cmd.Parameters("oName").Value

Call_sp_test = cmd.Parameters("oName").Value

rst.Close
Set rst = Nothing
Set cmd = Nothing

ErrHandler:
MsgBox "Error Code: " & Err.Number & vbNewLine & _
"Description: " & Err.Description & vbNewLine & _
"Source: " & Err.Source, vbOKOnly + vbCritical
Err.Clear
'Set Call_sp_test = Nothing

End Function
Has anyone run into this problem before? What am I missing here?

Thanks for all replies!

cheers,
CC

CC,

In the stored procedure definition you want to specify a SPECIFIC NAME as
well as the procedure name.

You really do want to be using the enumeration adCmdStoredProc, which you
have commented out in favour of adCmdText.

You'll find a couple of good samples in -

C:\PROGRAM FILES\SQLLIB\SAMPLES\ADO\VB

From memory they are something like spcall.* (simple call) and rssp.*
(returning a result set).

I was just working on an example piece of code for one of our developers
today and the sample code works !!!

HTH

Phil

[Philip Nelson]

ColdCanuck wrote:

"ColdCanuck" <co**@canuck.ca> wrote in message
news:gY6mc.6987$F04.5734@clgrps13...

Greetings!

I am VERY new to DB2 but not Orable, Sybase and SQL Server.

I am trying to call a stored procedure via VB 6 and ADO/OLEDB. But when I
try to execute

Sorry, I accidently sent this before finishing...

Please excuse the cross-post but I am unsure which group my problem
pertains to.

As I was saying, when I try to execute my stored proc, I get the following
error:
"Parameter type cannot be determined for at least one variant
parameter."

My proc is quite simple:

CREATE PROCEDURE NORM.npn_test2
( IN pContactId character(3),
OUT oName character(20))
LANGUAGE SQL
BEGIN ATOMIC

DECLARE sName CHAR(20);

SET sName = (SELECT ADADTX || ', ' || ADAETX FROM MAILIB.UUADCPP WHERE
ADAANB = pContactId);
SET oName = sName;

END
My VB code looks like:

Public Function Call_sp_test(con As ADODB.Connection, ByVal sContactId As
String) As String
On Error GoTo ErrHandler
Dim sName As String

Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

Dim P1 As New ADODB.Parameter
Dim P2 As New ADODB.Parameter

Set cmd.ActiveConnection = con

cmd.CommandText = "{CALL NORM.npn_test2(?,?)}"

cmd.CommandType = adCmdText
'cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh
'cmd.Parameters("pContactId").Value = sContactId
cmd.Prepared = True

sName = " " '40 spaces
P1.Direction = adParamInput
P1.Type = adChar
P1.Size = 6
P1.Name = "ContactId"
P1.Value = "000006"

P1.Direction = adParamReturnValue
P2.Type = adChar
P2.Name = "Name"
P2.Size = 40
P2.Value = sName

cmd.Parameters.Append P1
cmd.Parameters.Append P2

'cmd.Execute
rst.Open cmd <- error occurs here

sName = cmd.Parameters("oName").Value

Call_sp_test = cmd.Parameters("oName").Value

rst.Close
Set rst = Nothing
Set cmd = Nothing

ErrHandler:
MsgBox "Error Code: " & Err.Number & vbNewLine & _
"Description: " & Err.Description & vbNewLine & _
"Source: " & Err.Source, vbOKOnly + vbCritical
Err.Clear
'Set Call_sp_test = Nothing

End Function
Has anyone run into this problem before? What am I missing here?

Thanks for all replies!

cheers,
CC

CC,

In the stored procedure definition you want to specify a SPECIFIC NAME as
well as the procedure name.

You really do want to be using the enumeration adCmdStoredProc, which you
have commented out in favour of adCmdText.

You'll find a couple of good samples in -

C:\PROGRAM FILES\SQLLIB\SAMPLES\ADO\VB

From memory they are something like spcall.* (simple call) and rssp.*
(returning a result set).

I was just working on an example piece of code for one of our developers
today and the sample code works !!!

HTH

Phil

[ColdCanuck]

"Philip Nelson" <te*****@scotdb.com> wrote in message
news:Mq***********************@news.easynews.com.. .

In the stored procedure definition you want to specify a SPECIFIC NAME as
well as the procedure name.

You really do want to be using the enumeration adCmdStoredProc, which you
have commented out in favour of adCmdText.

You'll find a couple of good samples in -

C:\PROGRAM FILES\SQLLIB\SAMPLES\ADO\VB

From memory they are something like spcall.* (simple call) and rssp.*
(returning a result set).

I was just working on an example piece of code for one of our developers
today and the sample code works !!!

HTH

Phil

Thanks for that info Phil!

I have stepped back from the VB part and am focussing on the DB2 part. I
have added SPECIFIC NAME to the procedure npn_test2, but when I try to run
it via Ops Navigator: call npn_test2('000006', ?);

I get the following error: "The number of parameter values set or registered
does not match the number of parameters."

Yet when I run:

SELECT SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION, PARAMETER_MODE,
PARAMETER_NAME
FROM qsys2.sysparms
WHERE specific_name ='NORM';

I get:
NORM, NPN_TEST2, 1, IN, PCONTACTID

NORM, NPN_TEST2, 2, OUT, ONAME

So what's up with this error message?

cheers,
Norm

[ColdCanuck]

"Philip Nelson" <te*****@scotdb.com> wrote in message
news:Mq***********************@news.easynews.com.. .

In the stored procedure definition you want to specify a SPECIFIC NAME as
well as the procedure name.

You really do want to be using the enumeration adCmdStoredProc, which you
have commented out in favour of adCmdText.

You'll find a couple of good samples in -

C:\PROGRAM FILES\SQLLIB\SAMPLES\ADO\VB

From memory they are something like spcall.* (simple call) and rssp.*
(returning a result set).

I was just working on an example piece of code for one of our developers
today and the sample code works !!!

HTH

Phil

Thanks for that info Phil!

I have stepped back from the VB part and am focussing on the DB2 part. I
have added SPECIFIC NAME to the procedure npn_test2, but when I try to run
it via Ops Navigator: call npn_test2('000006', ?);

I get the following error: "The number of parameter values set or registered
does not match the number of parameters."

Yet when I run:

SELECT SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION, PARAMETER_MODE,
PARAMETER_NAME
FROM qsys2.sysparms
WHERE specific_name ='NORM';

I get:
NORM, NPN_TEST2, 1, IN, PCONTACTID

NORM, NPN_TEST2, 2, OUT, ONAME

So what's up with this error message?

cheers,
Norm

[Buddy Robbins]

"ColdCanuck" <co**@canuck.ca> wrote in message
news:577mc.7147$F04.4921@clgrps13...

"ColdCanuck" <co**@canuck.ca> wrote in message
news:gY6mc.6987$F04.5734@clgrps13...

Greetings!

I am VERY new to DB2 but not Orable, Sybase and SQL Server.

I am trying to call a stored procedure via VB 6 and ADO/OLEDB. But when I try to execute

Sorry, I accidently sent this before finishing...

Please excuse the cross-post but I am unsure which group my problem

pertains to.

As I was saying, when I try to execute my stored proc, I get the following
error:
"Parameter type cannot be determined for at least one variant
parameter."

My proc is quite simple:

CREATE PROCEDURE NORM.npn_test2
( IN pContactId character(3),
OUT oName character(20))
LANGUAGE SQL
BEGIN ATOMIC

DECLARE sName CHAR(20);

SET sName = (SELECT ADADTX || ', ' || ADAETX FROM MAILIB.UUADCPP WHERE
ADAANB = pContactId);
SET oName = sName;

END
My VB code looks like:

Public Function Call_sp_test(con As ADODB.Connection, ByVal sContactId As
String) As String
On Error GoTo ErrHandler
Dim sName As String

Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

Dim P1 As New ADODB.Parameter
Dim P2 As New ADODB.Parameter

Set cmd.ActiveConnection = con

cmd.CommandText = "{CALL NORM.npn_test2(?,?)}"

cmd.CommandType = adCmdText
'cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh
'cmd.Parameters("pContactId").Value = sContactId
cmd.Prepared = True

sName = " " '40 spaces
P1.Direction = adParamInput
P1.Type = adChar
P1.Size = 6
P1.Name = "ContactId"
P1.Value = "000006"

P1.Direction = adParamReturnValue
P2.Type = adChar
P2.Name = "Name"
P2.Size = 40
P2.Value = sName

cmd.Parameters.Append P1
cmd.Parameters.Append P2

'cmd.Execute
rst.Open cmd <- error occurs here

sName = cmd.Parameters("oName").Value

Call_sp_test = cmd.Parameters("oName").Value

rst.Close
Set rst = Nothing
Set cmd = Nothing

ErrHandler:
MsgBox "Error Code: " & Err.Number & vbNewLine & _
"Description: " & Err.Description & vbNewLine & _
"Source: " & Err.Source, vbOKOnly + vbCritical
Err.Clear
'Set Call_sp_test = Nothing

End Function
Has anyone run into this problem before? What am I missing here?

Thanks for all replies!

cheers,
CC

Hey CC,
Just judging by the error message you're getting (never having to use DB2
before) I would guess that your problem is that you are calling the
cmd.Parameters.Refresh method, which should auto-populate your Parameter
collection in the Command object, and then incorrectly creating 2 other
parameters to add to the collection.

The two new Parameters that you are creating as new.
This is an incorrect way to create parameters. You should call the
Cmd.CreateParameter method to create the parameter, set the values, and then
append it to the Paramters collection.

The bottom line is that you should only do one of the two-
Cmd.Parameters.Refresh (Which is inefficient if the parameter list doesn't
change), or Cmd.Parameters.Append(Cmd.CreateParameter(...)) (my personal
favorite).

--Buddy Robbins
MCSD, MCSE, MCDBA

[Buddy Robbins]

"ColdCanuck" <co**@canuck.ca> wrote in message
news:577mc.7147$F04.4921@clgrps13...

"ColdCanuck" <co**@canuck.ca> wrote in message
news:gY6mc.6987$F04.5734@clgrps13...

Greetings!

I am VERY new to DB2 but not Orable, Sybase and SQL Server.

I am trying to call a stored procedure via VB 6 and ADO/OLEDB. But when I try to execute

Sorry, I accidently sent this before finishing...

Please excuse the cross-post but I am unsure which group my problem

pertains to.

As I was saying, when I try to execute my stored proc, I get the following
error:
"Parameter type cannot be determined for at least one variant
parameter."

My proc is quite simple:

CREATE PROCEDURE NORM.npn_test2
( IN pContactId character(3),
OUT oName character(20))
LANGUAGE SQL
BEGIN ATOMIC

DECLARE sName CHAR(20);

SET sName = (SELECT ADADTX || ', ' || ADAETX FROM MAILIB.UUADCPP WHERE
ADAANB = pContactId);
SET oName = sName;

END
My VB code looks like:

Public Function Call_sp_test(con As ADODB.Connection, ByVal sContactId As
String) As String
On Error GoTo ErrHandler
Dim sName As String

Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

Dim P1 As New ADODB.Parameter
Dim P2 As New ADODB.Parameter

Set cmd.ActiveConnection = con

cmd.CommandText = "{CALL NORM.npn_test2(?,?)}"

cmd.CommandType = adCmdText
'cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh
'cmd.Parameters("pContactId").Value = sContactId
cmd.Prepared = True

sName = " " '40 spaces
P1.Direction = adParamInput
P1.Type = adChar
P1.Size = 6
P1.Name = "ContactId"
P1.Value = "000006"

P1.Direction = adParamReturnValue
P2.Type = adChar
P2.Name = "Name"
P2.Size = 40
P2.Value = sName

cmd.Parameters.Append P1
cmd.Parameters.Append P2

'cmd.Execute
rst.Open cmd <- error occurs here

sName = cmd.Parameters("oName").Value

Call_sp_test = cmd.Parameters("oName").Value

rst.Close
Set rst = Nothing
Set cmd = Nothing

ErrHandler:
MsgBox "Error Code: " & Err.Number & vbNewLine & _
"Description: " & Err.Description & vbNewLine & _
"Source: " & Err.Source, vbOKOnly + vbCritical
Err.Clear
'Set Call_sp_test = Nothing

End Function
Has anyone run into this problem before? What am I missing here?

Thanks for all replies!

cheers,
CC

Hey CC,
Just judging by the error message you're getting (never having to use DB2
before) I would guess that your problem is that you are calling the
cmd.Parameters.Refresh method, which should auto-populate your Parameter
collection in the Command object, and then incorrectly creating 2 other
parameters to add to the collection.

The two new Parameters that you are creating as new.
This is an incorrect way to create parameters. You should call the
Cmd.CreateParameter method to create the parameter, set the values, and then
append it to the Paramters collection.

The bottom line is that you should only do one of the two-
Cmd.Parameters.Refresh (Which is inefficient if the parameter list doesn't
change), or Cmd.Parameters.Append(Cmd.CreateParameter(...)) (my personal
favorite).

--Buddy Robbins
MCSD, MCSE, MCDBA

[ColdCanuck]

"Buddy Robbins" <Bu***********@sbcglobal.net> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

Hey CC,
Just judging by the error message you're getting (never having to use DB2
before) I would guess that your problem is that you are calling the
cmd.Parameters.Refresh method, which should auto-populate your Parameter
collection in the Command object, and then incorrectly creating 2 other
parameters to add to the collection.

The two new Parameters that you are creating as new.
This is an incorrect way to create parameters. You should call the
Cmd.CreateParameter method to create the parameter, set the values, and then append it to the Paramters collection.

The bottom line is that you should only do one of the two-
Cmd.Parameters.Refresh (Which is inefficient if the parameter list doesn't
change), or Cmd.Parameters.Append(Cmd.CreateParameter(...)) (my personal
favorite).

--Buddy Robbins
MCSD, MCSE, MCDBA

Thanks for that Buddy!

I've taken this back to the DB2 where I'm getting errors just trying to call
the stored proc.

cheers,
Norm

[ColdCanuck]

"Buddy Robbins" <Bu***********@sbcglobal.net> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

Hey CC,
Just judging by the error message you're getting (never having to use DB2
before) I would guess that your problem is that you are calling the
cmd.Parameters.Refresh method, which should auto-populate your Parameter
collection in the Command object, and then incorrectly creating 2 other
parameters to add to the collection.

The two new Parameters that you are creating as new.
This is an incorrect way to create parameters. You should call the
Cmd.CreateParameter method to create the parameter, set the values, and then append it to the Paramters collection.

The bottom line is that you should only do one of the two-
Cmd.Parameters.Refresh (Which is inefficient if the parameter list doesn't
change), or Cmd.Parameters.Append(Cmd.CreateParameter(...)) (my personal
favorite).

--Buddy Robbins
MCSD, MCSE, MCDBA

Thanks for that Buddy!

I've taken this back to the DB2 where I'm getting errors just trying to call
the stored proc.

cheers,
Norm

[ColdCanuck]

The bottom line is that you should only do one of the two-
Cmd.Parameters.Refresh (Which is inefficient if the parameter list doesn't change), or Cmd.Parameters.Append(Cmd.CreateParameter(...)) (my personal favorite).

--Buddy Robbins
MCSD, MCSE, MCDBA

You were spot on here Buddy! That did it!

many thanks...
Norm