Thanks for that Chris, using your pseudocode (see below) I have tried the
following code:
(N.B. I have a table called uniprot_entry_tbl which contains the protein_ids in
a column called primary_acc_no)
#checks to see if any of the proteins are already in the DB
my $question = 'P09466';
my $result = $dbh->selectcol_arrayref("SELECT primary_acc_no FROM
uniprot_entry_tbl WHERE primary_acc_no='P09466'");
if ($accession eq $result) {
print " the protein is already in the database\n";
}
else {
print "the protein needs to be inserted into the database\n";
$dbh->do("INSERT INTO uniprot_entry_tbl (primary_acc_no) values
('$accession')");
}
$dbh->disconnect;
exit;
However, when I run this code it throws back the error
"DBD::Pg::db selectcol_arrayref failed: ERROR: column "p09466" does not exist at
extract_ann_uni.pl line 41, <GENO> line 155."
I know the SQL command is correct because I checked it manually but I don't know
why it says the column doesn't exist?! Please help!!
Quoting Chris Devers <[EMAIL PROTECTED]>:
> On Mon, 14 Mar 2005, SG Edwards wrote:
>
> > I have a perl script that queries a protein database (uniprot) and
> > puts protein data from the query into a PostgreSQL table. However,
> > what I would like it to do is check if the protein is in my database
> > already!!
> >
> > In my database I have a table with a column containing all the
> > protein_ids so I can return this as a query.
> >
> > What is the best way/fastest to check the data in my table against the
> > data I retrive from the UniProt database?!
>
> As pseudo-code --
>
> if ( ! check_for_protein_in( $dbh ) ) {
> add_protein_to_database( $dbh, $protein );
> }
> else {
> print "The protein was already in the database.\n";
> }
>
> We could get into more detail about what these subroutines might look
> like, but to do so we'd need to know what code you've tried so far, and
> maybe a little bit about the database schema, or at least the fields in
> that table.
>
> I was thinking that you could do some kind of special SQL statement,
> with something like (making this up / definitely won't work):
>
> INSERT INTO uniprot
> VALUES ($id, $token{$id}, $marker{$id})
> UNLESS EXISTS id=$id
>
> But as far as I can tell, PostgreSQL doesn't support such a construct,
> unless you can figure out how to hammer it into a SELECT sub-statement
> at the end rather than a (non-existent) EXISTS clause.
>
>
>
> --
> Chris Devers
>
> --
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> <http://learn.perl.org/> <http://learn.perl.org/first-response>
>
>
>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
