CODEWITHSUNDEEP
phporacle-database
shorif2000
shorif2000

Reputation: 2654

php oci_bind_by_name not working in loop

I am trying to bind by name in a foreach loop which does not seem to be working.

Old way working:

oci_bind_by_name($result, ':exemptionid', $exemption_id, 10, SQLT_INT);
oci_bind_by_name($result, ':seq', $seq, 10, SQLT_INT);
oci_bind_by_name($result, ':action', $action, 20, SQLT_CHR);
oci_bind_by_name($result, ':s_zone',$sourceZones,48,SQLT_CHR);
oci_bind_by_name($result, ':s_seczone',$szone1,32,SQLT_CHR);
oci_bind_by_name($result, ':d_zone',$destinationZones,48,SQLT_CHR);
oci_bind_by_name($result, ':d_seczone',$dzone1,32,SQLT_CHR);
if($protocol_id != null) {
    oci_bind_by_name($result, ':protocolid', $protocol_id, 10, SQLT_INT);
}
if($ports != null) {
    oci_bind_by_name($result, ':ports', $ports, 32, SQLT_CHR);
}
if($note != null) {
    oci_bind_by_name($result, ':note', $note, 128, SQLT_CHR);
}
oci_bind_by_name($result, ':method', $method, 1, SQLT_CHR);
$authuser = authUser();
oci_bind_by_name($result, ':authuser', $authuser, 64, SQLT_CHR);

But when I put this all into an array and put in function and try process it in a loop it fails binding integers.

Not WORKING

function bind($resource, $binds){
    foreach ($binds as $placeholder => $varibale){
        oci_bind_by_name($resource,$placeholder, $varibale[0],$varibale[1],$varibale[2]);
    }
}

$binds = [
    ':exemptionid' => [
        $exemption_id,
        10,
        SQLT_INT
    ],
    ':source' => [
        ($source != "") ? $source : $sourceZones,
        strlen ($source),
        SQLT_CHR
    ],
    ':destination' => [
        ($destination != "" ) ? $destination : $destinationZones,
        strlen ($destination),
        SQLT_CHR
    ]
];

if ($protocol_id != null) {
    $binds [':protocolid'] = [
        $protocol_id,
        10,
        SQLT_INT
    ];
}
if ($ports != null) {
    $binds [':ports'] = [
        $ports,
        32,
       SQLT_CHR
    ];
}

bind($resource, $binds); // not working

The error I get is oci_execute(): ORA-01438: value larger than specified precision allowed for this column but none of my columns have decimals and I am not passing any decimals.

UPDATE

Why do I have to pass by ref?

function bind($resource, $binds)
{
    foreach ($binds as $key => $val) {
        $variable = &$binds[$key][0];
        $length = (isset($binds[$key][1])) ? $binds[$key][1] : -1;
        $type = (isset($binds[$key][2])) ? $binds[$key][2] : SQLT_CHR;
        oci_bind_by_name($resource, $key, $variable, $length, $type);
                        }
}

Upvotes: 1

Views: 1652

Answers (1)

timclutton
timclutton

Reputation: 13004

This is documented (albeit obscurely) on the oci_bind_by_name manual page in a comment for Example #3 Binding with a foreach() loop:

foreach ($ba as $key => $val) {
    // oci_bind_by_name($stid, $key, $val) does not work
    // because it binds each placeholder to the same location: $val
    // instead use the actual location of the data: $ba[$key]
    oci_bind_by_name($stid, $key, $ba[$key]);
}

So you should change your function like so (vars renamed to avoid line wrapping):

function bind($resource, $binds)
{
    foreach ($binds as $key => $val) {
        oci_bind_by_name($resource, $key, $binds[$key][0], $binds[$key][1], $binds[$key][2]);
    }
}

Upvotes: 5

Related Questions