将NULL值传递到参数化的Delphi SQL Server查询中 [英] Passing NULL value into parameterized delphi SQL server query

查看:394
本文介绍了将NULL值传递到参数化的Delphi SQL Server查询中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我试图将空值传递给TSQLDataset参数.查询的格式为:

I am trying to pass in a null value to a TSQLDataset parameter. The query has the form:

Query_text:='MERGE INTO [Table] 
             USING (VALUES (:A,:B)) AS Source (Source_A, Source_B)
             ....
             WHEN MATCHED THEN 
             UPDATE SET A = :A
             WHEN NOT MATCHED THEN
             INSERT(A, B) VALUES (:A,:B);

SQL_dataset.CommandType:=ctQuery; 
SQL_dataset.CommandText:=Query_text;

SQL_dataset.ParamByName('A').AsString:='A';  
SQL_dataset.ParamByName('B').AsString:={ COULD BE NULL, OR A STRING };    

SQL_dataset.ExecSQL;

参数B可为空,但也是外键.如果用户在此字段中输入内容,则必须对照另一个表中的值来验证B.如果为空,那么我希望将其忽略.我正在传递",但这显然会产生FK违规错误.

Parameter B is nullable, but is also a foreign key. If the user enters something in this field, then B must be validated against values in another table. If it is blank then I want it to be ignored. I was passing in '', but this obviously produces a FK violation error.

我尝试过:

SQL_dataset.ParamByName('B').Value:=Null;

..但是随后出现"dbexpress驱动程序不支持tdbxtypes.unknown数据类型"错误.

..but then I get a "dbexpress driver does not support the tdbxtypes.unknown data type" error.

我也尝试过:

SQL_dataset.ParamByName('B').DataType:=ftVariant;
SQL_dataset.ParamByName('B').Value:=Null;

..但是随后出现"dbexpress驱动程序不支持tdbxtypes.variant数据类型"错误.

..but then got "dbexpress driver does not support the tdbxtypes.variant data type" error.

不知道我在做什么错,任何帮助将不胜感激.我目前正在根据是否填充字符串来绘制参数列表,这很好用; (在我的实际查询中)这有点笨拙,因为有很多参数需要验证.

Not sure what I am doing wrong, any help would be appreciated. I am currently drawing up a parameter list based on whether the string is populated or not, and this works well; it's just a bit clunky (in my actual query) as there are quite a few parameters to validate.

我正在使用Delphi XE4和SQL Server 2012.

I am using Delphi XE4 and SQL server 2012.

更新:

感谢所有帮助,您的建议一直以来都是正确的,这是导致"dbexpress驱动程序"错误的其他原因.为了避免问题,我正在创建一个灵活的"参数列表,这导致了异常:

Thanks for all the help, your suggestions were right all along, it was something else that produced that 'dbexpress driver' error. I was creating a 'flexible' parameter list in an effort to get around my problem, and this caused the exception:

Parameter_string:='';

If B<>'' then Parameter_string:='B = :B,'

Query_text:='MERGE ...'
            '...'
            'UPDATE SET A = :A, '+Parameter_string+' C = :C' ....

...的想法是,如果B为空,则不会在查询中列出"参数.

... the idea being that if B is blank then the parameter won't be 'listed' in the query.

这不起作用,或者我的实现不起作用(不确定为什么,我显然在某处缺少了一步).

This doesn't work, or my implementation of it doesn't work (not sure why, I'm obviously missing a step somewhere).

无论如何,工作代码:

Query_text:='MERGE ...'
            '...'
            'UPDATE SET A = :A, B = :B, C = :C' ....

SQL_dataset.CommandType:=ctQuery; 
SQL_dataset.CommandText:=Query_text;

If B<>'' then
begin
  SQL_dataset.ParamByName('B').AsString:='B';
end
else
begin
  SQL_dataset.ParamByName('B').DataType:=ftString;
  SQL_dataset.ParamByName('B').Value:=Null;
end;

推荐答案

有关:

SQL_dataset.ParamByName('B').Clear;

这篇关于将NULL值传递到参数化的Delphi SQL Server查询中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