Quotes around field names in sql

rbuser

Hello,

Having just migrated from Interbase 6.02 to Firebird 1.5, a problem has
emerged with the ReportBuilder dataview sql. In some tables there is a
field called FIRST, which is not too bright because it is a reserved word.
Firebird's sql implementation requires that we reference it as "FIRST", i.e.
with quotes around it. Is there anyway to force ReportBuilder to do the
same when it creates the sql?

If not, that's ok, I will go back and rename the fields to FIRST_NAME.

Keep up the GREAT work.
Best regards, Alan

digitalmetaphors

You shouldn't try to change the source to try to add quotes around every
field. This may lead to more problems than it is worth.

If your users are going to be creating dataviews, then I would suggest
changing the actual fieldname on the database so that RB generates the
correct SQL where First is not a field name.

A work-around to leaving First as the field name, localizing this to a
certain report that uses this dataview, if you aren't going to let your
users create/edit dataviews, is to code a parser to edit the SQL before the
report is run. This will only be a run time editing of the SQL, so you won't
lose you query tools support because the report template won't be saved when
you do this. You could use the Report.Template.OnLoadEnd event to extract
the TdaSQL object, set TdaSQL.EditSQLAsText := True and reference the
TdaSQL.SQLText property. The SQLText property is a string list so you can
parse out all occurences of First and change them to "First" Here is an
example of extracting the SQL.

http://www.digital-metaphors.com/tips/ExtractSQLObject.zip


Cheers,

Jim Bennett
Digital Metaphors

rbuser

I would agree with Jim that one of the best solutions would be to
eliminate a column name that is a reserved word. However, that may not be
practical.

There is also a unit called daSQLReservedWords. In it, for each database
type is a list of reserved words (elements added to a string list).
Assuming you are still using Interbase as the database type, you could add
'First' as a reserved word. When RB encounters this word as a reserved
word, it will put quotes around it.

This does involve a change to RB source, but is only required in one place
and in a place that is designed to handle this issue.