ESQL/C limitations

>Jim Gordon <jgordon@ssf-sys.DHL.com> writes: >Tony Heskett writes in X-Informix-List-Id: <list.350> writes: >> >>> would you be looking for and why would a direct C Library help? >>How 'bout something like >> >> select foo >> from bar >> where dist(x1, y1, x2, y2) < some_number>> >>dist() figures out how far P1 (x1,y1) is from P2 (x2, y2), and is >>part of a C lib that you can add in. V. handy if you're doing >>anything complex [sic]. Last time I saw this was in Empress RDBMS. > >Yeah, but this would just be extensions to the current functions >available from Informix SQL (Eg. Count, Sum, Today, User, Length, >Date, Day, Current, Extend, etc). I'm sure we could all make a wish >list for here but it doesnt require anything more than currently >exists and is required for SQL generally not just esql/c. > >>> The programmer is >>> restricted to the SQL commands because otherwise he might?? be >>> able to screw up the connection with the backend server. > >>Don't think so. ESQL/C will let you do some fairly hairy things, >>otherwise a simple kill(3) or "tbmode -z $pid" will probably finish >>things off. > >I had got the impression, probably mistakenly, that John was >suggesting a more direct access to the functions that actually get >parsed into the c programs by the pre-processor to do things like >execute, declare, fetch previous/next, etc. If you were to call these >in the wrong order or with the wrong parameters you might be able to >screw up the sqlexec/turbo. Sigh. I can't figure out how to explain this problem in a simple, obvious way. ESQL/C currently restricts cursor names and statement-ids to hard-wired constants. This is fine if you know what SQL you are going to run into at compile time, but this restriction is a big problem if you don't know what SQL is coming at you. If you want to prepare 10 statements and hold them for later execution, you have to write 10 separate statements like "$ prepare stobj1 from $string_var" "$ prepare stobj2 from $string_var" "$ prepare stobj3 from $string_var" "$ prepare stobj4 from $string_var" "$ prepare stobj5 from $string_var" "$ prepare stobj6 from $string_var" "$ prepare stobj7 from $string_var" "$ prepare stobj8 from $string_var" "$ prepare stobj9 from $string_var" "$ prepare stobj10 from $string_var" because the statement-id has to be that hard-wired constant. What I would like Informix to do is to allow cursors and statement-ids to be host variables so it is possible to have the same execute or fetch statement refer to different statement-ids or cursors, like: $declare cursor cur[10]; $ prepare $cur[i] from $string_var What I have is a proprietary interpreted language that is written in C. This language contains currently contains an SQL interface that looks something like this (example code): sql execute immediate "database stores". sql prepare stmnt-1 from "select * from foo where bar = ?" sql execute stmnt-1 using hst-var1. !loop sql fetch stmnt-1 into hst-var3, hst-var3, hst-var4, hst-var5. . . _process row_ . if <sqlcode> = 0 goto !loop. hst-var1, hst-var2, etc. are host variables; stmnt-1 is a handle which indicates which previously prepared or executed statment to refer to. When my compiler is run, each handle is replaced by a number, and the compiler determines which kind of sql call this is: prepare, execute, fetch, execute immediate. The problem is that I can't make an association between the handle and ESQL/C's statement-id except by brute force (this is a C code example): /* parse the "handle-th" SQL statement, pointed to by "string" */ prepare(handle, string) int handle; char *string; { switch (handle) { case 1: $prepare st1 from $string; case 2: $prepare st2 from $string; case 3: $prepare st3 from $string; case 4: $prepare st4 from $string; . . . } } I would also have to do this for execute, declare, open, fetch, and close. If you look at the code produced by the pre-compiler, ESQL/C defines a _SQCURSOR data type. When you prepare a statement, the pre-compiler creates a cursor (named _SQ1, _SQ2), associated with each cursor name the programmer uses, and refers to that. For an example, look at the code "unload.ec" produces. All I want is the ability to name cursors directly, instead of having the compiler do it for me. The only reason I can think of that this is not currently done is because this is all the current ANSI-SQL embedded language interface standard requires. Oracle has both a pre-compiler and call interface. The call interface allows the programmer to name cursors, and it is much more flexible to use. John I do not speak for SNI, and they do not speak for me. Internet: jobrien@sni-usa.com UUCP: {linus,uunet}!nixbur!jobrien "We return you now to your regularly scheduled crisis..."