portable REPLACE INTO

NogDog

Any suggestions on the most portable method of emulating MySQL's REPLACE INTO statement? Is INSERT INTO . . . ON DUPLICATE KEY . . . reasonably portable, or would it be best to do some sort of multi-statement transaction, first checking if the record exists, and if it does do the update, else the insert?

PS: I just realized that for what I'm working on right now, I don't need to worry about this, so no biggie right now; but I'd still like to know for future reference.

bpat1434

Sxooter!!! What's the "better" function in Postgres?

NogDog

On a related note, I suspect using DESCRIBE in the following method would not necessarily port to other DBMS's (main concern being what column name is used for "Field"). Any thoughts on a replacement (which must work even if the table is empty)?

// get array keys for $data
protected function getFields()
{
   $sql = 'DESCRIBE `' . $this->table . '`';
   $stmt = $this->db->prepare($sql);
   if($stmt->execute() == false)
   {
      $this->error = $stmt->errorInfo();
      return false;
   }
   while($row = $stmt->fetch(PDO::FETCH_ASSOC))
   {
      $this->data[$row['Field']] = null;
   }
   return true;
}

bpat1434

I believe DESCRIBE does port to other DBMS's, at least Postgres. No-one can ever be sure about MSSQL or Access (::shudders:🙂.

/me breaks out old trusty SQL book... looks up DESCRIBE...

I don't see DESCRIBE as being a custom mySQL extension, and according to this list it's a reserved keyword, which usually means it's supported in the standard.

As far as your "replace" or "on duplicate key" issue, you could create a general stored procedure and have it general enough to do something like:

BEGIN;
-- other operations
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- Assume the above fails because of a unique key violation,
-- so now we issue these commands:
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
-- continue with other operations, and eventually
COMMIT;

I'm not a stored proc guy, but it would give you the functionality to call your stored procedure with parameters (table name, updates, where clause, etc.) to mimick the MySQL functionality.

Sxooter

Desc(ribe) is not defined by the sql spec. The information schema is defined by I think sql99 and sql3 (2003). PostgreSQL has both it's own custom \d command in psql which runs a set of complex queries that then report on table(s) and index(es) etc to tell you how they're put together. The information schema, which PostgreSQL and newer versions of Oracle and maybe a few other databases support would be most portable. IF it was supported by MySQL.

As for replace into / insert into on duplicate key neither of those are portable.

There's a merge command defined in the SQL spec. Oracle implements it properly. MySQL has their own version of merge which does not do the same thing or in the same way as the spec would define. PostgreSQL does not support the ANSI merge command or its own custom one.

The most portable method is likely to be to try an insert and if that fails then an update. There's a slight possibility of a race condition in that someone might delete the row between your tried (and failed insert due to dup key) and your update. So I guess you'd have to check for errors on update and then insert again. ugh.

Portability sucks.

So, the basic method would be something like:

insert into mytable ...;
Check for error in php
if error is duplicate key then
update mytable set...

NogDog

Thanks for the feedback.

Sxooter wrote:
Portability sucks.

I was looking into diving into PDO to use it for an application I was fiddling around with (one of those things you work on for yourself with grandiose plans and no funding 😉 ). The more I get into it, though, the more I start to agree with that sentiment. 🙁