How to pass NULL value from Unidac Query component in Delphi?

Question

When I am using StoredProc component in Delphi using ParamByname('ParamName').Clear I'm able to send NULL value.

But how can I pass NULL value when using a Query component?

with Query do
begin
 SQL.ADD('exec d_upd_calc'+Quoted(EditCalc.Text));
end

In the above scenario I want to send NULL if the edit box is blank.

I am using Delphi 2010, Unidac with Sybase.

score 2 · Accepted Answer · answered Nov 22 '11 at 17:13

2

Even in Queries you can work with parameters:

Query.SQL.Text := 'exec d_upd_calc :myparam';
Query.Prepare;
Query.ParamByName('myparam').Clear;

And it's better to use parameters than to build the complete string, because you must not handle quotes and avoid security leaks via SQL-injection.

answered Nov 22 '11 at 17:13

Michael

858
1
5
11

Addition: you may need to specify parameter data type `DataType := ftString` or how else. – da-soft Nov 22 '11 at 17:43

Heinrich Ulbricht · Answer 2 · 2011-11-22T17:18:17.277

1

With Advantage DB I would do something along these lines:

var
  sqlText: string;

with Query do
begin
  if EditCalc.Text = '' then
    sqlText := 'exec d_upd_calc NULL' else
    sqlText := 'exec d_upd_calc '+Quoted(EditCalc.Text);
  SQL.ADD(sqlText);
end;

If the keyword is also NULL then this should work.

Does Quoted remove/escape any dangerous user input to prevent SQL injection? If yes then it's good. If not then it should.

edited Nov 22 '11 at 17:18

answered Nov 22 '11 at 17:09

Heinrich Ulbricht

10,064
4
54
85

if Trim(CalcType.Text) <> '' then Calc:= Quoted(Trim(CalcType.Text)) else Calc := 'Null'; Before add to SQL am giving as Quoted..So it is not giving issue now..thanks – SSE Nov 22 '11 at 18:16

How to pass NULL value from Unidac Query component in Delphi?

2 Answers2