I. MySQL database usage in Unix/Linux C

I. MySQL database usage in Unix/Linux C

Linux MySQL database development

Joint development of MySQL and C

Development Library Installation

MySQL C API

Programming example

1. Software Package requirements

1) MySQL Server

Mysql-server-3.23.54a-11

Mysql-server-3.23.58-1.9

2) MYSQL client

Mysql-3.23.54a-11

Mysql-3.23.58-1.9

3) MySQL development interface library

Mysql-devel-3.23.54a-11

Mysql-devel-3.23.58-1.9

2. MySQL service configuration

L The mysqyld service must run. It is best to set automatic start upon startup. Method:

L chkconfig-level 2345 mysqld on

L ntsysv

L service mysqld start/stop/restart

L graphical mode

3. msqyl client command line tool

The command line tool of the mysqyl client is MySQL. The common method is:

MySQL [Options] [database] <script. SQL> output. Tab

Common parameters include:

-?, -- Help: Help

-D, -- database = db_name: Specifies the database

-H, -- Host = host_name: Specifies the host

-U, -- user = db_user_name: Specifies the user

-P […], -- Password [=…] : Password

4. Common Client commands

Enter MySQL or MySQL-u root at the command prompt to enter the MySQL system. The prompt is ">". You can use the following commands:

Show databases/tables;

Use Database;

Create Database dB;

Create Table TBL;

Drop database dB/table TBL;

Select... From TBL... Where...

Insert... Into TBL... Values...

Update... TBL... Set... Where...

Delete... From TBL... Where...

4. MySQL C development interface

Different Versions of MySQL have different directory settings.

1) header file

Directory:/usr/include/MySQL

Use: # include <MySQL/MySQL. h>

NOTE: If necessary, you can use the-I parameter.

2) database files

Static Library:/usr/lib/MySQL/libmysqlclient.

Dynamic library:/usr/lib/MySQL/libmysqlclient. So

Usage parameters:-L/usr/lib/MySQL-lmysqlclient

3) MySQL C development example Program

// Header file

# Include <stdio. h>

# Include <MySQL/MySQL. h>

Main ()

{

// Variable Declaration

MySQL;

Mysql_res * result;

Mysql_row row;

 

// Initialize the Data Structure

Mysql_init (& MySQL );

// Connect to the database

Mysql_real_connect (& MySQL, "localhost", "root", 0, "mydb", 0, null, 0 );

// Execute the query statement mysql_query

Mysql_query (& MySQL, "select * From person ");

 

// Save the result

Result = mysql_store_result (& MySQL );

// Process the result set

While (ROW = mysql_fetch_row (result ))){

Fprintf (stdout, "% S | \ t % s \ n", row [0], row [2]);

}

Mysql_free_result (result); // clear data

Mysql_close (& MySQL); // close the connection

}

4) Compilation and link Methods

The-L and-l parameters must be used to compile the link. For example:

CC-O MySQL. C-L/usr/lib/MySQL-lmysqlclient

5) Development Interface Description

MySQL provides a variety of development interfaces: PHP, ODBC, Perl, C/C ++, Java

C Common Development interfaces include:

Mysql_init, mysql_real_connect, mysql_query, mysql_store_result, mysql_fetch_row, mysql_free_result, mysql_close, mysql_init

(1) mysql_init

Purpose: Initialize the MySQL variable to prepare for mysql_real_connect.

Usage: MySQL * mysql_init (MySQL * MySQL)

Return Value: MySQL handle or descriptor; if the memory is insufficient, it is null;

(2) mysql_real_connect

Function: Link to the MySQL database;

Usage: MySQL * mysql_real_connect (MySQL * MySQL ,\

Const char * Host, const char * user ,\

Const char * passwd, const char * dB ,\

Unsigned int port, const char * unix_socket ,\

Unsigned int client_flag)

NOTE: If port! = 0, it will be used as the TCP/IP Port, the default is 0; If unix_socket! If it is set to null, you can specify a socket or pipe. The default value is null. client_flag can specify a specific value (omitted). The default value is 0.

(3) mysql_query

Function: Query implementation

Usage:

Int mysql_query (MySQL * MySQL, const char * query)

