Oracle - Editor Adding Table Name to Query, Causing Joined Table Inserts to Fail

gabe565.nic

Hi!

I am setting up Editor with an Oracle DB which has lots of joined tables. Read, update, and delete work just fine but I have a weird problem when I try to create an entry.

The main database gets a row added just fine with this query:

INSERT INTO  users  (user_type_id, status_id)
    VALUES (:user_type_id, :status_id)
    RETURNING users.user_id INTO :editor_pkey_value

But then any of the joined tables fail with ORA-00904: "USERS"."USER_ID": invalid identifier because the query looks like this:

INSERT INTO user_info (first_name, middle_initial, last_name, email_address, gender_id, birthdate, ethnic_id, user_id)
    VALUES (:first_name, :middle_initial, :last_name, :email_address, :gender_id, :birthdate, :ethnic_id, :user_id)
    RETURNING users.user_id INTO :editor_pkey_value

I assume this is because it is trying to pull the value of users.user_id when it actually wants user_info.user_id or just user_id.

I could fix this by changing line 116 in Driver/Oracle/Query.php from

$pkey = $this->pkey();

to

$pkey = preg_replace('/(.*)\.(.*)/', '$2', $this->pkey());

So that the table name always gets removed, but this solution seems hacky and I do not know if it will work in all cases.

Is this a current bug in the Oracle driver or do I have something configured wrong?
Thank you!

allan

That does look like a bug. Could you show me how you have configured the PHP libraries please?

Thanks,
Allan

gabe565.nic

Thanks for the response!

Yeah there's definitely something weird happening with the pkey there.
I can send whatever you need. Do you mean the PHP file where I instantiate the Editor instance and set all of the fields and joins? It is attached!

Thanks,
Gabe

allan

Perfect - thanks. I'll try to take a look into it tomorrow.

Allan

allan

I've remembered why I implemented it this way.

The database classes need to be able to get the primary key value for an inserted row so it can be returned after an insert. With postgres that's easy, we just add RETURNING ..., MySQL has a function for it, and with SQL Server it can be found by querying the system tables.

I wasn't able to find a way to dynamically determine what the primary key column of a table is in Oracle, so I went for the short cut of using the main id. That works for inserts on the host table, but if you also need to do an insert on a joined table, it isn't going to work.

If you have any ideas how the primary key column name can be determined for any given table, that would be very useful.

Allan

gabe565.nic

Allan,

Okay that does make sense, but shouldn't it only be doing that for the first insert?

In the table I am inserting into, the user_id is the same primary key for all of the inserted rows, so it makes sense to remove the table name after it initially gets the value from the sequence. On other tables, trying to return the initial table's primary key is obviously going to fail, though.

With the fix I posted, it will work if the joined tables have the same column name for the primary key. I don't know if Oracle has another way to reference that, though. I'll see what I can find.

Thank you!

allan

Okay that does make sense, but shouldn't it only be doing that for the first insert?

Yes - it should only be doing that on the main table. I'll add a bit of code that will present it doing any operation on joined tables until the primary key for those joined tables can be identified. It might be that a new API method need to be introduced to Editor's libraries to allow that to be indicates.

Allan