odbcconn/sqldb
lazarus / 16-Databases

----------------------------------------------------------------------
bertherngreen · 2011-03-14 20:22

Hi,

I can connect to an Informix database using odbcconn.
Also the query 'Select pk, f1 from test' succeeds.
However when I try to update f1, the ApplyUpdates fails.

The reason is that the field names in the autogenerated Update
statement, are surrounded with double quotes ("):

'update test set "f1"=? where ("pk"= ?)' fails

When I remove them in TODBCConnection.PrepareStatement by using:

Code: [Select]Buf := StringReplace(buf,'"','', [rfReplaceAll]);
before:
Code: [Select]  // prepare statement
  ODBCCursor.FQuery:=Buf;
The result 'update test set f1=? where (pk= ?)' succeeds.
Is there a property to chose if fieldnames should be surrounded by
(double) quotes?

It seems that sqldb always adds these quotes:
Code: [Select]Procedure TCustomSQLQuery.ApplyRecUpdate(UpdateKind :
TUpdateKind);

...
      sql_where := sql_where + '(' + FieldNamesQuoteChars[0] +
fields[x].FieldName + FieldNamesQuoteChars[1] + '= :"' + 'OLD_' +
fields[x].FieldName + '") and ';
  end;

  function ModifyRecQuery : string;
...
      if (pfInUpdate in Fields[x].ProviderFlags) then
        sql_set := sql_set +FieldNamesQuoteChars[0] +
fields[x].FieldName + FieldNamesQuoteChars[1] +'=:"' +
fields[x].FieldName + '",';
      end;
...
or am I wrong?

Kind regards,
Bert

----------------------------------------------------------------------
Lacak2 · 2011-03-15 06:11

Which version of FPC do you use ?
At least in version 2.5 (which I use) is already implemented in
sqldb.pp:

property FieldNameQuoteChars: TQuoteChars read FFieldNameQuoteChars
write FFieldNameQuoteChars;

So you can use it.

----------------------------------------------------------------------
bertherngreen · 2011-03-15 19:22

The problem is not how to chose between single and double quotes, but
how to chose between:

- No quotes, which I need,
- Single,
- double quotes.

I'll do it by defining const NoQuotes: TQuoteChars = (' ',' ') and
assign it to FieldNameQuoteChars.