Note: query is a database operation command string. Its syntax is query (select). It can include select, update, insert, delete, and other database operation commands. Mysql_query cannot be used to process binary data. You can use mysql_real_query to process binary data.

Returned value: 0 indicates normal, and non-0 indicates an error.

(4) mysql_store_resul

Function: process the result set. If mysql_query is used to run a SELECT statement or other queries that can return results, use the mysql_store_result function to access the returned results and save them in a variable for further processing.

Usage:

Mysql_res * mysql_store_result (MySQL * MySQL)

Mysql_res * mysql_use_result (MySQL * MySQL)

Description: MySQL is the return value of the mysql_real_connect function.

(5) use of result sets

After using the mysql_store_result function to save the result, you can use the following function to process the result set.

① Number of rows in the result set:

My_ulonglong mysql_num_rows (mysql_res * result)

② Number of fields in the row of the result set:

Unsigned int mysql_num_fields (mysql_res * result)

Unsigned int mysql_num_fields (MySQL * MySQL)

③ One row in the read result set:

Mysql_row mysql_fetch_row (mysql_res * result)

④ Number of fields in the row in the result set:

Unsigned int mysql_field_count (MySQL * MySQL)

⑤ Attributes of the fields in the result set:

Mysql_field * mysql_fetch_field (mysql_res * result)

6. Obtain the domain attribute array:

Mysql_field * mysql_fetch_fields (mysql_res * result)

7. query the affected rows such as update, delete, and insert:

My_ulonglong mysql_affected_rows (MySQL * MySQL)

 

(6) Aftercare

After the database is used, the created variables should be released:

Mysql_free_result (result );

Mysql_close (& MySQL );

(7) handle errors

Method 2: Use the return value of the function to determine whether the function execution is correct; 2. Use the error code and error message provided by MySQL:

Error code: Unsigned int mysql_errno (MySQL * MySQL)

Error message: char * mysql_error (MySQL * MySQL)

(8) Auxiliary Functions

Get Client Version Information: char * mysql_get_client_info (void)

Obtain host information: char * mysql_get_host_info (MySQL * MySQL)

Obtain the Protocol Version: Unsigned int mysql_get_proto_info (MySQL * MySQL)

Get server version information: char * mysql_get_server_info (MySQL * MySQL)

Obtain the list of available databases: mysql_res * mysql_list_dbs (MySQL * MySQL, const char * wild)

Obtain the list of tables available for the database: mysql_res * mysql_list_tables (MySQL * MySQL, const char * wild)

Ii. UNIX socket network programming 1. Waiting Process

Daemon is a long-lived process. They are often started during system boot loading and terminated when the system is shut down. Because they do not have control terminals, they are running in the background.

2. Client/Server (C/S) Model

Generally, a server is a process that waits for the client to contact it and puts forward some type of service requirements.

The C/S mode takes the active request mode during the operation.

The service can return the results or information to the client (two-way) or not (one-way ).

In terms of Bidirectional C/S, there are two types: repetitive and parallel hairstyles.

In network programming, TCP servers are generally concurrent, while UDP servers are repetitive.

1) duplicate

To interact with a duplicate server, follow these steps:

(1) Open a channel and inform the local host that it is willing to receive customer requests on a recognized address port.

(2) Wait for a customer request to arrive;

(3) handling customer requests;

(4) Send the service result data to the requesting customer;

(5) return step (2)

Major duplicate server problems occur in step (3. During this period, it cannot receive requests from other clients and therefore cannot provide services to other clients.

2) Concurrent hair style

Follow these steps to interact with the server:

(1) Open a channel and inform the local host that it is willing to receive customer requests on a recognized address port.

(2) Wait for a customer request to arrive;

(3) Start a new server to process the customer's requests.

The method for starting a new server may be different. A new process or thread may be generated, depending on the operating system or development system. The newly generated server processes all client requests. After processing, terminate the new server.

(4) return step (2)

The advantage of a concurrent server is that it uses other server generation methods to process customer requests. It can concurrently serve multiple clients.

3) Customer

(1) Open a channel and connect to the specific port of the host where the server is located.

(2) Send a service request to the server and wait for and receive a response;

