Re: Storing number '001' ?

Chuck,

> With these two tables, entering data gets a tad more complicated.

Yup. Time to write a GUI.

> CREATE TABLE blast (
> blast_id SERIAL PRIMARY KEY,
> others..,
> score integer,
> homolog text
> );
>
>
> CREATE TABLE contig_blast (
> blast_id INTEGER REFERENCES blast(blast_id) ON DELETE CASCADE,
> contig_id INTEGER REFERENCES contig(contig_id) ON DELETE CASCADE,
> UNIQUE(blast_id,contig_id)
> );
>
> w/ all contig data loaded into TABLE contig
>
> 1st: lookup contig_id for blast data to be entered.
> 2nd: INSERT INTO blast(col1..coln) VALUES(A...N)
> 3rd: after INSERT -> get blast_id for data just INSERTED
> $blast_id = currval('blast_id')
>
> 4th: insert contig_id & blast_id into TABLE contig_blast

100% correct.

>
>
> I can't seem to recover the value of blast_id form a Perl script:
>
> for a test TABLE test:
>
> CREATE TABLE test (
> blast_id SERIAL PRIMARY KEY,
> homolog text
> );
>
>
> *****************
>
> #!/usr/bin/perl -w
> use strict;
> use Pg;
> my ($conn,$result,$ID);
>
> my $CONNECT = $DBNAME . $DBHOST . $LOGIN . $PWD;
>
> $conn = Pg::connectdb($CONNECT);
> die $conn->errorMessage unless PGRES_CONNECTION_OK eq $conn->status;
>
>
> $conn->exec("INSERT INTO test(homolog) VALUES ('row5');");
>
> $ID = $conn->exec("currval('blastx_id');");

Correction: $ID = $conn->exec("SELECT currval('blastx_id');");
And are you sure the sequence is named "Blastx_id"? Normal naming would
be: 'blast_blast_id_seq'

Fnally, if this system ever goes mulit-user, you will want to wrap the
above in a transaction to prevent simultaneous updates from yielding a
deceptive CURRVAL result.

>
> print "ID: $ID\n";
>

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco