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";

 

[varakal]

I tried with and without quotes, it didnt worked.

And I need to only retrieve data from database, I should not add any row.. well, I dont have any permissions to add data into this table, though I have, I should not.

 

[MikeLacey]

Annoying :-(

I had a similar problem a while ago but with VB and Oracle.

An SQL statement that ran when used with sqlplus (Oracle SQL command line tool) didn't run in a VB application.

Turned out to be a locking problem, the method used in VB to connect to the database (Oracle's Objects 4 OLE I think) was asking for a lock on that row as it read it. The lock request was failing and nothing was returned to the client process.

I've used The DBI quite a bit in the past but not really with MySQL; can you turn DBI debugging on and examine the SQL statements that are being sent to MySQL?

Mike

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

 

[varakal]

This is the DBI trace at this point

-> 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())


I dont feel it is the lock problem. Because if there is no WHERE clause in the SQL above, it returns all rows.
Above, we can see the SQL statement, and if I run that in MySQL command, it works fine.. quite weird.

 

[MikeLacey]

I have no clue.....

In the script example, from your first post, you do a print of the SQL statement that will be executed. Have you tried executing that output from your script in the MySQL command?

Mike

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

 

[varakal]

Absolutely.. I tried it and kept trying it, and it always gives a row.. !!!

 

[stevexff]

Varakal

Problem seems to be centred around your where clause. What is the column data type of tracking_id please? Also, you include the line

print "sql is $dbh_delmetadata_sql\n";

in your program, what is the (verbatim) output of this? Apologies if it's already been posted, but I couldn't see it in the above...

Steve

 

[varakal]

sql will be something of this type.
sql is SELECT `document_id` FROM `delivery_metadata` WHERE `tracking_id` = '0004107566598346900022421108461909413'

tracking_id is of type varchar(255)

 

[duncdude]

what happens if you run this?:-

my $do_dbh_delmetadata = $dbh_delmetadata->prepare("SELECT document_id FROM delivery_metadata WHERE tracking_id = '0004107566598346900022421108461909413'");
$do_dbh_delmetadata->execute();

while (@row = $do_dbh_delmetadata->fetchrow_array) {
foreach $field (@row) {
print "$field</td>\n";
}
}


Kind Regards
Duncan

 

[stevexff]

Does mySQL allow you use a varchar in a where clause? The clunky old mainframe DB2 v6 certainly doesn't, so I don't know if this is an SQL restriction or just a 'feature' of DB2.

If so, it could explain why it works without the WHERE clause. But in that case I'm surprised that the PREPARE worked.

Steve

 

[varakal]

rows are not being fetched. so nothing is printed for $field !!

Again.. when I execute the same sql in MySQL command, I get a row.

 

[varakal]

My mind boggled over this issue and tried all other ways. I didnt find any other option than to scrap this file and try it in some other different way.. changed the logic and it did my purpose.
But I didnt delete this file as I can look into this at my free time.
Thanks for all your time.