Linux application integrated MySQL database access skills

This article describes MySQL, an effective tool for developing electronic commerce and other complex, dynamic Web sites using Third-party databases. MySQL is a fast, multi-threaded, and full-featured SQL Server. In addition to describing the basic architecture of the MySQL system, this article provides a simple example written in Tcl and C + + to help you develop Web applications that support databases. An application that must store or access a large amount of information can benefit from using Third-party database products. This is especially true when access to information must be done on multiple instances of the program. web-based applications, including electronic commerce, are good examples of this.

Why use a standalone database?

The Web server must have a way of processing scripts to store state information for later access. Although it is possible to use a more primitive approach-such as dumping to a text file or developing a homemade mini database-only mature database applications can provide all the services required for more complex web applications. Because there are some freeware packages available for that purpose, it's not much of a benefit to write customized, application-specific database engines. In addition, using a Third-party database also eliminates the need for web developers to invest in the task of developing and maintaining databases.

MySQL Database

By using scripting languages and compiled system languages (such as C), it can be fairly easy to integrate databases into Linux applications. Free MySQL (published under the GNU Public License) database provides a range of complex SQL features and is easy to integrate into applications. MySQL is fast, multi-threaded, and supports ANSI and ODBC SQL standards. With Third-party Software, MySQL supports the transaction security table for transaction processing applications.

Note: What is transaction processing?

A transaction is a series of changes made to a database that need to be executed atomically. They either have to be executed entirely or none of them are executed. For example, when you sell a product on the web, all of the required database changes make up a transaction. The database needs to subtract both the customer account balance and the product inventory, otherwise it fails and none of the operations are executed. It should not cause a transaction to be partially executed, regardless of the cause of the server crash. For example, the billing, the product is not delivered, or the inventory is not real and so may be the result of partially completed transactions. A database that supports transactions can encapsulate a set of database code in a single transaction, and any failure during transaction execution will cause the database to roll back to the state before the transaction started. This is done by maintaining a log of all database operations and a copy of its original state table, and allowing the rollback operation the next time the server restarts after a failure. This time and space overhead is a trade-off necessary for a transactional security database system. A single MySQL server controls a series of databases that can be accessed in a similar way through the server. Each database is actually a set of any number of tables, and the concept is similar to that of other SQL database users. Each table is made up of data columns with a type. The data can be an integer, a real value, a string, or other type, including the original binary stream. Each row in the table is a record that is stored in the database.

MySQL is designed and constructed as a client/server. Server mysqld can be run on any machine that can be accessed from the Internet (preferably on the same or closest computer as the Web server to ensure a reasonable response time). The MySQL client uses the request to contact the MySQL server, modifying or querying the database owned by the server. In a Web application that supports databases, the database client is a Web server or a CGI script generated by the Web server. These clients can be written in advanced scripting languages or low-level system languages, as long as there is a database API for that language. In Linux, most scripting languages are implemented in C, because there is a MySQL C API, so it should be easy to add MySQL support to any existing scripting language or tool. Most scripting languages have completed this step.

MySQL API

The MySQL API can be used in a variety of languages, including nearly all the languages that are actually used to write the site's backend. Using these APIs, we can build MySQL clients controlled by the Web server.

APIs (for database access) work in connection-based mode. The first thing the client must do is to open a connection to the MySQL server. This includes properly authenticating the connection with the user name and password that the server recognizes. After the connection is established, the server selects a specific database to use. After the initialization has been determined, the client application (as we are the server-side CGI script) is free to interact with the database in one of two ways: You can run regular SQL commands, including adding and deleting tables, and adding records to them, or you can run queries against the database that returns the results. The query generates a set of records that match the query, and then the client can access the records one at a-point until all the records are viewed, or the client cancels the pending record retrieval. Once the script completes the operation of the database, the connection to the server is closed.

