468,107 Members | 1,303 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,107 developers. It's quick & easy.

Help again

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


Jan 29 '07 #1
3 3810

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" <no***********@nowhere.comwrote in message
news:OB**************@TK2MSFTNGP02.phx.gbl...
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


Jan 29 '07 #2
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" <Ro****@NoSpam.yah.nonewrote in message
news:5r******************************@comcast.com. ..
>
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" <no***********@nowhere.comwrote in message
news:OB**************@TK2MSFTNGP02.phx.gbl...
>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



Jan 29 '07 #3
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.
--------------------------------------------

"Darth Ferret" <no***********@nowhere.comwrote in message
news:OT**************@TK2MSFTNGP02.phx.gbl...
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" <Ro****@NoSpam.yah.nonewrote in message
news:5r******************************@comcast.com. ..
>>
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" <no***********@nowhere.comwrote in message
news:OB**************@TK2MSFTNGP02.phx.gbl...
>>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




Jan 30 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by xuatla | last post: by
9 posts views Thread by Dom Boyce | last post: by
6 posts views Thread by Mark Reed | last post: by
27 posts views Thread by SK | last post: by
23 posts views Thread by Jason | last post: by
13 posts views Thread by Chua Wen Ching | last post: by
15 posts views Thread by DavidS | last post: by
15 posts views Thread by Jay | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.