Help again

[Darth Ferret]

This thing is about to drive me crazy. I have about 50 queries in the AS400
that I need to put on a menu. Once I conquer this I have a bunch more rpg
reports that I need to pass a date to. In the AS400 I have a stored
procedure (SPRUNQRY) that runs the RUNQRY command with the name of the query
as a parameter. In the AS400 I would type "RUNQRY SUNTR401A" on a command
line to run this query. My connection is opening, and I can run some reports
that do not have any parameters. My VB.net 2003 is:

'create command object

Dim cmd As New iDB2Command(" ", cn)

'setup the parameters

Dim cmdparm1 As New iDB2Parameter("cmdparm1", iDB2DbType.iDB2VarChar, 9)

cmdparm1.Value = "SUNTR401A"

cmdparm1.Direction = ParameterDirection.Output

cmd.Parameters.Add(cmdparm1)

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "{CALL hteusrj/sprunqry ( ? )}"

Try

cmd.ExecuteNonQuery()

Catch exc As iDB2Exception

MsgBox("execute did not work", MsgBoxStyle.OKOnly, "OK")

Exit Sub

End Try

This catches an error when it tries to execute the command The error says:
{IBM.Data.DB2.iSeries.iDB2SQLErrorException}

and also: message code -104 and message: "SQL0104 Token {was not valid.
Valid tokens : : <IDENTIFIER>."

I can't figure out exactly what it wants. I could sure use some help, I've
spun my wheels for several days on this.

Thanks in advance,

Joe in Florida

 

[RobinS]

The parameter direction should be input. (That's the default, so I would
just eliminate that line). That indicates whether you are sending into *in*
to the SP or if you're getting something back. Output Parameters are
usually used for getting back autoincrement values assigned to inserted
records.

You command text needs to *not* have curly braces in it; it looks like
that's what the AS400 is kicking back. The CommandText needs to be the
*name* of the stored procedure that you are running, so if
"hteusrj/sprunqry" is really the name of the stored procedure in the
database, I think this will work:

cmd.CommandText = "hteusrj/sprunqry"

Does that work?

Robin S.

 

[Darth Ferret]

Thank you for your help Robin,

I made the changes and received the message below the code about a qualified
object. On a lark I tried using hteusrj.spcrunqry with a period instead of a
slash and it just hangs:
'create command object

Dim cmd As New iDB2Command(" ", cn)

'setup the parameters

Dim cmdparm1 As New iDB2Parameter("cmdparm1", iDB2DbType.iDB2VarChar, 9)

cmdparm1.Value = "SUNTR401A"

cmd.Parameters.Add(cmdparm1)

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "hteusrj/sprunqry"

Try

cmd.ExecuteNonQuery()

Catch exc As iDB2Exception

MsgBox("execute did not work", MsgBoxStyle.OKOnly, "OK")

Exit Sub

End Try

=========================

Message "SQL5016 Qualified object name SPRUNQRY not valid." String

It's getting closer. MessageDetails "Cause . . . . . : One of the
following has occurred: -- The syntax used for the qualified object name is
not valid for the naming option specified. With system naming, the
qualified form of an object name is schema-name/object-name. With SQL
naming the qualified form of an object name is
authorization-name.object-name. -- The syntax used for the qualified object
name is not allowed. User-defined types cannot be qualified with the schema
in the system naming convention on parameters and SQL variables of an SQL
procedure or function. Recovery . . . : Do one of the following and try
the request again: -- If you want to use the SQL naming convention, verify
the SQL naming option in the appropriate SQL command and qualify the object
names in the form authorization-id.object-name. -- If you want to use the
system naming convention, specify the system naming option in the
appropriate SQL command and qualify the object names in the form
schema-name/object-name. -- With the system naming convention, ensure the
user-defined types specified for parameters and variables in an SQL routine
can be found in the current path." String

 

[RobinS]

What is the name of the parameter in the stored procedure? For example,
this is a stored procedure from SQLServer, and the parameter name is
@CustomerID. So when I add a parameter to my parameterlist containing the
value for this, I have to call the parameter @CustomerID. Is yours really
called "cmdparm1" on the database side?

SELECT CustomerID,
LastName,
FirstName,
Address,
City,
State,
Zip,
Phone
FROM Customer
WHERE (CustomerID = @CustomerID)

And is the stored procedure *really* called "hteusrv/spcrunqry" ? I ask
because I've never seen a stored procedure name with a slash in it.

Robin S.
--------------------------------------------