El Sáb 16 Oct 2004 09:52, Stuart Felenstein escribió: > My statements are all working but I'm not sure if > things are set up correctly. I say this because at > one point the first $query failed, yet the rest of > inserts wre committed. Now I believe I need to set > autocommit to 0 , yet the query failed due to a syntax > error. Hence 0 records effected wouldn't be the case > here. [snip] > > connection statement with error checking...........
I am not a MySQL user, but checking the sentences in a transactional way I see that you would have to check for ever query (at least in the way you are processing the transaction). > begin(); // transaction begins > $query = "INSERT INTO firsttable.....//first query > $result = mysql_query($query); // process first query > $query = "INSERT INTO secondtable...//second query > $result = mysql_query($query); // process second query > $query = "INSERT INTO thirdtable....//third query > $result = mysql_query($query); // process third query > > then: > > if(!$result) > { > echo mysql_errno($link) . ": " . mysql_error($link). > "\n"; > rollback(); // transaction rolls back > > exit; > } > else > { > commit(); // transaction is committed > echo "your insertion was successful"; OK, lets see how I would execute the queries in PostgreSQL: prueba=> create table tab1 ( prueba(> cod SERIAL PRIMARY KEY, prueba(> first INT CHECK (first < 0), prueba(> names varchar(25) prueba(> ); CREATE TABLE prueba=> begin; BEGIN prueba=> insert into tab1 values (default,10,'Martín'); ERROR: new row for relation "tab1" violates check constraint "tab1_first" prueba=> insert into tab1 values (default,-8,'José'); ERROR: current transaction is aborted, commands ignored until end of transaction block prueba=> end; COMMIT prueba=> select * from tab1; cod | first | names -----+-------+------- (0 filas) Now, as you see, because the CHECK constraint was violated in the first INSERT, ever other comand that would modify the table would be ignored until the transaction is over. This is the way a transactional database works (Oracle, Informix, Sybase, etc.) As I already stated, I don't work with MySQL so I don't know how it works with transactions. Maybe you should try to execute the same sentences I did in the mysql shell (does MySQL have CHECK constraints? If not just try putting a string larger then 25 chars in the names field) to see what happens. Good luck. -- 10:55:02 up 34 days, 1:13, 2 users, load average: 0.36, 0.26, 0.19 ----------------------------------------------------------------- Martín Marqués | select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral ----------------------------------------------------------------- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php