[Bioperl-l] Trying to get a mysql DB from genbank flat files

[Osborne, Brian]

Wilfred,

Yes. And there is also the quote() method, which escapes offending
characters. However, I don't know what the complete offending character set
is and how it overlaps with the set of annoying characters in the
description, it might not help here.

quote 
$sql = $dbh->quote($string); 
This method escapes special characters (quotation marks, etc.) from strings
and adds the required outer quotation marks. May not be able to handle all
types of input (i.e binary data). 

Example using the above methods: 
#!/usr/bin/perl -w 
use DBI; 
use strict; 
my $dbh = DBI->connect("DBI:mysql:contacts",undef,undef) 
or die "Unable to connect to contacts Database: $dbh->errstr\n"; 
my $sth = $dbh->prepare("SELECT uid FROM contact WHERE last_name =
'Flaherty'"); 
$sth->execute or die "Unable to execute query: $dbh->errstr\n"; 
my $row = $sth->fetchrow_arrayref; 
my $uid = $row->[0]; 
$sth->finish; 
my $newname = $dbh->quote("The Flahertys'"); 
my $statement =qq(UPDATE contact SET last_name = '$newname' 
                  WHERE uid = $uid); 
my $rc = $dbh->do($statement) or die "Unable to prepare/execute $statement:
$dbh->errstr\n"; 
print "$rc rows were updated\n"; 
$dbh->disconnect; 
exit; 

Brian O.

 -----Original Message-----
From: 	Wilfred Li, Ph.D. [mailto:wilfred@sdsc.edu] 
Sent:	Thursday, November 15, 2001 12:41 PM
To:	bioperl-l@bioperl.org
Subject:	RE: [Bioperl-l] Trying to get a mysql DB from genbank flat
files

>thanks for your mail, you have magically bumped into our daily nightmare,
>that is that very often people put crazy characters in the description
>lines, and we have to find ways of backslashing all of them otherwise
>they will break mysql statements. Could you mail me the offending record?
Hi,

If bind variables are used in place of a plain insert statement, many of
the special characters will be taken care of by perl DBI. e.g.

$sth->prepare("insert ... values (?, ..., ?)");
$sth->execute($id, ..., $kw);

or use

$dbh->do("insert ... values (?, ..., ?)", $id, ..., $kw);

to combine the two steps into one.

I had the problem with SeqAdaptor.pm when parsing SwissProt.

Wilfred


_______________________________________________
Bioperl-l mailing list
Bioperl-l@bioperl.org
http://bioperl.org/mailman/listinfo/bioperl-l