ibm midrange - AS400 SQL query with Parameter

Question

I am testing a simple query to get data from an AS400 database. I am not sure if the way I am using the SQL query is correct.

I get an error: "The parameter is incorrect."

Select FIELD1, FIELD2 From Mylibrary.MyTable WHERE FIELD1 = @Field1

I don't get an error when I run the following query:

Select FIELD1, FIELD2 From Mylibrary.MyTable WHERE FIELD1 = 'myvalue'

I am using ADODB, VBScript to test.

Set Param1 = cmd.CreateParameter("@Field1", 129, 1, 9, "myvalue")  ' 129 String
cmd.Parameters.Append Param1

I am coming from MS Sql environment, so writing for AS400 is totally new for me.
Thanks

Answer 1

Ok, I got the solution by playing around and trying different things.

As I said before, I am used to OLEDB and ADO.Net so I am used to doing things like:

Select FIELD1, FIELD2 From Mylibrary.MyTable WHERE FIELD1 = @Field1

which work in Access and SQL Server but not in AS/400.

I got the following to work:

Select FIELD1, FIELD2 From Mylibrary.MyTable WHERE FIELD1 = ?

cmd.ActiveConnection = connstr
cmd.CommandType = 1'4   'Stored Procedures '1 Text
cmd.CommandText = sql
Set Param1 = cmd.CreateParameter("@Field1", 129, 1, 9, "myvalue")  ' 129 String
cmd.Parameters.Append Param1
Set rs = cmd.Execute()

This is all VbScript. The trick was to add the question mark(?) in the sql statement.