将NULL值传递给参数化的delphi SQL Server查询

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

我试图将空值传递给TSQLDataset参数。查询具有以下形式:

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;

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.

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

I tried:

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

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

..但后来我得到一个“dbexpress驱动程序不支持tdbxtypes.unknown数据类型”错误。

I also tried:

我也尝试过:

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

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

..但后来得到“dbexpress驱动程序不支持tdbxtypes.variant数据类型”错误。

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.

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

I am using Delphi XE4 and SQL server 2012.

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

Update:

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:

感谢所有的帮助,你的建议一直都是正确的,这是产生'dbexpress驱动'错误的其他因素。我正在创建一个“灵活的”参数列表以试图解决我的问题,这导致了异常:

Parameter_string:='';

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

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

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

...这个想法是,如果B为空,那么参数将不会在查询中“列出”。

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

这不起作用,或者我的实现不起作用(不知道为什么,我显然在某个地方错过了一步)。

Anyway, the working code:

无论如何,工作代码:

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;

3 个解决方案

#1

what about:

SQL_dataset.ParamByName('B').Clear;

#2

If I recall correctly, the db-null equivalent in Delphi is Variants.Null

如果我没记错的话,Delphi中的db-null等价物是Variants.Null

#3

Usual approach would be using parameters once per query and assign the appropriate datatype. Value may be assigned to NULL.

通常的方法是每个查询使用一次参数并分配适当的数据类型。值可以指定为NULL。

var
 Query_text:String;
begin
  Query_text:='Declare @A varchar(100) ' // or e.g. integer
       +#13#10'Declare @B varchar(100)'  
       +#13#10'Select @A=:A'
       +#13#10'Select @B=:B'
       +#13#10'Update Adressen Set Vorname=@A,Strasse=@B where Name=@B';
  SQL_dataset.CommandType := ctQuery;
  SQL_dataset.CommandText := Query_text;
  SQL_dataset.Params.ParseSQL(SQL_dataset.CommandText,true);
  Showmessage(IntToStr(SQL_dataset.Params.Count));
  SQL_dataset.ParamByName('B').DataType := ftString;
  SQL_dataset.ParamByName('B').Value := 'MyText';
  SQL_dataset.ParamByName('A').DataType := ftString;  // or e.g. ftInteger
  SQL_dataset.ParamByName('A').Value := NULL;
  SQL_dataset.ExecSQL;
end;

#1