Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Multiuser and SQL select issue..
#1
Hi All,

I have a multiform application, created as a SA HTTP.SYS EXE-file, placed on a SQL server where it runs as a Service. The username to start the service has been granted Logon as Service, and the same user has been granted logon to the database. Thereby the program can access and manipulate data in the SQL database. All good.

My program is created with UniqueUrl = on. It seem to be working as all UserSession vars for each user seem to be ok and intact individually. All good as well.

The program uses ADOConnection and ADOQuery, or ADOStoredProc to access and manipulate data in the various tables in my database.

Now it turns out there is multiuser problem. When calling a select from the same table from 2 different users and program instances, it appears that User 1 receives the rows User 2 selected. I have a search form showing a number of records depending on individual search criteria, and it appears as if results are being mixed among the users.

Where the IW application itself appears to be multiuser (thread?) safe, the ADOconnection do not.

Is that a well know problem with a well known solution or is this news for everyone ?

As it is the same username performing the call (the Logon as Service username) could it simply be a SQL problem ?

I'm fishing a bit here as I don't really know what to do, so if anyone have any proposals I will be very grateful to hear them.

Regards
Soren
Reply
#2
Most ADO uses COM. Have you set any ComInitialization settings?
Reply
#3
 Thanks to Alexandre (IntraWeb), there is a VERY important thing you MUST do for every dataset component in your application connected to a database. You MUST manually assign it's database connection at run time before use. I do it in BeforeConnect.

If you don't then you will have data "bleeding" between user sessions.

I hope this helps
Reply
#4
Hi Kudzu,

Can you explain a bit more what any ComInitialization settings cover ? I have set the ComInitialization = ciMultithreaded in ServerController but are otherwise a little in the dark. Which other settings exists ?

In the ADO DBConnection Component, I build the ADO connectionstring, using the Delphi interface, to connect to the database with the usual parameters, but otherwise left all other properties of the DBConnection component as they are. In fact now is the first time I'm looking at other properties apart from loginprompt, and I'm not sure which to change. Currently they are:

Attributes = (all false)
ConnectOptions = coConnectUnspecified
CursorLocation = clUseClient
IsolationLevel = ilCursorStability (this sounds as if it could be a property to change. Many options of which one is ilIsolated. Maybe that is needed.
KeepConnection = true (should that be false?)
LoginPrompt = true (but I'm not asked for a login and it do not appear to have any influence whether it's true or false.The program still connects without asking.
Mode = cmUnknown. Available options do not seem to apply to this.

NB! I have the ADO Connection in UserSession and all Ado Query and Ado StoredProc in datamodules, and in the create of each module, the ADOQuery or ADOStoredproc has it's connection set to UserSession.DBConnection.

Is there anything you will recommend I change ?

Regards
Soren

hi zsleo,

As you can see at the bottom of my reply to Kudzu, I'm seting the connection in the DataModuleCreate of every datamodule as I use the datamodules.

Is that what you mean by manually assigning the connections at runtime ? I do not have any components which have an Open property set at designtime.

Alexandre once recommended using a datamodule for each form with database access, and I have followed that.

Regards
Soren



On my search on Google I have come across MARS, MultipleActiveResultSets, defined as part of the connectionstring. It's described like this:

When true, an application can maintain multiple active result sets (MARS). When false, an application must process or cancel all result sets from one batch before it can execute any other batch on that connection.

Surely a SA HTTP.SYS application servicing multiple users and thereby multiple requests, would be an application that need to maintain multiple active result sets, I guess. Do any of you have any experience with this ?
Reply
#5
Soren,

In answer to your basic question, ADO is NOT thread safe. It works great if you keep the ADO pieces within each session, or you can pool it. I've played with pooled connections but haven't used them myself.

Put your ADO components on session forms or, for persistence during a session, on a datamodule that you then put on UserSession datamodule.

Regardless of where you put the ADO components, you should set the ComInitialization property of ServerController to ciMultiThreaded. This setting is needed if you use any COM component, not just ADO.

Dan
Reply
#6
No. I mean set connection on every ADO data component (query, storedproc, etc.).

I had exactly the same problem as you but after making the change to every component in the app (and all IW apps since) I have not had the issue... notwithstanding correctly setting ComInitialization property for the IW deployment
Reply
#7
Hi zsleo,

Again thanks for your input. And sorry for asking you to clarify a doubt:

I have only one ADO DBConnection defined in UserSession, with the needed connectionstring set (build and tested using it's integrated build function), and for all forms I have a number of either ADOQuery or ADOStoredProc in datamodules, created for and used by each form. Each time I create and show a form, I create the Datamodule (defined as a private property of the form) used by that form in it's IwAppFormCreate event, and in the datamodule, in it's datamodulecreate event, I set the Connection property of each ADOQuery or ADOStoredProc to the connection of the ADO DBConnection in the UserSession form.

Each ADOQuery and ADOStoredProc has a Connection property and a Connectionstring Property. As defined above, I set the Connection property to the ADO DBConnection in UserSession.

a. If I understand you correctly, I should get rid of the ADO DBConnection in the UserSession module, and instead set the ConnectionString of EACH ADOQuery or ADOStoredProc I use in my project, to the same value as the connectionstring of my current ADO DBConnection. Is that correct ?

or b. are you proposing that I, for each ADOQuery or ADOStoredProc I have placed on my datamodules, I also place an ADO DBConnection (with a unique name), and set the connection property of the ADOQuery to that ADO DBConnection. In this way, an ADOQuery or an ADOStoredProc always form a unique pair with a ADO DBConnection.

If it is a. how do I make the actual connection ? just by opening (activating) the ADOQuery or ADOStoredProc ? and
if it is b. should I then activate (Connect) the ADO DBConnection just before I activate the corresponding ADOQuery, and close connection again when the ADOQuery is done, or would I activate the ADO DBConnection in the forms FormCreate event and close it in the FormClose event ?

I'm sorry to ask this, but could you show me an example code for how you do these things ?

Many, many thanks in advance.

Regards
Soren
Reply
#8
Hi zsleo,

After a number of tests, and much moving components around making sure all is with UserSession, I have tested and tested various scenarios. Finally I have found a working solution, and after that, I'm in doubt as to how many of my changes are actually necessary. The main thing is that is works now, but I would like to know for certain, exactly what is needed to make it work.

So my question to you is still: Do you add a DBConnection component to the same datamodule as the ADOQuery components, and then set the ADOQuery.connection := dbconnection, and if, do you do that in the create event of the datamudule, or in the procedure just before the call to ADOQuery.open ?

Regards
Soren
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)