yajra / pdo-via-oci8

PHP PDO_OCI functions via OCI8 extension
Other
88 stars 61 forks source link

$stmt->bindParam shifts default null values #144

Open lazar2038 opened 1 month ago

lazar2038 commented 1 month ago

Summary of problem or feature request

Declaration of the procedure in Oracle :

  procedure p_add_private(pr_client_id   in clients.client_id%type default null,
                          pr_login       in logins.login%type default null,
                          pr_password    in logins.password%type default null,
                          pr_contract_id in contracts.contract_id%type default null);

I want to execute it it Laravel.

Code snippet of problem

If i call

            $client_ors_id = 1;
            $pr_contract_id = 2;
            $pdo = DB::connection('orange')->getPdo();
            $stmt = $pdo->prepare("begin os_bill.bill_quantum_api.p_add_private(:pr_client_id,:pr_contract_id);end;");
            $stmt->bindParam(':pr_client_id', $client_ors_id, \PDO::PARAM_INT);
            $stmt->bindParam(':pr_contract_id', $service_id, \PDO::PARAM_INT);
            $stmt->execute();

...the param pr_contract_id is becomes binded to pr_login, i.e. is shifted from 4 to 2 bind-place.

Then if i call

            $client_ors_id = 1;
            $pr_contract_id = 2;
            $null = null;
            $pdo = DB::connection('orange')->getPdo();
            $stmt = $pdo->prepare("begin os_bill.bill_quantum_api.p_add_private(:pr_client_id,:pr_login,:pr_password,:pr_contract_id);end;");
            $stmt->bindParam(':pr_client_id', $client_ors_id, \PDO::PARAM_INT);
            $stmt->bindParam(':pr_login', $null, \PDO::PARAM_NULL);
            $stmt->bindParam(':pr_password', $null, \PDO::PARAM_NULL);
            $stmt->bindParam(':pr_contract_id', $service_id, \PDO::PARAM_INT);
            $stmt->execute();

...two null params cause an error, though they are null by default in Oracle :) it senses like null !== null here ;)

Now variant that works:

         $sql = "
                DECLARE
                    pr_client_id NUMBER := :pr_client_id;
                    pr_login NUMBER := :pr_login;
                    pr_password NUMBER := :pr_password;
                    pr_contract_id NUMBER := :pr_contract_id;
                BEGIN
                    os_bill.bill_quantum_api.p_add_private(:pr_client_id,:pr_login,:pr_password,:pr_contract_id);
                END;
                ";

            DB::connection('orange')->statement($sql, [
                'pr_client_id' => $client_ors_id,
                'pr_login' => null,
                'pr_password' => null,
                'pr_contract_id' => $service_id
            ]);

System details

yajra commented 1 month ago

Thanks for reporting, I will review it as soon as I can. Please do not hesitate to submit a PR for a fix if you can.