Re: Prepared V Inline sql

Jim Gordon wrote: > > It's really pretty clear in the manuals. You obviously come from a DB2 > backround which precompiles SQL in the engine during program > compilation. DEC's Rdb/VMS product also provided a precompiler that did more with static SQL than does Informix's ESQL compiler. > Informix doesn't do this the same way. All SQL in programs is > effectively dynamic but you have two choices with it. Prepare it in the > program which case the engine will parse and optimise it and provide you > with a pointer to it. This means that this work doesn't have to be done > each time you call it but must be done once per program run. It lasts > as long as you do not free the prepared statement. True dynamic which > just takes the string and passes it to the engine for handling each > time. All good Informix programmers use prepared SQL as much as they > can. > > Inline code of the type you refer to - where the compiler takes the sql > gives it to the engine which stores, parses and optimisers it, returning > a pointer to it which is embedded in the executable, requires that the > database supplier also provides a tightly coupled compiler. That isn't > possible in an open environment. The precompiler can still do more work for you without necessarily feeding the queries to the engine and embedding references to them in an application. For example, it can parse the SQL checking for correct syntax, generate host langauge calls to routines that accept information from the SQL statement without having to recheck it, verify the data types of selected columns and host variables match, etc. All of these things will need to be done the first time you prepare dynamic SQL statements. For those programs that generally don't execute the same queries over and over, these can be worthwhile benefits. ----------- Roger Tomas AG Communication Systems