1.1.c API
MySQL provides a client library written in a C programming language with the installation package to use it to write the client programs accessing the mysql. This library defines the application programming interface, including the following utility:
1. Establish and terminate the connection management routine with the server session.
2. Construct the routine of the query, send the routine to the server, and process the results.
3. When other C API calls fail, determine the status and error report functions of the error-accurate reason. Really found in the programming test, the 4.1.1A-Alpha version of the 4.1.1a-alpha version of the dynamic library does not perform dynamic statement processing, download the version of the Dynamic library of the mysql5.0 to override 4.1 dynamic library, and the test code is running successfully. See Appendix 1 for the test code.
1.1.1. How to perform a data definition statement
You can use the mysql_query or mysql_real_query function.
E.g:
Mysql_Query (MySQL, "Drop Table if EXISTS TEST_TABLE);
MySQL_Query (MySQL, "CREATE TABLE TEST_TABLE (Col1 INT, /
COL2 VARCHAR (40), /
COL3 Smallint, /
COL4 TIMESTAMP ";
Where mysql is a MySQL type pointer. Example program See Appendix 1.
1.1.2. How to perform data operation statement
You can use the mysql_prepare, mySQL_PARAM_COUNT function.
E.g:
CHAR INSERT_SAMPLE [] = "INSERT INTO TEST_TABLE (COL1, COL2, COL3) VALUES (?,?,?)
Mysql_stmt * stmt = mysql_prepare (mysql, insert_sample, strlen);
/ * Get the parameter count from the statement * /
PARAM_COUNT = mysql_param_count (stmt);
Example program See Appendix 1.
1.1.3. How to perform database dynamic operation
You can use the following functions.
C API Prepared Statement Function Descriptions.
FUNCTION
Description
MySQL_PREPARE ()
Prepares an SQL STRING for Execution.
MySQL_PARAM_COUNT ()
Returns The Number of Parameters in a prepared SQL Statement.
MySQL_GET_METADATA ()
Returns Prepared Statement Metadata in The Form of a Result Set.
MySQL_BIND_PARAM ()
Associates Application Data Buffers with The Parameter Markers in a Prepared SQL Statement.
MySQL_EXECUTE ()
Executes the prepared state.
MySQL_STMT_AFFECTED_ROWS ()
Returns the Number of Rows Changes, Deleted, or Insert by The Last Update, Delete, or Insert Query.
MySQL_BIND_RESULT ()
Associates Application Data Buffers with columns in the result set.
Mysql_stmt_store_result () Retrieves The Complete Result Set To The Client.
MySQL_STMT_DATA_SEEK ()
Seeks to an Arbitrary Row Number in a statement result.
mysql_stmt_row_seek ()
Seeks to a row offset in a statement result set, using value returned from mysql_stmt_row_tell ().
MySQL_STMT_ROW_TELL ()
Returns The Statement Row Cursor Position.
MySQL_STMT_NUM_ROWS ()
Returns Total Rows from The Statement Buffered Result Set.
MySQL_FETCH ()
Fetches the next row of data from the result set and return data for all bound columns.
MySQL_STMT_Close ()
FREES MEMORY Used by Prepared Statement.
MySQL_STMT_ERRNO ()
Returns the error Number for the last statement execution.
MySQL_STMT_ERROR ()
Returns The Error Message for the Last Statement Execution.
MySQL_STMT_SQLSTATE ()
Returns The SqlState Error Code for the last statement execution.
MySQL_SEND_LONG_DATA ()
Sends long data in chunks to server.
Example program See Appendix 1.
1.1.4. Performance Test
Test Environment P4 1.8G / 512M Lenovo Computer, Windowns2000 System, VC6. Using the MySQL C API using pre-processed mysq_prepare () to perform SQL statement operation, the analog network management AGT produces a MOINFO information file process. The MOINFO table is defined as follows:
Create Table Moinfo
InstanceId Int (10) unsigned not null default '0',
ParentInstanceId Int (10) Unsigned Not Null Default '0',
DN Tinyblob Not Null,
MOCID INT (10) Unsigned Not Null Default '0',
Flag Tinyblob Not Null,
FID Tinyblob Not Null,
AdminState Tinyint (3) Unsigned Default '1',
OpState Tinyint (3) Unsigned Default '0',
USAGESTATE TINYINT (3) Unsigned Default '0',
AlarmState Tinyint (3) Unsigned Default '0',
UNKNOWNSTATE TINYINT (3) Unsigned default '0',
Attrdatlen Int (10) unsigned default '0',
Attrdat Blob,
Modidate TimeStamp Not Null,
Primary Key (InstanceID),
Key I_MoInfo (ParentInstanceId) Engine = InnoDB default charSet = latin1;
Delete statement:
Delete from Moinfo
Insert statement:
INSERT INTO MOINFO (InstanceID, ParentInstanceID, /
DN, MOCID, FLAG, FID, ATTRDATLEN, ATTRDAT VALUES (?,?,?,?,?,?,?,?)
Query statement 1 (including writing the result 42280254 bytes):
Select InstanceID, ParentInstanceID, DN, MOCID, FLAG, FID, /
Attrdatlen, Length (Attrdat) as Attrdatlen2, AttrDat /
From moinfo where parentinstanceid =? ")
Query Statement 2:
Select Count (1) from Moinfo;
For 10,000 records (4228 bytes per record), the processing time (unit seconds) of mysql is as follows:
Test serial number
Delete statement
Insert statement
Query statement 1
Query statement 2
1
117 "
476 "
37 "
6.80 "
2
114 "
479 "
30 "
6.69 "3
114 "
475 "
29 "
6.70 "4
117 "
476 "
30 "
6.72 "5
113 "
474 "
30 "
6.68 "
1.2.c API
MySQL's C open package is a MySQL interface tool based on standard template function library STL. The current version is 1.7.9, because we can use the VC only version 1.7.1 can be used. Since the C API is programmed to be more convenient than the C API because the STL container is used.
1.2.1. How to perform a data definition statement
All operations can be easily performed using the Query class. E.g:
Query Query = connection.Query (); // Create a new query Object
Try {// ignore Any Errors Here
// i hope to make this Simpler Soon
Query.execute ("DROP TABLE Stock");
} catch (badquery ER) {}
Example program See Appendix II.
1.2.2. How to execute data operation statements
Insert three records for the specified STOCK table, for example:
Query << "INSERT INTO% 5: Table VALUES (% 0Q,% 1Q,% 2,% 3,% 4Q)";
Query.Parse ();
// set up the template query i will use to insert the data. The THE
// Parse Method Call Is Important As It Is What Letrs The Query
// KNOW That this is a template and not a literal string
Query.def ["Table"] = "stock";
// this is setting the parameter named Table to stock.
Query.execute ("Hamburger Buns", 56, 1.25, 1.1, "1998-04-26");
Query.execute ("HotDogs' Buns", 65, 1.1, 1.1, "1998-04-23");
Query.execute ("DINNER ROLES", 75, .95, .97, "1998-05-25");
Query.execute ("Allen Lee", 87, 1.5, 1.75, "1998-09-04");
Example program See Appendix II.
1.2.3. How to perform database dynamic operation
Processing the SQL statement that changes the output and input parameters, for example:
Query query = con.query ();
Query << "Select * from stock";
Result res = query.store ();
Cout << "Query:" << query.preview () << endl;
Cout << "Records Found:" << res.size () << Endl << endl;
COUT << "Query Info: / N";
For (unsigned int i = 0; i Cout << SETW (2) << i << SETW (15) << res.names (i) .c_str () // this is the name of the field << SETW (15) << res. Types (i). SQL_NAME () // this is the SQL Identifier Name // Result :: Types (unsigned int) Returns a mysql_type_info Which in Many // Ways is like type_info except That It has additional SQL TYPE // Information in it. (with one of the methods being sql_name ()) << SETW (20) << Res.Types (i) .Name () // this is The C Identifier Name Which Most Closely Rembly // the SQL Name (ITS Is Implementation Defined And Offe Very Readable) << ENDL; } See Appendix III for the sample program. 2. Other instructions Mysql Offer similar SQLPlus Client login tool mysql , Can be convenient SQLPlus same spool The result of the query is placed in a text file. It also provides data export and import tools that can easily export data text files and import text files. Of course there are more online Mysql Database Total Management Tools.
