DBI call to mysql, trying to get return value from select...
-
trying to get a return value in Perl using DBI to use Mysql's date functions... can't find the result... code : $statement = $db_handle->prepare("SELECT ADDDATE($string, 7); "); I can't figure out how to get the value to see/use/print... tried a few things... anyone got a sample of doing this? thanks...
-
trying to get a return value in Perl using DBI to use Mysql's date functions... can't find the result... code : $statement = $db_handle->prepare("SELECT ADDDATE($string, 7); "); I can't figure out how to get the value to see/use/print... tried a few things... anyone got a sample of doing this? thanks...
This is more of a Perl question than MySQL, but here goes... If you use prepare, you have to use execute too, and then a fetch function to get data back:
$statement = $db_handle->prepare("SELECT ADDDATE($string,7)");
$statement->execute;
@data = $statement->fetchrow_array;
$new_date = $data[0];There are shorter functions for doing simple selects:
@data = $db_handle->selectrow_array("SELECT ADDDATE($string,7)");
$new_date = $data[0];All of this is covered quite well in the Perl DBI manual page[^].
-
This is more of a Perl question than MySQL, but here goes... If you use prepare, you have to use execute too, and then a fetch function to get data back:
$statement = $db_handle->prepare("SELECT ADDDATE($string,7)");
$statement->execute;
@data = $statement->fetchrow_array;
$new_date = $data[0];There are shorter functions for doing simple selects:
@data = $db_handle->selectrow_array("SELECT ADDDATE($string,7)");
$new_date = $data[0];All of this is covered quite well in the Perl DBI manual page[^].
thanks, I've been using DBI now for several years... My question was more for the syntax to retrieve function values from MySql... In this case, the SQL manual gave it "command line", and I didn't see how to retrieve it via DBI.. I can do this with Oracle "select "oracle-sequence-name" from dual", for example... I tried both of your examples and both returned nothing... I am researching further, but wanted to ask if you had run these? if so, I can take to my hosting support and see if they can assist in finding out why they return no value... thanks so much... Roy
-
thanks, I've been using DBI now for several years... My question was more for the syntax to retrieve function values from MySql... In this case, the SQL manual gave it "command line", and I didn't see how to retrieve it via DBI.. I can do this with Oracle "select "oracle-sequence-name" from dual", for example... I tried both of your examples and both returned nothing... I am researching further, but wanted to ask if you had run these? if so, I can take to my hosting support and see if they can assist in finding out why they return no value... thanks so much... Roy
Ah, OK. In that case the problem is probably what $string contains.
SELECT ADDDATE(2010-01-21, 7)- returns NULL, because 2010-01-21 is a numeric expression, and MySQL expects a date.SELECT ADDDATE('2010-01-21', 7)- returns 2010-01-28, as expected. So you can wrap the date string in quotes, or use placeholders:$statement = $db_handle->prepare("SELECT ADDDATE(?,7)");
$statement->execute($string);I've tried both methods out, and they are working for me with $string = '2010-01-21'.
-
Ah, OK. In that case the problem is probably what $string contains.
SELECT ADDDATE(2010-01-21, 7)- returns NULL, because 2010-01-21 is a numeric expression, and MySQL expects a date.SELECT ADDDATE('2010-01-21', 7)- returns 2010-01-28, as expected. So you can wrap the date string in quotes, or use placeholders:$statement = $db_handle->prepare("SELECT ADDDATE(?,7)");
$statement->execute($string);I've tried both methods out, and they are working for me with $string = '2010-01-21'.
get out your nominations for idiot of the year (ME!)... I spelled the result name wrong in my print statement... everything works fine... thanks so much for the resolution, I have been pounding away on this for a while...
-
Ah, OK. In that case the problem is probably what $string contains.
SELECT ADDDATE(2010-01-21, 7)- returns NULL, because 2010-01-21 is a numeric expression, and MySQL expects a date.SELECT ADDDATE('2010-01-21', 7)- returns 2010-01-28, as expected. So you can wrap the date string in quotes, or use placeholders:$statement = $db_handle->prepare("SELECT ADDDATE(?,7)");
$statement->execute($string);I've tried both methods out, and they are working for me with $string = '2010-01-21'.
OK, I can get it to work with the date coded into the select, but not with a var... Here's the code: $string='\'2010-04-16\''; print 'string val=',$string,''; $statement = $db_handle->prepare("SELECT ADDDATE(?,7)"); $statement->execute($string); @data = $statement->fetchrow_array; $new_date = $data[0]; print '<br>new date=',$new_date,''; I run this and get nothing... does this work as is for you?
-
Ah, OK. In that case the problem is probably what $string contains.
SELECT ADDDATE(2010-01-21, 7)- returns NULL, because 2010-01-21 is a numeric expression, and MySQL expects a date.SELECT ADDDATE('2010-01-21', 7)- returns 2010-01-28, as expected. So you can wrap the date string in quotes, or use placeholders:$statement = $db_handle->prepare("SELECT ADDDATE(?,7)");
$statement->execute($string);I've tried both methods out, and they are working for me with $string = '2010-01-21'.
I got it to work with the var in the select statement... this is fine, I'm able to work with this.... thanks for your assistance
-
OK, I can get it to work with the date coded into the select, but not with a var... Here's the code: $string='\'2010-04-16\''; print 'string val=',$string,''; $statement = $db_handle->prepare("SELECT ADDDATE(?,7)"); $statement->execute($string); @data = $statement->fetchrow_array; $new_date = $data[0]; print '<br>new date=',$new_date,''; I run this and get nothing... does this work as is for you?
The problem here is that the date string already contains the quotes - using the placeholder binding means the database driver does the quoting for you. To make your code above work, you just need to replace the
$string='\'2010-04-16\'';with$string='2010-04-16';.