Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

Quoting problem in DBI:ODBC

by Grygonos (Chaplain)
on Jun 27, 2003 at 14:57 UTC ( #269604=perlquestion: print w/replies, xml ) Need Help??

Grygonos has asked for the wisdom of the Perl Monks concerning the following question:

Here is my code. I'm using MS-Access 97' unfortunately.
#Compile all records under a clients aliases foreach $alias (@aliases) { $query = "SELECT collection_information.[field1], "; $query .= "collection_information.[field2], collection_informat +ion.[name] "; $query .= "FROM collection_information WHERE CLIENT=\'" . $alia +s ."\'"; $sth = $dbh->prepare($query); $sth->execute(); #For each record returned while(my @record = $sth->fetchrow_array) { #Insert the record into the database $query = "INSERT INTO " . $client{'name'} ."_table "; $query .= "([field1],[field2],[name]) VALUES "; $query .= "(\'".$record[0]."\',\'".$record[1]."\',\'".$record[ +2]."\')"; $dbh->do($query); } $sth->finish(); }
Ok here's my problem... the insert statement is inserting people's names in one field. Now if someone's name such as O'Sullivan occurs... then this piece of code will foobar because of the single quoting in the VALUES portion of the insert statement. However, when I try and double quote it... it tells me it expects 3 parameters.

In my early DBI experience I had this problem occur and corrected it by changing the query to double quote the values... that was under MySQL (God I miss those days). Any possible remedies to this?
Thanks in advance, Gry

Replies are listed 'Best First'.
Re: Quoting problem in DBI:ODBC
by hmerrill (Friar) on Jun 27, 2003 at 19:31 UTC
    Just personal preference, but you might like it. IMHO it makes it easier to read the SQL - try formatting the DBI SQL like this:
    $query = q{ SELECT field1, field2, name FROM collection_information WHERE CLIENT = ? }; $sth = dbh->prepare($query) || die "prepare failed: $DBI::errstr\n"; $sth->execute($alias) || die "execute failed: $DBI::errstr\n";
    And, most(?) DBD:: modules support table aliasing, like this:
    $query = q{ SELECT, c.state,, FROM collection_information c WHERE c.CLIENT = ? }
    which makes it so that you don't have to type the long table name in front of each field. In fact, when you're selecting from just one table, you don't need to use the table name at all in front of each field being selected.

      I prefer doing the sql that way too... didn't know that I could use q{} to accomplish it thanks.
      Thanks for the table aliasing tip I didn't know about that
      As for putting the table names in front of things, I don't like to, but I think Access bombed the last time I tried to leave them out. I'll give it a shot when I'm @ work on monday. Thanks !
Re: Quoting problem in DBI:ODBC
by Grygonos (Chaplain) on Jun 27, 2003 at 15:19 UTC
    please disregard this... I had always quoted my own literals... never realized that i should just
    and so on.. sorry.
      Another option is to use placeholders, which is even cleaner than using $dbh->quote(). It isn't the right fit for 100% of your queries and statements, but it is for 99% of them. Check the DBI:: docs for details on how to use it. It would be something like.
      eval { my $sth = $dbh->prepare("INSERT INTO users (fname, lname, phone) VA +LUES(?, ?, ?)"); $sth->execute($fname, $lname, $phone); $dbh->commit();

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://269604]
Approved by Seumas
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (6)
As of 2020-08-12 15:23 GMT
Find Nodes?
    Voting Booth?
    Which rocket would you take to Mars?

    Results (66 votes). Check out past polls.