Database query failing when called from php front end

[knellim]

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.

[Dormilich]

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

[knellim]

@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!

[JKing]

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

[knellim]

@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.

[JKing]

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.

[knellim]

@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