Search code examples
delphipostgresqlsyntax-errorbde

Delphi BDE PostgreSQL query execution (syntax error)

I am using BDS 2006 with PostgreSQL for my application.
I have the following code consisting of the query for finding the Primary key values inside my Table. 

 Query.SQL.Clear;
 Query.SQL.Add('SELECT pg_attribute.attname,format_type(pg_attribute.atttypid, pg_attribute.atttypmod)FROM pg_index, pg_class, pg_attribute WHERE pg_class.oid = '+#39+'tablename'+#39+' ::regclass AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey)AND indisprimary');
 Query.Open;

I get an syntax error message 

 General SQL error.  
 ERROR: syntax error at or near ":";

I tried using #58 in place of : but results the same.
The following query works fine inside my PostgreSQL.

Any views on how do i get it working

Solution

  • 1) If you put BDE tag, then I am expecting that you used BDE + BDE ODBC SQLLink + PgSQL ODBC driver. BDE components expect ':' as param marker, and '::' as an escape sequence which is translated into ':'. You have two basic options:

    • set TQuery.ParamCheck to False and fill in Params collection by hands;
    • double every one ':', which is not a parameter marker. So, it will be '::::'.

    2) You can use 3d party libraries, like AnyDAC, which are understanding what PgSQL '::' means. So, they will not recognize '::' as a parameter marker.