Use c to execute SQL statements in the mysql database Excerpts Exax
Neil Matthew and Richard Stoneswrox Press Ltd. January 2001
content:
Execute SQL statement not returning data SQL statement returns the statement of the statement of the data MYSQL_FETCH_ROWMYSQL_FETCH_ROWMYSQL_DATA_SEEKMY_SQL_ROW_TELL, mYSQL_ROW_SEEKMYSQL_FREE_RESULT Search Data Retrieved Data Reference Information About the author
Similar to PostgreSQL, you can use many different languages to access mysql, including C, C , Java, and Perl. From the Chapter 5 of Professional Linux Program About MySQL, Neil Matthew and Richard Stones introduced to us how to perform SQL statements in the MySQL database using a detailed Mysql C interface. They will look at the statements that return data, such as INSERT, and statements that do not return data, such as Update and Delete. Then they will write simple programs that retrieve data from the database.
Executing the SQL statement now, we already have a connection, and know how to handle errors, it is time to discuss using our database to do some actual work. Performing all types of SQL primary keywords is mysql_query:
INT mysql_query (mysql * connection, const char * query)
As you can see, it is very simple. It takes a pointer to the connection structure and the text string containing the SQL to be executed; unlike the command line tool, the end semicolon will not be used. After success, return 0. In special cases that need to include binary data, you can use the related functions, mysql_real_query. Although we need to discuss mysql_query from the purpose of this chapter.
SQL statements do not return data We will discuss Update, DELETE, and INSERT statements first. Because they don't return data, it is easier to use.
Another important function we will introduce this is a function of checking the number of rows affected:
MY_ULONGLONG MYSQL_AFFECTED_ROWS (mysql * connection);
The most obvious thing that may be on this function is its extraordinary return results. This is a special non-symbol type due to portability. In order to use in Printf, it is recommended to force it to convert it into unsigned long integers that use% LU format specification. This function returns the number of rows affected by the previous Update, Insert or Delete query, which is performed using MySQL_Query.
Typically for the mysql_ function, the return code 0 indicates that there is no row that is affected; the positive number represents the actual result, usually the number of rows that are affected.
As mentioned earlier, an undesirable result may occur when using mysql_affected_rows. Let us first discuss the number of rows affected by the INSERT statement, which will operate as expected. Add the following code to the program connect2.c and call it for INSERT1.C:
#include
#include
#include "mysql.h"
INT Main (int Argc, char * argv []) {
Mysql my_connection;
Int res;
MySQL_INIT (& my_Connection);
IF (Mysql_Real_Connect (& MY_CONNECTION, "Localhost", "Rick", "Bar", "Rick", 0, NULL, 0)) {
Printf ("Connection Success / N");
Res = mysql_query (& my_connection, "INSERT INTO Children (FName, AGE)
VALUES ('ANN', 3) ");
IF (! res) {
Printf ("INSERTED% lu rows / n",
(unsigned long) mysql_affected_rows; & my_Connection);
} else {
FPRINTF (stderr, "insert error D: S / N", mysql_errno (& my_connection),
MySQL_ERROR (& my_Connection));
}
MySQL_Close (& my_Connection);
} else {
FPRINTF (stderr, "connection failed / n");
IF (MySQL_ERRNO (& my_Connection) {
FPRINTF (stderr, "connection error% d:% s / N",
MySQL_ERRNO (& my_connection), mysql_error (& my_connection));
}
}
Return EXIT_SUCCESS;
}
As expected, the number of rows insert is 1.
Now, we change the code, so the 'INSERT' section is replaced:
MySQL_ERRNO (& my_connection), mysql_error (& my_connection));
}
}
Res = mysql_query (& my_connection, "Update Children Set agn = 4
WHERE FNAME = 'Ann' ");
IF (! res) {
Printf ("Updated% lu rows / n",
(unsigned long) mysql_affected_rows; & my_Connection);
} else {
FPRINTF (stderr, "Update Error% D:% S / N",
MySQL_ERRNO (& my_Connection),
MySQL_ERROR (& my_Connection));
}
Now suppose data in the sub-table, as follows:
Childnofnameage1 2 3 4 5 6 7 8 9 10 11jenny Andrew Gavin Duncan EMMA Alex Adrian Ann Ann Ann14 10 4 2 0 11 5 3 4 3 4
If we execute Update1, the number of affected rows you want to report is 4, but actually program report 2, because it only must change 2 lines, although the WHERE clause identifies 4 lines. If you want Mysql_Affected_Rows reports to be 4 (this may be expected to be familiar with other databases), you need to remember to pass the client_found_rows flag to mysql_real_connect, in update2.c as follows:
IF (MySQL_Real_Connect (& my_Connection, "Localhost", "Rick", "Bar", "Rick", 0, NULL, Client_Found_Rows) {
If we reset the data in the database, then run the program with this modification, the number of rows it report is 4.
The function mysql_affected_Rows has the last strange place that occurs when deleting data from the database. If you use the WHERE clause, MySQL_AFFECTED_ROWS will return the number of rows in the expected return. However, if there is no WHERE clause, delete all rows, report the number of rows that are affected is 0. This is because the entire table is deleted due to the optimization of efficiency. This behavior is not affected by the Client_Found_Rows option flag.
The statement that returns the data is now the most common usage of SQL, and the SELECT statement of the data is retrieved from the database.
MySQL also supports the SHOW, DESCRIBE, and EXPLAIN SQL statements that returns the results, but they don't consider them here. In order to practice, the instructions for these statements are included in the manual.
You will retrieve data from the PostgreSQL chapter, you can retrieve data from the SQL SELECT statement in PQEXEC, here, all data is obtained, or retrieve data from the database from the database to get big data.
Due to the exact same reason, MySQL retrieval method is almost identical, although it actually does not need to describe the progressive retrieval in the form of a cursor. However, it provides an API that reduces the difference between these two methods. If necessary, it usually makes the interchange of the two methods easier.
Typically, there are 4 phases from the retrieval data from the MySQL database:
Send a query to retrieve any finishing required by the data execution data
As before, we sent a query using mysql_query. Data retrieval is done using mysql_store_result or mysql_use_result, depending on how to retrieve data, then use the mysql_fetch_row call sequence to process data. Finally, mysql_free_result must be called to allow MYSQL to perform any desired finishing.
All the functional data retrieval can be retrieved from the SELECT statement (or other returned data statements), in a single call, use mysql_store_result:
Mysql_res * mysql_store_result (mysql * connection);
This function must be called after mysql_query to retrieve data to store the data in the result set. This function retrieves all the data from the server and store it immediately in the client. It returns a pointer to the structure (result set structure) that we never encountered before we have never met. Returns NULL if the statement fails.
When using equivalent PostgreSQL, you should know that NULL means that an error has occurred, and this is different from the case where the data is not retrieved. Even, the return value is not NULL, nor does it mean that there is currently data to be processed.
If NULL is not returned, mysql_num_rows can be called and retrieve the number of rows returned, which may of course be 0.
MY_ULONGLONG MYSQL_NUM_ROWS (mysql_res * result);
It acquires the result structure returned from mysql_store_result, and the number of rows in the result is set, and the number of rows may be 0. If mysql_store_result is successful, MySQL_NUM_ROWS is always successful.
The combination of this mysql_store_result and mysql_num_rows is a simple and direct way to retrieve data. Once MySQL_Store_Result successfully returns, all query data has been stored on the client and we know that you can retrieve it from the resulting structure without worrying a database or network error, because all data for programs is local. It can also immediately find the number of rows returned, which makes the encoding easier. As mentioned earlier, it sends all results to the client. For large results set, it may cost a large number of servers, networks, and client resources. For these reasons, it is best to retrieve only the required data when using a larger data set. Soon, we will discuss how to use the MySQL_USE_RESULT function to complete this operation. Once the data is retrieved, you can use mysql_fetch_row to retrieve it, and use mysql_data_seek, mysql_row_seek, MySQL_ROW_TELL operation result set. Let's discuss these functions before starting to retrieve the data phase.
MySQL_FETCH_ROWMYSQL_FETCH_ROW
Mysql_row mysql_fetch_row (mysql_res * result);
This function uses the result structure acquired from the storage result, and retrieves a single line from the middle, and returns the data assigned to you in the row structure. Returns NULL when there is no more data or an error occurs. Later, we will come back to handle data in this line.
MySQL_DATA_SEEK
Void mysql_data_seek (mysql_res * result, my_ulonglong offset);
This function allows you to enter the result set, and the setting will be returned by the next acquisition operation. Offset is a line number, which must be within a range from 0 to the result set. Transfer 0 will result in returning a first line when the mysql_fetch_row is called next time.
MY_SQL_ROW_TELL, MySQL_ROW_SEEK
MySQL_ROW_OFFEST MySQL_ROW_TELL (mysql_res * result);
This function returns an offset value that represents the current location in the result set. It is not a quotes and cannot use it for mysql_data_seek. However, it can be used for:
MySQL_ROW_OFFSET MySQL_ROW_SEEK (mysql_res * result, mysql_row_offset offset);
It moves the current location in the result set and returns the previous location.
Sometimes this pair of functions are useful for jump between the known points in the result set. Note that you should not confuse the offset values used by the ROW TELL and ROW SEK with the line number used by Data_Seek. These are inexhaustible, the result will be what you want to see.
MySQL_Free_Result Before using these new functions, mysql_free_result is MYSQL_FREE_RESULT.
Void mysql_free_result (mysql_res * result);
This function must always be called to complete the result set, allowing the MySQL library to be assigned to it.
Retrieving data is now now writing the first program from the database to retrieve data. We will choose the contents of all ages greater than 5. Unfortunately, we still don't know how to handle this data, so we can do only loop it. This is SELECT1.C:
#include
#include
#include "mysql.h"
Mysql my_connection;
Mysql_res * res_ptr;
MySQL_ROW SQLROW;
INT Main (int Argc, char * argv []) {int res;
MySQL_INIT (& my_Connection);
IF (MySQL_REAL_CONNECT (& my_Connection, "Localhost", "Rick",
"Bar", "Rick", 0, NULL, 0)) {
Printf ("Connection Success / N");
Res = mysql_query (& my_Connection, "SELECT CHILDNO, FNAME,
Age from children where agn> 5 ");
IF (res) {
Printf ("SELECT Error:% S / N", MySQL_ERROR (& my_Connection));
} else {
RES_PTR = mysql_store_result (& my_connection);
IF (res_ptr) {
Printf ("Retrieved% Luows / N", (unsignedlong) mysql_num_rows (res_ptr));
While ((SQLROW = mysql_fetch_row (res_ptr))) {
Printf ("Fetched Data ... / N");
}
IF (MySQL_ERRNO (& my_Connection) {
FPrintf (stderr, "retrive error: s / n", mysql_error (& my_connection));
}
}
MySQL_FREE_RESULT (res_ptr);
}
MySQL_Close (& my_Connection);
} else {
FPRINTF (stderr, "connection failed / n");
IF (MySQL_ERRNO (& my_Connection) {
FPRINTF (stderr, "connection error% d:% s / N",
MySQL_ERRNO (& my_connection), mysql_error (& my_connection));
}
}
Return EXIT_SUCCESS;
}
The retrieval result set and the important part of the recirculated data has been retrieved has been highlighted.
Once retrieves a line of data, you need to retrieve data by line by line, instead of getting all the data immediately and store it in the client, you can replace mySQL_STORE_RESULT to replace mysql_use_result:
Mysql_res * mysql_use_result (mysql * connection);
This function also acquires a connection object and returns a result combined with a pointer, or returns NULL when an error occurs. Similar to mysql_store_result, it returns a pointer to the result set object; the key difference is that when returned, it actually retrieves any data to the result set, just the initialization result set is ready to retrieve data.
Reference
This article takes the 5th chapter of the Professional Linux published by Wrox Press Ltd.
