Workspace and transaction question

[carolyn]

I'm writing multiuser application using VB5 SP3 with DAO connects to PostgreSQL as database via ODBC.

The application allows user to update datbase. At the same time, there might be some timer events that import data files into the database without user interventions.

Should the application has one global workspace and connection object for all transactions? Or should I create a different workspace and/or connection object for each database queries (select, insert, update etc)?

If i'm using the global method, will the Begin/Commit/Rollback transaction for updating table A affect the transaction on table B? Since these methods are workspace properties.



Edited by carolyn on 03-07-2000 at 11:55 PM

[Clunietp]

Use a single connection to your data source. If you keep opening and closing connections, it just wastes time, and your DBMS should be able to handle the concurrency.

The Begin/Rollback/Commit should only affect table B when you are directly manipulating Table B or when there is referential integrity involved....

[pardede]

I have to disagree on one point from ClunietP. The BeginTrans/Commit/Rollback is global to the workspace, thus all tables and recordsets opened withing the workspace is affected. If you are busy with a transaction on table A and do a commit on table B, table A also commited. So my suggestion is, use different workspaces for every isolated transaction you want. This topic is explained in detail in the books online, by the way.

[Clunietp]

Thanks Pardede -- I've been in the ADO/SQL server mindset for a while and have pretty much abandoned DAO..... I suppose once I relate DAOs WORKSPACE object with ADOs CONNECTION object, the situation you described makes sense. I'll keep that in mind for the future, thanks!

Tom

[carolyn]

Thank you for your replies and clear up some of my queries. :)

I'm just not sure if it's a common practice to create a workspace for every form (each form has its own transactions) and doing so will affect performance or not.

[pardede]

I don't know what the exact effect is of opening and closing workspaces (databases, recordsets etc..) for performance, but my policy is always: only access a database when necessary and keep the accessing time as short as possible. To my understanding this is most efficient in terms of not over-occupying resources. So generally I would avoid any global use of a database object (or any object whatsoever) since this would meand keeping it in memory all the time during execution of my app. What I do declare and set globally is the name of the database, so that I could use it everywhere to open and close the database. e.q.:

'in the global declaration section i have
Dim gsDB as string
gsDB = App.Path & "mydb.mdb"

'.. and somewhere else in the app i have something like
Sub UseMyDB

Dim db as Database
Set db = OpenDatabase(gsDB)

' do things with the db,

' when finished close it

db.Close
Set db = Nothing

End sub

Edited by pardede on 03-13-2000 at 05:09 AM