Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2001
    Location
    South Africa
    Posts
    33

    Unanswered: sending chained queries

    How can I send the following to mysql - using the mysql C API functions:

    START TRANSACTION;
    SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
    UPDATE table2 SET summmary=@A WHERE type=1;
    COMMIT;

    IE : how can I use 1 function call to run this batch ???

    THANKS

  2. #2
    Join Date
    Jun 2003
    Location
    Phoenix, AZ
    Posts
    8

    Re: sending chained queries

    Originally posted by tag
    How can I send the following to mysql - using the mysql C API functions:

    START TRANSACTION;
    SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
    UPDATE table2 SET summmary=@A WHERE type=1;
    COMMIT;

    IE : how can I use 1 function call to run this batch ???

    THANKS
    Sending multilpe queries and receiving multiple result sets is only supported in 4.1.x and above.

  3. #3
    Join Date
    Nov 2001
    Location
    South Africa
    Posts
    33

    Re: sending chained queries

    Hi,

    Originally posted by kellewic
    Sending multilpe queries and receiving multiple result sets is only supported in 4.1.x and above.
    OK - understood - but how can I send the batch using the API ??
    any sugestions ??

  4. #4
    Join Date
    Jun 2003
    Location
    Phoenix, AZ
    Posts
    8

    Re: sending chained queries

    Originally posted by tag
    OK - understood - but how can I send the batch using the API ??
    any sugestions ??
    You can't do it with one function call as your original post wanted. You would have to use several mysql_real_query() calls using the MySQL API located at http://www.mysql.com/doc/en/C.html


    something like:

    const char *query = "START TRANSACTION";

    if (mysql_real_query(&mysql, query, strlen(query))){
    // Success
    }


    Repeat the above for the entire set. Best thing to do is write a function like:

    int
    Query(MYSQL *mysql, const char *query)
    {
    return mysql_real_query(mysql, query, strlen(query));
    }


    I suppose if you wanted to get real creative you could declare something like:

    struct transaction {
    char *queries[20];
    }


    int main()
    {
    // Note this will not actually work with how the above struct is
    // declared since there is no memory allocation to point to
    // Just an example

    addQueryToTrans("SELECT @A:=SUM(salary) FROM table1 WHERE type=1");
    addQueryToTrans("UPDATE table2 SET summmary=@A WHERE type=1");
    }


    void
    addQueryToTrans(struct transaction *trans, char *query)
    {
    // To get around the problem in main() of no memory location
    // to point to you *could* do something like:

    char *tmp = malloc((sizeof(char)*strlen(query))+1);
    strncpy(tmp, query, strlen(query));

    trans->queries[##] = tmp;
    }

    void resetTrans(struct transaction *trans)
    {
    for (loop through trans){
    set element = NULL;
    }
    }

    int doTransaction(MYSQL *mysql, struct transaction *trans)
    {
    Query(mysql, "START TRANSACTION");

    // Loop through transaction->queries
    for(loop through trans){
    Query(mysql, element);
    }

    If (all goes well){
    Query(mysql, "COMMIT");
    }
    else{
    Query(mysql, "ROLLBACK");
    }
    }


    Not sure if that really answered your question. I'm also unsure if all that works with transactions in MySQL since I typically use MyISAM tables which do not support transactions.

Posting Permissions

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