Method of executing SQL statements using C in the MySQL database
- 2020-05-14 05:03:17
- OfStack
They will discuss statements that return data, such as INSERT, and statements that do not return data, such as UPDATE and DELETE. They then write simple programs to retrieve data from a database
Execute the SQL statement
Now that we have a connection and know how to handle errors, it's time to talk about doing some real work with our database. The primary key for executing all types of SQL is mysql_query:
int mysql_query(MYSQL *connection, const char *query)
As you can see, it's very simple. It takes a pointer to the connection structure and a text string containing SQL to execute. Unlike command-line tools, you will not use an end semicolon. After success, return 0. You can use the related function, mysql_real_query, in special cases where you need to include data in base 2. For the purposes of this chapter, though, we only need to discuss mysql_query.
An SQL statement that does not return data
We will first discuss the UPDATE, DELETE, and INSERT statements. Because they do not return data, they are easier to use.
Another important function we will introduce here is the function that checks the number of lines affected:
my_ulonglong mysql_affected_rows(MYSQL *connection);
Perhaps the most obvious thing about this 1 function is its unusual return result. This is a special unsigned type for portability reasons. For use in printf, it is recommended to cast it to an unsigned long integer using the %lu format specification. This function returns the number of rows affected by the previous UPDATE, INSERT, or DELETE queries, which were executed using mysql_query.
Normally for the mysql_ function, the return code 0 means that no rows are affected; A positive number represents the actual result and is usually the number of rows affected.
As mentioned earlier, unexpected results can occur when mysql_affected_rows is used. Let's 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 insert1.c:
As expected, the number of rows inserted is 1.
Now, we change the code, so the 'insert' section is replaced with:
Now suppose there is some data in the subtable, as follows:
childno fname age 1
If we reset the data in the database and then run the program with this modification, it reports the number of rows to be 4.
The last oddity of the function mysql_affected_rows occurs when data is deleted from the database. If the WHERE clause is used, mysql_affected_rows returns the number of deleted rows as expected. However, if there is no WHERE clause, all rows are deleted and the number of rows reported to be affected is 0. This is because the entire table is dropped for efficiency reasons. This behavior is not affected by the CLIENT_FOUND_ROWS option flag.
A statement that returns data
Now it's time to discuss the most common use of SQL, the SELECT statement for retrieving data from a database.
MySQL also supports SHOW, DESCRIBE, and EXPLAIN SQL statements that return results, but they are not considered here. As a rule, the manual contains instructions for these statements.
As you will recall from the PostgreSQL chapter, you can retrieve data from the SQL SELECT statement in PQexec, where you get all the data at once, or you can use a cursor to retrieve data row by row from the database to handle big data.
For exactly the same reason, MySQL's retrieval method is almost identical, although it does not actually describe line-by-line retrieval in the form of a cursor. However, it provides API to bridge the gap between the two methods, which generally makes it easier to swap the two methods if needed.
Typically, there are four phases to retrieving data from the MySQL database:
A query
Retrieve the data
Process the data
Perform any collation required
As in the previous 1, we issue queries using mysql_query. Data retrieval is done using either mysql_store_result or mysql_use_result, depending on how you want to retrieve the data, and then the mysql_fetch_row call sequence is used to process the data. Finally, mysql_free_result must be called to allow MySQL to perform any required collation.
Function for all immediate data retrieval
All data can be retrieved from the SELECT statement (or any other statement that returns data), and in the single 1 call, mysql_store_result:
This function must be called after mysql_query retrieves the data to store it in the result set. This function retrieves all the data from the server and immediately stores it in the client. It returns a pointer to a structure (result set structure) that we have never encountered before. If the statement fails, NULL is returned.
When using the equivalent PostgreSQL, you should know that returning NULL means that an error has occurred, and that this is different from the case where no data was retrieved. Even if the return value is not NULL, it does not mean that there is currently data to process.
If NULL is not returned, you can call mysql_num_rows and retrieve the actual number of rows returned, which could of course be 0.
It gets the returned result structure from mysql_store_result and returns the number of rows in the result set, which may be 0. If mysql_store_result is successful, mysql_num_rows is always successful.
This combination of mysql_store_result and mysql_num_rows is a simple and direct way to retrieve data. Once mysql_store_result returns successfully, all the query data is already stored on the client and we know that we can retrieve it from the result structure without worrying about database or network errors because all the data is local to the program. You can also immediately discover the number of lines returned, which makes coding easier. As mentioned earlier, it sends all the results back to the client immediately. For large result sets, it can consume significant server, network, and client resources. For these reasons, it is best to retrieve only the data you need when using a larger data set. Shortly, we will discuss how to do this using the mysql_use_result function.
Once the data has been retrieved, it can be retrieved using mysql_fetch_row and the result set can be manipulated using mysql_data_seek, mysql_row_seek, mysql_row_tell. Before we begin the data retrieval phase, let's discuss these functions for a moment.
This function takes the result structure retrieved from the stored result and retrieves the single 1 row from it, returning the data assigned to you in the row structure. When there is no more data or an error occurs, NULL is returned. We will come back later to process the data in this 1 row.
This function allows you to enter the result set and set the rows that will be returned by the next fetch operation. offset is a line number that must be in the range from 0 to the number of rows in the result set minus 1. Passing 0 causes line 1 to be returned on the next call to mysql_fetch_row.
This function returns an offset value that represents the current position in the result set. It is not a line number and cannot be used for mysql_data_seek. However, it can be used for:
It moves the current position in the result set and returns to the previous position.
Sometimes, this 1 pair is useful for jumping between known points in the result set. Be careful not to confuse the offset values used by row tell and row seek with the line Numbers used by data_seek. These are not interchangeable, and the result will be what you want to see.
When the result set is complete, this function must always be called to allow the MySQL library to collate the objects assigned to it.
Retrieve the data
Now start writing the first program to retrieve data from the database. We will select all lines older than 5. Unfortunately, we don't know what to do with this data yet, so all we can do is loop through it. This is select1.
Important parts of the retrieved result set and loop through the retrieved data are highlighted.
Retrieve 1 row of data at a time
To retrieve the data line by line as needed, instead of getting all the data at once and storing it in the client, replace the mysql_store_result call with mysql_use_result:
This function also takes a connection object and returns the result in combination with a pointer, or returns NULL in case of an error. Similar to mysql_store_result, it returns a pointer to the result set object; The key difference is that when you return, you don't actually retrieve any data to the result set, you just initialize the result set to be ready to retrieve the data.
The resources
You can refer to the English text of this article on the developerWorks global site.
This article is taken from chapter 5 of Professional Linux1 published by Wrox Press Ltd.
About the author
Rick Stones is a systems designer who works in the IT division of a large pan-european pharmaceutical distribution and distribution company. Since 1985, he has been using UNIX in various forms and found Linux with the early.99 CD-ROM distribution. He writes software for UNIX, Linux, and other platforms in a variety of languages, and also installs and manages several Linux servers. In his very limited spare time, he worked hard to improve his piano playing skills.
C and UNIX V6 have been in contact with C and UNIX V6 since their university days. Neil Matthew has more than 20 years of experience in UNIX and Linux. Since then he has worked in the IT industry, developing communications software and maintaining a passion for esoteric programming languages and development techniques. Now, as a systems designer, he advises on technology strategy and QA. He is enthusiastic about building the Linux business case within the corporate enterprise. In his spare time, writing or riding for pleasure, Neil tried to persuade his wife and two children to join him for walks in the country. Neil has co-authored several books published by Wrox Press, most notably Beginning Linux Programming and Professional Linux Programming.
Execute the SQL statement
Now that we have a connection and know how to handle errors, it's time to talk about doing some real work with our database. The primary key for executing all types of SQL is mysql_query:
int mysql_query(MYSQL *connection, const char *query)
As you can see, it's very simple. It takes a pointer to the connection structure and a text string containing SQL to execute. Unlike command-line tools, you will not use an end semicolon. After success, return 0. You can use the related function, mysql_real_query, in special cases where you need to include data in base 2. For the purposes of this chapter, though, we only need to discuss mysql_query.
An SQL statement that does not return data
We will first discuss the UPDATE, DELETE, and INSERT statements. Because they do not return data, they are easier to use.
Another important function we will introduce here is the function that checks the number of lines affected:
my_ulonglong mysql_affected_rows(MYSQL *connection);
Perhaps the most obvious thing about this 1 function is its unusual return result. This is a special unsigned type for portability reasons. For use in printf, it is recommended to cast it to an unsigned long integer using the %lu format specification. This function returns the number of rows affected by the previous UPDATE, INSERT, or DELETE queries, which were executed using mysql_query.
Normally for the mysql_ function, the return code 0 means that no rows are affected; A positive number represents the actual result and is usually the number of rows affected.
As mentioned earlier, unexpected results can occur when mysql_affected_rows is used. Let's 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 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 inserted is 1.
Now, we change the code, so the 'insert' section is replaced with:
mysql_errno(&my_connection), mysql_error(&my_connection));
}
}
res = mysql_query(&my_connection, "UPDATE children SET AGE = 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 there is some data in the subtable, as follows:
childno fname age 1
2
3
4
5
6
7
8
9
10
11
JennyAndrew
Gavin
Duncan
Emma
Alex
Adrian
Ann
Ann
Ann
Ann
1410
4
2
0
11
5
3
4
3
4
if (mysql_real_connect(&my_connection, "localhost",
"rick", "bar", "rick", 0, NULL, CLIENT_FOUND_ROWS)) {
If we reset the data in the database and then run the program with this modification, it reports the number of rows to be 4.
The last oddity of the function mysql_affected_rows occurs when data is deleted from the database. If the WHERE clause is used, mysql_affected_rows returns the number of deleted rows as expected. However, if there is no WHERE clause, all rows are deleted and the number of rows reported to be affected is 0. This is because the entire table is dropped for efficiency reasons. This behavior is not affected by the CLIENT_FOUND_ROWS option flag.
A statement that returns data
Now it's time to discuss the most common use of SQL, the SELECT statement for retrieving data from a database.
MySQL also supports SHOW, DESCRIBE, and EXPLAIN SQL statements that return results, but they are not considered here. As a rule, the manual contains instructions for these statements.
As you will recall from the PostgreSQL chapter, you can retrieve data from the SQL SELECT statement in PQexec, where you get all the data at once, or you can use a cursor to retrieve data row by row from the database to handle big data.
For exactly the same reason, MySQL's retrieval method is almost identical, although it does not actually describe line-by-line retrieval in the form of a cursor. However, it provides API to bridge the gap between the two methods, which generally makes it easier to swap the two methods if needed.
Typically, there are four phases to retrieving data from the MySQL database:
A query
Retrieve the data
Process the data
Perform any collation required
As in the previous 1, we issue queries using mysql_query. Data retrieval is done using either mysql_store_result or mysql_use_result, depending on how you want to retrieve the data, and then the mysql_fetch_row call sequence is used to process the data. Finally, mysql_free_result must be called to allow MySQL to perform any required collation.
Function for all immediate data retrieval
All data can be retrieved from the SELECT statement (or any other statement that returns data), and in the single 1 call, mysql_store_result:
MYSQL_RES *mysql_store_result(MYSQL *connection);
This function must be called after mysql_query retrieves the data to store it in the result set. This function retrieves all the data from the server and immediately stores it in the client. It returns a pointer to a structure (result set structure) that we have never encountered before. If the statement fails, NULL is returned.
When using the equivalent PostgreSQL, you should know that returning NULL means that an error has occurred, and that this is different from the case where no data was retrieved. Even if the return value is not NULL, it does not mean that there is currently data to process.
If NULL is not returned, you can call mysql_num_rows and retrieve the actual number of rows returned, which could of course be 0.
my_ulonglong mysql_num_rows(MYSQL_RES *result);
It gets the returned result structure from mysql_store_result and returns the number of rows in the result set, which may be 0. If mysql_store_result is successful, mysql_num_rows is always successful.
This combination of mysql_store_result and mysql_num_rows is a simple and direct way to retrieve data. Once mysql_store_result returns successfully, all the query data is already stored on the client and we know that we can retrieve it from the result structure without worrying about database or network errors because all the data is local to the program. You can also immediately discover the number of lines returned, which makes coding easier. As mentioned earlier, it sends all the results back to the client immediately. For large result sets, it can consume significant server, network, and client resources. For these reasons, it is best to retrieve only the data you need when using a larger data set. Shortly, we will discuss how to do this using the mysql_use_result function.
Once the data has been retrieved, it can be retrieved using mysql_fetch_row and the result set can be manipulated using mysql_data_seek, mysql_row_seek, mysql_row_tell. Before we begin the data retrieval phase, let's discuss these functions for a moment.
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);
This function takes the result structure retrieved from the stored result and retrieves the single 1 row from it, returning the data assigned to you in the row structure. When there is no more data or an error occurs, NULL is returned. We will come back later to process the data in this 1 row.
void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset);
This function allows you to enter the result set and set the rows that will be returned by the next fetch operation. offset is a line number that must be in the range from 0 to the number of rows in the result set minus 1. Passing 0 causes line 1 to be returned on the next call to mysql_fetch_row.
MYSQL_ROW_OFFEST mysql_row_tell(MYSQL_RES *result);
This function returns an offset value that represents the current position in the result set. It is not a line number and cannot be used 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 position in the result set and returns to the previous position.
Sometimes, this 1 pair is useful for jumping between known points in the result set. Be careful not to confuse the offset values used by row tell and row seek with the line Numbers used by data_seek. These are not interchangeable, and the result will be what you want to see.
void mysql_free_result(MYSQL_RES *result);
When the result set is complete, this function must always be called to allow the MySQL library to collate the objects assigned to it.
Retrieve the data
Now start writing the first program to retrieve data from the database. We will select all lines older than 5. Unfortunately, we don't know what to do with this data yet, so all we can do is loop through it. This is select1.
mysql_errno(&my_connection), mysql_error(&my_connection));
}
}
res = mysql_query(&my_connection, "UPDATE children SET AGE = 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));
}
Important parts of the retrieved result set and loop through the retrieved data are highlighted.
Retrieve 1 row of data at a time
To retrieve the data line by line as needed, instead of getting all the data at once and storing it in the client, replace the mysql_store_result call with mysql_use_result:
MYSQL_RES *mysql_use_result(MYSQL *connection);
This function also takes a connection object and returns the result in combination with a pointer, or returns NULL in case of an error. Similar to mysql_store_result, it returns a pointer to the result set object; The key difference is that when you return, you don't actually retrieve any data to the result set, you just initialize the result set to be ready to retrieve the data.
The resources
You can refer to the English text of this article on the developerWorks global site.
This article is taken from chapter 5 of Professional Linux1 published by Wrox Press Ltd.
About the author
Rick Stones is a systems designer who works in the IT division of a large pan-european pharmaceutical distribution and distribution company. Since 1985, he has been using UNIX in various forms and found Linux with the early.99 CD-ROM distribution. He writes software for UNIX, Linux, and other platforms in a variety of languages, and also installs and manages several Linux servers. In his very limited spare time, he worked hard to improve his piano playing skills.
C and UNIX V6 have been in contact with C and UNIX V6 since their university days. Neil Matthew has more than 20 years of experience in UNIX and Linux. Since then he has worked in the IT industry, developing communications software and maintaining a passion for esoteric programming languages and development techniques. Now, as a systems designer, he advises on technology strategy and QA. He is enthusiastic about building the Linux business case within the corporate enterprise. In his spare time, writing or riding for pleasure, Neil tried to persuade his wife and two children to join him for walks in the country. Neil has co-authored several books published by Wrox Press, most notably Beginning Linux Programming and Professional Linux Programming.