Am Sonntag, den 21.10.2007, 12:06 -0400 schrieb Drew Jensen: > 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.
Ah okay, so the only thing to change is the way the connection is retrieved, very nice. > 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. Stored procs are beautiful because thy are fast, but you're lost when the database has to be exchanged. > 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. Very interesting and deeply elaborated concept. Although in this case I'm sure users won't do anything they shouldn't (they fear me barking at them ;) I'll keep this in mind and try it out if I find the time. I know how ... funny users can act. I remember a case some years ago where three persons were working on a file based MSA-database in the times before MSA had frontend-backend distinction. This thingy got corrupted every now and then. So as an emergency workaround I made a backup script to run by an icon placed on the desktop (using as startup took far to long) that ran automatically when booting the computers. I told those persons to use that icon for backing up when they had typed in big amounts of data or important things. Now it's your guess, how often did they use it? :-P Many thanks, Marc > > 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] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
