Re: Compare 2 database queries?!

[Chris Devers]

My response breaks the flow of your script for clarity, but don't take 
that as me suggesting to just randomly move things around :-)

On Tue, 15 Mar 2005, SG Edwards wrote:

> if ($accession eq $result) { 
>   print " the protein is already in the database\n";
>   ...
>   print "the protein needs to be inserted into the database\n";

Nitpick: putting the variable in the print statement makes the print 
statment more useful:

print " protein $accession is already in the database\n";
print " protein $accession must be added to the database\n";

> my $result = $dbh->selectcol_arrayref("SELECT primary_acc_no FROM
>uniprot_entry_tbl WHERE primary_acc_no='P09466'");
>   ...
>   $dbh->do("INSERT INTO uniprot_entry_tbl (primary_acc_no) 
>values ('$accession')");
>   ...
> $dbh->disconnect;

It may help to add error checking code to all of this, as per this page:
 

You may already have one of these elsewhere in your code:

$h->{PrintError} = 1;
$h->{RaiseError} = 1;

...in which case you're way ahead of me :-)

Also, are you *positive* that the PostgreSQL SQL query --

INSERT INTO uniprot_entry_tbl (primary_acc_no) VALUES ('p09466')

-- is correct? Because it looks to me (and apparently to PostgreSQL) 
like you may have your data field being used as a column name in the 
query, which probably isn't correct :-)

Put another way, if the SQL statement works directly against the 
database, such as in the `psql` interactive shell, then you should be 
able to use the statement in your DBI script. If it doesn't work, then 
it may help to see all the database sections of your code, along with 
file numbers so that the error output can be lined up by list readers.



-- 
Chris Devers

-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 

Re: Compare 2 database queries?!

[SG Edwards]

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,  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]
>  
>
>
>



-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 

Re: Compare 2 database queries?!

[Chris Devers]

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]
 

Compare 2 database queries?!

[SG Edwards]

Hi,

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?!

Many thanks


-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]