(3) Close the communication channel after the request ends.

3. Transactional Middleware

Before the emergence of middleware, the traditional layer-2 C/S structure was adopted. This layer-2 structure is a major improvement in the development of computer and software technologies.

Although it brings considerable flexibility, it gradually exposes the phenomenon that the client and server are overloaded, and the scalability is also poor, and it cannot span different business departments and business systems.

This is not only technically difficult, but more importantly, in terms of system and security. As a result, the layer-3 structure emerged.

1) layer-3 Structure

A layer-3 structure adds an intermediate layer between the original two-layer structure. This intermediate layer includes both services and requests.

Services include the transaction processing application service and database query proxy. For more real-time business systems, this layer is mainly used for communication forwarding, protocol conversion, and security control;

Requests are mainly manifested in requests sent to the other party or the target party in C/S mode based on actual business needs when they need to span different business systems and business departments, when the service information is returned, the response information of the customer's request is returned to the customer. This is the real purpose of the middleware.

After the two-layer structure changes to the three-layer structure, middleware can share the workload of some clients and servers, so the load on the clients and servers is reduced accordingly. The most important thing is that the introduction of middleware enables cross-platform and cross-Department services and transmission, solving problems such as unreliable information transmission and security control between departments and enterprises.

2) middleware Working Mechanism

Middleware is essentially an abstraction of distributed applications. Aside from application-related business details, it retains the key features of typical distributed interaction modes, after refining and necessary isolation, complicated distributed systems are presented to applications in a unified form.

Middleware plays the role of data bus in the entire Distributed System and organically combines various heterogeneous systems into a whole through middleware.

Its working mechanism is: when an application on the client needs to obtain certain data or services from a node in the network, these data and services may be on a platform that runs a different hardware, software, network, or operating system from the client. The part in the c/s application that is responsible for data retrieval only needs to access a middleware system. The middleware completes the process of finding data sources or services in the network, so as to transmit customer requests and reorganize response information, finally, return the result to the application task.

3) middleware model 4) Types of Middleware

(1) message-oriented middleware: provides reliable message transmission between different network protocols, different operating systems, and different applications.

(2) transaction middleware: It is responsible for correctly transferring transactions, managing transaction integrity, dispatching application programs, and ensuring the efficiency of the entire system.

(3) object-oriented middleware: similar to soft bus, it meets the needs of key tasks such as object-oriented, data consistency, and application integration.

(4) Security Platform: an open application development platform based on public key infrastructure (PKI) and a series of related international security standards. It provides development interfaces for application systems, unified cryptographic algorithm interfaces, and driver interfaces for various IC cards, security chips, and other devices.

(5) database middleware: ODBC.

4. Socket Introduction

The socket library is the most popular API on UNIX networks. You can call each program provided by the socket library to develop network software and network systems.

When the TCP/IP protocol is integrated into the Unix kernel, it is equivalent to introducing a new type of I/0 operation in the Unix system. The interaction between Unix user processes and network protocols is much more complex than that between user processes and traditional I/O devices.

In Unix systems, there are two types of network application programming interfaces: Socket of unix bsd and TLI of UNIX System V. Sun adopts a Unix BSD operating system that supports TCP/IP, which enables the development of TCP/IP applications. Its Network Application Programming Interface (socket) it is widely used in network software.

5. Socket Type

Stream socket (sock-stream): it provides a connection-oriented and reliable data transmission service, which can send data in the same order without errors or duplicates. Internal traffic control to avoid data flow exceeding the limit. Data is considered as a byte stream with no length limit. The file transfer protocol (FTP) uses a streaming socket.

Datagram socket (SOCK-DGRAM): provides a connectionless service. Data packets are sent in the form of independent packets. No error guarantee is provided. data may be lost or duplicated, and the receiving order is disordered.

Sock-raw: this interface allows direct access to lower-layer protocols, such as IP and ICMP. It is often used to verify new protocol implementations or access new devices configured in existing services.

6. Basic Socket System Call

Create socket-socket ()

Address binding-bind ()

Establish a connection-Connect () and accept ()

Listener connection-Listen ()

Data transmission-send ()/write () and Recv ()/read ()

Close socket-close ()

1) create a socket-socket ()

