phpLens
 home  products examples manual  faq support forum  contact news  login store

Forums: phplens   adodb
Forums:   ADOdb Help & Dev:   Replies 
Search
Topic: why SELECT before GetInsertSQL?
author: Andreas   created: 19-01-2005 06:21:16 PM
Hi!

I have a question about GetInsertSQL()/GetUpdateSQL() (thanks for your response, John!). I don't understand why it needs a RecordSet Object, to generate an Update or Insert query.

I found a lot of examples like this:

fields["col"] = "val";
$rs = $db->Execute("SELECT col FROM table WHERE key='bar'" );
$update_sql = $db->GetUpdateSQL($rs, fields);
$db->Execute($update_sql);

If I use PEAR_DB::autoExecute() it would look like that:

$fields["col"] = "val";
$db->autoExecute('table', $fields, DB_AUTOQUERY_UPDATE, "key='bar'");

Why does ADOdb use an extra query? For GetInsertSQL() it is even more strange for me ;-)
At the moment I try to port a project from PEAR::DB to ADOdb, so I write some wrappers.

I think it should work as follows:

function autoExecute ($table, $fields_values, $mode = DB_AUTOQUERY_INSERT, $where = FALSE) {
$sql = 'SELECT * FROM '.$table;
if ($where!==FALSE) {
$sql .= ' '.$where;
}
$rs = $this->Execute($sql);
if ($mode == DB_AUTOQUERY_UPDATE) {
return $this->Execute($this->GetUpdateSQL($rs, $fields_values));
}
else if ($mode == DB_AUTOQUERY_INSERT) {
return $this->Execute($this->GetInsertSQL($rs, $fields_values));
}
}

But if it is a really large table, I don't like to execute something like "SELECT * FROM table WHERE foo1='bar1' AND foo2='bar2'" when I cannot see the sense ;-)

The nice thing about autoExecute() is that you don't need any SQL. But would be nice if one could use something like:

GetInsertSQL($table_name, $fields_values)
GetUpdateSQL($table_name, $fields_values, $where_cond)


Why this extra query?


regards
Andreas

PS: do you think my autoExecute() would be OK?
Topic: Re:why SELECT before GetInsertSQL?
author: John Lim   created: 19-01-2005 11:36:36 PM
These are code contributions by others. I personally never use these features. Some people like them, but i can never understand why.

Perhaps it is a good idea to implement autoexecute() like you suggest, modelled like PEAR DB's version.
Topic: Re:why SELECT before GetInsertSQL?
author: Andreas   created: 20-01-2005 06:33:55 AM
Hello!

I like to use autoExecute(), because for me it is nice to use and I safe some code.

To INSERT data:

$data = array (
'col1' => $val1,
'col2' => $val2,
'col3' => $val3,
);
$db->autoExecute('table', $data);


That's all! And it uses Prepare() by default.
For an UPDATE of the same data I only need another line:

$db->autoExecute('table', $data, DB_AUTOQUERY_UPDATE "id=$id");


What I do not like so much is the WHERE-condition. But I don't know how to do it better.

I have written a small autoExecute() methode(untested yet), which has to be compatible with the PEAR::DB API (because of my old scripts):

	function autoExecute($table, $table_fields, $mode = DB_AUTOQUERY_INSERT, $where = false) {

if (count($table_fields) == 0) {
$this->raiseError(DB_ERROR_NEED_MORE_DATA);
}
switch ($mode) {
case DB_AUTOQUERY_INSERT:
$cols = implode(',', array_keys($fields_values));
$values = '?'.str_repeat(',?', count($fields_values)-1);
$sql = 'INSERT INTO '.$table.' ('.$names.') VALUES ('.$values.')';
break;
case DB_AUTOQUERY_UPDATE:
$set = implode('= ?,', array_keys($fields_values)).'= ?';
$sql = 'UPDATE '.$table.' SET '.$set;
if ($where !== false) {
$sql .= ' WHERE '.$where;
}
break;
default:
$this->raiseError(DB_ERROR_SYNTAX);
}
$stmt = $this->Prepare($sql);
return $this->Execute($stmt, array_values($fields_values));
}



