empty result set

[yeller2]

Hi,

I'm running a perl script that accesses a mysql database. I'm trying to run a simple select statement that gives me an empty result set. The wierd thing is that if I simply replace the table name with another table name - the statement returns stuff. Could the one table have some sort of issue with it?

Thanks

 

[audiopro]

What does the 'rows' var return?
If not 0(zero) it is a good indication that something is wrong with the query

Keith

http://www.studiosoft.co.uk

 

[yeller2]

It is 0(zero).

 

[audiopro]

Is there data in the table

Try:-
select * from table

Any results?


Keith

http://www.studiosoft.co.uk

 

[yeller2]

Yes there is data in the table.
Yes I do get the resulting data when I perform that select.

 

[guelphdad]

have you tried your query outside of PERL (i.e. directly in the mysql client) to make sure the error isn't something you are doing with your PERL code?

if you have then perhaps you can post your query here.

 

[yeller2]

Sirs,

I can assure you there is nothing wrong with the query and I don't think there is anything wrong with the perl because if I simply replace the table name with another - when using the get all fields (*) guy - I receive data. I'm wondering if there could be some sort of issue with the table itself.

 

[audiopro]

Showing your query may help us to help you

Without seeing the query it is hard to diagnose but a couple of things to try.
Check the field types

select field from table where field = 5
and 
select field from table where field = '5'

would give different results depending on the field type.
The first would find results in a numeric field but not in a text field.

Keith

http://www.studiosoft.co.uk

 

[yeller2]

Here's the query except for tablename:

select * from test;

 

[audiopro]

Your reply before the last one was a little unclear.
Do both tables (call them good and bad) return data with a wild card (*) search?

Keith

http://www.studiosoft.co.uk

 

[yeller2]

yup

 

[audiopro]

What is the query which fails to return results and what are the field types of the selected fields?.


Keith

http://www.studiosoft.co.uk

 

[yeller2]

I've already given the query and here are the field types = int(10) unsigned, text, timestamp

 

[audiopro]

select * from test;

How does the query appear in your Perl script, that example would fall over.

Example from my current proj.

my $sql="SELECT * FROM table";
my $sth=$dbh->prepare($sql);
$sth->execute();

Keith

http://www.studiosoft.co.uk

 

[yeller2]

After some more playing around, I ran the same script on another server and set the db host to be the server I've been trying to run the queries on (unsuccessfully) and it worked. Any ideas?

 

[yeller2]

What I found was that I was having an issue with certain tables when I used tried to read them using a host other than localhost from the local computer. The permissions were the same in the mysql user table, but someone told me that a permission issue could be somewhere else. I'll just settle for reading the tables using a localhost account.