--- In firebird-support@yahoogroups.com, ian <ian@...> wrote:

>
> Hi,
>
> This is a sql string formation error in Delphi, not a Firebird issue.
>
> umarko4life wrote:
> > This statement:-
> > var
> > ws_bdr_date : TDateTime
> >
> > begin
> > ws_bdr_date := StrToDate(Edit1.Text);
> > with IBQuery1 do
> > begin
> > Close;
> > Sql.Clear;
> > Sql.Add ('Select adj.ADJ_NO, adj.SRC_PLU, adj.DEST_PLU, ' +
> > 'adj.ADJ_TYPE, adj.QTY, adj.COST, ' +
> > 'adj.SELL, adj.DEST_QTY, adj.DEST_COST, adj.DEST_SELL ' +
> > 'from adj ' +
> > // 'where adj.ADJ_DATE
> > = "'FormatDateTime 'mm"/"dd"/"yyyy', ws_bdr_date) + '"' + //
> > 'where adj.ADJ_DATE = ws_bdr_date ' +
> > 'where adj.ADJ_DATE = ws_bdr_date ' +
> > ' and adj.ADJ_SOURCE = ''Stock Adj'' ' +
> > 'order by adj.ADJ_NO');
> > Open;
> > end;
> >
> > using this statement returns "Dynamic SQL error
> > SQL error code -206
> > Column unknown WS_BDR_DATE ..."
> >
> > What should I do?
>
> You have two errors here:
>
> 1.
> The statement 'where adj.ADJ_DATE = ws_bdr_date ' is telling it to
> match two columns adj.ADJ_DATE and ws_bdr_date, although the latter

is a

> variable, not a column, so it correctly gives an error.
>
> Your string should look more like one of these:
>
> 'where adj.ADJ_DATE = ' + DateToStr(ws_bdr_date) +
>
> which will insert the date value as a string
>
> or
>
> 'where adj.ADJ_DATE = ' + QuotedStr(FormatDateTime('MM/DD/YYYY',
> ws_bdr_date)) +
>
> which will format the date first. Which you use depends on the

format in

> which you store your dates in the database.
>
> If in doubt, read the Delphi help on datetime routines and SQL.
>
>
> 2.
> You can only have one "where". Replace your second and any subsequent
> "where"s with "and".

and

3. Do not dynamically build your query string at runtime unless
absolutely required because it is slower (each run requires a prepare
overhead) and it is less secure (buggy code can leave you open to SQL
injection attack vectors). Use parameters.

For eg (and snipped)

'where adj.ADJ_DATE = ' + DateToStr(ws_bdr_date)

(which would actually need to be 'where adj.ADJ_DATE = ''' +
DateToStr(ws_bdr_date) + '''' to work correctly, and even then the
shortdate format in the region settings would cause problems pretty
much anywhere outside the US)

This line can become

'where adj.ADJ_DATE = :ws_bdr_date'

Then you can just do

IBQuery1.ParamByName('ws_bdr_date').Value := ws_bdr_date;

This also works for any other field type, for any type of query. The
paramatised query does all the formatting, is region setting
independent and easier to read and maintain.

Adam