AS400 SQL query with Parameter

Hello,

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

From stackoverflow

• 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).

• 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 but not in AS400.

  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.

  Mike Wills : Oh duh! I forget about that myself...

• 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();
  }