PEAR uses:
autoExecute()
-> autoPrepare()
-> buildManipSQL()
-> prepare()
-> execute()


I use:
autoExecute()
-> Prepare()
-> Execute()


I generated the queries using implode() and str_repeat() instead of using .= with foreach.

I don't see a reason to split this methode as PEAR does, but it could be easily refactored to get a function returning the SQL-string, or use 2 methodes autoInsert() and autoUpdate() instead of constants (I think I would like that, so you don't need the constants).

But I have 2 questions about it:

1. Error Handling:
I have no Idea what the error-handling (inside adodb) should look like?! For now I took the PEAR Error-Handling as used in PEAR_DB::autoExecute(), but I don't think it works as used.

2. Constants

To get PEAR::DB compatibility for my scripts, I use:

// PEAR constants
if (!defined('DB_FETCHMODE_ASSOC')){
define('DB_FETCHMODE_ASSOC',2);
}
if (!defined('DB_FETCHMODE_OBJECT')){
define('DB_FETCHMODE_OBJECT',3);
}

But this is not the best solution I think. Wohot do you think?


regards
Andreas
Topic: Re:why SELECT before GetInsertSQL?
author: John Lim   created: 20-01-2005 08:07:32 AM
This will be in next version of adodb:

/*
Similar to PEAR DB's autoExecute(), except that
$mode can be 'INSERT' or 'UPDATE' or DB_AUTOQUERY_INSERT or DB_AUTOQUERY_UPDATE
If $mode == 'UPDATE', then $where is compulsory as a safety measure.

$forceUpdate means that even if the data has not changed, perform update.
*/
function AutoExecute($table, $fields_values, $mode = 'INSERT', $where = FALSE, $forceUpdate=true, $magicq=false)
{
//$flds = array_keys($fields_values);
//$fldstr = implode(', ',$flds);
$sql = 'SELECT * FROM '.$table;
if ($where!==FALSE) $sql .= ' WHERE '.$where;
else if ($mode == 'UPDATE') {
ADOConnection::outp('AutoExecute: Illegal mode=UPDATE with empty WHERE clause');
return false;
}

$rs =& $this->SelectLimit($sql,1);
if (!$rs) return false; // table does not exist

switch((string) $mode) {
case 'UPDATE':
case '1':
$sql = $this->GetUpdateSQL($rs, $fields_values, $forceUpdate, $magicq);
break;
case 'INSERT':
case '2':
$sql = $this->GetInsertSQL($rs, $fields_values, $magicq);
break;
default:
ADOConnection::outp("AutoExecute: Unknown mode=$mode");
return false;
}
if ($sql) return $this->Execute($sql);
return false;
}
Topic: Re:why SELECT before GetInsertSQL?
author: Andreas   created: 20-01-2005 08:51:49 AM
Hi John!

Wow, that's really great!

What I'm interested in - why did you use the version with SELECT...GetUpdateSQL...?

I think my second version is by far more effective, I merged it with your version:


// complete AutoExecute() with no extra methodes needed
// - does not perform extra query as GetInsertSQL and GetUpdateSQL
// - dows not use extra mothodes to build and prepare the sql-query
// - uses implode() and str_repeat() instead of .= and foreach as PEAR::DB does
function AutoExecute($table, $table_fields, $mode = 'INSERT', $where = false)
{
if (count($table_fields) == 0) {
ADOConnection::outp('AutoExecute: insufficient data supplied');
return false;
}
switch ((string) $mode) {
case DB_AUTOQUERY_INSERT:
case '1':
$cols = implode(',', array_keys($fields_values));
$values = '?'.str_repeat(',?', count($fields_values)-1);
$sql = 'INSERT INTO '.$table.' ('.$names.') VALUES ('.$values.')';
break;
case DB_AUTOQUERY_UPDATE:
case '2':
$set = implode('= ?,', array_keys($fields_values)).'= ?';
$sql = 'UPDATE '.$table.' SET '.$set;
if ($where !== false) $sql .= ' WHERE '.$where;
else {
ADOConnection::outp('AutoExecute: Illegal mode=UPDATE with empty WHERE clause');
return false;
}
break;
default:
ADOConnection::outp("AutoExecute: Unknown mode=$mode");
return false;
}
$stmt = $this->Prepare($sql);
if ($stmt) return $this->Execute($stmt, array_values($fields_values));
return false;
}