Before using a socket, an application must have a socket. The system calls socket () to create a socket for the application. The call format is as follows:

# Include <sys/socket. h>

Int socket (INT domain, int type, int Protocol );

This call needs to receive three parameters, create a socket based on the three parameters, allocate the corresponding resources to it, and return an integer socket number, only associated with a specific protocol. In fact, the "protocol" dollar in the related quintuple is specified.

Domain (protocol family): af_inet, af_unix, af_ipx ,...

Type (type): sock_stream, sock_dgram

Protocol: 0-enables the system to use the default protocol of the specified type and protocol family.

2) bind the local address-bind ()

After a socket is created with socket (), a namespace (address family) exists, but it is not named, BIND () bind the socket address (including the local host address and local port address) with the created socket number to assign the socket name to specify the local half-correlation. The call format is as follows:

# Include <sys/socket. h>

Int BIND (INT sockfd, const struct sockaddr * my_addr, int addrlen );

Note:

Sockfd: Return Value-socket descriptor when the socket is successful.

My_addr is the data structure of the local address.

Addrlen = sizeof (my_addr ).

3) Listener connection-Listen ()

This call is intended for connection servers, indicating that it is willing to receive connections. 1 isten () must be called before accept (). The call format is as follows:

# Include <sys/socket. h>

Int listen (int s, int backlog );

During the execution of the call, listen () can complete the required connection for the socket s without calling BIND () and establish a request connection queue with the length of backlog.

Backlog defines the maximum length of socket waiting queue. The default value is 5.

4) connect (), accept ()

These two system calls are used to complete a complete related establishment, and connect () is used by the customer to establish a connection. Accept () is used to make the server wait for the actual connection from a customer process.

(1) connect

Function: the client calls a request.

Usage:

Int connect (INT sockfd, const structct sockaddr * server_addr, socklen_t addrlen );

In the connection-oriented protocol, this call causes the actual connection between the local system and the external system.

(2) accept

Function: Server call. The client connection request is received from the socket numbered S.

Usage:

Int accept (int s, struct sockaddr * ADDR, socklen_taddrlen );

(3) Description of connect and accept

After accept () is called, the server waits for the client connection request to be accepted from the socket number S. The connection request is sent by the client's connect () call.

When a connection request arrives, the accept () call puts the first client socket address and length in the request connection queue into ADDR and addrlen, create a new socket number with the same features as S.

The new socket can be used to process concurrent requests from the server.

5) data transmission: Send ()/write and Recv ()/read ()

After a connection is established, data can be transmitted. Common system calls include send () and Recv (). The send () call is used to send output data on the connected datagram or stream socket specified by parameter S. The format is as follows:

Int send (int s, void * Buf, int Len, int flags );

Int Recv (int s, void * Buf, int Len, int flags );

Recv/send can be replaced by the file system calling read/write.

When processing binary data, the latter is better than the former.

6) Close socket: Close ()

Close () closes socket S and releases the resources allocated to the socket. If s involves an open TCP connection, the connection is released.

The call format of close () is as follows:

Int close (int s );

7. byte order

Network byte sequence: TCP/IP uses large-end bytes to transmit protocol information.

Big-Endian stores high-end bytes in the low address, while little-Endian stores high-end bytes in the high address space.

You can use the following functions to convert the machine's byte sequence to the network's byte sequence:

Unsignd int htonl (unsigned int hostlong)

Unsignd short htons (unsigned int hostshort)

Unsignd int nhtonl (unsigned int netlong)

Unsignd int nhtons (unsigned int netshort)

The first two are positive, and the latter two are reverse. In this case, the "long integer" refers to 32 bits, rather than the long integer in C language.

8. Address Translation

Applications often need to convert the IP address in the dotted-decimal format to the IP address in the binary format. The following functions can achieve this conversion:

# Include <netinet/in. h>

# Include <socket. h>

# Include <ARPA/inet. h>

Int inet_aton (const char * ddaddress, struct in_addr * adress)

Char * inet_ntoa (struct in_addr address );

Unsigned long int inet_addr (const char * ddaddress );

9. host information and related data structures and functions

