-
I've posted this question at the Database forum but didn't get any feedbacks.
I'm writing a multiuser application with VB5 SP3 that connects to the PostgreSQL database via ODBC.
The application allows user to enquire and update database. The application also import/export data files periodically (fired by timer events) -- without user interventions.
Is it common practice to create a new workspace and/or connection whenever database access is required or to use a global variable for using throughout the application?
Say, if I'm using the global method, if the import is in progress (updating certain tables in database) while the user is updating certain information in the database as well, will the begin/commit/rollback transactions affects one another??
-
If you are talking a TRUE multi-user environment then your application will be run for each person using the system.
Each of these people will therefor have an "instance" of your application and their connection to the database (while GLOBAL for that specific person's "instance" if your application) will be different to every other person. You will probably note that the other people logged on using different usernames to show they are separate.
The Begin/End/Rollback will NOT clash with other users but you do have to be careful not to lock more records than you truely need.
For instance if you create a recordset using a table name ONLY as the SQL string and you do not state that you want it read only for example... you *might* end up locking every records which stops other users from getting access to update it. If you are very careful to read the different access methods for both the Database connection and for the Recordset creation you should be fine.
-
Thank you for your reply.
This just brings up another question.
What happens if within one "instance" that the user is updating data in database while the timer fires up another process to update data in the same database. both processes could update the same table at the same time or update different tables. If a global workspace and/or connection is defined for that instance, will the begin/commit/rollback transactions affect one another?
-
I dont think you are quite understanding something here.
"Global" goes only as far as that "instance" of your application.
If 10 people run you app.exe then you have 10 DIFFERENT database connections. They are in absolutely NO way related to each other and can do anything they like seperately.
If you define a variable as Global :
Code:
Global wrkDatabase as Workspace
Global dbsDatabase as Database
"Global" does NOT mean that 10 different people running your application get the SAME workspace... It means that EACH person gets their own workspace that is "Global" as far as THEIR instance of the application is concerned.
Now if you are talking about "timer" events coming from the same application that people are using you have to realise that VB is NOT multi-threading. This means that is CANNOT execute a timer event at the same time that someone is doing something by pressing a button etc. What will happen is that if the timer event "starts" before you press the button it will have to complete before you can press the button. you cant run both at once.
Edited by Gen-X on 03-09-2000 at 12:05 AM
-
Yes, I understand what you mean. But my 2nd question is not regarding a global variable for 10 people, but for the 1 instance (ie 1 person on one machine). (Isn't this what you mean by 'instance' in your 1st reply?)
I want to know what happens to the database transactions if 2 updates are occurring concurrently on the same application on the same machine. eg, the user might be updating 1 user information (by pressing button), then it so happens the timer event of the same application is firing at the same time to batch update user information, or vice versa.
With regarding to your reply, does that mean that such scenerio will never happen? (Just need confirmation, then defining a global variable might be sufficient and i don't have to handle such case).
-
I cannot be absolutely sure, but I am about 90% certain that Visual Basic is not a multi-threaded programming language.
What this means is that it cannot execute 2 parts of the same code be that in a timer or anything else.
What I would do to test this is set up a timer to go off in 60 seconds and inside the code for the timer get it to do a very large For statement and make sure you update some control on the form to let you know when it is running.
ie:
Code:
Dim iCount as long
txtStatus.Caption = "Running Timer"
For iCount = 1 to 100000000
Next iCount
When you see the textbox fill with the "Running Timer" try and press a button that does something else.
If you find the button cannot be pressed (or you get a message saying it is busy and to "Switch/Retry") then you have confirmed the fact that Visual Basic ceases ALL operations while performing a section of code in a timer.
Usually Timers trigger small events, not ones that may take a long time to process.
If you DO want very long events to be processed I would suggest writing a seperate application that would handle all of these events and be set to go off at regular intervals (read up on DCOM and stuff)
