Michael A Chase wrote:
On Mon, 17 Mar 2003 20:49:49 +0000 Tim Bunce <[EMAIL PROTECTED]> wrote:
On Mon, Mar 17, 2003 at 12:04:22PM -0800, Michael A Chase wrote:
Spliting into separate prepare and execute stages and using placeholders would allow prepare once and execute many times and allow the subroutines to be used for SELECTs. I'd also sort the hash keys so the order of the keys and values would be consistant.
would that be because of some sql-reason? i always thought that 'values' was guaranteed to return the same order as 'keys'. could be my ignorance, though.
# All untested
# call as: my $sth = hash_prepare( $dbh, $sql, \%hash ); # %col% = place to insert column list # %ph% = place to insert placeholder list # %val% = place to insert value list # %eqp% = place to insert col=? pairs # %eqv% = place to insert col=val pairs sub hash_prepare {
I'd rather something like construct_sql(...) so people can use do(), prepare(), prepare(cached) etc as needed.
I started out that way and then noticed I needed $dbh if I was going to handle value tokens. As long as I had the $dbh anyway...
And using the established ODBC style of SQL escape clause would probably be smart, so something like:
INSERT INTO table ({dbi names}) VALUES ({dbi placeholders}) INSERT INTO table ({dbi names}) VALUES ({dbi values}) UPDATE table SET ... WHERE {dbi names = placeholders} UPDATE table SET ... WHERE {dbi names = values}
I haven't used ODBC, so I'm not familiar with it's style. Using consistant escapes makes sense though.
What about UPDATE table SET col = val, col = val WHERE {dbi ...}? Two hashes perhaps or does that just start to get too hard?
i am personally not gifted enough to think of a gneric, great way to do this (a generic sql-constructor), but wouldn't this be incredibly, incredibly cool to have? i, for one, have WAY too many lines that 'kind of' try to gnerate things like WHERE or GROUP BY clauses according to args passed to a sub (e.g. generateReport( 'name', 'ordercol', 'starttime', ....); ), and i'd bet that i'm not alone with that.
update: SQL::Generator seems to do some of these things.
*sigh*
M.
and used like:
($sql, @bind_values) = $dbh->construct_sql("...", \%hash); $dbh->do($sql, undef, @bind_values); or $sth = $dbh->prepare_cached($sql); $sth->execute(@bind_values);
A $dbh->construct_val( \%hash ) might also be useful to make sure nested queries keep getting the values in the right order. Maybe just defining the ordering explicitly in the fine manual would be enough.
