Mysqli execute multiple Statement methods in batch execution of multiple statements and one function call

Source: Internet
Author: User
This article is mainly to share with you mysqli batch execution of multiple statements and a function call to execute multiple statement methods, I hope you can have their own ideas through the example of this article.

Supports the execution of multiple statements specified in a single string. To use this feature with a given connection, you must assign the Client_multi_statements option in the flag parameter to Mysql_real_connect () when you open the connection. You can also set it for an existing connection by calling Mysql_set_server_option (mysql_option_multi_statements_on).

Common routines:

   /* Connect to server with option Client_multi_statements */mysql_real_connect (..., client_multi_statements);/* Now Execute multiple queries */mysql_query (MySQL, "DROP table IF EXISTS test_table;\                   CREATE TABLE test_table (id INT); \
  insert into test_table VALUES, \                   UPDATE test_table SET id=20 WHERE id=10;\                   SELECT * from test_table;\                   DR OP TABLE test_table ");d o{/  * Process All results */  ...  printf ("Total Affected rows:%lld", Mysql_affected_rows (MySQL));  ...  if (! ( result= mysql_store_result (mysql))  {     printf (stderr, "Got fatal error processing query\n");     Exit (1);  }  Process_result_set (result); /* Client function */  mysql_free_result (result);} while (!mysql_next_result (MySQL));

See the code specifically:

#include <stdio.h> #include <stdlib.h> #include <string.h> #include <dlfcn.h> #include < mysql/mysql.h> #include <stdio.h> #include <stdlib.h> #include <unistd.h> #include <string.h > #include <errno.h> #include <termios.h> #include <mysql/mysql.h>void process_result_set (MySQL *        MySQL, mysql_res *result) {int i = 0;        unsigned int fieldnum;        From the result set, get the header information Mysql_field *fields = mysql_fetch_fields (result);        Fieldnum = Mysql_field_count (mysql);        for (i=0; i<fieldnum; i++) {printf ("%s\t", fields[i].name);        } printf ("\ n");        From the result set, the information is obtained by row mysql_row row = NULL;            Gets the data from a row in the result set (row = Mysql_fetch_row (result)) {fieldnum = Mysql_field_count (mysql); optimization, how many columns of my row are ....            Look for such API functions for (i=0; i<fieldnum; i++)//tested to find a pointer array that is not ending with 0:     {printf ("%s\t", Row[i]);       } printf ("\ n");    }}int Main () {int ret = 0, status = 0;    MYSQL *mysql;    Mysql_res *result;    Mysql_row ROW;    Char *query;    MySQL = Mysql_init (NULL);    MySQL =mysql_real_connect (mysql, "localhost", "root", "123456", "Mydb2", 0, NULL, client_multi_statements);        if (mysql = = NULL) {ret = Mysql_errno (MySQL);        printf ("Func mysql_real_connect () err\n");    return ret;    } else {printf ("ok......\n"); }/* Execute multiple statements */status = mysql_query (mysql, "DROP table IF EXISTS test_table;\create table test_table  (id INT); \insert into Test_table VALUES (ten); \update test_table SET id=20 WHERE id=10;\select * from Test_table;\drop table    Test_table ");        if (status) {printf ("Could not execute statement (s)");        Mysql_close (MySQL);    Exit (0); }/* Process Each statement result */do {/* Did current statement return data? */result = M YsqL_store_result (MySQL); if (result) {/* YES; process rows and free the result set */Process_result_set (                MySQL, result);            Mysql_free_result (result);                } else/* No result set or error */{if (mysql_field_count (mysql) = = 0)                {printf ("%lld rows affected\n", Mysql_affected_rows (MySQL)); } else/* Some error occurred */{printf ("Could not retrieve result set\                    n ");                Break }}/* More results? -1 = no, >0 = error, 0 = yes (keep looping) */if (status = Mysql_next_result (mysql)) > 0) pr    intf ("Could not execute statement\n");    } while (status = = 0); Mysql_close (MySQL);}

This is the first function call of MySQL to execute the contents of multiple statements.

Next, we mainly introduce the PHP implementation of mysqli batch execution of multiple statements, combined with an example of PHP connection mysqli and batch execution of multiple statements of the relevant operation skills,

Specific as follows:

You can perform multiple operations at once or retrieve multiple result sets.

Instance:


<?php$mysqli = new mysqli ("localhost", "root", "111111", "test");/* check  Connection */if (Mysqli_connect_errno ()) {printf ("Connect failed:%s\n", Mysqli_connect_error ()); Exit ();} /* Multi_query executes one or more queries against the database. Multiple queries are delimited by semicolons. */$query = "SELECT * from Test where id = 1;"; $query. = "SELECT name from Test";/* executes the query in bulk and returns FALSE if the first query fails.  */if ($mysqli->multi_query ($query)) {do {/*) gets the first result set */if ($result = $mysqli->store_result ()) {while ($row = $result->fetch_row ())      {printf ("%s\n", $row [0]);    } $result->free ();    }/* Check if a multi-query has more results */if ($mysqli->more_results ()) {printf ("-----------------\ n"); }//Prepare the next result set} while ($mysqli->next_result ());} /* Close connection */$mysqli->close ();? 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.