TdaSQL Error when quoting Reserved word for MySQL

rbuser

Hi,

A suggestion:
RB7, daSQL.pas, line 5364, method:
procedure TdaSQL.NameToSQLString(var aName: String);
begin
if (Length(Trim(aName)) = 0) then Exit;

if daContainsInvalidChars(aName, FDatabaseType) or
IsSQLReservedWord(aName) then
begin
if (IsMSAccess) or (IsApollo) then
aName := '[' + aName + ']'

// ADDED
else if IsMySQL then
aName := '`' + aName + '`'
// END ADD

else
aName := '"' + aName + '"';
end;
end; {procedure, NameToSQLString}
---

Currently RB double quote reserved words for MySQL which results in wrongly
compiled SQL which in turn results in SQL error messages from most MySQL
servers rendering the reports useless. MySQL uses the backtick/reverse
quote to quote columns, tables and databasenames which clash with a reserved
sql word. Another suggestion would be to make a function for a TdaSession
(which can then be overriden) for example QuoteReservedName.

Regards, Jacques

nardmoseley

Thanks for the information. Will using a single quote work for all mySQL
servers or is this configurable? Or perhaps it varies by version? Please
clarify.



--
Nard Moseley
Digital Metaphors Corporation
www.digital-metaphors.com

rbuser

Hi,

Firstly, it is not a single quote, it is a reverse single quote (backtick).
Here they are next to each other '`, the correct one is the latter and is
usually found on the same key as the ~ on your keyboard. To answer the
question, yes it will work for all MySQL servers post version MySQL 3.23.4
(which was released about 4 years ago, and since then some 63
revisions/versions have been released). Prior to version 3.23.4 there was
no way in MySQL to "mark" column/table/database names if it clashed with the
sql language. The stable (and most widely used MySQL today) is version
4.0.21, and thus this change will pose no problem for RB. If someone is
still using such an old MySQL, which they would have no reason to do as the
server and upgrading is free, then they would be used to not being able to
use reserved words for columnnames, because if they did, none of their sql
(nevermind report sql) would work.

Regards,Jacques

nardmoseley

Jacques,

Thanks for the clarification and for sharing your knownledge on this. We
will incorporate your recommended modification into the next release.


Cheers,

--
Nard Moseley
Digital Metaphors Corporation
www.digital-metaphors.com

rbuser

I have a similar issue. We are using a TDataBase to access a folder
containing dBase files via ODBC. When some fields with "reserved word" names
are selected then the query wizard generates the following error: "Invalid
SQL statement. General SQL error". The generated SQL uses double quote for
reserved words..

This are my DataSettings property values (on TppDesigner)
CollationType: ctASCII
DatabaseType: dtParadox
SQLType: sqBDELocal

We are not using DataDictionary nor DataPipelines.

Can you tell me what is wrong with my approach?

I think that Jacques Venter post is closely related to my problem.

nardmoseley

ReportBuilder's support for BDE Local SQL works correctly. Local SQL refers
to the Borland Paradox engine. No ODBC is used. Try using the Standard
driver.

A better solution for DBase files is to use the Advantage database engine
available from Extended Systems.

rbuser

Hi Nard.

Our reporting solution have some limitations/issues to deal with:

1. The database does not exist when the user is designing a report template.
2. There are not a database server or another third party software when the
report templates are loaded and run. (This is not an option to us).
3. When the end user select a report template to run, then the dbf files are
created dynamically in a folder specified by the user and with names derived
from a root name given by the user.

Our solution to this is:
1. We create an ODBC dynamically writing the respective entries on the user
Windows registry, and set a fixed alias name to that ODBC which is
associated to a fixed folder with a group of dbf files which represents an
example of the final database, so the user could design it's reports with
something real even if the database does not exist yet. Every "design table"
matches one possible real table.

2. We associated the alias name of the ODBC to the AliasName property of the
TDatabase object and with the DataSettings.DatabaseName property of the RB
designer. So when the user wants to create a new report template the Data
workspace points to our "Design Database".

3. When the end-user wants to run their templates we change the entries on
Windows registry to set the same ODBC to point to the location of the
generated dbf files location and then dynamically change the names of the
tables in the template dataviews to the names of the tables that must be
used to generate the report.

4. For the switching process between "design database" and "real database"
we clean the RB metadata everytime.

All seems to work very well. Now, we have some tables with fields named like
"TIME", "DATE", "DROP", "COUNT", etc. When the user is in "Design mode" if
he select some of this fields the designer throws the error: "General SQL
Error".
We try to set designer.DataSettings but every combination that we test fail.
Now I'm confuse what is the problem because the SQL generated by the RB
seems to be Ok.

Can you help us on this issue. I'd appreciate any suggestion that you could
give us.

Note: We could not use "DataPipeline" or "DataDictionary".

rbuser

Hi. I resolve my problem.
Apparently when we create our ODBC we use: "Microsoft dBase Driver
(*.dbf)", so we change the settings of the designer to the following:

CollationType: ctANSI
DatabaseType: dtMSAccess
SQLType: sqSQL1

Now all the fields could be selected without any problems. We could not
changed the ODBC because it is being used in other parts of the software. It
seems that all is working fine but I don't know if our change could harm
some other functionality of the RB.

I want to apologize by the time that you lost on this subject. Also I want
to thank you for your support. Definitely DM has an excellent support team.