Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011
    Location
    india- new delhi
    Posts
    31

    Unanswered: mysql multistatement query problem(if one query fail how we rollback)

    Hello all,
    I am using Mysql c api for database interaction, I want to know that if one query of Mysql multi statement fail then how we roll back all query which successfully executed..
    ex-
    mysql_query(conn,"insert into t values(1);insert into t values(2);insert into t values('c')")
    if there are a scenario where queries are dependent to each other and here if one query fail then related other query should be rollback for making database consistency,[ mysql multi statement execute query one by one so how we will check all query will be run , if all can be run, then only process continue]
    Here in this example , second query is wrong, i want to do, if anyone query fail, no changes should be occour in database, is this possible?
    Last edited by rohitamitpathak; 03-17-11 at 04:54. Reason: code not justifying my prob//
    Amit Pathak
    S/w Developer
    Zero Systems Pvt Ltd..

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    look up START TRANSACTION in da manual

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2011
    Location
    india- new delhi
    Posts
    31
    now i got one solution of this, i need to use transaction here, for reading transaction go-> Transactions in MySQL — DatabaseJournal.com
    so my query will look like-
    mysql_query(conn,"begin;insert into t values(1);insert into t values(2);insert into t values('c');commit")

    and where in the code u got one query fail there we can write

    mysql_query(conn,"rollback");

    mysql_query(conn,"commit");

    now if any query failed all query updation will be rollback.............
    Amit Pathak
    S/w Developer
    Zero Systems Pvt Ltd..

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •