Re: Help! with ESQL cursors

In article <1nh2s6INN4e0@emory.mathcs.emory.edu> dave.snyder@snide.com (Dave Snyder) writes: >Quoting Scott A Mayo... >} >} I am writing a database server based on informix SE. I get >} handed a list of field names, and an SQL selection (=WHERE) >} criteria. Then my caller want to fetch records, generally >} a few at a time, and see the fields he requested for each. Not >} terribly complicated. He also wants to know the total number >} of records his WHERE clause netted. >} >The way I've handled this is as follows: > > 1) Declare a cursor that selects just the rowid(s) based on the user's WHERE > clause. > 2) Declare another cursor that selects everything based on a rowid. > 3) Open the first cursor and fetch all the rowid(s) into a malloc'ed array of > integers and free that 1st cursor. > 4) Use whatever logic you want to move back and forth through the array of > rowid(s). > 5) Open the second cursor with the current array element's stored rowid, fetch > the data into local variables, and close the second cursor. and hope that the record hasn't been deleted. > 6) If new user criteria arrives, start the process all over again. I've done similar where it's important that a user can move around at will (with a provision to say "deleted record" if that's happened!), but for Scott's specific situation, I prefer a different approach: 1) Declare a cursor that selects what you want 2) Create a temp file with tmpfile() 3) Open the cursor and fetch all the rows, stuffing the results in the temp file (perhaps using "unload" format, with | and \\n escaped with \\) 4) rewind the file, and output it This is especially attractive for Scott's situation, since he's outputting the data to a stream anyway. One nice thing about this is that a tmpfile() can get a lot bigger than a malloc()'d array. Another nice thing about tmpfile() over mktemp() or tmpnam() is that the file automatically goes away when your program exits (even if it exits because of a core dump). If you don't like the overhead of parsing the unload-format file, you can always use fread() and fwrite(). The other nice thing is that you don't have to do the storage management of the dynamic array. OK, realloc() isn't that painful, but it can be subtile: Filling an N element array can be O(N^2) if you aren't careful (if you always realloc() a fixed number of additional elements, it's O(N^2)... To get to O(n) you need to increase the size of the dynamic array geometrically). Yes, I know that this sort of thing isn't really a concern in your typical database application, but I can't help myself! > This [arrays of rowid's] is the core logic of how db4glgen functions, > you may want to pick up a copy (for examples since you are using ESQL/C). Out of curiosity... Does db4glgen increase array size linearly or geometrically? -- Bill Foote bill@pds.com PDS Corp, Los Angeles, CA USA uunet!promdist!bill