taq / pdooci Goto Github PK

View Code? Open in Web Editor NEW

67.0 9.0 29.0 100 KB

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

PHP 100.00%

php pdo oci

pdooci's People

Contributors

Stargazers

Watchers

pdooci's Issues

When inserting value with "?" then ":pdooci_m" is inserted instead of "?"

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.

Why my code isn't easily translated

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

Enable dbms_output

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);

ATTR_STRINGIFY_FETCHES

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

Undefined variable: case in file: vendor/taq/pdooci/src/Statement.php on line: 642

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 question mark problem

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);
    }

Statement->execute() needs to bind values by reference

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.

\PDO::FETCH_BOTH is only fetched as \OCI_NUM

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 :)

Class 'PDOStatement' not found

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.

do not return queryString in PDO::query()!!

hi
very bad problem
in function PDO::query() you do not return queryString!

Can't find way to set prefetch

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.

PHP 5.6 or PHP 7.1 Support?

The comment block shows


/**
 * PDOCI
 *
 * PHP version 5.3

Is this the max. tested PHP Version?
Thanks

Statement->execute() doesn't like bind variables with null values

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?

datatype mismatch PDO vs OCI

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.

Bind array for sql IN(?)

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.

Error inserting CLOB value

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

PHP Warning: oci_fetch_all(): ORA-24374:

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

ATTR_DEFAULT_FETCH_MODE

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);

php 8.1

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

bindParam doesn't use type and leng informations

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).

taq / pdooci Goto Github PK

pdooci's People

Contributors

Stargazers

Watchers

Forkers

pdooci's Issues

Recommend Projects

Recommend Topics

Recommend Org