Database query failing when called from php front end

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • knellim
    New Member
    • Feb 2010
    • 23

    Database query failing when called from php front end

    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'),curr ent_timestamp)} as "t1" from sysibm.sysdummy 1;

    So as a test, I wrote a small php program to check if i am getting the results. Its as shown below
    Code:
    <?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
    Recognized Expert Expert
    • Aug 2008
    • 8694

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

    Comment

    • knellim
      New Member
      • Feb 2010
      • 23

      #3
      Originally posted by Dormilich
      can you get anything out of db2_stmt_errorm sg()? (I’d prefer to use PDO, since that throws Exception … but that’s another story)
      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!

      Comment

      • JKing
        Recognized Expert Top Contributor
        • Jun 2007
        • 1206

        #4
        Originally posted by 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'),curr ent_timestamp)} as "t1" from sysibm.sysdummy 1;

        So as a test, I wrote a small php program to check if i am getting the results. Its as shown below
        Code:
        <?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.
        You mention this as being the equivalent:
        select {fn TIMESTAMPDIFF( SQL_TSI_day, timestamp('1970-01-01-00.00.00'),curr ent_timestamp)

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

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

        Comment

        • knellim
          New Member
          • Feb 2010
          • 23

          #5
          Originally posted by JKing
          You mention this as being the equivalent:
          select {fn TIMESTAMPDIFF( SQL_TSI_day, timestamp('1970-01-01-00.00.00'),curr ent_timestamp)

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

          Try switching that around.
          Code:
              $qh ="select {fn TIMESTAMPDIFF(SQL_TSI_DAY,timestamp('1970-01-01-00.00.00'),current_timestamp)} as \"t1\" from sysibm.sysdummy1";
          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.

          Comment

          • JKing
            Recognized Expert Top Contributor
            • Jun 2007
            • 1206

            #6
            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_SE COND
            • 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.

            Comment

            • knellim
              New Member
              • Feb 2010
              • 23

              #7
              Originally posted by 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_SE COND
              • 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.
              Sure..Here is the code i ran:
              Code:
              <?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.Succe ssfully 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

              Comment

              Working...