Zikula Community

• **unknown user**
  
  

  • Rank: Softmore
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 240

  Posted: Nov 03, 2005 - 12:24 AM

  Hi,
  
  I'm trying to UPDATE or INSERT some data into the database.
  
  If I do an UPDATE, is there any indication as whether the UPDATE worked? I'm not talking about a database connection error, but more like:
  
  Did the UPDATE faile because:
  1. The data didn't exist in the database to upadate
  2. The UPDATE failed because a duplicate key was found.
  
  I'm trying to do an:
  
  UPDATE data
  if (failed)
  INSERT data
  
  I can't figure out if I can get any error codes or not.
  
  I'm using:
  
  

  Code

  $result = $dbconn->Execute($sql);

  
  
  To execute my SQL query, but when I do a print_r($results) there is nothing there to let me know the status of the UPDATE.
  
  Thanks,
  Grant

  • 
  •  
• markwest
  
  

  • Rank: Team Member
  • Registered: Mar 18, 2002
  • Last visit: Oct 21, 2009
  • Posts: 6606

  Posted: Nov 03, 2005 - 12:50 AM

  Grant,
  
  I suggest a read of the adodb docs. This shoud give you all the info you need. Basically adodb will return false in the event of an error - more info on the error can be obtained from the ErrorMsg and ErrorNo methods.
  
  -Mark
  
  --
  Visit My homepage and Zikula themes.

  • 
  •  
• **unknown user**
  
  

  • Rank: Softmore
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 240

  Posted: Nov 03, 2005 - 05:22 AM

  Mark,
  
  I checked out the Error Methods. I am now using:
  
  

  Code

  $result = $dbconn->Execute($sql);
  echo "Error MSG: ".$dbconn->ErrorMsg()."<br>";
  echo "Error Num: ".$dbconn->ErrorNo()."<br>";

  
  
  to check my status. The echo statement are for testing.
  
  If I try to UPDATE a row that doesn't exist, I'm not getting an error code. I was hoping to see something like "UPDATE failed, row doesn't exist".
  
  However, if I do a INSERT and the row exists, I do get the error:
  

  Quote

  
  Error MSG: Duplicate entry '[KEY HERE]' for key 2
  Error Num: 1062
  

  
  
  My problem is that I am trying to bulk import a lot of data. I'm importing the data on a regular basis and sometimes the data changes or new data is added. For speed reasons and to reduce the number of SQL calls, I would like to do:
  
  UPDATE data
  if (failed)
  INSERT data
  
  I can't seem to figure out how to get an error condition when I try to UPDATE a row that doesn't exist.
  
  Thanks,
  Grant

  • 
  •  
• markwest
  
  

  • Rank: Team Member
  • Registered: Mar 18, 2002
  • Last visit: Oct 21, 2009
  • Posts: 6606

  Posted: Nov 03, 2005 - 05:25 AM

  One way would be to ise a SELECT first to attempt to get the record. If this fails use an INSERT otherwise use an UPDATE.
  
  -Mark
  
  --
  Visit My homepage and Zikula themes.

  • 
  •  
• **unknown user**
  
  

  • Rank: Softmore
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 240

  Posted: Nov 03, 2005 - 05:45 AM

  I thought about that, but if I do a SELECT first, I'm still at 2 SQL statements. If I can do a update first then INSERT on a failure, I can get down to only 1 SQL statement if the row already exists. I don't mind updating the row even if the data hasn't changed since it will save me from doing the second SQL statement.

  • 
  •  
• markwest
  
  

  • Rank: Team Member
  • Registered: Mar 18, 2002
  • Last visit: Oct 21, 2009
  • Posts: 6606

  Posted: Nov 03, 2005 - 11:01 AM

  Grant,
  
  I don't see how your going to achieve your aims. My solution is the best I can some up with.
  
  -Mark
  
  --
  Visit My homepage and Zikula themes.

  • 
  •  
• **unknown user**
  
  

  • Rank: Softmore
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 240

  Posted: Nov 06, 2005 - 10:39 AM

  It's just that I'll be doing a lot more updates than inserts. I figured I do an update first and then only an INSERT if the UPDATE failed. That way I'd only have 1 SQL transaction most of the time and 2 SQL transactions at worst case.

  • 
  •  
•  
•  

Users on-line

• internetking,
• slam

This list is based on users active over the last 60 minutes.