Re: DB class, MySQL and nextId()

on 2004/04/15 17:37, Torsten Roehr at roehr@zilleon.com made the following allegation: >> Then, you would need to convert *every >> single* bit of code that is inserting into those tables to use PEAR and >> the new sequence table. That sounds like a pretty massive overhaul if >> you wanted to use PEAR DB in one small function/class that might run >> INSERT queries. That doesn't sound very user friendly nor does it sound >> worthwhile to start using PEAR DB late in the game (at least for systems >> using MySQL). > > I think the benefit is worth the work. And you only need a sequence for > those tables that are referenced as foreign keys in other tables (where you > need the id for the reference insert). You don't need it for every table. > E.g. if you have a child and a parents table you only need a sequence for > the parents table because usually you don't need the id from the child > insert. I agree with the original poster that this behavior is not helpful if you already have a bunch of tables that are interlinked and that currently use auto_incremented numbers. One thing to consider is that the data in the database may be updated in another way, i.e., command line or other app, etc. Let's say a java program inserts another record to my table which up to that point was in sync with the sequence. My next insert call via PEAR::DB will fail. I'm not sure how well PEAR works with other databases (i.e., PostgreSQL, Oracle, MSSQL), but I would *hope* that it makes use of the native sequencing/identities instead of one always having to use the extra table. If not, I imagine there would also be problems with standard stored procedures as well. Chris, one thing you may wish to do is use a wrapper function for your PEAR::DB calls. It's an ugly hack of course, but something like this might work for you: function my_insert_query($table, $data, $pk, $connection) { // This function performs an INSERT query // with parameters and returns the sequence ID // $data should be an associative array of name/value pairs // $connection is the established database connection if (!preg_match('~^mysql~', $connection->phptype)) { $id = $connection->nextId($table); $data[$pk] = $id; } $result = $connection->autoExecute($table, $data, DB_AUTOQUERY_INSERT); if (DB::isError($result)) { trigger_error('Problem with INSERT query: ' . $result->getMessage() . "\n\n" . $result->getDebugInfo(), E_USER_WARNING); return 0; } else { if (!preg_match('~^mysql~', $connection->phptype)) { return $id; } else { return mysql_insert_id($connection->connection); } } } Hope that helps, Sincerely, Paul Burney <http://paulburney.com/> <?php while ($self != "asleep") { $sheep_count++; } ?>