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

Picflight,

You might have connected using the system naming conventions instead of SQL naming conventions. Change Mylibrary.Mytable (SQL period qualifier) to Mylibrary/Mytable (system slash qualifier).

Answer 2

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.

Answer 3

If you're going to connect to the AS400 using .NET, first of all you should use the IBM.Data.DB2.iSeries .NET provider from IBM. According IBM's documentation, this is the preferred method for calling sql:

iDB2Connection conn = new IDB2Connnection(connectionstring);
iDB2Command cmd = null;

try
{
  conn.Open();
  string sql = "select * from somelibrary.sometable where a = @A and b = @B";
  cmd = conn.CreateCommand();
  cmd.CommandText = sql;
  cmd.DeriveParameters(); //this will talk to the AS400 to determine the param types
  cmd.Parameters["@A"].Value = Avalue;
  cmd.Parameters["@B"].Value = Bvalue;

  //execute the query
  cmd.ExecuteScalar(); //doesn't have to be Scalar but you get the idea
}
catch (Exception ex)
{
   //handle your exceptions
}
finally
{
  cmd.Dispose();
  conn.Close();
}