将NULL值传递到参数化的Delphi SQL Server查询中 [英] Passing NULL value into parameterized delphi SQL server query
问题描述
我试图将空值传递给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屋!