Anyone can add to the FAQ, they just need to register and create a password first, which can be done from the FAQ screen.
Ilya -----Original Message----- From: Jeff Zucker [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 12, 2002 1:39 PM To: David Steinbrunner; dbi-users Subject: Re: Having Problems with DBD::CSV (Ilya - FAQ?) Hi David, SQL string values must occur between single quotes, not double quotes: SELECT * FROM foo WHERE bar = 'baz' CORRECT SELECT * FROM foo WHERE bar = "baz" WRONG Values that have single quotes inside them must not appear in the SQL as raw single, quotes, they must be escaped. In DBD::CSV they can be escaped with either a backslash or by using two single quotes (not a double quote): SELECT * FROM foo WHERE bar = 'Tim O\'Reilly' CORRECT SELECT * FROM foo WHERE bar = 'Tim O''Reilly' CORRECT SELECT * FROM foo WHERE bar = 'Tim O'Reilly' WRONG Although you can manually escape the quotes, it is much better to use the methods provided by DBI to handle this situation (in all DBDs, not just DBD::CSV) - the $dbh->quote() method and placeholders. See the DBI docs for the quote() method and placeholders. The placeholders method is best since it also offers other protections. Here's a sample: my $sth = $dbh->prepare( "SELECT * FROM foo WHERE bar = ?" ); $sth->execute("Tim O'Reilly"); Notice the O'Reilly is not escaped, the placeholder method does that for you. Also notice that there are no quotes around the question mark in the SQL. If you always use placeholders or the quote() method, you won't have to manually escape anything and you won't have to know or care whether values have embedded quotes. Placeholders can also be passed as part of a do() call, so here's a small script that illustrates four different ways to correctly use embedded single quotes: #!/usr/local/bin/perl -w use strict; use DBI; my $table = 'foo'; my $dbh = DBI->connect('dbi:CSV(RaiseError=1):') or die $DBI::errstr; my $val = q~Tim O'Reilly~; my $escaped_val1 = q~Tim O\'Reilly~; my $escaped_val2 = q~Tim O''Reilly~; my $quoted_val = $dbh->quote($val); $dbh->do("DROP TABLE IF EXISTS $table"); $dbh->do("CREATE TABLE $table ( id INTEGER, txt CHAR(4) )"); $dbh->do("INSERT INTO $table VALUES (1,?)",{},$val); $dbh->do("INSERT INTO $table VALUES (2,'$escaped_val1')"); $dbh->do("INSERT INTO $table VALUES (3,'$escaped_val2')"); $dbh->do("INSERT INTO $table VALUES (4,$quoted_val)"); my $sth=$dbh->prepare("SELECT * FROM $table"); $sth->execute; while (my $r=$sth->fetch) { print "@$r\n"; } Except for the method of escaping quotes (which can vary between SQL implementations) all of this applies to any DBD, not just DBD::CSV. -- Jeff David Steinbrunner wrote: > Hello, > > I'm having two problems with DBD::CSV. I am working with two comma > delimited files and to start out on the small project I need to match > names from one file to a name in the other file. Here is the block of > code that I am using to get an idea of how many matches I have and what > is not matching: > > my $dir = "/path/to/files/"; > my $dbh = DBI->connect("DBI:CSV:f_dir=$dir;csv_eol=\n;csv_sep_char=,;") > or die "Cannot connect: " . $DBI::errstr; > > my $sql = "Select fname, lname from customer_info"; > my $sth = $dbh->prepare($sql) > or die "Cannot prepare $sql: " . $DBI::errstr; > my $rc = $sth->execute > or die "Cannot execute SQL Statement: " . $DBI::errstr; > > my ($yes,$no); > > while (my ($fname,$lname) = $sth->fetchrow) > { > my $name = "$fname $lname"; > my $sql2 = "Select email from order_info where name = '$name'"; > print "$sql2\n"; > my $sth2 = $dbh->prepare($sql2) > or die "Cannot prepare $sql2: " . $DBI::errstr; > my $rc2 = $sth2->execute > or die "Cannot execute SQL Statement: " . $DBI::errstr; > > my $rows = $sth2->rows; > > if ($rows > 0) { > $yes++; > print "1\n"; > } else { > $no++; > print "$name\n"; > } > } > print "\nyes: $yes\no: $no\n"; > $sth->finish if $sth; > $dbh->disconnect if $dbh; > > On the first run I found I have one Irish man to contend with. The > error follows but the name has been changed to protect the innocent: > > > Select email from order_info where name = 'Tim O'Reilly' > DBD::CSV::db prepare failed: Mismatched single quote before: 'Reilly' > Cannot prepare Select email from order_info where name = 'Tim O'Reilly': > Mismatched single quote before: 'Reilly' > > > So I changed the single quotes to escaped double quotes (\") as it > likely should have been in the first place but found my self with a new > error: > > > Select email from order_info where name = "George W. Bush" > SQL ERROR: Table '"George W' in WHERE clause not in FROM clause! > > SQL ERROR: Couldn't find predicate! > > SV = PVIV(0x83b86c0) at 0x82cbd48 > REFCNT = 1 > FLAGS = (PADBUSY,PADMY,ROK) > IV = 0 > RV = 0x81eefd8 > DBD::CSV::st execute failed: dbih_getcom handle > DBD::CSV::Statement=HASH(0x81eefd8) is not a DBI handle (has no magic) > at /usr/lib/perl5/site_perl/5.6.0/SQL/Statement.pm line 164. > Cannot execute SQL Statement: dbih_getcom handle > DBD::CSV::Statement=HASH(0x81eefd8) is not a DBI handle (has no magic) > at /usr/lib/perl5/site_perl/5.6.0/SQL/Statement.pm line 164. > > > Well, it seems that DBD::CSV thinks anything in double quote should be a > table/file. Also if I remove the or die statements from the prepare and > execute I will find that I still get an error from our Irish friend. > > > Select email from order_info where name = "Tim O'Reilly" > DBD::CSV::db prepare failed: Mismatched single quote before: 'Select > email from order_info where name = "Tim O'Reilly" > > > I'm I doing something wrong or is the world against me? =) Thanks for > any help in advance! > >
