ginaa
asked on
problem to access database
I use IIS4, ASP to access database.
If I query page sequencially, it's OK.
If I make 10 queries at the same time,
only one is OK and another 9 are fail.
After that fail, I must wait a period time.
Otherwise, it will fail.
But even I wait, only the first query is OK.
And then must wait another period time.
What's the problem?
Thanks
My ASP file like this:
<%
sn = request("sn")
pr = request("pr")
set Conn = Server.CreateObject("ADODB
Conn.Open "DBQ=" & Server.MapPath("db1.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};FIL=MS Access;"
set rs = Server.CreateObject("ADODB
SQL = "Select * from user where serialno = '" & sn & "'"
rs.open SQL, Conn, 2, 3
if RS.EOF then
Response.Write("###Account
rs.Close
Conn.Close
set RS = Nothing
set Conn = Nothing
else
amt = pr + RS("amount")
Response.Write("***" & amt)
rs("amount") = amt
rs.update
rs.Close
Conn.Close
set RS = Nothing
set Conn = Nothing
end if
%>
If I query page sequencially, it's OK.
If I make 10 queries at the same time,
only one is OK and another 9 are fail.
After that fail, I must wait a period time.
Otherwise, it will fail.
But even I wait, only the first query is OK.
And then must wait another period time.
What's the problem?
Thanks
My ASP file like this:
<%
sn = request("sn")
pr = request("pr")
set Conn = Server.CreateObject("ADODB
Conn.Open "DBQ=" & Server.MapPath("db1.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};FIL=MS Access;"
set rs = Server.CreateObject("ADODB
SQL = "Select * from user where serialno = '" & sn & "'"
rs.open SQL, Conn, 2, 3
if RS.EOF then
Response.Write("###Account
rs.Close
Conn.Close
set RS = Nothing
set Conn = Nothing
else
amt = pr + RS("amount")
Response.Write("***" & amt)
rs("amount") = amt
rs.update
rs.Close
Conn.Close
set RS = Nothing
set Conn = Nothing
end if
%>
ASKER
After try, the result has NO different.
It seems causes by openning same mdb file at the same time.
Should I make some setting in IIS?
Or I need to take care file share violation in ASP?
Thanks.
It seems causes by openning same mdb file at the same time.
Should I make some setting in IIS?
Or I need to take care file share violation in ASP?
Thanks.
Make sure the database isn't set for exclusive use. If it is, it will only allow ONE connection, the others will fail until that one is completed.
Forgot this...
Make sure it's not set to lock the ENTIRE database when you access it. You should set it to lock only the record, not the table.
Make sure it's not set to lock the ENTIRE database when you access it. You should set it to lock only the record, not the table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oops...
I meant 'I would not use the connection you are using...'
I meant 'I would not use the connection you are using...'
pjbaratelli ,
it is ALWAYS recommended to NOT use ODBC but a DSN-less connection. That way you save the roundtrip to the registry which is rather slow.
CJ
it is ALWAYS recommended to NOT use ODBC but a DSN-less connection. That way you save the roundtrip to the registry which is rather slow.
CJ
This was what I would would do regardless of the slowness, which in my experience has not ever added up to a second, because I found it more stable and reliable on my system when connecting to an access database. And if I were dealing with that many records that it did (as in millions or even 100,000+) I would use a different database platform.
When attaching to other database servers that are more robust, I use DSN-less connections.
All the tools provided to connect to a database can be used and are a matter of preference. And as you mentioned, it is recommended and not required. So don't get your panies in an uproar.
When attaching to other database servers that are more robust, I use DSN-less connections.
All the tools provided to connect to a database can be used and are a matter of preference. And as you mentioned, it is recommended and not required. So don't get your panies in an uproar.
ginaa,
if you do not want to use an odbc connection, it's your choice. It is not required for the code to work. Only that there is a connection to the database.
if you do not want to use an odbc connection, it's your choice. It is not required for the code to work. Only that there is a connection to the database.
The other consideration is whether you can create a DSN or not. Not everybody can, and not for every database you might use.
Like everything else to do with computers, it depends. ;-)
Like everything else to do with computers, it depends. ;-)
As far as I know Access allows up to 10 connections at once, so less then 10 should not make any problems but try changing :
----------------
set Conn = Server.CreateObject("ADODB
Conn.Open "DBQ=" & Server.MapPath("db1.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};FIL=MS Access;"
set rs = Server.CreateObject("ADODB
SQL = "Select * from user where serialno = '" & sn & "'"
rs.open SQL, Conn, 2, 3
----------------
to:
----------------
ConnStr = "DBQ=" & Server.MapPath("db1.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};FIL=MS Access;"
set rs = Server.CreateObject("ADODB
SQL = "Select * from user where serialno = '" & sn & "'"
rs.open SQL, ConnStr, 2, 3
----------------
so instead of openeing a connection, then opening a recordset with the already opened connection object, you open the connection only as needed for the recordset.
this way you will not need the connection object at all.
You will also should not call rs.close, setting rs = Nothing will close the connection as well.
I think it's a better style of coding and opening recorsets, and I beleive it may solve your problem.
and another thing, why don't you use:
"Provider=Microsoft.Jet.OL
as your connection string?
----------------
set Conn = Server.CreateObject("ADODB
Conn.Open "DBQ=" & Server.MapPath("db1.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};FIL=MS Access;"
set rs = Server.CreateObject("ADODB
SQL = "Select * from user where serialno = '" & sn & "'"
rs.open SQL, Conn, 2, 3
----------------
to:
----------------
ConnStr = "DBQ=" & Server.MapPath("db1.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};FIL=MS Access;"
set rs = Server.CreateObject("ADODB
SQL = "Select * from user where serialno = '" & sn & "'"
rs.open SQL, ConnStr, 2, 3
----------------
so instead of openeing a connection, then opening a recordset with the already opened connection object, you open the connection only as needed for the recordset.
this way you will not need the connection object at all.
You will also should not call rs.close, setting rs = Nothing will close the connection as well.
I think it's a better style of coding and opening recorsets, and I beleive it may solve your problem.
and another thing, why don't you use:
"Provider=Microsoft.Jet.OL
as your connection string?
If you are using access 97 it's up to 10 connections. Access 2000 allows more, though I am not sure of the number..
On my NT server when I open a recordset I use
rs.open sql,conn,3,3
On 2000 server :
rs.open sql,conn,3,1
On my NT server when I open a recordset I use
rs.open sql,conn,3,3
On 2000 server :
rs.open sql,conn,3,1
If you are using access 97 it's up to 10 connections. Access 2000 allows more, though I am not sure of the number..
On my NT server when I open a recordset I use
rs.open sql,conn,3,3
On 2000 server :
rs.open sql,conn,3,1
On my NT server when I open a recordset I use
rs.open sql,conn,3,3
On 2000 server :
rs.open sql,conn,3,1
A specific IIS configuration would cause exactly this behaviour:
In Internet Services Manager, right-click your website (resp. "Default Website"), Properties, tab Home Directory, Application Settings, Configuration,
make sure "Cache ISAPI applications" is checked
In Internet Services Manager, right-click your website (resp. "Default Website"), Properties, tab Home Directory, Application Settings, Configuration,
make sure "Cache ISAPI applications" is checked
ASKER
Thanks every,
I try all cases according your recommands. It still has problem.
Right now I use Microsoft JET OLEDB engine.
When concurrent open a mdb file, only one connection is OK. It's reasonable. And I need NOT wait a period time for next connection.
So now, the question is how to set lock to record, not the table. In the connection string? What's the format?
Now I use "Provider=Microsoft.Jet.OL
Because if add "Mode=ReadWrite" it say can NOT lock file.
I try all cases according your recommands. It still has problem.
Right now I use Microsoft JET OLEDB engine.
When concurrent open a mdb file, only one connection is OK. It's reasonable. And I need NOT wait a period time for next connection.
So now, the question is how to set lock to record, not the table. In the connection string? What's the format?
Now I use "Provider=Microsoft.Jet.OL
Because if add "Mode=ReadWrite" it say can NOT lock file.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ODBC has the ability to provide for multiple concurrent connections..
ASKER
Or my testing method are wrong??
I write an unix client program. That program make socket connection to server port 80.
In that program, I send a string "GET /db3.asp?sn=argv[3]&amt=ar
In normal case, it's OK.
Then I write a batch, call the client 10 times for different serialno and run in backgroud.
batch file:
cli serv_ip 80 0000001 100 &
cli serv_ip 80 0000002 100 &
............
cli serv_ip 80 0000010 100 &
Only one, at most two can success. Why?????
Thanks
I write an unix client program. That program make socket connection to server port 80.
In that program, I send a string "GET /db3.asp?sn=argv[3]&amt=ar
In normal case, it's OK.
Then I write a batch, call the client 10 times for different serialno and run in backgroud.
batch file:
cli serv_ip 80 0000001 100 &
cli serv_ip 80 0000002 100 &
............
cli serv_ip 80 0000010 100 &
Only one, at most two can success. Why?????
Thanks
ASKER
So the key point is how to lock record(recordset?) instead of full database file?
Thanks
Thanks
Hi ginaa
Yes dont lock the whole database.Lock the recordset which u r going to access.And lock it as late as possible and release it as soon as possible.And use adLockOptimistic method.Then it will lock the recordset when u execute the update method.This will help u to improve ur program performance.
Lalitha.
Yes dont lock the whole database.Lock the recordset which u r going to access.And lock it as late as possible and release it as soon as possible.And use adLockOptimistic method.Then it will lock the recordset when u execute the update method.This will help u to improve ur program performance.
Lalitha.
ASKER
Thanks,
In my ASP code :
.....
set Conn = Server.CreateObject("ADODB
Conn.Open "DBQ=" & Server.MapPath("db1.mdb") & ";Driver=(Microsoft Access Driver (*.mdb)};FIL=MS Access;"
set rs = Server.CreateObject("ADODB
SQL = "Select * from user where serialno = '" & sn & "'"
rs.open SQL, Conn, 2, 3
.....
The param 4 of rs.open, value = 3 means adLockOptimistic.
But it doesn't work.
So I need someone tell me what's wrong in my code?
In my ASP code :
.....
set Conn = Server.CreateObject("ADODB
Conn.Open "DBQ=" & Server.MapPath("db1.mdb") & ";Driver=(Microsoft Access Driver (*.mdb)};FIL=MS Access;"
set rs = Server.CreateObject("ADODB
SQL = "Select * from user where serialno = '" & sn & "'"
rs.open SQL, Conn, 2, 3
.....
The param 4 of rs.open, value = 3 means adLockOptimistic.
But it doesn't work.
So I need someone tell me what's wrong in my code?
What errors do you get?
Ginna.,
try this code .this one is working .I got it from one of my asp page that I developed.Change the provider parameter to suit to ur access driver.This is for access 97.If u r using access 2000 then it would be "Microsoft.Jet.OLEDB.4".
set conn = Server.CreateObject("ADODB
conn.Provider = "Microsoft.Jet.OLEDB.3.51"
conn.ConnectionString = "Data source=c:\database\db1.mdb
conn.Open
set rs = Server.CreateObject("ADODB
rs.Open "Select * from user where serialno = '" & sn & "'"
,conn,adOpenDynamic,adLock
If u want full source code tell me I will send u.
Lalitha.
try this code .this one is working .I got it from one of my asp page that I developed.Change the provider parameter to suit to ur access driver.This is for access 97.If u r using access 2000 then it would be "Microsoft.Jet.OLEDB.4".
set conn = Server.CreateObject("ADODB
conn.Provider = "Microsoft.Jet.OLEDB.3.51"
conn.ConnectionString = "Data source=c:\database\db1.mdb
conn.Open
set rs = Server.CreateObject("ADODB
rs.Open "Select * from user where serialno = '" & sn & "'"
,conn,adOpenDynamic,adLock
If u want full source code tell me I will send u.
Lalitha.
ASKER
I try yuor comment, and one is ok.
my system in Chinese.
It response said "file in use"
my system in Chinese.
It response said "file in use"
ginaa.,
May be u have opened that perticular database in MSAccess editor.Just check whether u have opened it in Access.
Lalitha.
May be u have opened that perticular database in MSAccess editor.Just check whether u have opened it in Access.
Lalitha.
ASKER
I have tried several days.
I am sure I don't open it in Access editor at time.
Could it be problem in attribute of mdb file?
I am sure I don't open it in Access editor at time.
Could it be problem in attribute of mdb file?
Ginaa.,
Sometimes this happen because the application which access the database is not propely closed in previous times.So database may be locked.What u have to do is check for the databse folder if it has .ldb file then it is confirmed that databse was locked.(if ur database is db1.mdb then ldb file is db1.ldb).Solution is remove the .ldb file reboot the machine and run ur application again.If any problem write me again.
Lalitha.
Sometimes this happen because the application which access the database is not propely closed in previous times.So database may be locked.What u have to do is check for the databse folder if it has .ldb file then it is confirmed that databse was locked.(if ur database is db1.mdb then ldb file is db1.ldb).Solution is remove the .ldb file reboot the machine and run ur application again.If any problem write me again.
Lalitha.
ASKER
Thanks,
When I open mdb file with access editor, there is a .ldb file in folder. After exit editor, it disapper.
When I want to test, I check there is no .ldb file exist.
In my batch file, if I call client application 10 times in background, it will fail.
But if I call it sequencially in batch file, it will be OK for all 10 calls. So I think, the ASP really unlocked the mdb file after finish.
Ginaa
When I open mdb file with access editor, there is a .ldb file in folder. After exit editor, it disapper.
When I want to test, I check there is no .ldb file exist.
In my batch file, if I call client application 10 times in background, it will fail.
But if I call it sequencially in batch file, it will be OK for all 10 calls. So I think, the ASP really unlocked the mdb file after finish.
Ginaa
ginna..,
Now I undestood the problem.To solve this u have to do this.
This happen because u have written code to open the recordset as when as u open the asp page.So when u open the asp page simultaniosly 10 times ,u open the recordset 10 times.This problem occurs u going to open the recordset that already opened in another browser.What u have to do is create the connection in asp normal way and put the recordset open code in command button.Then it will not encounter the problem when u open the asp page.U can write the code to create the connection in global.asa if u using visual interdev.so then it will be very efficient because then u donot need to create the connection each and everytime.
And check the lock type u have put.it should be adLockOptimistic.And one another important thing if u are not going to update the recordset then use the readonly recorset.if so u wont connect the database the all the time that open the recordset.It will retrieve the recordset and close the connection to database.
If u r not going to recordset then use this code.,
set conn = Server.CreateObject("ADODB
conn.Provider = "Microsoft.Jet.OLEDB.3.51"
conn.ConnectionString = "Data source=c:\database\db1.mdb
conn.Open
set rs = Server.CreateObject("ADODB
rs.Open "Select * from user where serialno = '" & sn & "'"
,conn,adOpenForwardOnly,ad
rs.close
DONT forget to close the recordset after u retrieve the recordset.From that u will unlock the database.
If u using the recordset for updation then write the code for connection in normal way and write the code to open the recordset and update in commandbutton and close it after u did the operation.And use the adOpenKeyset and adLockOptimistic.
Tell me if u have any problem.
Lalitha.
Now I undestood the problem.To solve this u have to do this.
This happen because u have written code to open the recordset as when as u open the asp page.So when u open the asp page simultaniosly 10 times ,u open the recordset 10 times.This problem occurs u going to open the recordset that already opened in another browser.What u have to do is create the connection in asp normal way and put the recordset open code in command button.Then it will not encounter the problem when u open the asp page.U can write the code to create the connection in global.asa if u using visual interdev.so then it will be very efficient because then u donot need to create the connection each and everytime.
And check the lock type u have put.it should be adLockOptimistic.And one another important thing if u are not going to update the recordset then use the readonly recorset.if so u wont connect the database the all the time that open the recordset.It will retrieve the recordset and close the connection to database.
If u r not going to recordset then use this code.,
set conn = Server.CreateObject("ADODB
conn.Provider = "Microsoft.Jet.OLEDB.3.51"
conn.ConnectionString = "Data source=c:\database\db1.mdb
conn.Open
set rs = Server.CreateObject("ADODB
rs.Open "Select * from user where serialno = '" & sn & "'"
,conn,adOpenForwardOnly,ad
rs.close
DONT forget to close the recordset after u retrieve the recordset.From that u will unlock the database.
If u using the recordset for updation then write the code for connection in normal way and write the code to open the recordset and update in commandbutton and close it after u did the operation.And use the adOpenKeyset and adLockOptimistic.
Tell me if u have any problem.
Lalitha.
ASKER
Oh! God.
Finally, I use PostgreSQL on linux as database server and PostgreSQL ODBC driver for WinNT ASP to query.
Then everything is OK.
So, is the MS access ODBC driver version too old?
I use WinNT 4.0 server, sp6, IIS 4.0, MS Access driver 4.00.3711.08.
Finally, I use PostgreSQL on linux as database server and PostgreSQL ODBC driver for WinNT ASP to query.
Then everything is OK.
So, is the MS access ODBC driver version too old?
I use WinNT 4.0 server, sp6, IIS 4.0, MS Access driver 4.00.3711.08.
Ginaa.,
Thats depend on ur MS Access version.If u r using Access 97
then driver 3.51.if u r using MSAcess 2000 then it will be 4.00.3711.08.
Lalitha.
Thats depend on ur MS Access version.If u r using Access 97
then driver 3.51.if u r using MSAcess 2000 then it will be 4.00.3711.08.
Lalitha.
ASKER
Thanks.
I use MS Access2000, so the version is OK.
Then this problem still has no proper solution.
No one ever do testing like me?
Ginaa
I use MS Access2000, so the version is OK.
Then this problem still has no proper solution.
No one ever do testing like me?
Ginaa
ginaa.,
Just try what I have told u in 05/28/2002 10:55PM comment.I hope u will get good solution for that.
Lalitha.
Just try what I have told u in 05/28/2002 10:55PM comment.I hope u will get good solution for that.
Lalitha.
ASKER
Lalitha,
I have try your code and it's great.
But I do the testing is trying to simulate multi-user UPDATE their records at the same time. Each one update his individual record, but all in same database table.
In my case, I think, system should manage concurrent access to database for me if I do the right setting.
I have try two drivers, one for SQL server, the other is MSAccess, and SQL server is OK and MSAccess is not.
Essentially, I think the ASP code is OK except parameter setting. So my question is what is right setting or correctly function call.
Thanks
Ginaa
I have try your code and it's great.
But I do the testing is trying to simulate multi-user UPDATE their records at the same time. Each one update his individual record, but all in same database table.
In my case, I think, system should manage concurrent access to database for me if I do the right setting.
I have try two drivers, one for SQL server, the other is MSAccess, and SQL server is OK and MSAccess is not.
Essentially, I think the ASP code is OK except parameter setting. So my question is what is right setting or correctly function call.
Thanks
Ginaa
Ginaa.,
Yes u r right.Normaly when u do the transaction with MS Access then system is lock whole the table .But thats depend on ur locking mechanism.If u put adLockPessimistic then it will lock whole table when u create the recordset.If u put adLockOptimistic then it will lock whole table when only u call the update method.So thats why I told u to create the recordset when as only necessary and close the recordset as soon as possible.So better create the recordset inside the command button code and close it inside same code.In MSSQL 7.0 u have page lock.But it doesnot lock whole table.If u using MSSQL 2000 no problem it will lock only the record.So thats why u didnt encounter that problem with MSSQL.
But U have another solution thats if u r using MTS(Microsoft transaction server).U can create MTS component(DCOM) and let it to handle the transaction.Just write the code inside the component to create the recordset and update.Dont forget to pass the connection parameter to component.So it will handle all concorrent transaction and u dont need to worry about that.
lalitha.
Yes u r right.Normaly when u do the transaction with MS Access then system is lock whole the table .But thats depend on ur locking mechanism.If u put adLockPessimistic then it will lock whole table when u create the recordset.If u put adLockOptimistic then it will lock whole table when only u call the update method.So thats why I told u to create the recordset when as only necessary and close the recordset as soon as possible.So better create the recordset inside the command button code and close it inside same code.In MSSQL 7.0 u have page lock.But it doesnot lock whole table.If u using MSSQL 2000 no problem it will lock only the record.So thats why u didnt encounter that problem with MSSQL.
But U have another solution thats if u r using MTS(Microsoft transaction server).U can create MTS component(DCOM) and let it to handle the transaction.Just write the code inside the component to create the recordset and update.Dont forget to pass the connection parameter to component.So it will handle all concorrent transaction and u dont need to worry about that.
lalitha.
I know I sound like a broken record, but I maintain a Access Database with 2000 users and over 30,000 active records with an average of 2.4 concurrent connections using an ODBC connection(100,000+ auditing records). Some of the web pages make enormous calls to the database return over a 1000 records on a single page(this takes about a second at the most). I have never had any issues with speed or connection problems using an ODBC connection to an access database. The ODBC connection will handle your issue with multiple connections to the database.
Another thing to consider is if you look at alot of the MS Office Applications, you will see they use ODBC to make a majority of their connections.
Another thing to consider is if you look at alot of the MS Office Applications, you will see they use ODBC to make a majority of their connections.
ASKER
pjbaratelli,
Thanks.
Is it possible your concurrent access are all opened in read only?
Ginaa
Thanks.
Is it possible your concurrent access are all opened in read only?
Ginaa
User can insert,update,delete,and read any data they wish at any time they wish.
ASKER
MSAccess is so popular product, so I don't think the problem is caused by it. It should be me.
But I really don't understand what mistake I take?
Ginaa
But I really don't understand what mistake I take?
Ginaa
ginna.,
MSAcess is popular and good product thats right.And u can add,delete,update and can do any operation in data manipulations.But thats depend on ur code how u write the code.As I told u in last my comments u should organize ur code properly.U cant write evry operations in every places.It has some suitable places to write the code.Read my previous comments u will get good picture.
Lalitha.
MSAcess is popular and good product thats right.And u can add,delete,update and can do any operation in data manipulations.But thats depend on ur code how u write the code.As I told u in last my comments u should organize ur code properly.U cant write evry operations in every places.It has some suitable places to write the code.Read my previous comments u will get good picture.
Lalitha.
No comment has been added lately and it seems that this question have been abandoned. So it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question or invite a Moderator to close this question if there's no reply from you after seven days.
In the absence of responses, I will recommend the following:
Split points to lalithaw pjbaratelli
** PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER **
Just trying to help for the cleanup...
gladxml
I will leave a recommendation in the Cleanup topic area that this question or invite a Moderator to close this question if there's no reply from you after seven days.
In the absence of responses, I will recommend the following:
Split points to lalithaw pjbaratelli
** PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER **
Just trying to help for the cleanup...
gladxml
rs.open SQL, Conn, 2