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.
> 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(> );
prueba=> 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;
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 

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

Reply via email to