Frustrations with the BDE
Kevin Davidson
Part of the frustration lies with my expectation. I expect to write standard SQL and have the BDE make it work on any database it supports (presuming there is SOME way the database can be made to do what I want). In practice, I find that I have to make all kinds of database-specific code in my applications to handle different databases or the way the BDE interacts with different databases.
Let me give some examples:
- Creating Primary Keys: The BDE doesn't support creating a table with the SQL CREATE TABLE...PRIMARY KEY clause. This means in effect that different (non-SQL) code has to be used to create primary keys on Paradox and Access databases than is used on a SQL database like SQL Server.
- Primary Key Information: The fact that a table has a primary key is not returned for ODBC data sources, so special code (if I can figure out how to write it) is required to support ODBC vs other data sources.
- The Length of supported Correlation Names varies from driver to driver, but in all cases (I know of) less than the length of a field name. And field names supported must be shorter than most databases support.
- Renaming Tables: Depending on the database, one can rename a table with SQL, rename with a stored procedure, rename with a TDataSet method. Nothing I know of works for all databases.
- Many basic SQL features are not supported by the BDE (meaning they don't work in Paradox) such as parenthesis to group Joins, Primary Key clause in the Create Table and many more that I've forgotten.
- The BDE doesn't handle the differences between databases which support Logical data types and those that don't. The AsBoolean property handles this in Delphi, but the BDE doesn't do similar processing when the SQL statement has a "= True" in a where clause.
- The BDE doesn't handle the weirdnesses between Paradox Levels. For example, you can use SQL to create a Paradox table, and then try to use SQL to add a Unique secondary index; it will fail because the Level of the Paradox table is too low.
- And let's not even start on BLOBs.
Kevin Davidson,
HumanSoft LLC
Steve Koterski
<kwda...@worldnet.att.net> wrote:
[...]
>Part of the frustration lies with my expectation. I expect to write
>standard SQL and have the BDE make it work on any database it supports
>(presuming there is SOME way the database can be made to do what I
>want). In practice, I find that I have to make all kinds of
>database-specific code in my applications to handle different databases
>or the way the BDE interacts with different databases.
>
>Let me give some examples:
>
> * Creating Primary Keys: The BDE doesn't support creating a table
> with the SQL CREATE TABLE...PRIMARY KEY clause. This means in
> effect that different (non-SQL) code has to be used to create
> primary keys on Paradox and Access databases than is used on a SQL
> database like SQL Server.
What version of the BDE are you using? The latest (version 5) supports it,
as well as the preceding versions, 4.x. Local SQL (what the BDE uses for
Paradox, dBASE, and FoxPro tables) supports two variations on syntax for
creating primary indexes with the CREATE TABLE statement. One variation
uses just the PRIMARY KEY keywords.
CREATE TABLE "KevinDavidson.db"
(
ID CHAR(3),
Data CHAR(10),
PRIMARY KEY (ID)
)
The other variation uses the syntax CONSTRAINT..PRIMARY KEY. (Paradox
primary indexes never have names, so this name for a constraint is ignored
in all subsequent operations.)
CREATE TABLE "KevinDavidson.db"
(
ID CHAR(3),
Data CHAR(10),
CONSTRAINT xyz PRIMARY KEY (ID)
)
Microsoft Access is another case. Fairly well known for a number of
deviations from SQL standards (# characters to enclose DATE literals,
square brackets for enclosing table and column names, a number of
nonstandard DML functions, and more), SQL to Access is pass-through.
Pass-through SQL is not affected by the SQL the BDE does or does not
support, but is subject to the limitations and capabilities of the database
back-end. If you have grievances with Access SQL, you must address them to
Microsoft. This cannot be controlled from Delphi or the BDE.
Similar principles apply to other, non-local database types. The SQL will
be pass-through or (in the case of live queries) requires strict compliance
with the SQL-92 specification.
> * Primary Key Information: The fact that a table has a primary key is
> not returned for ODBC data sources, so special code (if I can
> figure out how to write it) is required to support ODBC vs other
> data sources.
Not familiar enough with ODBC and operating with it to comment on this one.
I can say that anecdotal reports I have seen in these newsgroups tend to
indicate a great deal of variance in functionality, between different ODBC
drivers and even between different versions of the same ODBC driver. (Visit
the SQL Servers newsgroup for threads on this one.)
> * The Length of supported Correlation Names varies from driver to
> driver, but in all cases (I know of) less than the length of a
> field name. And field names supported must be shorter than most
> databases support.
True for Paradox and dBASE tables. Column correlation names are limited to
the maximum size the table type supports (taking into consideration the
table level), not exceeding 25 characters. For example, if the LEVEL
setting in the BDE configuration for the dBASE driver is set to 5, column
correlation names are limited to 10 characters. If level 7, to 25
characters. Level 5 dBASE tables only supported 10 character wide column
names. Level 7 tables support column names up to 31 characters (but the
local SQL limit of 25 is reached first).
Do you need column correlation names that exceed 25 characters? Enter this
on the bug reporting page (which is not just for bugs, but for suggestions
too).
http://www.inprise.com/devsupport/delphi/delphi_bugs.html
Bear in mind that this is not insurmountable. Unless you are allowing your
end-users the ability to enter SQL statements manually, they never need to
see actual column names (or column correlation names). So there really need
not be a direct correlation to what the columns are called and what the
application user sees. The only data-aware control that by default displays
actual column names is the TDBGrid. And even then, you can alter what the
end-user sees using such properties as TField.DisplayName and
TColumn.DisplayName.
And, again, when using other than BDE-native local tables and pass-through
SQL, the rules are subject to variations.
> * Renaming Tables: Depending on the database, one can rename a table
> with SQL, rename with a stored procedure, rename with a TDataSet
> method. Nothing I know of works for all databases.
Can you cite the SQL statement you used to rename a table? I am not
familiar with a single statement in the SQL-92 specification that has this
capability. True, multiple statements can be used to do this, in steps. But
in a single statement?
Certainly, local SQL (which is a subset of SQL-92) does not include such a
statement.
And, again, when using other than BDE-native local tables and pass-through
SQL, the rules are subject to variations. Some database systems may support
renaming tables in situ, other will require you drop and then recreate
them.
> * Many basic SQL features are not supported by the BDE (meaning they
> don't work in Paradox) such as parenthesis to group Joins, Primary
> Key clause in the Create Table and many more that I've forgotten.
When I rewrote the local SQL online help file for BDE 5, I used a number of
different references and how-to books that were based on the SQL-92
specification (I can cite if you need it). I did this to empirically test
the syntax -- statement by statement and in variations -- so that the help
file would be as accurate in its representation of the BDE's implementation
of SQL as was possible. In none of those references did I see statements
where parentheses were use to prioritize or group join operations. That is
not to say that this cannot be done or that it is not part of SQL-92 (or
other commonly accepted SQL standard). I just never saw it.
I can verify that this is not supported in local SQL.
And, again, when using other than BDE-native local tables and pass-through
SQL, the rules are subject to variations.
> * The BDE doesn't handle the differences between databases which
> support Logical data types and those that don't. The AsBoolean
> property handles this in Delphi, but the BDE doesn't do similar
> processing when the SQL statement has a "= True" in a where clause.
The specifications for BDE-native local tables include true Boolean column
types, so this is moot for them.
For nonnative tables types, the ability to do this is subject to a few
considerations. The first consideration is whether the query is live
(updatable) or read-only.
Live queries are evaluated by the BDE before being sent off to the database
back-end. This is done to ensure that the SQL statement strictly complies
with SQL-92. The language set used for this evaluation is separate from and
more all-encompassing than local SQL (see the list of reserved words in the
local SQL help file for an idea of the extent of this language set). This
compliancy is needed so that the BDE is able to use that SQL statement to
compose the necessary background update statements necessary to apply
changes to the updatable result set.
Most database back-end systems support SQL-92 syntax (though typically with
a number of added language extentions unique to the specific database
system). This enforcement of strict SQL-92 compliancy ensures the greatest
commonality of SQL syntax with the greatest number of database back-ends
that can be accessed through the BDE.
Read-only queries are true pass-through. They are not first evaluated by
the BDE, just passed as a string from the application to the database
back-end. Because the BDE does not evaluate the SQL, you are subject to the
SQL supported by the specific database back-end used.
I am not aware of any SQL-92 standard for a standard of treatment of
various column data types as BOOLEAN -- especially as SQL-92 does not
include a BOOLEAN data type. So while you might have a SMALLINT column that
is to act as a BOOLEAN column, SQL-92 would have you make comparisons
treating it as a SMALLINT.
And, this could vary from one database vendor's implementation to the next.
> * The BDE doesn't handle the weirdnesses between Paradox Levels. For
> example, you can use SQL to create a Paradox table, and then try to
> use SQL to add a Unique secondary index; it will fail because the
> Level of the Paradox table is too low.
The BDE has the capability to handle this, but does not do so
automatically. It is up to an application to either automatically handle
this (using BDE API) or to generate an error. This provides maximal control
over this type situation. (I personally tend to be an anal-retentive
control freak when it comes to my code and anything happening automagically
<grin>.)
Take for example adding a descending index to a Paradox 4 table (this level
table did not support this type index). In the Database Desktop utility
(DBD), the restructuring is handled somewhat automatically. You see a
Restructure Warning dialog indicating a needed structure change (a higher
table level) and asking if you want DBD to do this. But when trying to do
the same thing in a Delphi application (such as with TTable.AddIndex), all
you get is an exception. You, the programmer, must handle it from there.
The difference in how this is handled by the BDE versus a Delphi
application is that the DBD (as a BDE-using application front-end) is
programmed to handle this.
The differences in the various levels of dBASE and Paradox tables has more
to do with the dBASE and Paradox table specification than with the BDE.
Over the years (1982 for dBASE, 1984 (?) for Paradox), improvements in
these products have included additional features in the specification for
their respective table types. To accommodate legacy applications that use
older versions of dBASE or Paradox tables, there has to be differentiation
between the various levels. We could not just replace whatever existed
previously with one level of updated table files. We would have an angry
horde of torch-bearing villagers clamoring at the gates <visions of the
original Frankenstein movie>. There are just too many such legacy
applications out there. And older versions of the dBASE and Paradox
software just cannot use many of the newer types of these table files.
> * And let's not even start on BLOBs.
Please do (start on BLOBs). Let's address the questions and problems.
Perhaps I can solve some or all of your issues.
---------------------------------------------------------------------
All of what I posted here was meant to be informational and to hopefully
shed some light on what is behind your issues. It is not meant to be either
argumentive or confrontational, please do not take this post as such. This
should be a dialog; no more, no less.
Very frequently in this post I have pointed out that the SQL syntax
available to you will vary from one database back-end system to the next.
This was a major complaint that you had, too. But the reality of the
situation is that you will see this in pretty much every programming tool
intended to support a variety of database types -- Delphi, Visual Basic,
Visual C++, whatever. The attempts to come up with industry-accepted
standards and norms for the SQL language (SQL-92, SQL2, SQL3) was a good
idea, but things have not worked out as intended. Nearly all database
systems have greater or lesser variations on or deviations from the
established standards.
It would be difficult or impractical to the point of being impossible for a
front-end application programming tool to be able to accurately translate
to and from all these variations of different database products, and even
versions and subversions of the same database. Unless someone is able to
universally and strictly control what SQL database vendors use in their
products, application programmers are going to always face having to deal
with differences in SQL implementations.
One other thing to remember is that INPRISE (through the BDE) has no
control over what SQL language set a database system uses, except local
SQL. For all other database types, the vendor has control.
//////////////////////////////////////////////////////////////////////////
Steve Koterski "The knowledge of the world is only to
Technical Publications be acquired in the world, and not in a
INPRISE Corporation closet."
http://www.inprise.com/delphi -- Earl of Chesterfield (1694-1773)
Kevin Davidson
things and I'm going to study the content carefully and may have further
comments.
One issue surfaced in reading the material, and that was that things that were
once unsupported in the BDE became supported later without my knowing about it.
I don't know if this due to my negligence or the information not being
available. Probably BEFORE BDE 5.0, it was more of the latter. I'm sure
everyone who works with the BDE appreciates Steve's re-write of the Local SQL
Help File. My problem with that document is that Inprise's description of the
differences between BDE 4.51 and 5.0 focus on features that I don't use rather
than talking about changes in the core functionality. So I don't know what in
the 5.0 Local SQL Help file works in 4.51 and what doesn't. I've backed off on
installing BDE 5.0 because of the performance problems with Paradox mentioned
frequently in this newsgroup. My AP is slow enough already (it does HUGE
amounts of database stuff) without taking a new performance hit.
Steve Koterski wrote:
> On Thu, 13 Aug 1998 14:24:20 -0400, Kevin Davidson
> <kwda...@worldnet.att.net> wrote:
>
Kevin Davidson
Steve Koterski wrote:
> On Thu, 13 Aug 1998 14:24:20 -0400, Kevin Davidson
> <kwda...@worldnet.att.net> wrote:
>
> ...
>
> > * The BDE doesn't handle the weirdnesses between Paradox Levels. For
> > example, you can use SQL to create a Paradox table, and then try to
> > use SQL to add a Unique secondary index; it will fail because the
> > Level of the Paradox table is too low.
>
> The BDE has the capability to handle this, but does not do so
> automatically. It is up to an application to either automatically handle
> this (using BDE API) or to generate an error. This provides maximal control
> over this type situation. (I personally tend to be an anal-retentive
> control freak when it comes to my code and anything happening automagically
> <grin>.)
I disagree strongly here. If I enter a SQL CREATE TABLE which is correct
according to the Local BDE syntax, and then I enter a SQL ADD INDEX which is
correct according to the Local BDE syntax, I shouldn't have to handle an error
condition when the only error is that the BDE created the table in such a way
that it can't support its own syntax. If the BDE claims to be able to handle
the ADD INDEX statement, then it darned well needs to be able to add the index,
and to do whatever it takes to add the index. If I am writing application code
which adds an index, then I already know that I REALLY want to add the index
and I don't need a database to ask me "do you really want to add the index?" If
the developer has to write huge amounts of exception code, then the claim that
the BDE supports the ADD INDEX is false.
When I look under "CREATE INDEX" in the BDE 5.0 Local SQL help file, there's
nothing about Paradox Levels. When I look for "PARADOX LEVEL", there's nothing.
Oh, I should be looking in the BDE API help file? Why am I having to use the
BDE API just to add an index using the syntax that Local SQL supports? And I
spend half an hour pouring through the BDE API help and didn't find any
function or reference on how to upgrade the level of a table anyhow.
Kevin Davidson
Steve Koterski wrote:
...Please do (start on BLOBs). Let's address the questions and problems.
Perhaps I can solve some or all of your issues.
- Depending on which database driver and which database and what type of blob field one is using, the BDE reports the field length differently--either as 0 or 1. When working in the Delphi IDE, a TBlobField is created with length either 0 or 1 and depending on what real database one is working with. Then at run time, the application will crash because it detects a difference in the length of the field that the BDE returns and what is in the TBlobField. I seem to recall that Paradox reports the length of a memo blob as 1 and MS SQL Server via SQL Links reports it as 0. Binary Blobs are both 0, I think.
- Blobs won't update when using Cached updates.
- Blobs don't update at all when using certain database drivers (e.g. IBM Client Access/400 ODBC).
Steve Koterski
<kwda...@worldnet.att.net> wrote:
>One issue surfaced in reading the material, and that was that things that were
>once unsupported in the BDE became supported later without my knowing about it.
>I don't know if this due to my negligence or the information not being
>available. Probably BEFORE BDE 5.0, it was more of the latter. I'm sure
>everyone who works with the BDE appreciates Steve's re-write of the Local SQL
>Help File. My problem with that document is that Inprise's description of the
>differences between BDE 4.51 and 5.0 focus on features that I don't use rather
>than talking about changes in the core functionality. So I don't know what in
>the 5.0 Local SQL Help file works in 4.51 and what doesn't. I've backed off on
>installing BDE 5.0 because of the performance problems with Paradox mentioned
>frequently in this newsgroup. My AP is slow enough already (it does HUGE
>amounts of database stuff) without taking a new performance hit.
The rewrite of the local SQL help file I did involved testing under both
version 4.51 and pre-release versions of BDE 5.
The only local SQL syntax difference I have encountered so far (4.51 versus
5) is the automatic translation of data types across different tables in a
UNION join (discussed in another thread in this newsgroup, "UNION
compatible types"). This change in functionality was not due to any change
in local SQL, but to internal changes in the BDE itself. As it works out,
the change requires stricter data type handling, actually resulting in an
increased compliancy with SQL-92.
The rest of the local SQL language should function the same whether you
have version 4.51 or 5 of the BDE.
This does not, of course, take into consideration any changes in the table
specifications (Paradox and dBASE), these being independent of SQL in their
original. Different table levels quite often require special handling (such
as the level 4 Paradox table not supporting unique secondary indexes,
though the current implementation of local SQL does support the syntax to
create them).