Error in fetching row of a database table

[varakal]

I couldnt understand where the error could be.
I get the correct sql statement, when I execute this statement in MySQL, it gives the correct document_id. So there is no mistake in SQL statement.

There are no compilation errors or runtime errors, but I couldnt get correct value in $document_id[0].
Thanks in advance.


The code is

my $dbh_delmetadata_sql = "SELECT document_id FROM delivery_metadata WHERE tracking_id = '$data[1]'";

print "sql is $dbh_delmetadata_sql\n";

my $do_dbh_delmetadata = $dbh_delmetadata->prepare($dbh_delmetadata_sql) or die "Couldn't prepare for delivery_metadata";

$do_dbh_delmetadata->execute() or die "Couldn't execute dest delivery metadata statement";

my @document_id = $do_dbh_delmetadata->fetchrow_array();

print "Document id is $document_id[0]\n";

 

[arn0ld]

I would try
print "@document_id\n"; ## see full set of returned values

 

[parkers]

hi varakal,

Here are one or two things you investigate...

Initial assumption is that tracking_id is the primary key and therefore contains unique values only?

i.e. $data[1] will match only one tracking_id and return only one document_id?

if tracking_id is unique and therefore only returns one value for document_id then you could replace your fetchrow code with the following ...

my $document_id = $do_dbh_delmetadata->fetchrow_array();
print "\n",$document_id;

Now ... if tracking_id is not the PK and not unique then your code may be returning the last instance of tracking_id ... if this is the case then you should change your code to the following:

while (my @document_id = $do_dbh_delmetadata->fetchrow_array())
{
push (@stored_doc_ids, @document_id)
}

print "\n @stored_doc_ids \n";

Just some thoughts... hope this helps a bit...
parkers

 

[parkers]

... sorry i should have said if the tracking_id is not PK then your code may be returning the last instance of document_id ... (not tracking_id)

 

[varakal]

I tried everything but still not getting any value. I even tried hardcoding and writing the tracking_id instead of data[1]. Still it didnt worked.

tracking_id is not a primary key, it may appear more than once. I tried the push too, but still I couldnt see any value.

 

[arn0ld]

Have you run your query from whatever interactive/command-line interface mysql provides?

have you tried
print "@document_id\n";

 

[parkers]

I assume you can connect to the database OK?

only asking as I do not see the code to connect ...

e.g.
$dbh_delmetadata = DBI->connect ( DBI:xxxx:host:xx.xx.xx.xx; database='xxxx',"user","pass") or die ("Cannot connect to DB");

Other than that you could also try adding back-ticks around each attribute so as to clearly define what the attribute name is. With this you would change your SQL to be:

SELECT `document_id` FROM `delivery_metadata` WHERE `tracking_id` = '$data[1]'

 

[varakal]

When I run the query outside, it fetches a row.
I tried printing @document_id, it shows nothing as below

document_ids is

There is the code to connect:
my $dbh_delmetadata = DBI->connect("DBI:mysql:database=$dest_data_db;host=$dest_host",$dest_user,$dest_password) or die "Couldn't connect to destination database" . DBI->errstr;

Yes, I even tried putting back-ticks, couldnt help.

I tried to trace using DBI->trace(2, 'dbtrace.log');
and this is what I got in the middle.

-> prepare for DBD::mysql::db (DBI::db=HASH(0x81fccb4)~0x81fcd38 'SELECT `document_id` FROM `delivery_metadata` WHERE `tracking_id` = '0004107566598346900022421108461909413'')
Setting mysql_use_result to 0
<- prepare= DBI::st=HASH(0x8201b5c) at populate_fax_log.pl line 57
-> execute for DBD::mysql::st (DBI::st=HASH(0x8201b5c)~0x8201c1c)
-> dbd_st_execute for 081fca38
<- dbd_st_execute 0 rows
<- execute= '0E0' at populate_fax_log.pl line 58
-> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x8201b5c)~0x8201c1c)
-> dbd_st_fetch for 081fca38, chopblanks 0
<- fetchrow_array= ( ) [0 items] row-1 at populate_fax_log.pl line 61

Last line says that no rows are fetched.
Line 61 is
while (@document_id = $do_dbh_delmetadata->fetchrow_array())

 

[parkers]

If you take out the WHERE clause from your SQL does it still return no data?

 

[varakal]

It returns lots of document_ids without WHERE clause.

The thing is, when I place the WHERE clause, it is not even going into the while loop as it is not fetching any row.

while (@document_id = $do_dbh_delmetadata->fetchrow_array())
{
print "\n Inside document id is @document_id";
push(@stored_doc_ids, @document_id);
}

Inside print statement is never printed when there is WHERE clause. But when there is no WHERE clause, many rows are being fetched.
I again tried executing the SQL statement with WHERE clause at the MySQL command prompt, it just works fine and it returns a row with document_id. I dont know why this perl program is not taking this!!

 

[parkers]

good I guess, at least the process of fetching data is OK ... try removing the single quotes around the $data[1] ... maybe the tracking_id is being interpreted as the wrong type of variable? e.g. VARCHAR vs INT ???

 

[PaulTEG]

It could be that your SELECT statement isn't returning any data because there's no match, are you sure the $data[1] variable is being interpolated correctly

--Paul

 

[parkers]

interpolated ... that was the word I was looking for

 

[PaulTEG]

Sorry parkers, missed your post
--Paul

It's important in life to always strike a happy medium, so if you see someone with a crystal ball, and a smile on their face ...

 

[varakal]

tracking_id is varchar. I tried with and without quotes. It didnt worked.

-----
There is a match, as I am getting a row at MySQL command.

 

[PaulTEG]

just before your SELECT statement is executed print out the value of $data[1];
--Paul

 

[varakal]

Code:
my $dbh_delmetadata_sql = "SELECT `document_id` FROM `delivery_metadata` WHERE `tracking_id` = '$data[1]'";
my $do_dbh_delmetadata = $dbh_delmetadata->prepare($dbh_delmetadata_sql) or die "Couldn't prepare for delivery_metadata";

print "sql is $dbh_delmetadata_sql\n";
print "data[1] is $data[1]\n";

$do_dbh_delmetadata->execute() or die "Couldn't execute dest delivery metadata statement";



Result:
sql is SELECT `document_id` FROM `delivery_metadata` WHERE `tracking_id` = '0004107566598346900022421108461909413'
data[1] is 0004107566598346900022421108461909413

 

[PaulTEG]

Well that's not it so ...

What's the story with mixing backticks and single quotes ?

--Paul

 

[MikeLacey]

Try it without any single quotes, forward or backward.

Mike

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[PaulTEG]

If mike's answer doesn't work(and it most likely will) try adding a row to the database with this actual value as "$data[1]" and without the quotes and see if one of these gets pulled back with the query

--Paul