Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re^3: DBI Problem

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


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

AutoCommit => 0

means you have to explicitly call $dbh->commit or your changes will be rolled back.

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

    Yes! Thank you! I should've seen that sooner.. But I guess I figured that was fine because other queries have worked..

    Now my new question is, how did it ever work for my previous calls?

    Previously, I was running a script that did Create tables statements and Insert Statements.

    I had to put the Insert statements first or I had the same 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?

      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.

        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?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (4)
As of 2019-12-15 17:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?