What we have here is a need for a complete paradigm shift. I found that
a big conceptual jump when I made it, but it will really make life
easier in the long run.

BDE is a wrapper around ISAM file databases such as paradox and dBase.
It manages file level locking, head movements, and so on. These actions
are directly mandated by your application. BDE is comfortable in this
world, but fares poorly in the RDBMS world because it can't conceive of
not holding the hardware control in the application.

Firebird (and DB2, Oracle, Interbase, ADabas, MySQL, PostGres, etc) are
transactional relational database managers. Under the covers, they are
based on ISAM technology, but your application and technology wrappers
(IBO, IBX, DBExpress, etc) hav no awareness or contact with that level
of operation. The DBMS is a separate application handles and hides all
of the ISAM work.

Your application issues commands in the form of strings that express the
SQL language to the DBMS application. The essential commands are
insert, select, update, and delete.

In the BDE ISAM world, because you are directly connected to the
hardware storage, you can identify a block of records, and essentially
modify them directly on the storage medium.

Since the storage interactions are actually carried out by a separate
application in the RDBMS world, your application must issue the
appropriate commands first to select copies of the data from the storage
medium, then once you have edited the copies, submit each record back to
the server with its correct command string. Your application only sees
copies of the data.

This is over-generalized, since there are many extensions to the basic
RDBMS model, but it may give you some insight into why the BDE model
must be abandoned if you are to go to a database server.

In the ISAM world, you spend a lot of effort making sure that your
record is locked while you do your work, then relatively little effort
to write it back.

In the server based RDBMS world you do the opposite. You avoid locking
your records when possible, keeping them in some sort of briefcase model
data object (value objects, pascal records, client datasets, etcetera).
Then you expend a great deal of effort in your code to write those
altered records back to the database.

To the OO purist, the keyword is CRUD - Create, Read, Update, and
Delete. Each of those operations is represented by its own query and
wrapper methods.

With the ISAM world, you managed cursors into the disk based tables
directly. With the transactional RDBMS world you retrieve a result set
(typically read only) and may cursor through your transient copy of the
records for reading, but you must explicitly write your changes back.
You have no direct connection between your application and the hardware
implementation.

Those of us who have been around a while sometimes forget how difficult
it is to make that first conceptual leap. Since applications talking
directly to the hardware through ISAM has been a dead end for a decade
or so, so it is easy for us to get impatient and forget what it is like
to make the initial jump.

You will need to start small and do some exploratory coding if you are
going to get past the initial learning curve and become comfortable with
any transactional database server. Initially, it will feel like you are
doing a lot of work for very modest returns. However, when you realize
that you are no longer getting paged at 1:00 AM every night for the
application that almost works but has failed again, you will suddenly
become a convert.

Also, your code construct is begging for poor performance. With many
queries, the largest portion of time is spent in the prepare. The "with
new TQuery.Create (NIL) do ..." construct means that the query has to be
parsed and prepared every time it is used, and you cannot gain
efficiencies from parameterization.

I would love to spend the time to help you out, but I am much better
with pictures than words. I just can't explain it well in email.

Does anyone know of a good primer on transactional database system
design principles that would approach the question from a perspective
that an old style ISAM programmer would grasp quickly?

Hope this helps,
David Johnson

> >
> > I tried with the instruction "SQL.Add('SET TRANSACTION');" or with the other one that is commented.
> >
> > Can anybody tell me what4s wrong in what I4m doing?
>
> Gustavo - ExecSQL does not know WHAT you are going to do in the SQL so
> the first thing IT does is create the transaction. It's years since I
> used BDE, but I am fairly certain that you can not get round that fact.
> You need an interface that talks directly to the database - which BDE
> never did.
>
> --
> Lester Caine
> -----------------------------
> L.S.Caine Electronic Services
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>