Re: Easy Q: Dummy table?

"Richard Thomas" a 'crit dans le message <62koso$tg@cssun.mathcs.emory.edu>... >Chad Chervitz wrote: > >> I have a need to print CURRENT variables from within a simple SQL file >> being piped to dbaccess. It is so that I can surround a SQL >> statement with two datetime selects to trace how long the SQL >> statement took to run. >> >> Is there a way just to "select CURRENT" so that it gets output along = >with >> the SQL results? >> >> IOW, I'm looking for the equivalent of a tableless >> select in Sybase (i.e., select sysdate()) or a DUMMY table select in >> Oracle (i.e., select to_date("MM-DD-YYY", now) from DUMMY). >> >> Thanks, >> Chad > >If you are piping through DB-ACCESS, then you could use this: > >cat << EOSQL | dbaccess stores7 >!date >SELECT * FROM customer;>!date >EOSQL > >It works like a charm, and is really really simple. My preference, = >however, is the following: > >CREATE PROCEDURE "informix".timestamp( message CHAR(30) ) > RETURNING CHAR(80); > > DEFINE t_stamp DATETIME YEAR TO SECOND; > DEFINE r_count INTEGER; > DEFINE msg_line CHAR(80); > > LET t_stamp =3D CURRENT; > LET r_count =3D DBINFO('sqlca.sqlerrd2'); > LET msg_line =3D t_stamp || " " || message || r_count; > RETURN msg_line; > >END PROCEDURE > >...and having done that once, you can use: > >EXECUTE PROCEDURE TIMESTAMP("Some comments about my last SQL...")> >Which displays the date-time, your comments, and the number of rows = >processed by the last statement in a reasonably neat format. There is however a problem: this does not allow you to follow execution times, because all occurences of your TIMESTAMP procedure will return the SAME date/time info. Informix Online seems to compute the value of the CURRENT function within a session global storage, only at the time of the initial request sent to the server, and then all SQL requests and procedures only reference that area for the whole time of execution of each request sent by the client to the server. So using a separate TIMESTAMP procedure or directly the CURRENT buil-in function does not help, both methods will return the same date/time value, which is the time when the client's request started execution. This behavior may be desirable when you want to create transactions that should use the same CURRENT value throughout its execution (because the CURRENT value may be used to update several tables, and because the CURRENT value may be used within triggers). However this is not compatible with Sybase's getdate() or Oracle equivalent functions, which both return an updated value. So you cannot use the Informix CURRENT built-in function to track execution times of your long-processing batch procedures. To achieve this, you must make successive requests to the server, and call the CURRENT function separately from your batch procedures, so that the session-level global area which stores the CURRENT value is updated after each request. Why Informix does not have an optional specifier for the CURRENT function to get the updated datetime value, I don't know. So this is a problem.