SQL question

I am working with a db that has a datetime field (modified timestamp) in every table and would like to write a stored procedure that will loop through every table in a that db to determine what tables had new/modified records after a certain point in time. I am working with an application that I cannot get the source code to or documentation to determine when an entry is made using the app, where exactly it ends up in the db (I know the primary table it ends up in, but am also aware of at least one ancillary table and want to ensure I know ALL of them). Since I have a test db setup exactly like the production, I can control the entries to that db and should only see my entries (plus there is a userid field for double-check). I know I could write an individual query for every table, but there are a couple of hundred tables and was hoping there was a way to use the system tables to pull all the user tables out of to use in a foreach loop. TIA, Randy

Kennedy, Randy wrote: > I am working with a db that has a datetime field (modified timestamp) in > every table and would like to write a stored procedure that will loop > through every table in a that db to determine what tables had new/modified > records after a certain point in time. I am working with an application > that I cannot get the source code to or documentation to determine when an > entry is made using the app, where exactly it ends up in the db (I know the > primary table it ends up in, but am also aware of at least one ancillary > table and want to ensure I know ALL of them). Since I have a test db setup > exactly like the production, I can control the entries to that db and should > only see my entries (plus there is a userid field for double-check). > > I know I could write an individual query for every table, but there are a > couple of hundred tables and was hoping there was a way to use the system > tables to pull all the user tables out of to use in a foreach loop. SPL is not very good for dynamic code like that. What about something like this in dbaccess: OUTPUT TO PIPE "dbaccess my_db" WITHOUT HEADINGS SELECT "SELECT '", TRIM(tabname), "', my_datetime", " FROM ", TRIM(tabname), " WHERE my_datetime ...", ";" FROM systables WHERE tabid > 99 AND tabtype = "T" Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+

As Mark said, plain SPL is not capable of handling dynamic SQL (such as a list of table names). You could investigate the Dynamic SQL Datablade available at the IIUG web site. You'd have to worry about the returned data, of course -- not so bad with the blade since it puts everything into an LVARCHAR or thereabouts -- but a real pain in pure SPL. Using DB-Access is certainly one option. If you have Perl experience, Perl + DBI + DBD::Informix would be another option. SQLCMD might also be an option. You could arrange for a single SQLCMD process to generate a script file containing the SQL statements to execute, and then read that script back in as its input. -- set the field delimiter to blank delim ' '; output '/tmp/where.ever'; SELECT "SELECT '", TRIM(tabname), "', my_datetime", " FROM ", TRIM(tabname), " WHERE my_datetime ...", ";" FROM systables WHERE tabid > 99 AND tabtype = "T"; output '/dev/stdout'; input '/tmp/where.ever'; The SQL in the middle I cribbed from Mark's email. You'd probably get a leading and trailing blank on the literal table name -- if you replaced the commas with double pipes, you could avoid that. Note that if you have a different column name in each table, life gets very much harder. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management Solutions 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "Kennedy, Randy" | | | <RKennedy@scottsd| | | aleaz.gov> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 02/22/2003 11:36 | | | AM | | | | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: SQL question [469] | | | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| I am working with a db that has a datetime field (modified timestamp) in every table and would like to write a stored procedure that will loop through every table in a that db to determine what tables had new/modified records after a certain point in time. I am working with an application that I cannot get the source code to or documentation to determine when an entry is made using the app, where exactly it ends up in the db (I know the primary table it ends up in, but am also aware of at least one ancillary table and want to ensure I know ALL of them). Since I have a test db setup exactly like the production, I can control the entries to that db and should only see my entries (plus there is a userid field for double-check). I know I could write an individual query for every table, but there are a couple of hundred tables and was hoping there was a way to use the system tables to pull all the user tables out of to use in a foreach loop. TIA, Randy

Jonathan Le.... wrote: > The SQL in the middle I cribbed from Mark's email. You'd probably get a > leading and trailing blank on the literal table name -- if you replaced the > commas with double pipes, you could avoid that. In fact the commas cause dbaccess a headache (horizontal formatting breaks down to vertical), so the double pipes are necessary. I sent an update (after testing this time :) in an off-line discussion with Randy. > Note that if you have a different column name in each table, life gets very > much harder. Indeed. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+