Re: IDS Feature Request List (including potential new requests).

Talk to Art Kagel. He already does this kind of stuff with dynamic names for prepared SQL statements a feature Oracle does not have! http://www.thetechtwo.com/detail-11064865.html "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." Each session (application server process) needs to prepare the sql but the new-ishSQL statement cache should make this process fast. Still need this feature?

>>> Talk to Art Kagel. He already does this kind of stuff with dynamic names for prepared SQL statements a feature Oracle does not have! >>> Seems like if people are doing this in a round about away it makes an even stronger argument for giving them this feature so they can do it in a straight forward way. I think it would be a win for Informix. It is also a clear demonstration that this is a useful feature. The problem with my application is that it uses a connection pool where each hit to the database uses any available connection that exists. Because prepared statements are centric to the connection and not the server if you have 2000 connections and 25 prepared statements then you have 50,000 prepared statements. You can create a hash map with the statements that you have prepared and manage it for each connection but if the server could manage this I think you would reduce the number of prepared statements on the server side and the client side reducing memory usage on both sides. Basically, you have a table similar to sysprocedures and or sysprocplan where the "procedures" are basically degenirate stored procedures that consist of 1 SQL statement that takes a given number of parameters (prepared statement variables) which returns the result set to the application. Obnoxio suggested using stored procedures (in a direct email not posted here, I guess he is being shy ;-) as a work around which is basically how they could/should be implemented on the server side. I have had much trouble processing multiset return values from stored procedures in Java (if I could do this it would work as a work around but I haven't gotten it to work). So if you know how to do this in java please tell me and I'll use this as a work around until Informix implements this shortly. Does Oracle own a patent on this? Also, I haven't upgraded to 10 yet (I am running 9.21) and SSC seems to make my server crash horribly. So I have no confidence in it.

david@smooth1.co.uk wrote: > Talk to Art Kagel. He already does this kind of stuff with dynamic > names for > prepared SQL statements a feature Oracle does not have! > > http://www.thetechtwo.com/detail-11064865.html > > "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." > > Each session (application server process) needs to prepare the sql but > the > new-ishSQL statement cache should make this process fast. > > Still need this feature? <BRAG> actually, that happens to be a feature of SQSL - eg a simple parallel load generator for c in 1,2,3,4,5,6,7,8,9,10; let pid(c)=fork; if (pid(c)==0); whenever error continue; output to "/dev/null"; for s in 0,1,2,3,4,5,6,7,8,9; prepare st(s) from "create table t"||s||"(col1 int)"; prepare st(s+10) from "insert into t"||s||"values(1)"; prepare st(s+20) from "updtate t"||s||" set col1=2 where col1=1"; prepare st(s+30) from "delete from t"||s||" where col1=1"; prepare st(s+40) from "select col1 from t"||s; prepare st(s+50) from "drop table t"||s; done; while (1); execute st(random(60)); done; fi done for c in 1,2,3,4,5,6,7,8,9,10; let r=waitpid(pid(c)); done; (from memory, untested) the code above is rather unrefined but you get the idea - in fact SQSL implements jagged multidimensional hashes, so you can use mnemonics, and create classes of statements, with each class not needing to have the same number of statements, eg prepare st("ddl", s) from "create table t"||s||"(col1 int)"; prepare st("dml", s) from "insert into t"||s||"values(1)"; prepare st("dml", s+10) from "updtate t"||s||" set col1=2 where col1=1"; prepare st("dml", s+20) from "delete from t"||s||" where col1=1"; or, if you want to go the inefficient way, and test the capabilities of the statement cache, use the expansion facility: ... let st(s)="create table t"||s||"(col1 int)"; ... while (1); <+get st(random(60))+>; done; ... if that sounds interesting, then wander to 4glworks.com </BRAG> -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Informix faq http://www.iiug.org/techinfo/faq/informix.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Nice. How does Informix treat it on the server side? I think this is another vote for Shared "Named" Prepared statements. ;-)

I just thought of another feature that I have asked for and would like. Tuple comparison. It is part of the SQL standard now and makes much SQL easier to read and less complicated for example: select * from employee where (last_name, first_name, MI) >= ("Smith","Jack", "L") ; instead of select * from employee wherelast_name > "Smith" or ( last_name = "Smith" and first_name > "Jack" or ( first_name = "Jack" and MI >= "L" ) ) ; Harder to work around example: select * from employee where (hire_date, retire_date) >= (selectmin(hire_date), min(retire_date) from employee) ; So in summary I would like to add 3 features: 1) Named server side prepared statements (can be persistant or they can exist as long as the server is online. Use SQL or just API changes) If persistant then you would need to add SQL extensions to create and drop them otherwise you can just make a change to the API. I think I prefer persistant with SQL extension. 2) Count Distinct with multiple columns. This seems to be part of the standard. 3) n-tuple comparison. This is also part of the SQL standard.