Help again

  • Thread starter Thread starter Darth Ferret
  • Start date Start date
D

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
 
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.
 
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
 
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.
--------------------------------------------
 
Back
Top