Re: ODBC - "ODBC Call Failed" - PLZ HELP

If you have any info... Please email me! - this problem is killing us, noone know whats happening! Hello... > I'm pulling my hair out with this error. I will really appreciate any help > on this one, ANY HELP. > We are recieving an "ODBC Call Failed" error many times over. The error > occurs throughout the day across multiple applications. Here is some info: > > Our System: > > IBM RS-6000: > > Model F50 (IBM hardware) > AIX Unix (Release 4.3.1) > DMACS Manufacturing Software System (Release 7.0) from On-Line Software Labs > Informix On-Line Dynamic Server engine (Release 7.23.UC7) > > PC System: > > Windows NT 4.0 network (100mb Ethernet) > Service Pack 3 on PC's > Microsoft Office 97 Professional > Informix supplied Intersolv 3.10 ODBC driver to access Informix Engine on > RS-6000 > > Scenario - I think... > > 1) DMACS system locks and unlocks records all day long as a normal course of > processing > > 2) Applications written in Microsoft Access send requests to Informix engine > for data > > 3) Request from Access encounters a record that DMACS has locked > > 4) Access request fails and sends failure back to Access, resulting in "ODBC > call failed" message to user (see attachment) > > 5) Access processing stops until user manually restarts application > > Problem > > 1) Informix reports that you cannot globally change the isolation level > (locking behavior) of the engine. (we would not want to, anyway) > > 2) Microsoft Access is incapable of sending requests for data and isolation > level changes together in the same "send" to Informix > > a) It is possible to send an sql-specific query to Informix ("SET > ISOLATION LEVEL TO DIRTY READ"). We > have tested this. > > b) In an sql-specific query, you cannot send a select for data. > > c) In a select query, you cannot send the above sql-specific statement. > Access will not allow this. > > d) Sending the sql-specific query followed by the select query does not > work, because each query sent to the RS-6000 is treated as a distinct > entity. When the sql-specific query statement is done being processed, the > "child" process spawned to process this request goes away. The select query > is then run, spawning its own "child" process with its own set of defaults. > It does not know anything about the previous sql-specific query that was > run. > > Some Possible Solutions As We See Them > > 1) Have an ODBC driver that is capable of prefixing each query to the > Informix engine with the isolation level change outlined above. > > 2) Trap errors in Microsoft Access and reprocess the query that failed > (note: we have developed 2500+ queries to date- dont like this one). > > Any suggestions???? > > > A screen dump IS AVAILABLE UPON REQUEST. > > > Please note that the error numbers (#-244,#-107,#-243) returned are actually > passed back from Informix. The Informix explanations of these > errors are as follows: > > -107 ISAM error: record is locked. > > Another user request has locked the record that you requested or the > file (table) that contains it. This condition is normally transient. A > program can recover by rolling back the current transaction, waiting a > short time, and re-executing the operation. For interactive SQL, redo > the operation. For C-ISAM programs, review the program logic and make > sure that it can handle this case, which is a normal event in > multiprogramming systems. You can obtain exclusive access to a table by > passing the ISEXCLLOCK flag to isopen. For SQL programs, review the > program logic and make sure that it can handle this case, which is a > normal event in multiprogramming systems. The simplest way to handle > this error is to use the statement SET LOCK MODE TO WAIT. For bulk > updates, see the LOCK TABLE statement and the EXCLUSIVE clause of the > DATABASE statement. > > > -243 Could not position within a table table-name. > > The database server cannot set the file position to a particular row > within the file that represents a table. Check the accompanying ISAM > error code for more information. A hardware error might have occurred, > or the file might have been corrupted (truncated). Unless the ISAM > error code or an operating-system message points to another cause, run > the bcheck or secheck utility to verify file integrity. > > > -244 Could not do a physical-order read to fetch next row. > > The database server cannot read the disk page that contains a row of a > table. Check the accompanying ISAM error code for more information. A > hardware problem might exist, or the table or index file might have > been corrupted. Unless the ISAM error code or an operating-system > message points to another cause, run the bcheck or secheck utility to > verify file integrity. > > > The database integrity checks referred to in the 243 and 244 errors have > been performed without finding any errors in the database. > > > Any light you may be able to shed on this problem would be appreciated. This > problem occurs company-wide all day. > > > Thank you > > > Randy Petersen > Drives Incorporated > Phone: (815)589-5395 > e-mail: someone@clinton.net > > > >

This is an error routine for handling record locking etc. Will only work (duh) if you are calling the query from code. - Good Luck Err_LockRecord: Select Case Err Case 3197 'Data has changed since last accessed mrsCase.Requery Resume 'Use Edit method a second time Case 3260 'Record is locked nLockCount = nLockCount + 1 If nLockCount > 2 Then 'Tried to get lock twice. 'Display error and give user choice to Retry. nChoice = MsgBox(Error, 21) If nChoice = 4 Then 'User chose Retry nLockCount = 1 'Reset to try 2 more times Else Resume Exit_LockRecord End If End If DoEvents 'Perform system events DBEngine.Idle DB_FREELOCKS 'Free any stray locks 'Delay a short random interval, longer each time. lRndCount = nLockCount ^ 2 * Int(Rnd * 3000 + 1000) For lCount = 1 To lRndCount: Next lCount Resume 'Try the edit again Case Else Resume Exit_LockRecord End Select Randy Petersen <someone@clinton.net> wrote in message news:929456266.481.9@news.remarQ.com... > If you have any info... Please email me! - this problem is killing us, noone > know whats happening! > > Hello... > > > I'm pulling my hair out with this error. I will really appreciate any help > > on this one, ANY HELP. > > We are recieving an "ODBC Call Failed" error many times over. The error > > occurs throughout the day across multiple applications. Here is some info: > > > > Our System: > > > > IBM RS-6000: > > > > Model F50 (IBM hardware) > > AIX Unix (Release 4.3.1) > > DMACS Manufacturing Software System (Release 7.0) from On-Line Software > Labs > > Informix On-Line Dynamic Server engine (Release 7.23.UC7) > > > > PC System: > > > > Windows NT 4.0 network (100mb Ethernet) > > Service Pack 3 on PC's > > Microsoft Office 97 Professional > > Informix supplied Intersolv 3.10 ODBC driver to access Informix Engine on > > RS-6000 > > > > Scenario - I think... > > > > 1) DMACS system locks and unlocks records all day long as a normal course > of > > processing > > > > 2) Applications written in Microsoft Access send requests to Informix > engine > > for data > > > > 3) Request from Access encounters a record that DMACS has locked > > > > 4) Access request fails and sends failure back to Access, resulting in > "ODBC > > call failed" message to user (see attachment) > > > > 5) Access processing stops until user manually restarts application > > > > Problem > > > > 1) Informix reports that you cannot globally change the isolation level > > (locking behavior) of the engine. (we would not want to, anyway) > > > > 2) Microsoft Access is incapable of sending requests for data and > isolation > > level changes together in the same "send" to Informix > > > > a) It is possible to send an sql-specific query to Informix ("SET > > ISOLATION LEVEL TO DIRTY READ"). We > > have tested this. > > > > b) In an sql-specific query, you cannot send a select for data. > > > > c) In a select query, you cannot send the above sql-specific statement. > > Access will not allow this. > > > > d) Sending the sql-specific query followed by the select query does not > > work, because each query sent to the RS-6000 is treated as a distinct > > entity. When the sql-specific query statement is done being processed, the > > "child" process spawned to process this request goes away. The select > query > > is then run, spawning its own "child" process with its own set of > defaults. > > It does not know anything about the previous sql-specific query that was > > run. > > > > Some Possible Solutions As We See Them > > > > 1) Have an ODBC driver that is capable of prefixing each query to the > > Informix engine with the isolation level change outlined above. > > > > 2) Trap errors in Microsoft Access and reprocess the query that failed > > (note: we have developed 2500+ queries to date- dont like this one). > > > > Any suggestions???? > > > > > > A screen dump IS AVAILABLE UPON REQUEST. > > > > > > Please note that the error numbers (#-244,#-107,#-243) returned are > actually > > passed back from Informix. The Informix explanations of these > > errors are as follows: > > > > -107 ISAM error: record is locked. > > > > Another user request has locked the record that you requested or the > > file (table) that contains it. This condition is normally transient. A > > program can recover by rolling back the current transaction, waiting a > > short time, and re-executing the operation. For interactive SQL, redo > > the operation. For C-ISAM programs, review the program logic and make > > sure that it can handle this case, which is a normal event in > > multiprogramming systems. You can obtain exclusive access to a table by > > passing the ISEXCLLOCK flag to isopen. For SQL programs, review the > > program logic and make sure that it can handle this case, which is a > > normal event in multiprogramming systems. The simplest way to handle > > this error is to use the statement SET LOCK MODE TO WAIT. For bulk > > updates, see the LOCK TABLE statement and the EXCLUSIVE clause of the > > DATABASE statement. > > > > > > -243 Could not position within a table table-name. > > > > The database server cannot set the file position to a particular row > > within the file that represents a table. Check the accompanying ISAM > > error code for more information. A hardware error might have occurred, > > or the file might have been corrupted (truncated). Unless the ISAM > > error code or an operating-system message points to another cause, run > > the bcheck or secheck utility to verify file integrity. > > > > > > -244 Could not do a physical-order read to fetch next row. > > > > The database server cannot read the disk page that contains a row of a > > table. Check the accompanying ISAM error code for more information. A > > hardware problem might exist, or the table or index file might have > > been corrupted. Unless the ISAM error code or an operating-system > > message points to another cause, run the bcheck or secheck utility to > > verify file integrity. > > > > > > The database integrity checks referred to in the 243 and 244 errors have > > been performed without finding any errors in the database. > > > > > > Any light you may be able to shed on this problem would be appreciated. > This > > problem occurs company-wide all day. > > > > > > Thank you > > > > > > Randy Petersen > > Drives Incorporated > > Phone: (815)589-5395 > > e-mail: someone@clinton.net > > > > > > > > > >

>> 1) Have an ODBC driver that is capable of prefixing each query to the >> Informix engine with the isolation level change outlined above. Openlink Multi-tier drivers have this capability. You can specify a series of SQL statement that get executed automatically everytime a session is established. We use it to set isolation to dirty read. Bashar Chalabi CTL, London (Not related in any way to Openlink. Just a user)