Remy Wetzels
2008-05-28 14:31:09 UTC
Any interest in a patch for a OCI8CallableStatement class to support
(stored) procedures in Oracle?
It's not fully tested yet for all field types (clobs, blobs, etc), but
it should work on the basic fields like varchar2, integer, date and
stuff... No support for cursors yet. (We do not need it at the moment
and there is I believe no CreoleType for cursors (SQLT_RSET))
There is a small variation in the prepareCall as opposed to the MSSQL
(see also
http://creole.phpdb.org/trac/wiki/Documentation/CreoleGuide#StoredProced
ures). You only need to give the procedure name for MSSQL. For Oracle
you need to create the complete 'BEGIN ... END' SQL string. Also the
setXXX and registerOutParameter have a different behavior due to the
differences in procedure calling between MSSQL and Oracle.
We use a slightly modified version of Creole 1.1.0 (non-locale float
patch and our NLS_DATE_FORMAT has time in it).
My base test was procedure:
==== snip ====
create or replace PROCEDURE
sayHello (name IN VARCHAR2, datum IN DATE, greeting OUT
VARCHAR2)
AS
BEGIN
greeting := 'Hello ' || name || ' on ' || datum;
END;
==== snip ====
The test PHP code ($con is the propel connection):
==== snip ====
$sqlString = 'BEGIN sayHello(:name,:datum,:greeting); END;';
echo '<br>['.$sqlString.']';
$statement = $con->prepareCall($sqlString);
/* setString($paramIndex, $value, $maxLength = -1) */
$statement->setString(':name', 'PutYourNameHere');
/* setTimestamp($paramIndex, $value, $maxLength = -1) */
$statement->setDate(':datum', time());
/* registerOutParameter($paramIndex, $sqlType, $maxLength = -1) */
$statement->registerOutParameter(':greeting', CreoleTypes::VARCHAR,
255);
$result = $statement->executeQuery(ResultSet::FETCHMODE_NUM);
echo '<br>';
var_dump($result);
echo '<br>';
echo '<br><b>result:</b> '.$statement->getString(':greeting');
// the statement below gives an error:
// ORA-24374: define not done before fetch or execute and fetch
// while ($result->next())
// {
// echo '<br><b>Result</b>';
// echo '<br>['.$result->get(1).']';
// }
$statement->close();
==== snip ====
Which will result in the following output:
==== snip ====
[BEGIN sayHello(:name,:datum,:greeting); END;]
object(OCI8ResultSet)#329 (7) { ["fetchmode:protected"]=> int(2)
["conn:protected"]=> object(OCI8Connection)#41 (7) {
["lastStmt:protected"]=> NULL ["execMode:protected"]=> int(32)
["transactionOpcount:protected"]=> int(0) ["dblink:protected"]=>
resource(74) of type (oci8 connection) ["dsn:protected"]=> array(5) {
["database"]=> NULL ["hostspec"]=> string(13) "HOSTSPEC" ["password"]=>
string(7) "PASSWORD" ["phptype"]=> string(6) "oracle" ["username"]=>
string(7) "USERNAME" } ["flags:protected"]=> int(0) ["lastQuery"]=>
string(62) "ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD
HH24:MI:SS'" } ["result:protected"]=> resource(299) of type (oci8
statement) ["cursorPos:protected"]=> int(0) ["fields:protected"]=> NULL
["lowerAssocCase:protected"]=> bool(false) ["rtrimString:protected"]=>
bool(false) }
result: Hello PutYourNameHere on 2008-05-28 00:00:00
==== snip ====
If anyone is interested I will sent it to Hans so that he can check it
into SVN...
Regards,
- Remy Wetzels.
(stored) procedures in Oracle?
It's not fully tested yet for all field types (clobs, blobs, etc), but
it should work on the basic fields like varchar2, integer, date and
stuff... No support for cursors yet. (We do not need it at the moment
and there is I believe no CreoleType for cursors (SQLT_RSET))
There is a small variation in the prepareCall as opposed to the MSSQL
(see also
http://creole.phpdb.org/trac/wiki/Documentation/CreoleGuide#StoredProced
ures). You only need to give the procedure name for MSSQL. For Oracle
you need to create the complete 'BEGIN ... END' SQL string. Also the
setXXX and registerOutParameter have a different behavior due to the
differences in procedure calling between MSSQL and Oracle.
We use a slightly modified version of Creole 1.1.0 (non-locale float
patch and our NLS_DATE_FORMAT has time in it).
My base test was procedure:
==== snip ====
create or replace PROCEDURE
sayHello (name IN VARCHAR2, datum IN DATE, greeting OUT
VARCHAR2)
AS
BEGIN
greeting := 'Hello ' || name || ' on ' || datum;
END;
==== snip ====
The test PHP code ($con is the propel connection):
==== snip ====
$sqlString = 'BEGIN sayHello(:name,:datum,:greeting); END;';
echo '<br>['.$sqlString.']';
$statement = $con->prepareCall($sqlString);
/* setString($paramIndex, $value, $maxLength = -1) */
$statement->setString(':name', 'PutYourNameHere');
/* setTimestamp($paramIndex, $value, $maxLength = -1) */
$statement->setDate(':datum', time());
/* registerOutParameter($paramIndex, $sqlType, $maxLength = -1) */
$statement->registerOutParameter(':greeting', CreoleTypes::VARCHAR,
255);
$result = $statement->executeQuery(ResultSet::FETCHMODE_NUM);
echo '<br>';
var_dump($result);
echo '<br>';
echo '<br><b>result:</b> '.$statement->getString(':greeting');
// the statement below gives an error:
// ORA-24374: define not done before fetch or execute and fetch
// while ($result->next())
// {
// echo '<br><b>Result</b>';
// echo '<br>['.$result->get(1).']';
// }
$statement->close();
==== snip ====
Which will result in the following output:
==== snip ====
[BEGIN sayHello(:name,:datum,:greeting); END;]
object(OCI8ResultSet)#329 (7) { ["fetchmode:protected"]=> int(2)
["conn:protected"]=> object(OCI8Connection)#41 (7) {
["lastStmt:protected"]=> NULL ["execMode:protected"]=> int(32)
["transactionOpcount:protected"]=> int(0) ["dblink:protected"]=>
resource(74) of type (oci8 connection) ["dsn:protected"]=> array(5) {
["database"]=> NULL ["hostspec"]=> string(13) "HOSTSPEC" ["password"]=>
string(7) "PASSWORD" ["phptype"]=> string(6) "oracle" ["username"]=>
string(7) "USERNAME" } ["flags:protected"]=> int(0) ["lastQuery"]=>
string(62) "ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD
HH24:MI:SS'" } ["result:protected"]=> resource(299) of type (oci8
statement) ["cursorPos:protected"]=> int(0) ["fields:protected"]=> NULL
["lowerAssocCase:protected"]=> bool(false) ["rtrimString:protected"]=>
bool(false) }
result: Hello PutYourNameHere on 2008-05-28 00:00:00
==== snip ====
If anyone is interested I will sent it to Hans so that he can check it
into SVN...
Regards,
- Remy Wetzels.