multiple queries

amrigo

This is not a clear question.

I am using php and Oracle to develop an aplication having 47 fields (textfields and selects) that comes from 35 tables (some have static content)

So i have insert, update, delete and alter into a single operation, and these operations affect all those related tables.

What is the best aproach?
Wich are the pitfalls in such a great project!
or even if someone experienced in a project like this can share some tips.

Best regards

Sxooter

Seeing as how you're using a real database with stored procedures, I'd use them. Write a few stored procs that put the individual fields where they're supposed to go in each table and then just call them instead of writing tons of PHP code.

Or, if you're not comfy with stored procs, remember to wrap everything up in a transaction block so if your 37th out of 37 inserts fails, you don't have an inconsistent dataset.

amrigo

So i atach transactions as a way to take care of the integrity? i only see one article with transactions with php/postgresql here at phpbuilder http://www.phpbuilder.com/columns/linuxjournal200009.php3

if you know more articles about using transactions please tell me!

Cheers

Sxooter

At their simplest, transactions are a way of doing things all or nothing. I.e.:

begin;
insert into...
insert into...
update ...
delete...
insert...
<AN INSERT NOW FAILS>
rollback;

poof, the whole thing is rolled back. In Postgresql, once a single error occurs in a transaction, the whole thing is abandoned, while in Oracle you can do all kinds of fancy exception handling and continue if you so desire.

Stored procs usually run AS a transaction, so using them makes life easy because of part of it fails, the whole thing should just roll back.

I'm not sure where to go other than www.oracle.com for info on doing transactions in Oracle, but they're basically the same, with more bells and whistles, on Oracle as they are on Postgresql, so you can do it the way up above, catch any errors, and rollback or commit if no errors.

Note that Postgresql will not commit a transaction with errors, and I'm pretty sure that unless you catch the exception in Oracle it will also rollback regardless of whether you try to commit it or roll it back.