dongqiao6445 2016-06-23 13:19
浏览 55

ORACLE存储过程适用于sql开发人员,但不适用于php

I'm confronted to a strange issue here, I try to use an oracle stored procedure which works fine on sqldeveloper and java, but not on php.

I'm executing the query this way :

//$rqt_date = "SELECT DECODE( 1, NULL, 1, 33, '1', 44 ) as test FROM DUAL";
$rqt_date = "SELECT DATEPARAM('23/06/2016') as date_1900 FROM DUAL";

$parsed = ociparse($db_link, $rqt_date);
oci_execute($parsed,OCI_NO_AUTO_COMMIT);
while (($row = oci_fetch_array($parsed, OCI_BOTH)) != false)
{
    print_r($row);
}

I test using the commented $rqt_date (decode) : I get results

Array
(
    [0] => 44
    [TEST] => 44
)

But when I use DATEPARAM I get 0 as values instead of 42542 (number of days since 1900) :

Array
(
    [0] => 0
    [DATE_1900] => 0
)

What I get in sqldeveloper:

http://i.stack.imgur.com/6b40V.png

I have no errors in the php logs.

The info about my server : PHP Version 5.3.8 System Windows NT BOWSER 6.1 build 7601 (Windows Server 2008 R2 Standard Edition Service Pack 1) i586

  • 写回答

2条回答 默认 最新

  • doushi1510 2016-06-23 14:16
    关注

    But when I use DATEPARAM I get [...] number of days since 1900

    The DATEPARAM function should be very simple:

    CREATE FUNCTION dateparam (
      dt IN DATE
    ) RETURN INTEGER DETERMINISTIC
    IS
    BEGIN
      RETURN dt - DATE '1900-01-01';
    END;
    /
    

    Now, this takes a DATE datatype (not a string).

    So in PHP (not my language, so please excuse any errors) you can pass the required value as a bind variable:

    $rqt_date = "BEGIN :r := DATEPARAM( :dt ); END;";
    $parsed = ociparse($db_link, $rqt_date);
    oci_bind_by_name($parsed, ":dt", new DateTime( "2016-06-23" ) );
    oci_bind_by_name($parsed, ":r", $r, 40 );
    oci_execute($parsed,OCI_NO_AUTO_COMMIT);
    print "$r
    ";
    
    评论

报告相同问题?

悬赏问题

  • ¥20 有偿 写代码 要用特定的软件anaconda 里的jvpyter 用python3写
  • ¥20 cad图纸,chx-3六轴码垛机器人
  • ¥15 移动摄像头专网需要解vlan
  • ¥20 access多表提取相同字段数据并合并
  • ¥20 基于MSP430f5529的MPU6050驱动,求出欧拉角
  • ¥20 Java-Oj-桌布的计算
  • ¥15 powerbuilder中的datawindow数据整合到新的DataWindow
  • ¥20 有人知道这种图怎么画吗?
  • ¥15 pyqt6如何引用qrc文件加载里面的的资源
  • ¥15 安卓JNI项目使用lua上的问题