8.3 Accessing MySQL data using the C language 4. Processing returned data
MySQL returns two types of data
1. The information extracted from the table, that is, the column data
2. Data on data, known as metadata (metadata), such as column names and types.
The Mysql_field_count function provides some basic information about the results of the query, accepts the connection object, and returns the number of fields (columns) in the result set:
unsigned int mysql_field_count (MySQL *connection);
In more general cases, you can use Mysql_field_count to do other things, such as determining why Mysql_store_result calls fail. For example, if Mysql_store_result returns, but Mysql_field_ Count returns a positive number, which can be inferred that this is an extraction error. However, if Mysql_field_count returns 0, there is no column to extract, which can explain why the stored results fail. This function is useful for general purpose query processing modules or any arbitrary construction of queries. .
Write program select3.c output data
The output data is not very neat, if you need to format the data, you have to get the data and metadata returned by MySQL. Use Mysql_fetch_field to extract metadata and data into a new structure at the same time:
Mysql_field *mysql_fetch_field (Mysql_res *result);
You need to call this function repeatedly until you return a null value that represents the end of the data. You can then use a pointer to the data in the field structure to get information about the column. Structure Mysql_field defined in mysql.h.
Description of members in Mysql_field
Char *name; Column name, as String
Char *table; The name of the table to which the column belongs. This is especially useful when you want to use more than one table for a query.
Char *def; If Mysql_list_fields is called, it will contain the default value for that column
Enum Enum_field_types type; Column type
unsigned int length; Column width
unsigned int max_length; If you use Mysql_store_result, it will contain the length of the longest column value extracted in bytes
unsigned int flags; Flags for column Definitions
unsigned int decimals; Number of digits after the decimal point.
Also, use the following function to overwrite the current field number:
Mysql_field_offset Mysql_field_seek (mysql_res *result, Mysql_field_offset OFFSET);
This number is incremented automatically with each Mysql_fetch_field call. If the value 0 is passed to the parameter offset, the first column is skipped.
Writing a program select4.c
8.3.4 More Function Example API call description
Char *mysql_get_client_info (void); Returns the version information for the library used by the client
Char *mysql_get_host_info (MySQL *connection); Return Server connection Information
Char *mysql_get_server_info (MySQL *connection); Returns information for the currently connected server
Char *mysql_info (MySQL *connection); Returns the most recently executed query information
int mysql_select_db (MySQL *connection, const char *dbname);
If the user has permissions, the default database is changed to the database specified by the parameter
int Mysql_shutdown (MySQL *connection, enum mysql_enum_shutdown_level);
If the user has permissions, close the connected database server.
The 8.4 CD database application creates a new database and then takes it as the current database:
Create Database blpcd;
Use BLPCD;
Consider only two of the most important relationships of data
First, each CD consists of a different number of tracks, so the track data will be present in a separate table from the other CD data.
Second, each artist often has more than one album, so store the artist's information once and then extract all the CDs that belong to the artist individually.
8.4.1 Creating a table needs to determine the actual structure of the table. Starting with the Main Table--CD table, it holds most of the information. You need to save a CD ID, a category number, a title, and some callouts. An ID number from the artist table is also required to indicate which artist made the album.
The artist table is simple, it only preserves the artist's name and a unique artist ID number. The track table is also simple, requiring only a CD ID to indicate which CD, a track number, and a track title the tracks belong to.
The first is the CD table:
CREATE TABLE CD (
ID int auto_increment Not NULL primary key,
Title varchar (not NULL),
artist_id int not NULL,
Catalogue varchar (+) not NULL,
Notes varchar (100)
);
Create a table CD that contains some of the following columns:
The ID column, which contains an automatically incremented integer, which is the primary key of the table
Title with a maximum length of 70 characters
artist_id, an integer used in the artist table
Catalogue number up to 30 characters
Notes with a maximum of 100 characters
Only the Notes column can be null, and the other columns must contain values
Next is the artist table:
CREATE TABLE Artist (
ID int auto_increment Not NULL primary key,
Name varchar (+) not NULL
);
And finally the track table:
CREATE TABLE Track (
cd_id int not NULL,
track_id int not NULL,
Title varchar (70),
Primary KEY (cd_id, track_id)
);
This time, using a different method to declare the primary key, the track table differs in that the ID of each CD appears multiple times, and for any specified tracks, such as track 1, will appear multiple times on different CDs. However, the combination of the two will always be unique, so the primary key is declared as a combination of both columns, which is called the Union key.
Store these SQL statements in the file Create_table.sql, save the file in the current directory, and then start creating the database and its tables.
8.4.2 Add data to write the Insert_data.sql file, use the \. command to execute.
Note that this file will delete all data in the database BLPCD to ensure that the script is clean
In addition, this file inserts a value in the ID field instead of having the auto_increment automatically assigned.
Use the MySQL command client and some SQL statements to check. First, select the first two tracks of each album from the database:
Select Artist.name, Cd.title as "CD title", track.track_id, Track.title as "track" from artist, CDs, track WHERE artist.id = cd.artist_id and track.cd_id = Cd.id and track.track_id < 3;
The first part of this SQL statement is:
SELECT Artist.name, Cd.title, Track.track.id, Track.title
It describes which columns you want to display by using the tag tablename.column.
The As section of the SELECT statement, select Artist.name, Cd.title as "CD title", track.track_id and Track.title as "track" simply renames the column name at output. Therefore, the title bar from the titles column (Cd.title) from the CD table is named "CD title" and the Track.title column is named "Track". The use of as gives a better output, which is a useful word for SQL statements on the command line, but it is rarely used when invoking SQL statements through other programming languages.
From artist, CDs, track
It tells the server which table name to use
The WHERE clause is the part that requires a bit of skill:
WHERE artist.id = cd.artist_id and track.cd_id = Cd.id and track.track_id < 3
The first section tells the server that the ID in the artist table should be the same as the artist_id in the CD table. The second part tells the server that the cd_id column of the track table should be the same as the ID column in the CD table, and the third part track.track_id< 3 reduces the number of returned data so that you can only get track 1 and track 2 from each CD. Finally, the 3 conditions are combined using and, because the 3 conditions are true at the same time.
8.4.3 a common problem with using C-language access data to write a CD-like application is the inability to know the number of results returned, and how to pass these results between the client code and the code that accesses the database. In this application, a fixed-size structure is used in order to remain simple and focus on the database interface. But in the actual program, this may not be acceptable. A common workaround (which also helps reduce network traffic) is to always fetch one row of data at a time, just as the Mysql_use_result and Mysql_fetch_row described earlier.
1. Interface definition
Start with the App_mysql.h file first, which defines the structure and functions:
The first is some structure:
/* A simplistic structure to represent the current CD, excluding the track information */
struct current_cd_st{
int artist_id;
int cd_id;
Char artist_name[100];
Char title[100];
Char catalogue[100];
};
/* A simplistic track details structure */
struct current_tracks_st{
int cd_id;
Char track[20][100];
};
#define Max_cd_result 10
struct cd_search_st{
int Cd_id[max_cd_result];
}
Then a pair of functions, which are used to connect to the database and disconnect from the database
/* Database Backend functions */
int Database_start (char *name, char *password);
void Database_end ();
Now, turning to the function that manipulates the data, note that there are no functions created or three artists, will implement it in the background, create artist entries as needed, and then delete them when they are not used by any albums.
/* Functions for adding a CD */
int Add_cd (char *artist, Char *title, char *catalogue, int *cd_id);
int add_tracks (struct current_tracks_st *track);
/* Functions for finding and retrieving a CD */
int Find_cds (char *search_str, struct cd_search_st *results);
int get_cd (int cd_id, struct current_cd_st *dest);
int get_cd_tracks (int cd_id, struct current_tracks_st *dest);
/* Function for deleting items */
int delete_cd (int cd_id);
The search function is fairly generic: When a string is passed, then it searches for the string in the artist, title, or catalogue entry.
2. Test the Application interface
It may seem odd to make some code before implementing the interface, but it's often a good idea to know how it will work before you start implementing the interface.
Here is the source code for APP_TEST.C, first of all, some includes and structs:
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include "app_mysql.h"
int main () {
struct Current_cd_st cd;
struct Cd_search_st cd_res;
struct Current_tracks_st ct;
int cd_id;
int res, I;
/* The first thing in the application is always to initialize a database connection and provide a correct user name and password */
Database_start ("Rick", "secret");
/* Test Add a CD */
res = ADD_CD ("Mahler", "Symphony No 1", "4596102", &cd_id);
printf ("Result of adding a CD was%d, cd_id is%d\n", res, cd_id);
memset (&ct, 0, sizeof (CT));
ct.cd_id = cd_id;
strcpy (Ct.track[0], "Langsam schleppend");
strcpy (Ct.track[1], "Krafig bewag");
strcpy (ct.track[2], "Feierlich und Gemeseen");
strcpy (Ct.track[3], "Sturmish bewegt");
Add_tracks (&CT);
/* Now search for CDs and extract information from the first CD found */
res = Find_cds ("Symphony", &cd_res);
printf ("Found%d CDs, first has ID%d\n", Res, cd_res.cd_id[0]);
res = GET_CD (cd_res.cd_id[0], &CD);
printf ("Get_cd returned%d\n", res);
memset (&ct, 0, sizeof (CT));
res = Get_cd_tracks (cd_res.cd_id[0], &ct);
printf ("Get_cd_tracks returned%d\n", res);
printf ("Title:%s\n", cd.title);
i = 0;
while (I < res) {
printf ("\ttrack%d is%s\n", I, ct.track[i]);
i++;
}
/* Last Delete CD */
res = DELETE_CD (cd_res.cd_id[0]);
printf ("Delete_cd returned%s\n", res);
/* Disconnect and exit */
Database_end ();
return exit_success;
}
3. Implementing the interface
Implements the specified interface, which is contained in the file app_mysql.c
The first is some basic includes, the required global connection structure, and a flag dbconnected, which is used to ensure that the program does not attempt to access the data without establishing a connection.
Linux program Design-Execute SQL statement (Eighth chapter)
