taq / pdooci Goto Github PK
View Code? Open in Web Editor NEWWrapping on PHP OCI functions to simulate a PDO object, since PDO support for OCI is very confuse and slow.
License: GNU General Public License v2.0
Wrapping on PHP OCI functions to simulate a PDO object, since PDO support for OCI is very confuse and slow.
License: GNU General Public License v2.0
Hi there, first and foremost, thank you for this project. It's fantastic!
I was wondering if there is a way to capture the DMBS output. I have a few statements which ouput data using the dbms_output.putline, which I need to capture.
Is there a flag which I can enable to allow/capture dbms_ouput? I tried adding this to the beginning of the statement: DBMS_OUTPUT.ENABLE(NULL); and this to the end: dbms_output.put_line( lv_msg_out || '| ' || lv_update_out || '| ' || lv_sqlerrm_out); where those variables should be populated.
Thanks kindly!
---EDIT--- Please disregard, I found I could bind the parameters to a PHP variable using the "PARAM_INPUT_OUTPUT"
$fetch->bindParam(':lv_msg_out', $lv_msg_out, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
$fetch->bindParam(':lv_update_out', $lv_update_out, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 1);
$fetch->bindParam(':lv_sqlerrm_out', $lv_sqlerrm_out, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
Hi,
here is quick example:
$db->query("INSERT INTO table (col) VALUES ('abc?')");
and it generates query "INSERT INTO table (col) VALUES ('abc:pdooci_m0')".
Check the Statement::insertMarks($query) function.
hi
very bad problem
in function PDO::query() you do not return queryString!
hi
do you support ATTR_STRINGIFY_FETCHES ?
I am using PDO::ATTR_STRINGIFY_FETCHES => true, because I want to get CLOB field as string and not as resource #id
I've checked through the code but I can't seem to find a means to leverage oci_set_prefetch in order to optimize performance for certain queries. May be more of an enhancement than a bug but could prove useful.
hi
ATTR_DEFAULT_FETCH_MODE not work
$options = array(
PDO::ATTR_PERSISTENT =>true,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_CASE => PDO::CASE_LOWER,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
PDO::ATTR_STRINGIFY_FETCHES => true);
In method Statement::fetchAll
line \oci_fetch_all($this->_stmt, $rst, 0, -1, \OCI_FETCHSTATEMENT_BY_ROW + \OCI_NUM + \OCI_ASSOC);
is wrong, but I have no idea how to fix it.
Using FETCH_ASSOC temporarily :)
bindParam
doesn't use type
and leng
informations.
This cause an issue when you bind an OUT param with an undifined php variable. For exemple if your OUT param is defined as an integer in your stored procedure, you will get a char with the lefmost digit of the real result number after you execute the statement. (sorry for my english, i'm French).
I discovered and issue when trying to call a stored procedure that had an OUT VARCHAR variable. I used bindParam for PDO::PARAM_STR, but kept getting an ora-01722 invalid number. It turns out that PDO::PARAM_STR has value 2 which is not the same as as SQLT_CHR 1. In order to keep the code portable, I don't want to use the wrong constants. I added the following to Statement.php:
private function pdoToOciType($pdotype) {
$ocitype = SQLT_CHR;
$pdotype = $pdotype & ~ PDO::PARAM_INPUT_OUTPUT;
switch ($pdotype) {
case PDO::PARAM_STR:
case PDO::PARAM_NULL:
$ocitype = SQLT_CHR;
break;
case PDO::PARAM_INT:
$ocitype = SQLT_INT;
break;
case PDO::PARAM_BOOL:
$ocitype = SQLT_BOL;
break;
case PDO::PARAM_LOB:
$ociType = OCI_D_LOB;
break;
}
return $ocitype;
}
and changed the following line in bindParam:
$ok = \oci_bind_by_name($this->_stmt, $param, $value, $leng, $this->pdoToOciType($type));
Do you want me to try to check out the code and make changes, or do you want to add something appropriate of your own? Note: That this change did break some code that was accidentally specifying the wrong pdoDataType and there may be people that realized the pdoDataType was not actually used in many cases and just put in PARAM_STR everywhere.
Thanks for providing this code, it is working out well for my project.
hi
when we save a value that have question mark “?” it will be changed to “:pdooci_m0”
for example :
https://www.google.com/search?q=php ===> https://www.google.com/search:pdooci_m0q=php
problem is in this function , please fix this
/**
* Convert a query to use bind marks
*
* @param string $query to insert bind marks
*
* @return string query with bind marks
*/
public static function insertMarks($query)
{
$pos = -1;
$regex = '/(?<!\')\?(?!\')/';
return preg_replace_callback($regex, function($matches) use (&$pos) { $pos++; return ":pdooci_m$pos"; }, $query);
}
hi
dear we have too many fatal error on php 8.1
Deprecated: Return type of PDOOCI\PDO::beginTransaction() should either be compatible with PDO::beginTransaction(): bool, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in D:\wamp64\www\product\ussd\ussd-oracle\core\pdooci\PDO.php on line 256
Deprecated: Return type of PDOOCI\PDO::commit() should either be compatible with PDO::commit(): bool, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in D:\wamp64\www\product\ussd\ussd-oracle\core\pdooci\PDO.php on line 234
Deprecated: Return type of PDOOCI\PDO::errorCode() should either be compatible with PDO::errorCode(): ?string, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in D:\wamp64\www\product\ussd\ussd-oracle\core\pdooci\PDO.php on line 306
Deprecated: Return type of PDOOCI\PDO::errorInfo() should either be compatible with PDO::errorInfo(): array, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in D:\wamp64\www\product\ussd\ussd-oracle\core\pdooci\PDO.php on line 319
Deprecated: Return type of PDOOCI\PDO::exec($sql) should either be compatible with PDO::exec(string $statement): int|false, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in D:\wamp64\www\product\ussd\ussd-oracle\core\pdooci\PDO.php on line 164
Deprecated: Return type of PDOOCI\PDO::getAttribute($attr) should either be compatible with PDO::getAttribute(int $attribute): mixed, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in D:\wamp64\www\product\ussd\ussd-oracle\core\pdooci\PDO.php on line 205
Deprecated: Return type of PDOOCI\PDO::getAvailableDrivers() should either be compatible with PDO::getAvailableDrivers(): array, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in D:\wamp64\www\product\ussd\ussd-oracle\core\pdooci\PDO.php on line 370
Deprecated: Return type of PDOOCI\PDO::inTransaction() should either be compatible with PDO::inTransaction(): bool, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in D:\wamp64\www\product\ussd\ussd-oracle\core\pdooci\PDO.php on line 384
Deprecated: Return type of PDOOCI\PDO::lastInsertId($sequence = null) should either be compatible with PDO::lastInsertId(?string $name = null): string|false, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in D:\wamp64\www\product\ussd\ussd-oracle\core\pdooci\PDO.php on line 413
Deprecated: Return type of PDOOCI\PDO::prepare($query, $options = null) should either be compatible with PDO::prepare(string $query, array $options = []): PDOStatement|false, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in D:\wamp64\www\product\ussd\ussd-oracle\core\pdooci\PDO.php on line 270
Fatal error: Declaration of PDOOCI\PDO::query($statement, $mode = null, $p1 = null, $p2 = null) must be compatible with PDO::query(string $query, ?int $fetchMode = null, mixed ...$fetchModeArgs): PDOStatement|false in D:\wamp64\www\product\ussd\ussd-oracle\core\pdooci\PDO.php on line 141
Hi,
we're using your library with OCI8 and it's very useful!
We're having this warning in PHP logs:
PHP Warning: oci_fetch_all(): ORA-24374: define not done before fetch or execute and fetch in /XXXXX/vendor/taq/pdooci/src/Statement.php on line 284
Hi,
do you know how to bind parameter with type array?
I want sth like this:
$sql = 'SELECT * FROM blah WHERE id IN (?)'; $ids = [1,2,3,4,5]; $pdo->query($sql)->execute($ids);
Thanks.
I have some issues to make it work.
I have two identical tables, one in MySQL and the other in Oracle. However it seems that only my pdo code to call from the table in MySQL works.
It seems that I am connected with my oracle database, so I don't think that the problem is there.
This is the method i use to get my data from MySQL:
public function TestMySQL() {
$db = dbConn::getConnection();
$query = 'SELECT NAME FROM TESTTABLE';
$statement = $db->prepare($query);
$statement->execute();
$object = $statement->fetchAll(PDO::FETCH_OBJ);
return $object;
}
And the class to connect into the database:
class dbConn {
//variabili per tenere un oggetto di connessiane
protected static $db;
private $uname;
private $passwd;
private $hostname;
private $dbname;
//costruttore privato - non può essere inizializzato esternamente
private function __construct() {
$this->uname = "xxx";
$this->passwd = "xxx";
$this->hostname = "xxx";
$this->dbname = "xxx";
try {
// assegna un oggetto PDO alla variabile di db
self::$db = new PDO('mysql:charset=utf8mb4;host=' . $this->hostname . ';dbname=' . $this->dbname, $this->uname, $this->passwd);
self::$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
$e->getMessage();
}
}
// funzione di get per la connessione. Metodo statico.
public static function getConnection() {
//Garantisce una singola istanza, se non esiste alcun oggetto di connessione allore ne crea uno.
if (!self::$db) {
new dbConn();
}
//ritorna la connessione
return self::$db;
}
}
And this is from Oracle:
public function TestOracle() {
$db = Dboracle::getConnectionOra();
$query = 'SELECT NAME FROM TESTTABLE';
$statement = $db->prepare($query);
$statement->execute();
$object = $statement->fetchAll(PDO::FETCH_OBJ);
return $object;
}
And the class to connect into the database:
require_once "PDOOCI/PDO.php";
class Dboracle {
//variabili per tenere un oggetto di connessiane
protected static $db;
private $uname;
private $passwd;
private $hostname;
private $servicename;
private $dbname;
//costruttore privato - non può essere inizializzato esternamente
public function __construct() {
$this->uname = "xxx";
$this->passwd = "xxx";
$this->hostname = "xxx";
$this->servicename = "xxx";
$this->dbname = "xxx";
try {
// assegna un oggetto PDO alla variabile di db
$tns = "(DESCRIPTION=(ADDRESS_LIST = "
. "(ADDRESS = (PROTOCOL = TCP)(HOST = " . $this->hostname . ")"
. "(PORT = 1521)))(CONNECT_DATA=(SERVICE_NAME=" . $this->servicename . ")))";
$connStr = "oci:dbname=" . $tns;
self::$db = new PDOOCI\PDO($connStr, $this->uname, $this->passwd);
self::$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo $e->getMessage();
}
}
// funzione di get per la connessione. Metodo statico.
public static function getConnectionOra() {
//Garantisce una singola istanza, se non esiste alcun oggetto di connessione allore ne crea uno.
if (!self::$db) {
new Dboracle();
}
//ritorna la connessione
return self::$db;
}
}
I am trying to call the table in my index.php in this way:
<?php
$test = new Class();
$testlist = $test->TestOracle(); /* Or $test->TestMySQL(); */
foreach ($testlist as $element) {
echo '<a href="#" class="servicelink">' . $element->NAME . '</a>';
}
?>
The error I am getting with "TestOracle" is:
Warning: Invalid argument supplied for foreach() in "place where my foreach is called.
I want to thank you for your precious help
We started using your library and it seems to be working well most of the time. However, if we ever have a bind variable we want to fill with a null value, we get "Cannot insert NULL into ..." or other errors referencing empty bind values. Null is a valid value for some of our bind params (for example, when inserting into a table which has a trigger that will fill in a primary key). Do you have any insight as to what we should do in these cases if we are to use your library?
return array_map( function ($item) { if (is_array($item)) { $item = array_change_key_case($item, $case); } return $item; }, array_change_key_case($result, $case) );
Replace
function ($item)
with
function ($item) use ($case) {
The comment block shows
/**
* PDOCI
*
* PHP version 5.3
Is this the max. tested PHP Version?
Thanks
Hi,
First of all, thank you for this library.
I'm having problems while inserting rows with this library. Before migrating to it, the inserts worked fine but, after the migration, when I insert a row in a table that has a CLOB column, the value of this column is created always with the same value: "-0,00000000000000000000000000000000E+125"
During and after the execution, everything works ok, there are no exceptions or errors and the execution returns 'true'.
Have you had any problems like this before?
Would you mind telling me what I should look for in the code in order to fix it?
I have a table that has the following structure:
id INTEGER PK AUTO_INC
param_key VARCHAR(2048)
param_value CLOB
data_expires_at INTEGER'
This is the example code that I have been testing:
$conn = new PDO($dsn, $user, $pass);
$serObject = 'teststring';
$array = array(
"param_key" => 'keystring',
"param_value" => 'test string that should be very long'
"data_expires_at" => 1488800018,
);
$values = array();
$name_params = array_keys($array);
for ($i = 0; $i < count($name_params); $i++) {
$values[] = "?";
}
$query = "INSERT INTO " . $table . " (" . implode(",", $name_params) . ")";
$query .= " VALUES (" . implode(",", $values) . ")";
$sth2 = $conn->prepare($query);
$i = 1;
foreach ($array as $name => $value) {
if ($name == "param_value") { //This is the CLOB column
$sth2->bindParam($i, $value, PDO::PARAM_STR, strlen($value));
//I fill in the third parameter with PARAM_STR because of the bug on the oficial library
// However, I have tried with PARAM_LOB and it keeps inserting the wrong value.
} else {
$sth2->bindParam($i, $value);
}
$i++;
}
$result = $sth2->execute();
[...]
Thank you very much,
Best regards.
Elena
Currently, execute() on PDOOCI\Statement#157 uses bindValue(), which prototype's accepts a copy for the value instead of the reference.
This needs to be changed to bindParam which accepts the reference because oci_bind_by_name needs the reference to the variable (as in https://secure.php.net/manual/en/function.oci-bind-by-name.php), otherwise the execute() silently fails with multiple parameters.
Tested on PHP 7.0.9, OCI 2.1.1, Oracle Instant Client 12.1, Oracle Library Version 12.1.0.2.0.
I get this issue when using this library.
Class 'PDOStatement' not found in /vagrant/vendor/taq/pdooci/src/Statement.php
In this class
class Statement extends \PDOStatement implements \IteratorAggregate
you seem to be including PDOStatement from root scope. But in my installation this is not available, as I do not have any PDO module installed.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.