Struct hostent {

Char * h_name; // official name of host

Char ** h_aliases; // alias list.

Int h_addrtype; // hostaddress type

Int h_length; // length of address

Char ** h_addr_list; // list of addrsfrom NS

};

See netdb. h

Host information and related data structures and functions

# Include <netdb. h>

Struct hostent * gethostbyname (const char * name)

Name: Host Name. It can be a domain name, or it can be defined in/etc/hosts.

Struct hostent * gethostbyadress (const char * ADDR, intlen, int type)

ADDR: A structure pointing to in_addr (see netinet/in. H );

Len indicates the length of ADDR;

Type: For scok_stream, the type is af_inet.

10. Server-related data structures and functions

Struct servent {

Char * s_name; // officialservice name.

Char ** s_aliases; // alias list.

Int s_port; // port number.

Char * s_proto; // protocol touse.

};

See netdb. h

11. Server-related data structures and functions

# Include <netdb. h>

Struct servent * getservbyname (const char * Name, const char * Protocal );

Both name and protocal are defined in/etc/services:

Name: Service name;

Protocal: Protocol

Struct servent * getservbyport (INT port, const char * Protocal );

Use a number directly as the port number. We recommend that you do not do this because it is inconvenient to configure.

IP socket address

# Include <sys/socket. h>

# Include <netinet/in. h>

Struct sockaddr_in {

Short int sin_family; // af_inet

Unsigned short int sin_port; // port no.

Struct in_addr sin_addr; // ip ADDR.

}

12. Typical SOCKET call process 13. Socket development example

Currently, almost all systems that support network development support socket development. For example, VB Winsock Control, Windows Socket library, ms mfc casynsocket and csocket class,. Net System. net. sockets, Java, Delphi, Perl and so on have the corresponding class.

Different development environments can implement specific functions to achieve communication and networking.

Here is the example program of the UNIX socket development framework. They are the client, server, and middleware.

Room program: For the source program, see C. C.

Server-side program: For the source program, see the file S. C.

Iii. Middleware Programming

According to the definition and description of the middleware. Middleware programming is not difficult. It is both a server and a client. As a server, it must provide services for its clients. as a client, when the request submitted by the client exceeds its service capabilities, it has to make a request to its own server for its own client. Of course, there may be more than one server based on the customer's request for service. The number of servers to which a request is sent depends on the client's request. For example, a bank's middleware needs to send requests to China Mobile or China Unicom when it wants to act as a proxy for the business of a mobile company, send requests to China Netcom, China Telecom, or China tietong.

When designing a program, it requires a service to receive client requests. For its own service provider, the number of service providers must be defined.

I designed a middleware program with only one service provider as the model. For the source program, see the file M. C.

IV. Implementation

We simulate the bank's proxy mobile service to describe the specific implementation of the intermediary service.

1. Mobile Company (final server)

Functions: billing, query, payment, and statistics.

1) mobile phone traffic database

Database Name: cellphone

Master information table: Main

Basic info table: User

Traffic info table: Payment

Transaction flow meter: Journal

2) Table Structure

(1) Main

 

Use cell;

Create Table Main

(

Unit_id char (13) not null,

Unitname char (40) not null,

Unitaddr char (40) default '',

Curr_jnl bigint default 1

);

Create Table user

(

User_no char (13) not null primary key,

Username char (40) not null,

User_id char (18) not null,

Userlevel char (2) default '01 ',

Password char (30 ),

Usertime datetime,

Preserve char (20)

);

Create Table payment

(

User_no char (13) not null primary key,

Curr_0000int,

Month_fee int,

Cut_off int,

Transtime datetime,

Preserve char (20) default''

);

Create Table Journal

(

Journal char (20) not null primary key,

Trans_type char (2 ),

Trans_mount int,

Trans_all varchar (250)

);

2. Middleware party

The middleware's function is simplified, so that the middleware only records the stream information of the fermented transactions. The stream information adopts the text method. Of course, it is even more important to use a database, which is easier to implement when performing statistics and other service operations.

Function: Recording and statistics

2. Customer

The customer is the initiator of the transaction. The implementation function is the same as that of the server.

Database Name: Bank

Master information table: Main

Transaction info table: Trans

Transaction flow meter: Journal