By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,707 Members | 1,605 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,707 IT Pros & Developers. It's quick & easy.

Database query failing when called from php front end

P: 23
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
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. $database = 'abc';
  3. $user = 'xxx';
  4. $password = 'xxx';
  5.  
  6. $conn = db2_connect($database, $user, $password);
  7.  
  8.  
  9. if ($conn) {
  10.     echo "Connection succeeded.";
  11.     $qh ="select {fn TIMESTAMPDIFF(DAY,timestamp('1970-01-01-00.00.00'),current_timestamp)} as \"t1\" from sysibm.sysdummy1";
  12.  
  13.     $result = db2_exec($conn, $qh);
  14.     while($row=db2_fetch_array($result)){
  15.     $v=$row[0];
  16.     echo $v;
  17.     }
  18.  
  19.    db2_close($conn);
  20. }
  21. else {
  22.     echo "Connection failed.";
  23. }
  24. ?>
  25.  
  26.  
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.
Jun 2 '10 #1
Share this Question
Share on Google+
6 Replies


Dormilich
Expert Mod 5K+
P: 8,639
can you get anything out of db2_stmt_errormsg()? (I’d prefer to use PDO, since that throws Exception … but that’s another story)
Jun 2 '10 #2

P: 23
@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!
Jun 28 '10 #3

JKing
Expert 100+
P: 1,206
@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.
Expand|Select|Wrap|Line Numbers
  1.     $qh ="select {fn TIMESTAMPDIFF(SQL_TSI_DAY,timestamp('1970-01-01-00.00.00'),current_timestamp)} as \"t1\" from sysibm.sysdummy1";
  2.  
Jun 28 '10 #4

P: 23
@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.
Jun 28 '10 #5

JKing
Expert 100+
P: 1,206
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.
Jun 28 '10 #6

P: 23
@JKing
Sure..Here is the code i ran:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. $database = 'XYZ';
  3. $user = 'vXXX';
  4. $password = 'QQQQQ';
  5.  
  6. $conn = db2_connect($database, $user, $password);
  7.  
  8.  
  9. if ($conn) {
  10.     echo "Connection succeeded.";
  11.     $query="select {fn timestampadd(SQL_TSI_SECOND,30,end1)} from request";
  12.  
  13.     $result = db2_prepare($conn, $query);
  14.  
  15.    if ($result) {
  16.     print "Successfully prepared the table.\n";
  17.     $resultfinal=db2_execute($result);
  18.     echo $resultfinal;
  19.    }
  20.    if ($resultfinal){
  21.    $val=db2_fetch_row($resultfinal);
  22.    $val1=$val[0];
  23.   print $val1;
  24. }
  25.    else
  26.    echo db2_stmt_errormsg();
  27. }else {
  28.     echo "Connection failed.";
  29. }
  30. ?>
  31.  
  32.  
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
Jun 28 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.