Re: DBI execute() args in array format (or similar) possible?

Jim--
I'm starting to feel like a broken record, because most of the SoPWs I answer are about DBI, and my advice usually consists of either:

read the docs (which I assume you've done :)

consider using $dbh->quote() and $dbh->quote_identifier(), which it appears you haven't.

The whole reason I discovered these two methods was in trying to prepare dynamic sql statements, much like you're doing here. I would suggest that you try to apply $dbh->quote_identifier() to the names of any tables or columns, and $dbh->quote() to any values you're using. Try to do the join after these methods have been applied, and you might be successful.

One consideration is that you seem to be using placeholders. While this is encouraged and can greatly speed up your queries, I've found that it's sometimes difficult to generate queries that are dynamic both at the level of the structure of the query (table names and columns) and at the specific values in the query (using ? as a placeholder for values). While the docs say that specifying a value as a parameter to $dbh->execute() will automatically quote it appropriately, I just haven't had much luck with my attempts. If you continue to have problems, consider rewriting the queries to use explicit values instead of placeholders. Not optimal, but if it works it might give you a place to start debugging from (and a functioning application! :)

No post of mine regarding DBI would be complete without referring to chromatic's DBI is OK, which was my most valuable resource in moving out of the beginner stages of using DBI. He uses clever quoting and map combinations to dynamically generate queries, and also talks about using placeholders. You should definitely check it out if you haven't.

good luck with your project, feel free to reply with any questions
--au

Comment on Re: DBI execute() args in array format (or similar) possible? Select or Download Code

Replies are listed 'Best First'.
Re: Re: DBI execute() args in array format (or similar) possible? by perrin (Chancellor) on Aug 12, 2002 at 19:56 UTC
Um, I have never had any problems with placeholders quoting things correctly. If you think the quoting has bugs, please report them with examples to the DBI list. I don't think they do, and placeholders are definitely preferred.	[reply]
Re: Re: DBI execute() args in array format (or similar) possible? by dws (Chancellor) on Aug 12, 2002 at 19:57 UTC
While the docs say that specifying a value as a parameter to $dbh->execute() will automatically quote it appropriately, I just haven't had much luck with my attempts. Which DBD are you using, and can you provide an example of where execute() fails to quote appropriately?	[reply]
Re: Re: Re: DBI execute() args in array format (or similar) possible? by aufrank (Pilgrim) on Aug 12, 2002 at 20:27 UTC
apparently I was very wrong... any problems I had previously were simply a result of my own insuffiencies. I had always assumed this to be the case, but I mentioned my problems because the only solution I came to when I couldn't get placeholders to work was to take them out and thought the original poster might come to a similar place. The problems I was referring to were with using DBI and the ODBC DBD to perform UPDATE statements on MS Access DBs using placeholders. I just wrote up a test statement, though, and had no problems. Sorry to pass bogus information, perhaps this code can help the original poster anyway... `my @updates = map {$dbh->quote_identifier($_) . ' = (?)'} ('call 1 com +ment', 'call 2 comment', 'call 3 comment', 'call 4 comment'); my $name_field = $dbh->quote_identifier('last name'); my $update_string = join ", ", @updates; my $sql = qq{UPDATE $table SET $update_string WHERE $name_field = (?)} +; print "$sql\n"; my $sth = $dbh->prepare_cached($sql); $sth->execute('foo','bar','baz','qux','smith');` [download] I was thoroughly wrong, but maybe the code will end up helping where my previous post couldn't :) --au	[reply] [d/l]
Re: Re: DBI execute() args in array format (or similar) possible? by snafu (Chaplain) on Aug 12, 2002 at 20:26 UTC
read the docs (which I assume you've done :) Yes sir...sure have. Although, admittedly, a lot of that is a bit over my head and unfortunately, as many of us do, I don't have the time to truly study everything in the docs :(. consider using $dbh->quote() and $dbh->quote_identifier(), which it appears you haven't right again! :) Your suggestions have been quite helpful. _ _ _ _ _ _ _ _ _ _ - Jim Insert clever comment here...	[reply]


XP is just a number
	PerlMonks