Re:

From: mathprof@bigfoot.com > >I wasn't quite sure how to "bundle" my newbie questions, so instead I >thought I'd mention an "application" I'd like to write (using >Informix-SE) and point out the questions I have, as well as ask if >anyone sees additional problems or has comments on how I could write >the application better. > >I start with two tables: > >TABLE people: lastname char(255), firstname char(255), zipcode(255) > >TABLE comments: username char(255), timestamp datetime year to second, >refid int, comment char(255) > >Basically, I want to be able to make a comment anytime I change >(UPDATE or INSERT) 'people'. In a perfect world, I want to type >something like: > >EXAMPLE 1>> insert into people values ('Frapples','Robert','12345') >WITH COMMENT "met Bob at Halloween party" > >OR > >EXAMPLE 2>> update people set zipcode='12345' where zipcode='54321' >WITH COMMENT "the 54321 zip code has now changed to the 12345 zip >code" > >I realize I can't actually rewrite the SQL interpreter (nor would I >want to), so in real life, this might be a procedure or something. The >results I would want are: > >EXAMPLE 1>> > >insert into people values ('Frapples','Robert','12345')> >insert into comments values ("mathprof",current,***,"met Bob at >Halloween party")> >where "***" would be the rowid of the row just inserted into 'people'. > >EXAMPLE 2>> > >update people set zipcode='12345' where zipcode='54321'> >FOR EACH ROW AFFECTED BY THE UPDATE: insert into comments values >("mathprof",current,***,"the 54321 zip code has now changed to the >12345 zip code") > >where "***" would be the rowid of each row affected by the update (so, >if 15 rows were changed, this would insert 15 rows into comments) > >In this way, I would have a history of changes made to a given row (or >at least a history of comments whenever that row was changed). Note: >the username field is semi-useless since I'm the only one using my >machine right now-- however, if we get into a situation where multiple >people are updating a table, it's useful to know who did the actual >update. > >For realistic purposes, I'm assuming I'll need to create a procedure >that takes two arguments: the SQL command I want to execute, and the >comment I want to enter. For examples, if I named the procedure XYZ: > >CALL PROCEDURE XYZ("insert into people values >('Frapples','Robert','12345')", "met Bob at Halloween party"); > >CALL PROCEDURE XYZ("update people set zipcode='12345' where >zipcode='54321'", >"the 54321 zip code has now changed to the 12345 zip code"); > >Here are the questions/problems/issues I had: > >*Is it possible to write a procedure that does what I want? A >procedure that executes its first argument as an SQL command, stores >what rows were changed/inserted, and then inserts rows into a second >table based on those values? Dynamic SQL is frustratingly unavailable in Informix's SPL. What I would probably do is build a set of stored procedures for each action (ins_people, upd_people, del_people, etc.) which got passed all the data as parameters, or build one SP where the first parameter was the type of action I wanted to do. >*In an absolutely ideal world, I'd like to enforce comments-- in other >words, no one could UPDATE/INSERT the 'people' table without making a >comment -- is there any way to do this? (eg, "table 'people' can only >be updated by procedure xyz") Yes. Look into permissions in the manuals at http://www.informix.com/answers/ >*I believe that quoted strings in Informix-SE have a limit of 255 >characters-- this isn't a problem for the comment (since I'm storing >it in a char(255) anyway), but, in more complex situations, the SQL >query could easily exceed 255 characters. How do I work around this? No. Strings have a limit of 32K, it's _quoted_ strings that have a limit of 255 chars. So you can build a long string in a variable and pass that to your SP. >*In the 'comments' table, I'm using the 'rowid' from the 'people' >table to refer to a specific record. For example, if the 'comments' >table had a row like: > >username timestamp refid comment >======== ========= ===== ======================== >mathprof 1999-01-01 00:00:00 7 Blah > >then it would refer to the record whose rowid is 7 in the 'people' >table. Is this a good idea? Would 'update statistics' ruin my >indexing? Is there a better way to refer to a specific row in another >table? 1. Not really, it's OK in SE but not the best approach. 2. No. 3. Better to use referential integrity. :-) >*Philosophically speaking, is my approach valid? Should I be using a >completely different and unrelated approach? I've seen some discussion >that a database should be used to STORE data, and that a secondary >application program should be used to MANIPULATE data. I like the >unified approach of having the database do everything, because the >database knows a lot more about its own data than an external program. I'm on the side of your "opponents", but then I'm very old-fashioned. After all, things they put in databases must be good ideas, or they wouldn't do it, would they? :-) ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com