AS400 带参数的 SQL 查询

发布于 2024-07-12 13:39:14 字数 545 浏览 4 评论 0原文

我正在测试一个简单的查询以从 AS400 数据库获取数据。 我不确定使用 SQL 查询的方式是否正确。

我收到错误：“参数不正确。”

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

当我运行以下查询时，我没有收到错误：

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

我正在使用 ADODB、VBScript 进行测试。

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

我来自 MS Sql 环境，因此为 AS400 编写对我来说是全新的。
谢谢

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

痴骨ら 2024-07-19 13:39:14

好吧，我通过尝试不同的事情找到了解决方案。

正如我之前所说，我习惯了 OLEDB 和 ADO.Net，因此我习惯于执行以下操作：

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

它们在 Access 和 SQL Server 中工作，但在 AS/400 中不起作用。

我得到了以下工作：

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语句中添加问号（？）。

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.

回复收藏 0 原文

听闻余生 2024-07-19 13:39:14

如果您要使用 .NET 连接到 AS400，首先应该使用 IBM.Data.DB2.iSeries 来自 IBM 的 .NET 提供程序。根据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();
}

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

回复收藏 0 原文