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
