Hi,
I'm trying to use the firebird dbexpress driver for Kylix but I've hit a problem. We have a core table called ORDER which I realise is a reserved word and a very poor choice but that's a legacy issue I have to work around.
I can't get the driver to quote the table name. Monitoring the connection it's clearly stripping the " quotes I would normally type in the command text and I've not been able to force quoting on using the SetOptions method. (Code below with my other options selected for compatibility with the standard driver)
-----
procedure TForm1.SQLConnection3AfterConnect(Sender: TObject);
const
coUseQuoteChar = TSQLConnectionOption(202); // boolean
coBooleanDomainFields = TSQLConnectionOption(402); // boolean
coLongStrings = TSQLConnectionOption(101); // boolean
coOptimizedNumerics = TSQLConnectionOption(401); // boolean
begin
with TSQLConnection(Sender) do
begin
SQLConnection.SetOption(coUseQuoteChar,Integer(True) );
SQLConnection.SetOption(coBooleanDomainFields, Integer(False));
SQLConnection.SetOption(coLongStrings, Integer(False));
SQLConnection.SetOption(coOptimizedNumerics, Integer(False));
end;
end;
-----
I'd seen references to checking the quote character using the metadata property of the TCDSQLConnection but I don't seem to have that component available.
Any pointers greatly appreciated.
UseQuoteChar not working in Kylix
Re: UseQuoteChar not working in Kylix
The error is not due to the SQL-statement being executed (the 'SELECT * FROM "ORDER"' statement is executed successfully and quotes are not stripped), but it arises when the SQLQuery.RecordCount method is executed.
When the SQLQuery.RecordCount method is executed, dbExpress automatically builds an SQL-statement like 'select count(*) from TABLE_NAME'.
For quoting the table name dbExpress uses the quote character which is requested from the driver. The point is that dbExpress requests the quote character before the TSQLConnection.AfterConnect event fires. So, it retrieves the empty quote character, because the UseQuoteChar option is False at the moment. This is the specificity of dbExpress and we can not influence on this behaviour.
To avoid the problem you can try one of the following approaches:
- avoid working with the ORDER table using TSQLDataSet methods that will cause the automatic SQL generation (RecordCount, ApplyUpdates) and use explicitly built SQL-statements for working with the table;
- use the TCRSQLConnection component that is located on the "dbExpress" tab of Tool Palette instead of TSQLConnection. With TCRSQLConnection the error does not arise.
When the SQLQuery.RecordCount method is executed, dbExpress automatically builds an SQL-statement like 'select count(*) from TABLE_NAME'.
For quoting the table name dbExpress uses the quote character which is requested from the driver. The point is that dbExpress requests the quote character before the TSQLConnection.AfterConnect event fires. So, it retrieves the empty quote character, because the UseQuoteChar option is False at the moment. This is the specificity of dbExpress and we can not influence on this behaviour.
To avoid the problem you can try one of the following approaches:
- avoid working with the ORDER table using TSQLDataSet methods that will cause the automatic SQL generation (RecordCount, ApplyUpdates) and use explicitly built SQL-statements for working with the table;
- use the TCRSQLConnection component that is located on the "dbExpress" tab of Tool Palette instead of TSQLConnection. With TCRSQLConnection the error does not arise.