带参数的AS400 SQL查询 [英] AS400 SQL query with Parameter

问题描述

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

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'

我正在使用ADODB，VBScript进行测试.

I am using ADODB, VBScript to test.

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

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

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

推荐答案

好吧，我通过反复试验并尝试其他方法得到了解决方案.

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

正如我之前所说，我习惯于OLEDB和ADO.Net，所以我习惯于做类似的事情:

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

可在Access和SQL Server中使用，但不能在AS/400中使用.

which work in Access and SQL Server but not in 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语句中添加问号(?).

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

这篇关于带参数的AS400 SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！