And here an alternative, there you could use AutoExecute() as above, and also AutoInsert() and AutoUpdate()


// uses AutoInsert() and AutoUpdate() which perform queries themselves
function AutoExecute($table, $table_fields, $mode = 'INSERT', $where = false)
{
switch ((string) $mode) {
case DB_AUTOQUERY_INSERT:
case '1':
return $this->AutoInsert($table, $table_fields);
break;
case DB_AUTOQUERY_UPDATE:
case '2':
return $this->AutoUpdate($table, $table_fields, $where);
break;
default:
ADOConnection::outp("AutoExecute: Unknown mode=$mode");
return false;
}
}

// performs an (prepared) Insert-Query from given parameters
// - could be used somewhere else, directly...
function AutoInsert($table, $table_fields)
{
if (count($table_fields) == 0) {
ADOConnection::outp('AutoInsert: insufficient data supplied');
return false;
}
$cols = implode(',', array_keys($fields_values));
$values = '?'.str_repeat(',?', count($fields_values)-1);
$sql = 'INSERT INTO '.$table.' ('.$names.') VALUES ('.$values.')';
break;
$stmt = $this->Prepare($sql);
if ($stmt) return $this->Execute($stmt, array_values($fields_values));
return false;
}

// performs an (prepared) Update-Query from given parameters
// - could be used somewhere else, directly...
function AutoUpdate($table, $table_fields, $where)
{
if (count($table_fields) == 0) {
ADOConnection::outp('AutoUpdate: insufficient data supplied');
return false;
}
$set = implode('= ?,', array_keys($fields_values)).'= ?';
$sql = 'UPDATE '.$table.' SET '.$set;
if ($where !== false) $sql .= ' WHERE '.$where;
else {
ADOConnection::outp('AutoUpdate: Illegal mode=UPDATE with empty WHERE clause');
return false;
}
$stmt = $this->Prepare($sql);
if ($stmt) return $this->Execute($stmt, array_values($fields_values));
return false;
}



And another one, where you could use GetAutoInsertSQL() and GetAutoUpdateSQL(), which do the same as GetInsertSQL() and GetUpdateSQL(), but much more simple:


// uses GetAutoInsertSQL() and GetAutoUpdateSQL() which generate the SQL
function AutoExecute($table, $table_fields, $mode = 'INSERT', $where = false)
{
if (count($table_fields) == 0) {
ADOConnection::outp('AutoExecute: insufficient data supplied');
return false;
}
switch ((string) $mode) {
case DB_AUTOQUERY_INSERT:
case '1':
$sql = $this->GetAutoInsertSQL($table, $table_fields);
break;
case DB_AUTOQUERY_UPDATE:
case '2':
$sql = $this->GetAutoUpdateSQL($table, $table_fields, $where);
break;
default:
ADOConnection::outp("AutoExecute: Unknown mode=$mode");
return false;
}
$stmt = $this->Prepare($sql);
if ($stmt) return $this->Execute($stmt, array_values($fields_values));
return false;
}

// returns SQL as string from given parameters
// - could be used somewhere else, directly...
function GetAutoInsertSQL($table, $table_fields)
{
$cols = implode(',', array_keys($fields_values));
$values = '?'.str_repeat(',?', count($fields_values)-1);
return 'INSERT INTO '.$table.' ('.$names.') VALUES ('.$values.')';
}

// returns SQL as string from given parameters
// - could be used somewhere else, directly...
function GetAutoUpdateSQL($table, $table_fields, $where)
{
$set = implode('= ?,', array_keys($fields_values)).'= ?';
$sql = 'UPDATE '.$table.' SET '.$set;
if ($where !== false) $sql .= ' WHERE '.$where;
else {
ADOConnection::outp('GetAutoUpdateSQL: Illegal mode=UPDATE with empty WHERE clause');
return false;
}
return $sql;
}



best regards,
Andreas
Topic: Re:why SELECT before GetInsertSQL?
author: Andreas   created: 20-01-2005 09:42:01 AM
an error:

$sql = 'INSERT INTO '.$table.' ('.$names.') VALUES ('.$values.')';

needs to be changed to

$sql = 'INSERT INTO '.$table.' ('.$cols.') VALUES ('.$values.')';

(for each alternative).
Topic: Re:why SELECT before GetInsertSQL?
author: Andreas   created: 20-01-2005 09:49:49 AM
I would like the AutoExecute() + AutoUpdate() + AutoInsert() Alternative most, because it stays compatible to PEAR::DB/MDB2, but I would like to use special methodes for UPDATE and INSERT, with less parameters.

I think GetAutoInsertSQL() and GetAutoUpdateSQL() would be too special, and should only get the cols as parameter, not the data, too(as it is now). But if you do this, I think it becomes easy to build errors into scripts using this... And it is just to special.

AutoUpdate() and AutoInsert() would be fine to work with.
Topic: Re:why SELECT before GetInsertSQL?
author: Andreas   created: 20-01-2005 09:56:25 AM


/* Usage examples: */

// the array-key is the fieldname in the database table
// the array-value is the new content for that field
$data = array (
'col1' => $val1,
'col2' => $val2,
'col3' => $val3,
);

// this will insert the data above as a new recordset into 'table':
$db->autoInsert('table', $data);

// this will update the recordset of 'table' with id=$id using the data above:
$db->autoUpdate('table', $data, "id=$id");

Topic: Re:why SELECT before GetInsertSQL?
author: John Lim   created: 20-01-2005 11:00:58 PM
> why SELECT before GetInsertSQL?

Reasons:

1. GetInsertSQL is designed to work with arrays where not array elements are field values. So we have to check whether the keys are field values, so we query first.

2. GetInsertSQL/autoExecute will always be slower than hand-coding SQL. So if you use autoExecute, you are not interested in speed. So the initial SELECT is ok.

3. AutoUpdate/AutoInsert will not be added, as adodb's policy is to follow PEAR DB, which do not have these functions.
Topic: Re:why SELECT before GetInsertSQL?
author: Andreas   created: 21-01-2005 04:16:39 AM
OK, thank you!
Topic: Re:why SELECT before GetInsertSQL?
author: Andreas   created: 21-01-2005 04:31:30 AM
If you want PEAR::DB compatibility you should change:


case 'UPDATE':
case '1':

case 'INSERT':
case '2':

to

case 'UPDATE':
case '2':

case 'INSERT':
case '1':


From PEAR::DB's DB.php:
define('DB_AUTOQUERY_INSERT', 1);
define('DB_AUTOQUERY_UPDATE', 2);


After this change you version works for me.
Topic: Re:why SELECT before GetInsertSQL?
author: John Lim   created: 22-01-2005 00:22:53 AM
Thx. Fixed.
Topic: Re:why SELECT before GetInsertSQL?
author: Andreas Korthaus   created: 25-01-2005 07:08:30 AM
FYI, the difference between AutoExecute() used in 4.60 and my version is about 300-400% execution-time (small MySQL4 DB, no server-load, query-caching enabled). If I change the AutoExecute() in 4.60, a really complex script with only one AutoExecute() call becomes about >5% faster (xdebug was used).
Topic: Re:why SELECT before GetInsertSQL?
author: John Lim   created: 25-01-2005 11:43:15 AM
Your implementation will break existing code. Speed is not the issue. Flexiblity and backward compat is.
Page 1
Search

View Source

email: contact#phplens.com (change # to @)     telephone (malaysia): 60-3-7947 2888     fax (malaysia): 60-3-7947 2800