Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Re^5: DBI Problem

by erix (Parson)
on Jan 29, 2018 at 20:17 UTC ( #1208065=note: print w/replies, xml ) Need Help??

in reply to Re^4: DBI Problem
in thread SOLVED: DBI Problem

So does that mean Create table statements force a commit to the DB even if Autocommit is 0 and I never explicitly called commit?

Try it and see. I suppose it makes sense; CREATE TABLE is DDL; INSERT is DML; and only some databases (PostgreSQL, for instance) know how to do transactional DDL.

Transactional DDL example:

# begin; --> start a transaction create table testtable(id serial, c text); --> ... and create a tabl +e BEGIN CREATE TABLE # insert into testtable (c) values ('this will be short-lived'); --> +insert a row INSERT 0 1 # select * from testtable; --> have a look id | c ----+-------------------------- 1 | this will be short-lived (1 row) # rollback; --> go back to initial state before begin ROLLBACK # select * from testtable; --> now the table is gone... ERROR: relation "testtable" does not exist LINE 1: select * from testtable; ^

You get the idea...

Update: added a DDL transaction begin-create-rollback session.

Replies are listed 'Best First'.
Re^6: DBI Problem
by Zarquav (Novice) on Jan 29, 2018 at 20:30 UTC

    I see. That would do it, yup..

    The problem I had before was a loop where I was doing a Create followed by an Insert. In the end, all the tables were created, and all the inserts were done except the LAST one. Re-ordering the loop to do Insert then Create "solved" that problem, or rather concealed the fact I wasn't committing properly.. lol.

    I'm using mySQL, which a quick Google search reveals will not do rollbacks on DDL queries.

    Thanks again!

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1208065]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (5)
As of 2020-07-13 15:23 GMT
Find Nodes?
    Voting Booth?

    No recent polls found