Prev: How to pass array from java to perl using inline java
Next: Request for a PERL script to download files
From: PB0711 on 31 Jan 2007 22:48
Hello all ,

First THX. Second I have a program that looks through a file
extracting data and then puts it into a database. I thought I had
designed a nice check with the select * from table where name = $name
and number = $num; . Then I had an if statement to see if that
reported anything but of course it did cus sql gives the nothing found
stuff. I know this is pretty easy but I cannot think of a way to do.
So can someone suggest a way to check if the entry already exist.

Thank you,

PB

From: Brian McCauley on 1 Feb 2007 02:33
On Feb 1, 3:48 am, "PB0711" <hpben...(a)gmail.com> wrote:
> Hello all ,
>
> First THX. Second I have a program that looks through a file
> extracting data and then puts it into a database. I thought I had
> designed a nice check with the select * from table where name = $name
> and number = $num; . Then I had an if statement to see if that
> reported anything but of course it did cus sql gives the nothing found
> stuff.

Please produce a minimal but complete script illustrate what you mean
by "sql gives the nothing found stuff."

If you try to read one row and there isn't one then there wasn't one.

my $sth = $db->prepare('select * from table where name = ? and number
= ?');
$sth->execute($name,$num);
my $exists = !!$sth->fetchrow_arrayref;
$sth->finish;

unless ($exist) {
#....
}

You can simplify this using DBI's convenience method
selectrow_arrayref

my $exists = !!$db->selectrow_arrayref('select * from table where name
= ? and number = ?',{},$name,$num);

Alternatively count the rows:

my $rows = $db->selectrow_array('select count(*) from table where name
= ? and number = ?',{},$name,$num);

> I know this is pretty easy but I cannot think of a way to do.

There are many more.

From: DJ Stunks on 1 Feb 2007 11:48
On Jan 31, 8:48 pm, "PB0711" <hpben...(a)gmail.com> wrote:
> Hello all ,
>
> First THX.

Did George Lucas pay you to say that?

> Second I have a program that looks through a file
> extracting data and then puts it into a database. I thought I had
> designed a nice check with the select * from table where name = $name
> and number = $num; . Then I had an if statement to see if that
> reported anything but of course it did cus sql gives the nothing found
> stuff. I know this is pretty easy but I cannot think of a way to do.
> So can someone suggest a way to check if the entry already exist.

define a UNIQUE index and use INSERT IGNORE

-jp

From: PB0711 on 2 Feb 2007 15:34
What I was meaning by the SQL found nothing stuff is
" mysql> select * from metabolite where molid > 50000;
Empty set (0.00 sec)"
I was trying to see if it reported something back and if it didn't
then I assumed that it was emtpy and I could add stuff. But it always
of course gives the above.
Thank you for the help I see where to go with the check now.

Cheers,

Paul

DJ Stunks wrote:
> On Jan 31, 8:48 pm, "PB0711" <hpben...(a)gmail.com> wrote:
> > Hello all ,
> >
> > First THX.
>
> Did George Lucas pay you to say that?
>
> > Second I have a program that looks through a file
> > extracting data and then puts it into a database. I thought I had
> > designed a nice check with the select * from table where name = $name
> > and number = $num; . Then I had an if statement to see if that
> > reported anything but of course it did cus sql gives the nothing found
> > stuff. I know this is pretty easy but I cannot think of a way to do.
> > So can someone suggest a way to check if the entry already exist.
>
> define a UNIQUE index and use INSERT IGNORE
>
> -jp

From: J. Gleixner on 2 Feb 2007 17:30
PB0711 wrote:
> What I was meaning by the SQL found nothing stuff is
> " mysql> select * from metabolite where molid > 50000;
> Empty set (0.00 sec)"
> I was trying to see if it reported something back and if it didn't
> then I assumed that it was emtpy and I could add stuff. But it always
> of course gives the above.

Of course, using the mysql client really has nothing to
do with this news group. :-)

> Thank you for the help I see where to go with the check now.

That's not really a good query to use to test if something
exists or not, because it'll return everything from that table
for the values in the where.

To see if something is found, have SQL do the work:

select count(*) from...
or
select 1 from ...

That will return 0, if nothing, 1 or more, if using count, when
there are results.

You could also use the rows method from DBI to do something
based on the results too.
#.. select column from table where...
if( $sth->rows > 0 ) { #found results.. do something with them }
else { #query returned no results.. error or insert }


Depending on what you want to do, there's also a 'replace'
command in MySQL, which might be useful.
 | 
Pages: 1
Prev: How to pass array from java to perl using inline java
Next: Request for a PERL script to download files