Updating lots of database fields in a single row

[Chris Jack]

> On 3//1//013 0::1,, J?r?me ?t?v? wrote:
> > Something critical is missing in your code though: quoting:
> >
> > Replace $field = '$hash->{$field}' with " $field 
> > =".$dbh->quote($hash->{$field})

This would assume all fields were strings. To do it properly, you would need to have the metadata available and do:

$dbh->quote($hash->{$field}, $data_type)

You may also have to worry about $hash->{$field} containing SQL injection stuff. So bind parameters are potentially safer.

For Oracle, bind variable sometimes also offer performance benefits as the query plan is more cacheable. But be aware, this is at the price of losing specific statistical information about values in the where clause which will mean the optimiser has less information to look at it's histograms with. For specific queries, it may be faster to use actual values. e.g.:

select * from sometable where column_A = ?

If column_A is indexed and has 90% of rows with value 1 and has 1000 other well distributed values for the other 10% of rows: the best query plan will be different for value 1 versus other values.

The other reason I tend not to use bind parameters is it makes abstracting code harder to do. If you want to write a function that does something like:

populate_excel_tab_with_sql($excel_handle, $sql)

It's easier if you don't have to worry about bind variables. Obviously there are ways around this but the way I generate some SQL makes it easier to go with using values.

Chris