使用参数化SQL查询执行ADODB命令失败 [英] ADODB Command failing Execute with parameterised SQL query

问题描述

我有以下JScript代码:

I have the following JScript code:

var conn = new ActiveXObject ("ADODB.Connection");
conn.Open("Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=blah_blah_blah;User=foo;Password=bar;");
var cmd = new ActiveXObject("ADODB.Command");
cmd.ActiveConnection = conn;

var strSQL = "SELECT id FROM tbl_info WHERE title LIKE :search ORDER BY id";
var search = "test";

try{
    cmd.CommandText = strSQL;
    var param = cmd.CreateParameter(':search', 200, 1, 100, search);
    cmd.Parameters.Append(param);
    var rs = cmd.Execute();
    }
catch (ex) {
    Application.Alert("Error retrieving id information from database.");
}

我已经验证(通过打印它们)，将Connection对象设置为Command的ActiveConnection，参数对象具有正确的值，并且Command对象作为CommandText具有正确的SQL查询.我还在try块的每一行之后插入了一个警报语句，以查看发生错误的位置-在cmd.Parameters.Append之后就可以了，但是在执行Execute语句时会抛出异常.

I've verified (by printing them) that the Connection object is set to be the Command's ActiveConnection, the parameter object has the correct value and the Command object has the correct SQL query as its CommandText. I also inserted an alert statement after each line in the try block to see where the error was occuring - it's fine after cmd.Parameters.Append but the exception gets thrown upon running the Execute statement.

我尝试显示实际的异常，但这只是一般的对象错误"消息.

I've tried displaying the actual exception but it's just a generic 'Object error' message.

当我直接通过Connection对象执行SQL查询(不带参数)时，查询执行良好并返回正确的结果集，但是当我对Command对象使用参数化查询时，查询似乎失败了.

The query executes fine and returns the correct result set when I just execute the SQL query (without the parameter) straight through the Connection object, but seems to fail when I use a parameterised query with the Command object.

据我所知，Command和Connection对象的所有设置和属性都是正确的，但是无论出于何种原因，它都会引发异常.

As far as I can see all settings and properties of the Command and Connection objects are correct but for whatever reason it's throwing an exception.

任何帮助，将不胜感激.

Any help with this would be much appreciated.

推荐答案

通常，对于ODBC和ADO，问号?用作参数的占位符.参数按照它们在Parameters集合中附加到命令中占位符的顺序进行绑定.在您的示例中，将strSQL替换为:

With ODBC and ADO, generally speaking, a question mark ? is used as the placeholder for parameters. Parameters are bound in the order they are appended to the Parameters collection to the placeholders in the command. In your example, replace strSQL with:

var strSQL = "SELECT id FROM tbl_info WHERE title LIKE ? ORDER BY id";

您仍然可以命名您创建的参数，但是它唯一的作用是以后可以通过名称来引用它(例如，使用cmd.Parameters.Item(":search")).

You can still name the parameter that you create, but the only purpose it would serve is to be able to reference it by name later (e.g., with cmd.Parameters.Item(":search")).

这篇关于使用参数化SQL查询执行ADODB命令失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！