Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

run 2 query mysql

by bigup401 (Pilgrim)
on Mar 04, 2019 at 15:16 UTC ( #1230846=perlquestion: print w/replies, xml ) Need Help??

bigup401 has asked for the wisdom of the Perl Monks concerning the following question:

i want to run 2 sql query at once like similier example

#!/usr/bin/perl use strict; use warnings; my $sql = " CREATE TABLE test_dbi1 ( test_dbi_intr_no NUMBER(15), test_dbi_name VARCHAR2(100) ); UPDATE mytable SET col1=';yes;' WHERE col2=1; UPDATE mytable SET col1='Don\\'t use ;s and \\'s together, it is a pain' WHERE col2=1; CREATE TABLE test_dbi2 ( test_dbi_intr_no NUMBER(15), test_dbi_name VARCHAR2(100) ); ";

here is mine. i want to do it like slimier with placeholders

#deduct from the transferred account $update = $DBH->prepare("UPDATE USER SET BALANCE = BALANCE - ? WHERE U +SERNAME = ?"); $update->execute($amount, $user); $up = $update->finish(); #add to the receiving account $update1 = $DBH->prepare("UPDATE USER SET BALANCE = BALANCE + ? WHERE +USERNAME = ?"); $update1->execute($amount, $user); $up1 = $update1->finish();

Replies are listed 'Best First'.
Re: run 2 query mysql
by marto (Archbishop) on Mar 04, 2019 at 15:22 UTC

      cant be archived without module Try::Tiny

        It sure can, you just have to be willing to think about it instead of copying/pasting.

        { local $dbh->{AutoCommit} = 0; # enable transactions, if possible local $dbh->{RaiseError} = 1; local $@; eval { foo(...) # do lots of work here bar(...) # including inserts baz(...) # and updates $dbh->commit; # commit the changes if we get this far }; if ($@) { warn "Transaction aborted because $@"; eval { $dbh->rollback }; } } # Note, RaiseError and AutoCommit now revert to previous state.

        ...just one of a number of ways to adapt the example code.


        Dave

        That particular example uses Try::Tiny, the dependencies of which are all core.

Re: run 2 query mysql
by Marshall (Abbot) on Mar 04, 2019 at 18:10 UTC
    I would suggest:
    my $updateDeduct = $DBH->prepare("UPDATE USER SET BALANCE = BALANCE - +? WHERE USERNAME = ?"); my $updateAdd = $DBH->prepare("UPDATE USER SET BALANCE = BALANCE + ? W +HERE USERNAME = ?"); $DBH->do("BEGIN"); # explicitly start a new transaction # this overrides autocommit setting $updateDeduct->execute($amount, $user); $updateAdd=>execute($amount, $user); $DBH->do("COMMIT"); #end transaction, this is the "expensive part" ti +mewise
    In general, don't put prepare statements inside of a loop. You can prepare once and use many times.
      $DBH->do("BEGIN"); # explicitly start a new transaction # this overrides autocommit setting # ... $DBH->do("COMMIT"); #end transaction, this is the "expensive part" ti +mewise

      That way seems to be at least unportable, if not wrong. Why don't you follow the usual DBI procedure documented in DBD::mysql, DBD::MariaDB (with nearly exactly the same wording), DBI#Transactions and in DBI#begin_work? At least, manually issuing BEGIN and COMMIT commands bypasses anything DBI knows about transactions.

      Try::Tiny from the example in DBI#Transactions can easily be replaced by eval, and using the standard DBI methods works with all DBI-supported databases capable of transactions, not just with MySQL and MariaDB:

      my $dbh=DBI->connect('dbi:...', 'user', 'pass', { RaiseError => 1, Aut +oCommit => 1, ... }); $dbh->begin_work(); # if this fails, you have tried to nest transactio +ns. unless ( eval { change_some_stuff($dbh); change_more_stuff($dbh); $dbh->commit(); 1; } ) { warn "Rolling back because: $@"; eval { $dbh->rollback() }; } # At this point, either all changes or no changes have happened in the + database.

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
        Well as far as portability goes, you are correct in that BEGIN is not technically an offical SQL statement, but rather an extremely common alias for the standard SQL command "START TRANSACTION". I am unaware of a DB that doesn't have the BEGIN alias. But I guess there could be such a critter out there. COMMIT is a standard SQL command.

        I am certainly not an SQL guru and most of my DB code is fairly straightforward. The Perl DBI is fantastic in how easily command line SQL can be converted into Perl code. I tend to debug my SQL operations from the SQL command line and then transfer that knowledge into Perl DBI code. From that perspective, using BEGIN and COMMIT makes some sense and fits with my work flow. However as youi point out, there are DBI method calls for these SQL commands. I suspect that there is some efficiency to using them. However for me, a typical transaction is 500 operations and that would make no difference.

        Error handling is a big, a very big subject,
        I normally use the option RaiseError=>1 in the connect. If a fatal SQL error occurs, the program bombs. In a case like was posted, if a fatal error happens, the transaction fails. No rollback is needed. When the connection to the DB is lost, the DB will throw away the pending transaction (because COMMIT never occured).

        When you go to the trouble of trapping an error (as you show), You have to explictly do something to rollback or otherwise cancel the pending transaction. Then the question becomes "Now What?". If there is no answer to that question, then trapping the error doesn't matter.

        I have applications that are completely "restartable" as long as each transaction either fully works or doesn't work, the application can just be run again from scratch with an updated input file. Of course in some instance like shown, where a "delta" is being applied to the DB, that simple approach won't work. Lots of possilities that seemed to be outside the scope of the OP's question...

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (4)
As of 2019-06-19 00:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Is there a future for codeless software?



    Results (83 votes). Check out past polls.

    Notices?
    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!