To build a Web site that integrates database access, you need to write CGI scripts to generate dynamic results based on the state of the database. The Web server starts the CGI script and then outputs the appropriately formatted HTML to their standard output stream. The Web server captures the HTML and sends it back to the client, as if the request was for a static HTML page. In the process of generating HTML, scripts can modify the database or query and merge the results into their output.

As an example of a simple explanation for the above process, the following code (written in C and TCL) queries a database that contains a list of products that a company sells. This does not use all of the features of the MySQL API in both languages, but provides a quick, easy to extend example that can execute any SQL command on the contents of the database. In this example, the script shows all products that are below a specific price. In practice, a user might enter the price in a Web browser and then send it to the server. We omitted the details of reading from the environment variable to determine the HTML form value because it did not differ from the execution of the CGI script that does not support the database. For clarity, let's assume that certain parameters have been set beforehand (for example, the price to query).

The following code is implemented using TCL Generic Database interface for free. The advantage of such an interface is that TCL is an interpretive type that can be quickly developed and quickly modified for code.

Tcl Example

#This code prints out all products in the database

# that are below a specified, assumed to have been

# beforehand, and stored in the variable targetprice)

# The output is in HTML table format, appropriate for CGI output

#load the SQL shared object library. The TCL interpreter could also

#have been compiled with the library, making this line unnecessary

Load/home/aroetter/tcl-sql/sql.so

#these are defined beforehand, or they could

#be passed into the script

Set dbname "Clientwebsite";

Set tblname "Products";

Set Dbhost "Backend.company.com"

Set Dbuser "Mysqluser"

Set DBPASSWD "Abigsecret"

Set Targetprice 200;

#connect to the database

Set handle [SQL Connect $DBHOST $DBUSER $DBPASSWD]

SQL Selectdb $handle $DBNAME # Get test database

#run a query using the specified SQL code

SQL query $handle "SELECT * from $TBLNAME where price <= $targetPrice"

#print Out HTML table header

Puts "

Product Id	Description	Price ($) " #output Table Rows-each fetchrow retrieves one result #from the SQL query While {[Set row [SQL Fetchrow $handle]]!= '} { Set ProdID [lindex $row 0] Set Descrip [lindex $row 1] Set price [Lindex $row 2] Puts "
$prodid	$descrip	$price " } Puts "

"

#empty The query result buffer-should already is empty in the

SQL Endquery $handle

#close The DB connection-in practice this same connection

#is used for multiple queries

SQL Disconnect $handle

The following code is an equivalent script written in C + + using the official MySQL C + + API mysql++. The advantage of this version is that it is compiled, so it is faster than interpreting the language. Database code that is often used at a particular site should be written in C or C + + and then accessed by a script or directly by a Web server to improve the overall running time.

C + + Example

#include

#include

#include

const char *dbname = "Clientwebsite";

const char *dbtable = "Products";

const char *dbhost = "backend.company.com";

const char *dbuser = "Mysqluser";

const char *DBPASSWD = "Abigsecret":

int main () {

try {

Open the database connection and query

Connection con (dbname, Dbhost, Dbuser, DBPASSWD);

Query query = Con.query ();

Write valid SQL code to the query object

Query << "SELECT * from" << dbtable;

Run the query and store the results

Result res = Query.store ();

Write out the HTML table header

cout << "

Product Id	Description " << "	Price ($) "<< Endl; Result::iterator Curresult; Row row; Iterate over each result and put it into HTML table for (Curresult = Res.begin (); Curresult!= res.end (); curresult++) { row = *curresult; cout << "
"<< Row[0] << "	"<< Row[1] << "	"<< row[2] << Endl; } cout << "

"<< Endl;

catch (Badquery er) {

Handle a bad query (usually caused by a SQL syntax error)

Cerr << "Error:" << er.error << Endl;

return-1;

catch (Badconversion er) {

Handle conversion errors out of the database as

Cerr << "Error:can ' t convert" "<< er.data <<" "to a" "

<< er.type_name << "." << Endl;

return-1;

}

return 0;

}