Re: Informix w/MS Access97 - Lock Mode

Art S. Kagel wrote: > The SET LOCK MODE statement is of the same class as a DATABASE statement, > and besides, look at that error message it clearly states that you are not > permitted to combine a select with ANY other statement in a > multi-statement prepare! Just make them separate statements. > > Art S. Kagel > > Randy Petersen wrote: > > > > Informix w/MS Access97 - Lock Mode > > > > This problem is killin me. > > > > SET LOCK MODE TO WAIT; > > SELECT * FROM item;> > > > This query works great in dbaccess, but fails when I run a MS Access97 > > Pass-through Query. > > I get an error #555 > > > > ___________________________________________________________ > > 555 Cannot use a select or any of the database statements in a multi-query > > prepare. > > The statement text that is presented with this PREPARE statement has > > multiple statements divided by semicolons, and one is a SELECT, DATABASE, > > CREATE DATABASE, or CLOSE DATABASE statement. These statements must always > > be prepared as one-statement texts. Check the statement text string, and > > make sure that you intended multiple statements. If you did, revise the > > program to execute these four statement types alone. > > ___________________________________________________________ > > > > If anyone has any help for me, id really appreciate it. Im tryin to get > > access and avoid my current ODBC errors. Id lock every query to wait for > > records to come unlocked. > > > > Thank you very much! > > randy Art is right in pointing to you that you can't combine a set lock mode with a select statement. But if you issue these statements separately if you use the Informix CLI ODBC Drivers you will discover that the "SET LOCK MODE TO WAIT" doesn't have any effect! The reason for this is that the SELECT statement will belong to a different Informix Session than the SET LOCK MODE TO WAIT statement. If you issue a onstat -g ses to discover these sessions and then a onstat -g ses sid to see the details of each session you will notice that in the Lock Mode Column of the session belonging to the SELECT statement the status is "Not Wait" where in the Lock Mode Column of the session belonging to the SET LOCK MODE TO WAIT statement is correctly set to "Wait". The same applies if you try to issue a SET ISOLATION TO statement. I think that the Openlink ODBC Drivers provide the possibility to set things like that for all sessions that are created. For the Informix CLI Drivers the only solution is to create a stored procedure where you set the lock mode and execute your select statement and then execute the SP from your MsAccess Code. HTH Tolis Varnas -- V+K Relational Solutions mailto:tvarnas@compulink.gr Deligiorgi 26 mailto:tvarnas@orbit.de 546 42 Thessaloniki Voice: +30-31-820270 Greece Fax: +30-31-865463