Re: Question about Prepared statements.

Great question. Glad you asked. It depends... Please give version information when asking such questions; see the version dependent information in this answer. You may then be able to work out what is going on! Kerry: this material is likely to be the basis for another bit of the FAQ. >From: Joe Archer <joea@firstpac.com> >Date: Thu, 07 Dec 1995 13:30:30 -0800 >X-Informix-List-Id: <news.19536> > >How are prepared statement id's defined in ESQL/C? Are they defined as >global variables, as local variables, or as global within a source file? >I haven't seen anything in the documentation, but I could have sworn >that an Informix instructor said that they are global. There is a big difference between the versions of ESQL/C prior to 5.00 and those from 5.00 upwards. In the pre-5.00 versions (4.1x, mainly, but also 4.00, 2.10.0x, etc), all cursors and prepared statements had fixed names which were private to the file in which the statement was prepared or the cursor declared. Two separate source files could declare the same names with impunity and no damage was done. You couldn't redeclare the name in the same file, so you couldn't declare a single cursor in two different ways -- this was not allowed: if (something == SOMEVALUE) $ DECLARE cursor_name CURSOR FOR <select1>; else $ DECLARE cursor_name CURSOR FOR <select2>; You got around this by selectively assigning the text of the statement and then preparing the string, and then declaring the cursor. There were some other peculiarities. For example, the same structure was used for both the cursor and the statement, even though a declaration was emitted for both, just in case they were used independently. So a fussy compiler would warn about unused variables. And you could only free either the cursor or the statement, not both, mainly because of this shared variable implementation. The rule of thumb was to release the cursor where there was one, and to release the statement only when it was not a SELECT statement. Once a cursor was associated with a statement, there was no way to reuse the statement with a different cursor. In the post-5.00 versions (including 5.00 itself, of course), this all changes. Cursor and statement names become strings, and the names are global -- as the instructor said. This means that if you declare cursorA in file1.ec, you can use it in file2.ec without any work being required. You simply say 'OPEN cursorA' and it all works like a charm. Of course, if file2.ec also had a cursorA in it (eg, a cursor called 'c', or 'c_select' which I used to use in generated code), then all of a sudden you have a very different situation. You can have code which worked in 4.1x no longer working in 5.00, as below. This code should compile, but is otherwise inexcusable on a number of other grounds. But it needed to be simple enough to understand and explain. file1.ec: file1_function() { EXEC SQL BEGIN DECLARE SECTION; char tabname[19]; long tabid; EXEC SQL END DECLARE SECTION; EXEC SQL WHENEVER ERROR CALL sqlerror; EXEC SQL DECLARE c_cursor CURSOR FOR SELECT TabID, TabName FROM SysTables WHERE TabID >= 100; EXEC SQL OPEN c_cursor; while (sqlca.sqlcode == 0) { EXEC SQL FETCH c_cursor INTO :tabid, :tabname; file2_function(tabid); } EXEC SQL CLOSE c_cursor; EXEC SQL FREE c_cursor; } file2.ec: file2_function(char *p_tabid) { static int prepared = 0; EXEC SQL BEGIN DECLARE SECTION; long tabid = p_tabid; date created; char owner[9]; EXEC SQL END DECLARE SECTION; EXEC SQL WHENEVER ERROR CALL sqlerror; if (prepared++ == 0) { EXEC SQL PREPARE p_cursor FROM "SELECT owner, created FROM SysTables WHERE Tabid = ?"; EXEC SQL DECLARE c_cursor FOR p_cursor; } EXEC SQL OPEN c_cursor USING :tabid; EXEC SQL FETCH c_cursor INTO :owner, :created; EXEC SQL CLOSE c_cursor; } In 5.00, when file2_function() is called, it implicitly closes, frees, and redeclares c_cursor, does an open, fetch, close cycle, and returns. Unfortunately, this means that the next FETCH in file1_function() will fail because it is trying to fetch on a closed cursor. If it casually re-opens the cursor, it then runs into an error because the fetched data values are incompatible with the cursor declared in file2.ec. If file1_function() re-declares and re-opens c_cursor, then when it calls file2_function(), either the OPEN fails because too many parameters are supplied in the USING clause or the FETCH fails because of the type mismatch. Either way, the code is severely broken. I have been using old-style names so that the code would compile under 4.1x too. However, you can also use strings for cursor and statement names: EXEC SQL BEGIN DECLARE SECTION; char *string1 = "select * from systables where tabid >= 100"; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE "p_cursor" FROM :string1; EXEC SQL DECLARE "c_cursor" CURSOR FOR "p_cursor"; And you can use string-valued variables too: EXEC SQL BEGIN DECLARE SECTION; char *s_name = "p_cursor"; char *c_name = "c_cursor"; char *string1 = "select * from systables where tabid >= 100"; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE :s_name FROM :string1; EXEC SQL DECLARE :c_name CURSOR FOR :s_name; Just be very careful to make sure you track down all cursors and statements if you use these. It can get confusing if you are not careful. Also note that you can have multiple DECLARE statements for a single cursor name now -- that is the cause of all the problems in the file1.ec/file2.ec example. However, you can even do all that in a single file under 5.00. There is an option '-local' which provides mangled names to ensure that the names are different between files. This more or less works provided that your cursor names were short enough; if they were too long, then from 2 to 9 characters are dropped off the tail of the name (in 5.03 and later versions; I think one version of ESQL/C (possibly a pre-release) generated a name of more than 18 characters), and the previously distinct names may no longer be distinct. And if you use this mechanism, you cannot use: EXEC SQL DECLARE c_cursor CURSOR FOR SELECT SomeColumn FROM SomeTable FOR UPDATE; EXEC SQL PREPARE p_update FROM "UPDATE SomeTable SET SomeColumn = ? WHERE CURRENT OF c_cursor"; EXEC SQL OPEN c_cursor; EXEC SQL FETCH c_cursor INTO :variable; alter(&variable); EXEC SQL EXECUTE p_update USING :variable; This is because the declared cursor name is modified, but the WHERE CURRENT OF clause is not modified because it is inside a string. So, with this code, either the EXECUTE fails with unknown cursor name, or it executes an update using some other cursor altogether which may not even point to SomeTable. You'd probably get an error; in the worst case, it might work but simply update a completely different row from the one you thought it was updating. >I have two prepared statements that have the same name, but they do two >different sel