Binding date parameters to stored procedure

Question

I'm trying to bind date parameters to stored procedure. $conn is ok, all parameters are correct. I have no problem with calling this procedure in the db directly. I tried to print error, but I don't get anything. I'm not attaching exec_cur now cause I guess, that the problem is in binding or calling the procedure. I have also tried to quote i_start and i_end:

 TO_DATE(':i_start', 'DD.MM.YYYY'), TO_DATE( ':i_end', 'DD.MM.YYYY')

It seems to me, that the call doesn't get the result.I'm getting empty cursor while calling it from the code . While calling it directly, I'm getting many entries.

Here the function:

function &HST($conn, $i_qot_id, $i_startend, $i_order_by, $i_max_rows, $v_cache_filename)
{
        $outrefc = oci_new_cursor($conn);

         $oerr = OCIError( $outrefc);
         echo "Fetch Code 1:".$oerr["message"];

        $mycursor = oci_parse($conn, "begin PPCKG.HST (:i_qot_id, TO_DATE(:i_start, 'DD.MM.YYYY'), TO_DATE( :i_end, 'DD.MM.YYYY') , :i_order_by, :i_max_rows, :curs); end");

        print $i_startend[0].$i_startend[1].$i_qot_id. $i_order_by. $i_max_rows;
        $oerr = OCIError( $mycursor);
        echo "Fetch Code 1:".$oerr["message"];
        oci_bind_by_name($mycursor, ':i_qot_id'  , $i_qot_id);
        oci_bind_by_name($mycursor, ':i_start'   , $i_startend[0]);
        oci_bind_by_name($mycursor, ':i_end'     , $i_startend[1]);
        oci_bind_by_name($mycursor, ':i_order_by', $i_order_by);
        oci_bind_by_name($mycursor, ':i_max_rows', $i_max_rows);
        oci_bind_by_name($mycursor, ':curs'      , $outrefc, -1, OCI_B_CURSOR);
        $oerr = OCIError( $mycursor);
        echo "Fetch Code 1:".$oerr["message"];
        return exec_cur( $mycursor, $outrefc, $v_cache_filename);
}

The call doesn't get any result. – user897237 Aug 13 '14 at 10:18 — user897237, Aug 13 '14 at 10:18

score 0 · Answer 1 · edited May 23 '17 at 10:26

0

Why do you convert your string value into date when you call the stored procedure? Why don't you convert it into date before?

At first you have to convert your string to date

$time = strtotime(:i_start);
$newformat = date('Y-m-d',$time);

Then pass this value to the oracle stored procedure

$mycursor = oci_parse($conn, "begin PPCKG.HST (:i_qot_id, :newformat,...); end;');

Read this to find out more about php + oracle stored procedures.

edited May 23 '17 at 10:26

Community

1
1

answered Aug 13 '14 at 10:23

neshkeev

6,280
3
26
47

Why not? Were you able to convert the string value into date? – neshkeev Aug 13 '14 at 11:02
I"m able. Anyhow it makes no difference. I'm getting no result. – user897237 Aug 13 '14 at 12:03
Did you convert all your string date values into date values? Didn't you forget to bind this new values to your query? have you tried to execute this procedure outside of the php environment? Did it gives you any rows? Provide me more information. I don't really know what your system is like. Read [this](http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html) before asking. – neshkeev Aug 13 '14 at 12:19
Yes, i have converted everything and binded it. You can read in my query, that I have no problem with executing this procedure directly in db. – user897237 Aug 13 '14 at 12:56

Binding date parameters to stored procedure

1 Answers1