This is for Paradox v7, 32-bit.

For most operations I've seen, whether a tablename and its corresponding
extensions are uppercase or lowercase makes no difference. However, if you
generate the SQL from a query, it does make a difference. If all tables have
lowercase extensions, everything is fine. If all tables have uppercase
extensions, everything is fine. However, if tables used in the same query,
and therefore same SQL statement, have mixed case extensions (at least one
with uppercase; at least one with lowercase), this causes the sql statement
not to run, and "invalid field name" is returned. This is because Paradox is
case-sensitive, and therefore creates table aliases of different cases but
otherwise the same in the resulting sql statement. Here's an example:

SELECT DISTINCT D.IncidentNo
FROM "INCIDENT.DB" D, "test.db" d
WHERE
(d.TestNo = D.IncidentNo)
ORDER BY D.IncidentNo

The table "INCIDENT.DB" has uppercase extension (.DB), and the table
"test.db" has lowercase extension (.db). [Note that I've already narrowed
the issue down to the extension, not the tablename itself.] Notice how the
table aliases are "D" and "d". This is fine in the actual query, but in the
SQL editor, these are seen as the same, so the message "invalid field name"
is returned because the field "TestNo" does not exist in the "INCIDENT.DB"
table.


I guess my bottom line question is this: is there a setting to make sure all
tablenames/extensions generated are uppercase? Also, does anyone know why
tablenames would sometimes show in uppercase (say, when selecting a table
for a new query), and sometimes show in lowercase, such as in the Project
Viewer. Shouldn't they always show consistently?

The above issue isn't really a big deal for me, but a customer has been
using the SQL statements from numerous queries, and has run into this issue.
I'm trying to get as much information as possible on this so I can present
the best way for him to proceed without having to manually change every SQL
statement generated from the queries.

Thanks.



Corey

I'm not aware of any solution.

I have this in my buglist, but only for remote databases. It's bug PX0705.
"Running a QBE file with remote tables based on native drivers for Sybase
ASE or MS SQL Server, where two tablenames begin with same letter but with
different case, like dbo.uppgkod and dbo.Underlag, will cause a "General SQL
Error" when executed with setting 'Run query remotely', but not with setting
'Query may be local or remote'. The error is caused by Paradox' translation
of QBE to SQL, where the first letter in the tablename is used as table
alias in the SQL statement.

select ...
from dbo.uppgkod u, dbo.Underlag U

This translation is not accepted by the remote database.

If the query is translated to SQL using SQL button on the toolbar, same
table aliases are created, and the SQL statement will also cause a
GeneralSQL Error.

Using ODBC driver for MS SQL Server instead, the problem does not occur. "

--
Bertil Isberg - CTECH
Paradox buglist:
online: http://hem.bredband.net/bertilisberg/

"Corey" <czinn (AT) makromed (DOT) com> skrev i meddelandet
news:137vt8h4ipcgbf6 (AT) corp (DOT) supernews.com...