Database query failing when called from php front end – PHP

  php

Q(Question):

Hello All,
I am using PHP to connect to derby/db2 database.
I want to implement unix_timestamp() function (used in mysql) to its equivalent in derby database.
I found the equivalent as
select {fn TIMESTAMPDIFF( SQL_TSI_day, timestamp(‘1970-01-01-00.00.00’),current_timestamp)} as "t1" from sysibm.sysdummy1;

So as a test, I wrote a small php program to check if i am getting the results. Its as shown below


<?php
$database = 'abc';
$user = 'xxx';
$password = 'xxx';
$conn = db2_connect($database, $user, $password);
if ($conn) {
echo "Connection succeeded.";
$qh ="select {fn TIMESTAMPDIFF(DAY,timestamp('1970-01-01-00.00.00'),current_timestamp)} as \"t1\" from sysibm.sysdummy1";
$result = db2_exec($conn, $qh);
while($row=db2_fetch_array($result)){
$v=$row[0];
echo $v;
}
db2_close($conn);
}
else {
echo "Connection failed.";
}
?>

Basically, the db2_exec statement fails when I run this php file.
When I run the sql statement separately from derby prompt , it shows me the answer.
Please provide me any input as to why is it failing.

A(Answer):

can you get anything out of db2_stmt_errormsg()? (I’d prefer to use PDO, since that throws Exception … but that’s another story)

A(Answer):

@Dormilich

Hey,
I am still stuck with the same problem. The error mesage is:
[IBM][CLI Driver][Apache Derby] Column ‘DAY’ is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then ‘DAY’ is not a column in the target table. SQLCODE=-1

Has anyone tried to use PHP with derby database. I think PHP is unable to recognize the fn keyword(its a jdbc escape sequence). Do I need to add anythin to php for jdbc functions to be supported?
Please advise!

A(Answer):

@knellim

You mention this as being the equivalent:
select {fn TIMESTAMPDIFF( SQL_TSI_day, timestamp(‘1970-01-01-00.00.00’),current_timestamp)

But you use the word DAY in place of the keyword SQL_TSI_day

Try switching that around.


$qh ="select {fn TIMESTAMPDIFF(SQL_TSI_DAY,timestamp('1970-01-01-00.00.00'),current_timestamp)} as \"t1\" from sysibm.sysdummy1";

A(Answer):

@JKing

I actually tried that as well.. but it does not work. The fact is I am able to get values when I run it from the derby command prompt ij. But when i give this query from php it throws me an error.

Just for information, I am using php 5 and i am connecting php and derby using db2 runtime client and ibm_db2 driver.

A(Answer):

Do you get the same error using SQL_TSI_DAY?

From the error message you provided earlier it seems to me that it is interpreting the word day as a column name.

I also checked the documentation on the TIMESTAMPDIFF in the derby reference manual and this is what it says:

Valid intervals for TIMESTAMPADD and TIMESTAMPDIFF
The TIMESTAMPADD and TIMESTAMPDIFF functions are used to perform arithmetic
with timestamps. These two functions use the following valid intervals for arithmetic
operations:
• SQL_TSI_DAY
• SQL_TSI_FRAC_SECOND
• SQL_TSI_HOUR
• SQL_TSI_MINUTE
• SQL_TSI_MONTH
• SQL_TSI_QUARTER
• SQL_TSI_SECOND
• SQL_TSI_WEEK
• SQL_TSI_YEAR

If you don’t mind switching it around and posting the error message it could be helpful.

A(Answer):

@JKing

Sure..Here is the code i ran:


<?php
$database = 'XYZ';
$user = 'vXXX';
$password = 'QQQQQ';
$conn = db2_connect($database, $user, $password);
if ($conn) {
echo "Connection succeeded.";
$query="select {fn timestampadd(SQL_TSI_SECOND,30,end1)} from request";
$result = db2_prepare($conn, $query);
if ($result) {
print "Successfully prepared the table.\n";
$resultfinal=db2_execute($result);
echo $resultfinal;
}
if ($resultfinal){
$val=db2_fetch_row($resultfinal);
$val1=$val[0];
print $val1;
}
else
echo db2_stmt_errormsg();
}else {
echo "Connection failed.";
}
?>

The output with error mesage when i run it is:
Connection succeeded.Successfully prepared the table.
PHP Warning: db2_execute(): Statement Execute Failed in /var/www/html/testquery.php on line 17

Warning: db2_execute(): Statement Execute Failed in /var/www/html/testquery.php on line 17
[IBM][CLI Driver][Apache Derby] Column ‘SQL_TSI_SECOND’ is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then ‘SQL_TSI_SECOND’ is not a column in the target table. SQLCODE=-1

LEAVE A COMMENT