AS400 SQL query with parameter

Question

I am testing a simple query to retrieve data from an AS400 database. I am not sure if the SQL query is being used correctly.

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

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

I do not get an error when running the following query:

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

I am using ADODB, VBScript for testing.

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

I come from MS Sql, so the record for AS400 is completely new for me.
Thanks

Answer 1

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

As I said, I'm used to OLEDB and ADO.Net, so I'm 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:

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()

VbScript. , (?) sql.

Answer 2

AS400 .NET, , IBM.Data.DB2.iSeries.NET IBM. IBM , 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();
}

Answer 3

Picflight,

, SQL. Mylibrary.Mytable( SQL) Mimalibrary/Mytable ( ).