Re: Informix to Oracle Migration

DA Morgan wrote: > Art S. Kagel wrote: > >> Hubert Hoelzl wrote: >> >>> Has Aubit now got an Esql/C compiler ? >>> >>>> Try aubit4gl.... >>> >>> >>> >>> <SNIP> >> >> >> No but Orable does - ProC(?). Albeit not half as powerful as >> Informix's, but it should be sufficient to compile any independent >> ESQL/C code Girish has. >> >> Art S. Kagel > > > When you find something Pro*C can't do let me know. Sure, Pro*C does not support dynamic creation of prepared statement names and cursor names in host variables. That's one. We use that feature here to implement an extremely powerful middleware server that reads well over a thousand SQL strings - and the metadata describing their inputs to replacable parameters and output conversions - from a database. The server then prepares each and declares a cursor against each at startup so that all of those statements are ready to run and pre-optimized when needed. One requirement to be able to do that is for the server to generate cursor and statement names on the fly since the number and identifiers of these SQLs is ONLY known at runtime and can change from day to day as new SQL is added and unused SQL dropped from the database. A second item may actually be an Oracle limitation rather than a Pro*C one, I'm not sure. Which is that Oracle/Pro*C will not create thousands of active statements or active cursors (don't remember because it has been a while so it may be that the limitation is only for cursors not statements or it may be just more restrictive for the later in that it supports larger numbers of prepared statements but a smaller number of declared cursors). Pro*C only supports statements that are explicitely PREPARED while Informix ESQL/C supports implied PREPARation. So in ESQL/C you can: EXEC SQL DECLARE cursor_one CURSOR FOR "SELECT first_col FROM one_table WHERE second_col > 1"; or even: EXECUTE IMMEDIATE "UPDATE one_table SET first_col = 12 WHERE second_col = 1"; when the statement is only executed for its side effects. To convert this to Pro*C would require one to recode an explicit PREPARE followed by a DECLARE CURSOR or EXECUTE <statement_id>. While that's no great burden for new code development, porting existing code that takes advantage of these and other powerful Informix specific and ANSI ESQL standard features difficult. That's enough for me. Hence my statement and opinion that Pro*C is not as powerful as Informix ESQL/C. The same is true of IBM's ESQL/C for DB2 BTW, so much so that rather than continue trying to port Informix ESQL/C features to ESQL for DB2 IBM added optional DB2 connectivity to the latest release of Informix ESQL/C. Art S. Kagel