PHP & Oracle

Long time no see.

Changed job and been working on fixing an app.

In my new job PHP and Oracle are the standards so I’m back to Oracle and I already had my first issues.

I have a small database with a couple of related tables.

I wanted to do an insert and get the id of the recently added row, searched for it on Google and there it was, I’ll need to use RETURNING…INTO.

It’s pretty simple actually so I coded it, the primary key is an integer that runs a trigger with a sequence, kind of a MySQL autoincrement field.

So this is how the code ended looking:

$query = “SELECT id, field1, field2 FROM table WHERE field1 IS NOT NULL RETURNING id into :id”;

$con = new connection()

$stmt = oci_parse($con,$query);

if($stmt!==false){

$binded = oci_bind_by_name($stmt,’:id’,$id);

$executed = oci_execute($stmt);

if($executed!==false){

$row = oci_fetch_array($stmt,OCI_BOTH+OCI_RETURN_NULLS+OCI_RETURN_LOBS);

}

}

It worked fine for a couple of days, then id started working oddly. I analized the code and found out that id was returning a 3 digits id instead of 4 digits, so I’ll be getting 123 for ids 1230 – 1239 besides the previous original 123. That was messing my database integrity and making my code to fail.

Googled it and found nothing! What the hell was wrong with it?

Finally after a day I calmed down and started to remember the old times when I started to work with Oracle and suddenly I remembered!!!!

I was missing a parameter on an oci function. I added it and voila! It worked!

What was I missing? Simple, I was missing the maxLength parameter for the oci_bind_by_name function.

Finally it ended looking like this:

$binded = oci_bind_by_name($stmt,’:id’,$id,100);

Hope this helps others with the same issue.

Tags: ,

Comments are closed.