Marc Santhoff wrote:
Hi,
I'd like to know how base handles transaction regarding the underlying
database.
>From some documentation (IDL-ref or dev guide) I know a newly created
connection is set to autocommit by default.
Does bases connection layer handle it this way?
Or is it switched in the database directly?
Can I switch a connection to explicit commit without influencing other
connections to that database (i.e. on the client side, not the complete
db)?
You can not switch the default connection to explicit commit. In order
to do this you must use an isolated connection. In the IDL reference you
can look up the information for getIsolatedConnection instead of
getConnection.
In other words to use explicit commits / rollbacks with an embedded
database you must create the isolated connection explicitly. This can be
in a Basic macro, or an external application. You can not do this if you
just open an ODB file, and then open forms from it via the GUI.
Working with this isolated connection however is pretty easy. For
example the loadFromURL command to open forms takes a connection as one
of the parameters, so and then your statements and prepared statements
are all methods of the connection so those.
In practice what I do is this. Setup the odb file, build the forms,
queries and reports. Once I have the basic functions of the screens as I
want them, then I create a small basic library to handle the overall
form management and transaction control. In essence I treat the ooBasic
procedures as I would stored procedures in say Oracle. ( not quite the
same but you get maybe 80% of the functionality ). The forms can still
use all the data aware UI controls.
Now the down side here is that if someone opens the ODB file directly
they can work on the tables directly, or via forms without the
transaction control. So here is one way around that.
Create a new ODB file, before you do anything else open the SQL window,
and using all DDL / DML commands do the following.
Create a user account or accounts if you like.
Make the first user you add have DBA privlidges
Logon as the new user
Create a new schema
Change to use the new schema
Revoke the DBA privileges for user SA
Close the SQL window and build the application.
DO NOT remove user SA, or you cannot open the odb file directly anymore
( you can add the user account back by direct update to the internal
files in the odb files if you accidentally do that however :-(
Anyway it is not a way to make an embedded totally secure, but it will
thwart most casual users. They open the file and see the forms, queries
and reports but no tables. They can open the forms, queries and reports
but they get a connection failure, since SA has no access to the schema
where the data tables is.
Finally a simple wrapper Basic library to handle the user name password
request for working with database, and then to set the correct schema
makes for a nice little database application, that is reasonably secure
for a normal office environment.
For the curios some substantial info:
I'm preparing a database application using the internal HSQL. This
thingy will run with another backend if it is ready (Postgres, maybe
Firebird).
For some products in this db there has to be a check on order if the
stock count is sufficient to fulfill the order. If not, entries in a
table storing order requests have to be made for those parts not in
stock in adequate amount.
This stock checking is to be run "all or nothing", so if one
"insert"-statement fails, the whole thing must be rolled back.
This would be implemented perfectly easy using stored procedures, but
since it has to be database independant I do with a script in the
database currently.
TIA,
Marc
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
