-710 error
Porting an ESQL/C app from Informix 5 to 9.2 (HP-UX 11): after an ALTER TABLE, every later operation on that table returns -710 (table not found/altered), and re-preparing the statements didn't clear it, unlike in v5. Suggestions: re-prepare on -710 (didn't work); the poster found the ESQL/C manual's OPTOFC section explaining that cursors aren't really closed/freed on CLOSE, so the stale cursor is reopened and triggers -710. Advice was to FREE the cursor and statement after CLOSE, or look at AUTOFREE/SET AUTOFREE/IFX_AUTOFREE; a DISCONNECT/CONNECT would work but rolls back the transaction. No confirmation that any of these fixed it is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We are porting an application from Informix 5 to Informix 9.2 and SDK 2.5 on HP-UX 11. After an alter statement on a specific table completes in our esqlc application, every subsequent operation on the table gets a -710 error, even though the subsequent operations were prepared after the alter. In Informix 5, simply resubmitting the operation reset the error. Is there a work-around for this condition that does not require shutting the application down, closing the connection, and otherwise ruining the transaction in progress. -- Philip Walden 5301 Stevens Creek Blvd, MS 54L-GF Agilent Technologies Santa Clara, CA 95051 Engineering Services (408) 553-7745 FAX (408) 553-7659 mailto:phil_walden@agilent.com http://es.corporate.agilent.com/~pwalden
Because of the in-place alter features, your SQl statement is outdated with the altered table. You need to re-prepare your SQL statements on -710 and all should be fine. ray "Philip Walden" <phil_walden@agilent.com> wrote in message news:3A0861CF.55245BDA@agilent.com... > We are porting an application from Informix 5 to Informix 9.2 and SDK 2.5 > on HP-UX 11. > > After an alter statement on a specific table completes in our esqlc > application, every subsequent operation on the table gets a -710 error, > even though the subsequent operations were prepared after the alter. > > In Informix 5, simply resubmitting the operation reset the error. > > Is there a work-around for this condition that does not require shutting > the application down, closing the connection, and otherwise ruining the > transaction in progress. > > -- > Philip Walden 5301 Stevens Creek Blvd, MS 54L-GF > Agilent Technologies Santa Clara, CA 95051 > Engineering Services (408) 553-7745 FAX (408) 553-7659 > mailto:phil_walden@agilent.com http://es.corporate.agilent.com/~pwalden > > >
Ray Canuel wrote: > Because of the in-place alter features, your SQl statement is outdated with > the altered table. You need to re-prepare your SQL statements on -710 and > all should be fine. We do re-prepare the the SQL, but the error persists. We even try re-preparing the SQL 10 times with a 10 second delay in between and the error persists. > > > ray > > "Philip Walden" <phil_walden@agilent.com> wrote in message > news:3A0861CF.55245BDA@agilent.com... > > We are porting an application from Informix 5 to Informix 9.2 and SDK 2.5 > > on HP-UX 11. > > > > After an alter statement on a specific table completes in our esqlc > > application, every subsequent operation on the table gets a -710 error, > > even though the subsequent operations were prepared after the alter. > > > > In Informix 5, simply resubmitting the operation reset the error. > > > > Is there a work-around for this condition that does not require shutting > > the application down, closing the connection, and otherwise ruining the > > transaction in progress. > > > > -- > > Philip Walden 5301 Stevens Creek Blvd, MS 54L-GF > > Agilent Technologies Santa Clara, CA 95051 > > Engineering Services (408) 553-7745 FAX (408) 553-7659 > > mailto:phil_walden@agilent.com http://es.corporate.agilent.com/~pwalden > > > > > > -- Philip Walden 5301 Stevens Creek Blvd, MS 54L-GF Agilent Technologies Santa Clara, CA 95051 Engineering Services (408) 553-7745 FAX (408) 553-7659 mailto:phil_walden@agilent.com http://es.corporate.agilent.com/~pwalden
Philip Walden wrote: > Ray Canuel wrote: > > > Because of the in-place alter features, your SQl statement is outdated with > > the altered table. You need to re-prepare your SQL statements on -710 and > > all should be fine. > > We do re-prepare the the SQL, but the error persists. We even try re-preparing > the SQL 10 times with a 10 second delay in between and the error persists. Chapter 14 of the SDK states that dynamic cursors do not actually close whenever a $close is executed. They are closed whenever a subsequent $open is called on them. However, by then the prior $prepare has been rejected with a -710 error. I suspect that reusing the same cursor on the next operation means it is still tainted. Is there a way to explicity force a cursor to close. > > > > > > > > ray > > > > "Philip Walden" <phil_walden@agilent.com> wrote in message > > news:3A0861CF.55245BDA@agilent.com... > > > We are porting an application from Informix 5 to Informix 9.2 and SDK 2.5 > > > on HP-UX 11. > > > > > > After an alter statement on a specific table completes in our esqlc > > > application, every subsequent operation on the table gets a -710 error, > > > even though the subsequent operations were prepared after the alter. > > > > > > In Informix 5, simply resubmitting the operation reset the error. > > > > > > Is there a work-around for this condition that does not require shutting > > > the application down, closing the connection, and otherwise ruining the > > > transaction in progress. > > > > > > -- > > > Philip Walden 5301 Stevens Creek Blvd, MS 54L-GF > > > Agilent Technologies Santa Clara, CA 95051 > > > Engineering Services (408) 553-7745 FAX (408) 553-7659 > > > mailto:phil_walden@agilent.com http://es.corporate.agilent.com/~pwalden > > > > > > > > > > > -- > Philip Walden 5301 Stevens Creek Blvd, MS 54L-GF > Agilent Technologies Santa Clara, CA 95051 > Engineering Services (408) 553-7745 FAX (408) 553-7659 > mailto:phil_walden@agilent.com http://es.corporate.agilent.com/~pwalden -- Philip Walden 5301 Stevens Creek Blvd, MS 54L-GF Agilent Technologies Santa Clara, CA 95051 Engineering Services (408) 553-7745 FAX (408) 553-7659 mailto:phil_walden@agilent.com http://es.corporate.agilent.com/~pwalden
Philip Walden wrote: > Chapter 14 of the SDK states that dynamic cursors do not actually close whenever > a $close is executed. They are closed whenever a subsequent $open is called on > them. However, by then the prior $prepare has been rejected with a -710 error. I > suspect that reusing the same cursor on the next operation means it is still > tainted. Is there a way to explicity force a cursor to close. One possibility is to establish a new connection (DISCONNECT, CONNECT) on a -710. That will close all cursors, but may be a bit drastic. Alternatively, you could learn (as I did in a previous job that had the same problem) to live with ALTERing tables only during maintenance windows :( Rudy
Rudy Fernandes wrote: > Philip Walden wrote: > > > Chapter 14 of the SDK states that dynamic cursors do not actually close whenever > > a $close is executed. They are closed whenever a subsequent $open is called on > > them. However, by then the prior $prepare has been rejected with a -710 error. I > > suspect that reusing the same cursor on the next operation means it is still > > tainted. Is there a way to explicity force a cursor to close. > > One possibility is to establish a new connection (DISCONNECT, CONNECT) on a -710. > That will close all cursors, but may be a bit drastic. Are you saying to: 1. disconnect the current connection and reconnect OR 2. to make the current connection dormant (we are multi-threaded). Then create a new connection, disconnect that and then make the previous connection active. 1. is not possible as it would rollback any prior work. Thanks for the hints! > Alternatively, you could > learn (as I did in a previous job that had the same problem) to live with ALTERing > tables only during maintenance windows :( Our applications allow us to do this during production (yes, really). We do not have maintenance windows. If going from Informix 5 to Informix 9 means loss of this functionality, it woild be a severe set back for us. :( :( -- Philip Walden 5301 Stevens Creek Blvd, MS 54L-GF Agilent Technologies Santa Clara, CA 95051 Engineering Services (408) 553-7745 FAX (408) 553-7659 mailto:phil_walden@agilent.com http://es.corporate.agilent.com/~pwalden
Philip Walden wrote: > Rudy Fernandes wrote: > > > Philip Walden wrote: > > > > > Chapter 14 of the SDK states that dynamic cursors do not actually close whenever > > > a $close is executed. They are closed whenever a subsequent $open is called on > > > them. However, by then the prior $prepare has been rejected with a -710 error. I > > > suspect that reusing the same cursor on the next operation means it is still > > > tainted. Is there a way to explicity force a cursor to close. > > > > One possibility is to establish a new connection (DISCONNECT, CONNECT) on a -710. > > That will close all cursors, but may be a bit drastic. > > Are you saying to: > > 1. disconnect the current connection and reconnect OR > 2. to make the current connection dormant (we are multi-threaded). Then create a > new connection, disconnect that and then make the previous connection active. > > 1. is not possible as it would rollback any prior work. > I meant 1 and, yes, it will rollback an open transaction . I doubt that 2 will help. You may want to try FREEing the cursor and associated statement after CLOSEing it. BTW, you refer to Chapter 14 of the SDK - which manual are you talking about? Rudy
Rudy Fernandes wrote: > Philip Walden wrote: > > > Rudy Fernandes wrote: > > > > > Philip Walden wrote: > > > > > > > Chapter 14 of the SDK states that dynamic cursors do not actually close whenever > > > > a $close is executed. They are closed whenever a subsequent $open is called on > > > > them. However, by then the prior $prepare has been rejected with a -710 error. I > > > > suspect that reusing the same cursor on the next operation means it is still > > > > tainted. Is there a way to explicity force a cursor to close. > > > > > > One possibility is to establish a new connection (DISCONNECT, CONNECT) on a -710. > > > That will close all cursors, but may be a bit drastic. > > > > Are you saying to: > > > > 1. disconnect the current connection and reconnect OR > > 2. to make the current connection dormant (we are multi-threaded). Then create a > > new connection, disconnect that and then make the previous connection active. > > > > 1. is not possible as it would rollback any prior work. > > > > I meant 1 and, yes, it will rollback an open transaction . I doubt that 2 will help. > > You may want to try FREEing the cursor and associated statement after CLOSEing it. > > BTW, you refer to Chapter 14 of the SDK - which manual are you talking about? > > Rudy ESQL/C Programmers manual version 9.13 Chapter 14 page 36 Restrictions on OPTOFC With the OPTOFC feature enabled, the following restrictions exist: * You can only use the OPTOFC feature on select cursors whose SELECT statement has been prepared. For example, the OPTOFC feature reduces network round trips for the following select cursor: /* Valid select cursor for OPTOFC optimization */ EXEC SQL prepare sel_stmt 'select * from customer'; EXEC SQL declare sel_curs cursor for sel_stmt; * TheOPTOFC feature eliminates execution of the OPEN statement as a separate step. Therefore, any error conditions that opening the cursor might generate are not returned until after the initial FETCH. * Static cursors are not freed when they are closed. With the OPTOFC feature enabled, neither static nor dynamic cursors are freed when they are closed. Because ESQL/C does not actually send the CLOSE statement to the database server, a cursor is not implicitly freed. A subsequent OPEN and FETCH on a cursor actually opens the same cursor. Only at this time would the database server notice if the table was modified (if it was dropped, altered, or renamed), in which case it generates an error (-710). With the OPTOFC feature disabled, a static cursor is freed when it is closed. When ESQL/C reaches a CLOSE statement for a static cursor, it actually sends a message to close the cursor and free memory associated with this cursor. However, dynamic cursors are not implicitly freed when they are closed. -- Philip Walden 5301 Stevens Creek Blvd, MS 54L-GF Agilent Technologies Santa Clara, CA 95051 Engineering Services (408) 553-7745 FAX (408) 553-7659 mailto:phil_walden@agilent.com http://es.corporate.agilent.com/~pwalden
Philip Walden wrote: > Rudy Fernandes wrote: > > > > > You may want to try FREEing the cursor and associated statement after CLOSEing it. > > > > BTW, you refer to Chapter 14 of the SDK - which manual are you talking about? > > > > Rudy > > ESQL/C Programmers manual version 9.13 > > Chapter 14 page 36 > > Restrictions on OPTOFC > > With the OPTOFC feature enabled, the following restrictions exist: > > There's a mention to the AUTOFREE feature, the SET AUTOFREE statement and the IFX_AUTOFREE environment variable that could help you. Refer to page 14-39 and 14-23 onwards of the same manual (sort of, I have v9.21). All the best. Rudy