原文地址: http://www.cs.ait.ac.th/laboratory/database/manual/manual_Clients.shtml
8 MySQL APIs
This chapter describes the APIs available for MySQL, where to get them, and how to use them. The C API is the most extensively covered, as it was developed by the MySQL team, and is the basis for most of the other APIs.
8.1 MySQL PHP API
PHP is a server-side, HTML-embedded scripting language that may be used to create dynamic Web pages. It contains support for accessing several databases, including MySQL. PHP may be run as a separate program or compiled as a module for use with the Apache Web server.
The distribution and documentation are available at the PHP web site.
8.1.1 Common Problems with MySQL and PHP
- Error: "Maximum Execution Time Exceeded" This is a PHP limit; Go into the `php3.ini' file and set the maximum execution time up from 30 seconds to something higher, as needed. It is also not a bad idea to double the ram allowed per script to 16MB instead of 8 MB.
- Error: "Fatal error: Call to unsupported or undefined function mysql_connect() in .." This means that your PHP version isn't compiled with MySQL support. You can either compile a dynamic MySQL module and load it into PHP or recompile PHP with built-in MySQL support. This is described in detail in the PHP manual.
- Error: "undefined reference to `uncompress'" This means that the client library is compiled with support for a compressed client/server protocol. The fix is to add
-lz
last when linking with-lmysqlclient
.
8.2 MySQL Perl API
This section documents the Perl DBI
interface. The former interface was called mysqlperl
. DBI
/DBD
now is the recommended Perl interface, so mysqlperl
is obsolete and is not documented here.
8.2.1 DBI
with DBD::mysql
DBI
is a generic interface for many databases. That means that you can write a script that works with many different database engines without change. You need a DataBase Driver (DBD) defined for each database type. For MySQL, this driver is called DBD::mysql
.
For more information on the Perl5 DBI, please visit the DBI
Web page and read the documentation:
http://www.symbolstone.org/technology/perl/DBI/index.html
For more information on Object Oriented Programming (OOP) as defined in Perl5, see the Perl OOP page:
http://language.perl.com/info/documentation.html
Note that if you want to use transactions with Perl, you need to have Msql-Mysql-modules
version 1.2216 or newer.
Installation instructions for MySQL Perl support are given in section M.2 Perl Installation Comments.
8.2.2 The DBI
Interface
Portable DBI Methods
connect | Establishes a connection to a database server. |
disconnect | Disconnects from the database server. |
prepare | Prepares a SQL statement for execution. |
execute | Executes prepared statements. |
do | Prepares and executes a SQL statement. |
quote | Quotes string or BLOB values to be inserted. |
fetchrow_array | Fetches the next row as an array of fields. |
fetchrow_arrayref | Fetches next row as a reference array of fields. |
fetchrow_hashref | Fetches next row as a reference to a hashtable. |
fetchall_arrayref | Fetches all data as an array of arrays. |
finish | Finishes a statement and lets the system free resources. |
rows | Returns the number of rows affected. |
data_sources | Returns an array of databases available on localhost. |
ChopBlanks | Controls whether fetchrow_* methods trim spaces. |
NUM_OF_PARAMS | The number of placeholders in the prepared statement. |
NULLABLE | Which columns can be NULL . |
trace | Perform tracing for debugging. |
MySQL-specific Methods
insertid | The latest AUTO_INCREMENT value. |
is_blob | Which columns are BLOB values. |
is_key | Which columns are keys. |
is_num | Which columns are numeric. |
is_pri_key | Which columns are primary keys. |
is_not_null | Which columns CANNOT be NULL . See NULLABLE . |
length | Maximum possible column sizes. |
max_length | Maximum column sizes actually present in result. |
NAME | Column names. |
NUM_OF_FIELDS | Number of fields returned. |
table | Table names in returned set. |
type | All column types. |
The Perl methods are described in more detail in the following sections. Variables used for method return values have these meanings:
- Database handle
- Statement handle
- Return code (often a status)
- Return value (often a row count)
$dbh
$sth
$rc
$rv
Portable DBI Methods
-
Use the
connect
method to make a database connection to the data source. The$data_source
value should begin withDBI:driver_name:
. Example uses ofconnect
with theDBD::mysql
driver:$dbh = DBI->connect("DBI:mysql:$database", $user, $password); $dbh = DBI->connect("DBI:mysql:$database:$hostname", $user, $password); $dbh = DBI->connect("DBI:mysql:$database:$hostname:$port", $user, $password);
If the user name and/or password are undefined,DBI
uses the values of theDBI_USER
andDBI_PASS
environment variables, respectively. If you don't specify a hostname, it defaults to'localhost'
. If you don't specify a port number, it defaults to the default MySQL port (3306 ). As ofMsql-Mysql-modules
Version 1.2009, the$data_source
value allows certain modifiers:- Read `filename' as an option file. For information on option files, see section 4.1.2 my.cnf Option Files.
-
The default group when reading an option file is normally the
[client]
group. By specifying themysql_read_default_group
option, the default group becomes the[group_name]
group. - Use compressed communication between the client and server (MySQL Version 3.22.3 or later).
- Specify the pathname of the Unix socket that is used to connect to the server (MySQL Version 3.21.15 or later).
mysql_read_default_file=file_name
mysql_read_default_group=group_name
mysql_compression=1
mysql_socket=/path/to/socket
connect($data_source, $username, $password)
Multiple modifiers may be given; each must be preceded by a semicolon. For example, if you want to avoid hardcoding the user name and password into a DBI
script, you can take them from the user's `~/.my.cnf' option file instead by writing your connect
call like this:
$dbh = DBI->connect("DBI:mysql:$database" . ";mysql_read_default_file=$ENV{HOME}/.my.cnf", $user, $password);
This call will read options defined for the [client]
group in the option file. If you wanted to do the same thing but use options specified for the [perl]
group as well, you could use this:
$dbh = DBI->connect("DBI:mysql:$database" . ";mysql_read_default_file=$ENV{HOME}/.my.cnf" . ";mysql_read_default_group=perl", $user, $password);
disconnect
disconnect
method disconnects the database handle from the database. This is typically called right before you exit from the program. Example:
$rc = $dbh->disconnect;
prepare($statement)
($sth)
, which you can use to invoke the
execute
method. Typically you handle
SELECT
statements (and
SELECT
-like statements such as
SHOW
,
DESCRIBE
, and
EXPLAIN
) by means of
prepare
and
execute
. Example:
$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
execute
execute
method executes a prepared statement. For non-
SELECT
statements,
execute
returns the number of rows affected. If no rows are affected,
execute
returns
"0E0"
, which Perl treats as zero but regards as true. If an error occurs,
execute
returns
undef
. For
SELECT
statements,
execute
only starts the SQL query in the database; you need to use one of the
fetch_*
methods described below to retrieve the data. Example:
$rv = $sth->execute or die "can't execute the query: $sth->errstr;
do($statement)
do
method prepares and executes a SQL statement and returns the number of rows affected. If no rows are affected,
do
returns
"0E0"
, which Perl treats as zero but regards as true. This method is generally used for non-
SELECT
statements that cannot be prepared in advance (due to driver limitations) or that do not need to be executed more than once (inserts, deletes, etc.). Example:
$rv = $dbh->do($statement) or die "Can't execute $statement: $dbh- >errstr\n";Generally the 'do' statement is MUCH faster (and is preferable) than prepare/execute for statements that don't contain parameters.
quote($string)
quote
method is used to "escape" any special characters contained in the string and to add the required outer quotation marks. Example:
$sql = $dbh->quote($string)
fetchrow_array
while(@row = $sth->fetchrow_array) { print qw($row[0]\t$row[1]\t$row[2]\n); }
fetchrow_arrayref
while($row_ref = $sth->fetchrow_arrayref) { print qw($row_ref->[0]\t$row_ref->[1]\t$row_ref->[2]\n); }
fetchrow_hashref
while($hash_ref = $sth->fetchrow_hashref) { print qw($hash_ref->{firstname}\t$hash_ref->{lastname}\t\ $hash_ref- > title}\n); }
fetchall_arrayref
my $table = $sth->fetchall_arrayref or die "$sth->errstr\n"; my($i, $j); for $i ( 0 .. $#{$table} ) { for $j ( 0 .. $#{$table->[$i]} ) { print "$table->[$i][$j]\t"; } print "\n"; }
finish
$rc = $sth->finish;
rows
SELECT
execute
statement. Example:
$rv = $sth->rows;
NULLABLE
NULL
values. Example:
$null_possible = $sth->{NULLABLE};
NUM_OF_FIELDS
SELECT
or
SHOW FIELDS
statement. You may use this for checking whether a statement returned a result: A zero value indicates a non-
SELECT
statement like
INSERT
,
DELETE
, or
UPDATE
. Example:
$nr_of_fields = $sth->{NUM_OF_FIELDS};
data_sources($driver_name)
'localhost'
. Example:
@dbs = DBI->data_sources("mysql");
ChopBlanks
fetchrow_*
methods will chop leading and trailing blanks from the returned values. Example:
$sth->{'ChopBlanks'} =1;
trace($trace_level)
trace($trace_level, $trace_filename)
trace
method enables or disables tracing. When invoked as a
DBI
class method, it affects tracing for all handles. When invoked as a database or statement handle method, it affects tracing for the given handle (and any future children of the handle). Setting
$trace_level
to 2 provides detailed trace information. Setting
$trace_level
to 0 disables tracing. Trace output goes to the standard error output by default. If
$trace_filename
is specified, the file is opened in append mode and output for
all traced handles is written to that file. Example:
DBI->trace(2); # trace everything DBI->trace(2,"/tmp/dbi.out"); # trace everything to # /tmp/dbi.out $dth->trace(2); # trace this database handle $sth->trace(2); # trace this statement handleYou can also enable
DBI
tracing by setting the
DBI_TRACE
environment variable. Setting it to a numeric value is equivalent to calling
DBI->(value)
. Setting it to a pathname is equivalent to calling
DBI->(2,value)
.
MySQL-specific Methods
The methods shown below are MySQL-specific and not part of the DBI
standard. Several of them are now deprecated: is_blob
, is_key
, is_num
, is_pri_key
, is_not_null
, length
, max_length
, and table
. Where DBI
-standard alternatives exist, they are noted below:
-
If you use the
AUTO_INCREMENT
feature of MySQL, the new auto-incremented values will be stored here. Example:$new_id = $sth->{insertid};
As an alternative, you can use$dbh->{'mysql_insertid'}
. -
Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a
BLOB
. Example:$keys = $sth->{is_blob};
-
Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a key. Example:
$keys = $sth->{is_key};
-
Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column contains numeric values. Example:
$nums = $sth->{is_num};
-
Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a primary key. Example:
$pri_keys = $sth->{is_pri_key};
-
Returns a reference to an array of boolean values; for each element of the array, a value of FALSE indicates that this column may contain
NULL
values. Example:$not_nulls = $sth->{is_not_null};
is_not_null
is deprecated; it is preferable to use theNULLABLE
attribute (described above), because that is a DBI standard. -
Each of these methods returns a reference to an array of column sizes. The
length
array indicates the maximum possible sizes that each column may be (as declared in the table description). Themax_length
array indicates the maximum sizes actually present in the result table. Example:$lengths = $sth->{length}; $max_lengths = $sth->{max_length};
-
Returns a reference to an array of column names. Example:
$names = $sth->{NAME};
-
Returns a reference to an array of table names. Example:
$tables = $sth->{table};
-
Returns a reference to an array of column types. Example:
$types = $sth->{type};
insertid
is_blob
is_key
is_num
is_pri_key
is_not_null
length
max_length
NAME
table
type
8.2.3 More DBI
/DBD
Information
You can use the perldoc
command to get more information about DBI
.
perldoc DBI perldoc DBI::FAQ perldoc DBD::mysql
You can also use the pod2man
, pod2html
, etc., tools to translate to other formats.
You can find the latest DBI
information at the DBI
Web page:
http://www.symbolstone.org/technology/perl/DBI/index.html
8.3 MySQL ODBC Support
MySQL provides support for ODBC by means of the MyODBC program. This chapter will teach you how to install MyODBC, and how to use it. Here, you will also find a list of common programs that are known to work with MyODBC.
8.3.1 How To Install MyODBC
MyODBC is a 32-bit ODBC (2.50) level 0 (with level 1 and level 2 features) driver for connecting an ODBC-aware application to MySQL. MyODBC works on Windows95, Windows98, NT, and on most Unix platforms.
MyODBC is in public domain, and you can find the newest version at http://www.mysql.com/downloads/api-myodbc.html.
If you have problem with MyODBC and your program also works with OLEDB, you should try the OLEDB driver that you can find in the Contrib section. See section D Contributed Programs.
Normally you only need to install MyODBC on Windows machines. You only need MyODBC for Unix if you have a program like ColdFusion that is running on the Unix machine and uses ODBC to connect to the databases.
If you want to install MyODBC on a Unix box, you will also need an ODBC manager. MyODBC is known to work with most of the Unix ODBC managers. You can find a list at these in the ODBC-related links section on the MySQL useful links page. See section 1.2.3 Useful MySQL-related Links.
To install MyODBC on Windows, you should download the appropriate MyODBC .zip file (for Windows or NT/Win2000), unpack it with WINZIP
, or some similar program, and execute the SETUP.EXE
file.
On Windows/NT you may get the following error when trying to install MyODBC:
An error occurred while copying C:\WINDOWS\SYSTEM\MFC30.DLL. Restart Windows and try installing again (before running any applications which use ODBC)
The problem in this case is that some other program is using ODBC and because of how Windows is designed, you may not in this case be able to install a new ODBC drivers with Microsoft's ODBC setup program. In most cases you can continue by just pressing Ignore
to copy the rest of the MyODBC files and the final installation should still work. If this doesn't work, the solution is to reboot your computer in ``safe mode`` (Choose this by pressing F8 just before your machine starts Windows during rebooting), install MyODBC, and reboot to normal mode.
- To make a connection to a Unix box from a Windows box, with an ODBC application (one that doesn't support MySQL natively), you must first install MyODBC on the Windows machine.
- The user and Windows machine must have the access privileges to the MySQL server on the Unix machine. This is set up with the
GRANT
command. See section 4.3.1GRANT
andREVOKE
Syntax. - You must create an ODBC DSN entry as follows:
- Open the Control Panel on the Windows machine.
- Double-click the ODBC Data Sources 32 bits icon.
- Click the tab User DSN.
- Click the button Add.
- Select MySQL in the screen Create New Data Source and click the Finish button.
- The MySQL Driver default configuration screen is shown. See section 8.3.2 How to Fill in the Various Fields in the ODBC Administrator Program.
- Now start your application and select the ODBC driver with the DSN you specified in the ODBC administrator.
Notice that there are other configuration options on the screen of MySQL (trace, don't prompt on connect, etc) that you can try if you run into problems.
8.3.2 How to Fill in the Various Fields in the ODBC Administrator Program
There are three possibilities for specifying the server name on Windows95:
- Use the IP address of the server.
- Add a file `\windows\lmhosts' with the following information:
ip hostname
For example:194.216.84.21 my_hostname
- Configure the PC to use DNS.
Example of how to fill in the ODBC setup
:
Windows DSN name: test Description: This is my test database MySql Database: test Server: 194.216.84.21 User: monty Password: my_password Port:
The value for the Windows DSN name
field is any name that is unique in your Windows ODBC setup.
You don't have to specify values for the Server
, User
, Password
, or Port
fields in the ODBC setup screen. However, if you do, the values will be used as the defaults later when you attempt to make a connection. You have the option of changing the values at that time.
If the port number is not given, the default port (3306 ) is used.
If you specify the option Read options from C:\my.cnf
, the groups client
and odbc
will be read from the `C:\my.cnf' file. You can use all options that are usable by mysql_options()
. See section 8.4.3.159 mysql_options()
.
8.3.3 Connect parameters for MyODBC
One can specify the following parameters for MyODBC on the [Servername]
section of an ODBC.INI
file or through the InConnectionString
argument in the SQLDriverConnect()
call.
Parameter | Default value | Comment |
user | ODBC (on Windows) | The username used to connect to MySQL. |
server | localhost | The hostname of the MySQL server. |
database | The default database | |
option | 0 | A integer by which you can specify how MyODBC should work. See below. |
port | 3306 | The TCP/IP port to use if server is not localhost . |
stmt | A statement that will be executed when connection to MySQL . | |
password | The password for the server user combination. | |
socket | The socket or Windows pipe to connect to. |
The option argument is used to tell MyODBC that the client isn't 100% ODBC compliant. On Windows, one normally sets the option flag by toggling the different options on the connection screen but one can also set this in the opton argument. The following options are listed in the same order as they appear in the MyODBC connect screen:
Bit | Meaning |
1 | The client can't handle that MyODBC returns the real width of a column. |
2 | The client can't handle that MySQL returns the true value of affected rows. If this flag is set then MySQL returns 'found rows' instead. One must have MySQL 3.21.14 or newer to get this to work. |
4 | Make a debug log in c:\myodbc.log. This is the same as putting MYSQL_DEBUG=d:t:O,c::\myodbc.log in `AUTOEXEC.BAT' |
8 | Don't set any packet limit for results and parameters. |
16 | Don't prompt for questions even if driver would like to prompt |
32 | Simulate a ODBC 1.0 driver in some context. |
64 | Ignore use of database name in 'database.table.column'. |
128 | Force use of ODBC manager cursors (experimental). |
256 | Disable the use of extended fetch (experimental) |
512 | Pad CHAR fields to full column length. |
1024 | SQLDescribeCol() will return fully qualifed column names |
2048 | Use the compressed server/client protocol |
4096 | Tell server to ignore space after function name and before '(' (needed by PowerBuilder). This will make all function names keywords! |
8192 | Connect with named pipes to a mysqld server running on NT. |
16384 | Change LONGLONG columns to INT columns (Some applications can't handle LONGLONG). |
32768 | Return 'user' as Table_qualifier and Table_owner from SQLTables (experimental) |
65536 | Read parameters from the client and odbc groups from `my.cnf' |
131072 | Add some extra safety checks (should not bee needed but...) |
If you want to have many options, you should add the above flags! For example setting option to 12 (4+8) gives you debugging without package limits!
The default `MYODBC.DLL' is compiled for optimal performance. If you want to to debug MyODBC (for example to enable tracing), you should instead use MYODBCD.DLL
. To install this file, copy `MYODBCD.DLL' over the installed MYODBC.DLL
file.
8.3.4 How to Report Problems with MyODBC
MyODBC has been tested with Access, Admndemo.exe, C++-Builder, Borland Builder 4, Centura Team Developer (formerly Gupta SQL/Windows), ColdFusion (on Solaris and NT with svc pack 5), Crystal Reports, DataJunction, Delphi, ERwin, Excel, iHTML, FileMaker Pro, FoxPro, Notes 4.5/4.6, SBSS, Perl DBD-ODBC, Paradox, Powerbuilder, Powerdesigner 32 bit, VC++, and Visual Basic.
If you know of any other applications that work with MyODBC, please send mail to myodbc@lists.mysql.com about this!
With some programs you may get an error like: Another user has modifies the record that you have modified
. In most cases this can be solved by doing one of the following things:
- Add a primary key for the table if there isn't one already.
- Add a timestamp column if there isn't one already.
- Only use double float fields. Some programs may fail when they compare single floats.
If the above doesn't help, you should do a MyODBC
trace file and try to figure out why things go wrong.
8.3.5 Programs Known to Work with MyODBC
Most programs should work with MyODBC, but for each of those listed below, we have tested it ourselves or received confirmation from some user that it works:
-
Program
- Comment Access
-
To make Access work:
- If you are using Access 2000, you should get and install the newest (version 2.6 or above) Microsoft MDAC (
Microsoft Data Access Components
) from http://www.microsoft.com/data. This will fix the following bug in Access: when you export data to MySQL, the table and column names aren't specified. Another way to around this bug is to upgrade to MyODBC Version 2.50.33 and MySQL Version 3.23.x, which together provide a workaround for this bug! You should also get and apply the Microsoft Jet 4.0 Service Pack 5 (SP5) which can be found here http://support.microsoft.com/support/kb/articles/Q 239/1/14.ASP. This will fix some cases where columns are marked as#deleted#
in Access. Note that if you are using MySQL Version 3.22, you must to apply the MDAC patch and use MyODBC 2.50.32 or 2.50.34 and above to go around this problem. - Set the ``Return matching rows'' MyODBC option field when connecting to MySQL.
- You should have a primary key in the table. If not, new or updated rows may show up as
#Deleted#
. - You should have a timestamp in all tables you want to be able to update. For maximum portability
TIMESTAMP(14)
or simpleTIMESTAMP
is recommended instead of otherTIMESTAMP(X)
variations. - Only use double float fields. Access fails when comparing with single floats. The symptom usually is that new or updated rows may show up as
#Deleted#
or that you can't find or update rows. - If you still get the error
Another user has changed your data
after adding aTIMESTAMP
column, the following trick may help you: Don't usetable
data sheet view. Create instead a form with the fields you want, and use thatform
data sheet view. You should set theDefaultValue
property for theTIMESTAMP
column toNOW()
. It may be a good idea to hide theTIMESTAMP
column from view so your users are not confused. - Access on NT will report
BLOB
columns asOLE OBJECTS
. If you want to haveMEMO
columns instead, you should change the column toTEXT
withALTER TABLE
. - Access can't always handle
DATE
columns properly. If you have a problem with these, change the columns toDATETIME
. - In some cases, Access may generate illegal SQL queries that MySQL can't understand. You can fix this by selecting
"Query|SQLSpecific|Pass-Through"
from the Access menu. - If you have in Access a column defined as BYTE, Access will try to export this as
TINYINT
instead ofTINYINT UNSIGNED
. This will give you problems if you have values > 127 in the column! - If you are using Access 7.0, You should use the option flag
Return matching rows
. - If you are using Access 2.0, You should use the option flags
Return matching rows
andSimulate ODBC 1.0
.
ADO
- If you are using Access 2000, you should get and install the newest (version 2.6 or above) Microsoft MDAC (
-
When you are coding with the ADO API and
MyODBC you need to put attention in some default properties that aren't supported by the MySQL server. For example, using the
CursorLocation Property
asadUseServer
will return for theRecordCount Property
a result of -1. To have the right value, you need to set this property toadUseClient
, like is showing in the VB code below:Dim myconn As New ADODB.Connection Dim myrs As New Recordset Dim mySQL As String Dim myrows As Long myconn.Open "DSN=MyODBCsample" mySQL = "SELECT * from user" myrs.Source = mySQL Set myrs.ActiveConnection = myconn myrs.CursorLocation = adUseClient myrs.Open myrows = myrs.RecordCount myrs.Close myconn.Close
Another workaround is to use aSELECT COUNT(*)
statement for a similar query to get the correct row count.
Active server pages (ASP)
-
You should use the option flag
Return matching rows
.
BDE applications
-
To get these to work, you should set the option flags
Don't optimize column widths
andReturn matching rows
.
Borland Builder 4
-
When you start a query you can use the property
Active
or use the methodOpen
. Note thatActive
will start by automatically issuing aSELECT * FROM ...
query that may not be a good thing if your tables are big!
ColdFusion (On Unix)
- The following information is taken from the ColdFusion documentation: Use the following information to configure ColdFusion Server for Linux to use the unixODBC driver with MyODBC for MySQL data sources. Allaire has verified that MyODBC Version 2.50.26 works with MySQL Version 3.22.27 and ColdFusion for Linux. (Any newer version should also work.) You can download MyODBC at http://www.mysql.com/downloads/api-myodbc.html ColdFusion Version 4.5.1 allows you to us the ColdFusion Administrator to add the MySQL data source. However, the driver is not included with ColdFusion Version 4.5.1. Before the MySQL driver will appear in the ODBC datasources drop-down list, you must build and copy the MyODBC driver to `/opt/coldfusion/lib/libmyodbc.so'. The Contrib directory contains the program mydsn-xxx.zip which allows you to build and remove the DSN registry file for the MyODBC driver on Coldfusion applications. DataJunction
-
You have to change it to output
VARCHAR
rather thanENUM
, as it exports the latter in a manner that causes MySQL grief.
Excel
-
Works. Some tips:
- If you have problems with dates, try to select them as strings using the
CONCAT()
function. For example:select CONCAT(rise_time), CONCAT(set_time) from sunrise_sunset;
Values retrieved as strings this way should be correctly recognized as time values by Excel97. The purpose ofCONCAT()
in this example is to fool ODBC into thinking the column is of ``string type''. Without theCONCAT()
, ODBC knows the column is of time type, and Excel does not understand that. Note that this is a bug in Excel, because it automatically converts a string to a time. This would be great if the source was a text file, but is plain stupid when the source is an ODBC connection that reports exact types for each column.
Word
- If you have problems with dates, try to select them as strings using the
-
To retrieve data from MySQL to Word/Excel documents, you need to use the
MyODBC
driver and the Add-in Microsoft Query help. For example, create a db with a table containing 2 columns of text:- Insert rows using the
mysql
client command-line tool. - Create a DSN file using the MyODBC driver, for example, my for the db above.
- Open the Word application.
- Create a blank new documentation.
- Using the tool bar called Database, press the button insert database.
- Press the button Get Data.
- At the right hand of the screen Get Data, press the button Ms Query.
- In the Ms Query create a New Data Source using the DSN file my.
- Select the new query.
- Select the columns that you want.
- Make a filter if you want.
- Make a Sort if you want.
- Select Return Data to Microsoft Word.
- Click Finish.
- Click Insert data and select the records.
- Click OK and you see the rows in your Word document.
odbcadmin
- Insert rows using the
- Test program for ODBC. Delphi
-
You must use BDE Version 3.2 or newer. Set the `Don't optimize column width' option field when connecting to MySQL. Also, here is some potentially useful Delphi code that sets up both an ODBC entry and a BDE entry for
MyODBC (the BDE entry requires a BDE Alias Editor that is free at a Delphi Super Page near you. (Thanks to Bryan Brunton
bryan@flesherfab.com for this):
fReg:= TRegistry.Create; fReg.OpenKey('\Software\ODBC\ODBC.INI\DocumentsFab', True); fReg.WriteString('Database', 'Documents'); fReg.WriteString('Description', ' '); fReg.WriteString('Driver', 'C:\WINNT\System32\myodbc.dll'); fReg.WriteString('Flag', '1'); fReg.WriteString('Password', ''); fReg.WriteString('Port', ' '); fReg.WriteString('Server', 'xmark'); fReg.WriteString('User', 'winuser'); fReg.OpenKey('\Software\ODBC\ODBC.INI\ODBC Data Sources', True); fReg.WriteString('DocumentsFab', 'MySQL'); fReg.CloseKey; fReg.Free; Memo1.Lines.Add('DATABASE NAME='); Memo1.Lines.Add('USER NAME='); Memo1.Lines.Add('ODBC DSN=DocumentsFab'); Memo1.Lines.Add('OPEN MODE=READ/WRITE'); Memo1.Lines.Add('BATCH COUNT=200'); Memo1.Lines.Add('LANGDRIVER='); Memo1.Lines.Add('MAX ROWS=-1'); Memo1.Lines.Add('SCHEMA CACHE DIR='); Memo1.Lines.Add('SCHEMA CACHE SIZE=8'); Memo1.Lines.Add('SCHEMA CACHE TIME=-1'); Memo1.Lines.Add('SQLPASSTHRU MODE=SHARED AUTOCOMMIT'); Memo1.Lines.Add('SQLQRYMODE='); Memo1.Lines.Add('ENABLE SCHEMA CACHE=FALSE'); Memo1.Lines.Add('ENABLE BCD=FALSE'); Memo1.Lines.Add('ROWSET SIZE=20'); Memo1.Lines.Add('BLOBS TO CACHE=64'); Memo1.Lines.Add('BLOB SIZE=32'); AliasEditor.Add('DocumentsFab','MySQL',Memo1.Lines);
C++ Builder
- Tested with BDE Version 3.0. The only known problem is that when the table schema changes, query fields are not updated. BDE, however, does not seem to recognize primary keys, only the index PRIMARY, though this has not been a problem. Vision
-
You should use the option flag
Return matching rows
.
Visual Basic
-
To be able to update a table, you must define a primary key for the table. Visual Basic with ADO can't handle big integers. This means that some queries like
SHOW PROCESSLIST
will not work properly. The fix is to set add the optionOPTION=16834
in the ODBC connect string or set theChange BIGINT columns to INT
option in the MyODBC connect screen. You may also want to set theReturn matching rows
option.
VisualInterDev
-
If you get the error
[Microsoft][ODBC Driver Manager] Driver does not support this parameter
the reason may be that you have aBIGINT
in your result. Try setting theChange BIGINT columns to INT
option in the MyODBC connect screen.
Visual Objects
-
You should use the option flag
Don't optimize column widths
.
8.3.6 How to Get the Value of an AUTO_INCREMENT
Column in ODBC
A common problem is how to get the value of an automatically generated ID from an INSERT
. With ODBC, you can do something like this (assuming that auto
is an AUTO_INCREMENT
field):
INSERT INTO foo (auto,text) VALUES(NULL,'text'); SELECT LAST_INSERT_ID();
Or, if you are just going to insert the ID into another table, you can do this:
INSERT INTO foo (auto,text) VALUES(NULL,'text'); INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text');
See section 8.4.6.3 How Can I Get the Unique ID for the Last Inserted Row?.
For the benefit of some ODBC applications (at least Delphi and Access), the following query can be used to find a newly inserted row:
SELECT * FROM tbl_name WHERE auto IS NULL;
8.3.7 Reporting Problems with MyODBC
If you encounter difficulties with MyODBC, you should start by making a log file from the ODBC manager (the log you get when requesting logs from ODBCADMIN) and a MyODBC log.
To get a MyODBC log, you need to do the following:
- Ensure that you are using
myodbcd.dll
and notmyodbc.dll
. The easiest way to do this is to getmyodbcd.dll
from the MyODBC distribution and copy it over themyodbc.dll
, which is probably in yourC:\windows\system32
orC:\winnt\system32
directory. Note that you probably want to restore the old myodbc.dll file when you have finished testing, as this is a lot faster thanmyodbcd.dll
. - Tag the `Trace MyODBC' option flag in the MyODBC connect/configure screen. The log will be written to file `C:\myodbc.log'. If the trace option is not remembered when you are going back to the above screen, it means that you are not using the
myodbcd.dll
driver (see above). - Start your application and try to get it to fail.
Check the MyODBC trace file
, to find out what could be wrong. You should be able to find out the issued queries by searching after the string >mysql_real_query
in the `myodbc.log' file.
You should also try duplicating the queries in the mysql
monitor or admndemo
to find out if the error is MyODBC or MySQL.
If you find out something is wrong, please only send the relevant rows (max 40 rows) to myodbc@lists.mysql.com. Please never send the whole MyODBC or ODBC log file!
If you are unable to find out what's wrong, the last option is to make an archive (tar or zip) that contains a MyODBC trace file, the ODBC log file, and a README file that explains the problem. You can send this to ftp://support.mysql.com/pub/mysql/secret. Only we at MySQL AB will have access to the files you upload, and we will be very discrete with the data!
If you can create a program that also shows this problem, please upload this too!
If the program works with some other SQL server, you should make an ODBC log file where you do exactly the same thing in the other SQL server.
Remember that the more information you can supply to us, the more likely it is that we can fix the problem!
8.4 MySQL C API
The C API code is distributed with MySQL. It is included in the mysqlclient
library and allows C programs to access a database.
Many of the clients in the MySQL source distribution are written in C. If you are looking for examples that demonstrate how to use the C API, take a look at these clients. You can find these in the clients
directory in the MySQL source distribution.
Most of the other client APIs (all except Java) use the mysqlclient
library to communicate with the MySQL server. This means that, for example, you can take advantage of many of the same environment variables that are used by other client programs, because they are referenced from the library. See section 4.8 MySQL Client-Side Scripts and Utilities, for a list of these variables.
The client has a maximum communication buffer size. The size of the buffer that is allocated initially (16K bytes) is automatically increased up to the maximum size (the maximum is 16M). Because buffer sizes are increased only as demand warrants, simply increasing the default maximum limit does not in itself cause more resources to be used. This size check is mostly a check for erroneous queries and communication packets.
The communication buffer must be large enough to contain a single SQL statement (for client-to-server traffic) and one row of returned data (for server-to-client traffic). Each thread's communication buffer is dynamically enlarged to handle any query or row up to the maximum limit. For example, if you have BLOB
values that contain up to 16M of data, you must have a communication buffer limit of at least 16M (in both server and client). The client's default maximum is 16M, but the default maximum in the server is 1M. You can increase this by changing the value of the max_allowed_packet
parameter when the server is started. See section 5.5.2 Tuning Server Parameters.
The MySQL server shrinks each communication buffer to net_buffer_length
bytes after each query. For clients, the size of the buffer associated with a connection is not decreased until the connection is closed, at which time client memory is reclaimed.
For programming with threads, see section 8.4.8 How to Make a Threaded Client. For creating a stand-alone application which includes the "server" and "client" in the same program (and does not communicate with an external MySQL server), see section 8.4.9 libmysqld, the Embedded MySQL Server Library.
8.4.1 C API Datatypes
- This structure represents a handle to one database connection. It is used for almost all MySQL functions.
-
This structure represents the result of a query that returns rows (
SELECT
,SHOW
,DESCRIBE
,EXPLAIN
). The information returned from a query is called the result set in the remainder of this section. -
This is a type-safe representation of one row of data. It is currently implemented as an array of counted byte strings. (You cannot treat these as null-terminated strings if field values may contain binary data, because such values may contain null bytes internally.) Rows are obtained by calling
mysql_fetch_row()
. -
This structure contains information about a field, such as the field's name, type, and size. Its members are described in more detail below. You may obtain the
MYSQL_FIELD
structures for each field by callingmysql_fetch_field()
repeatedly. Field values are not part of this structure; they are contained in aMYSQL_ROW
structure. -
This is a type-safe representation of an offset into a MySQL field list. (Used by
mysql_field_seek()
.) Offsets are field numbers within a row, beginning at zero. -
The type used for the number of rows and for
mysql_affected_rows()
,mysql_num_rows()
, andmysql_insert_id()
. This type provides a range of0
to1.84e19
. On some systems, attempting to print a value of typemy_ulonglong
will not work. To print such a value, convert it tounsigned long
and use a%lu
print format. Example:printf (Number of rows: %lu\n", (unsigned long) mysql_num_rows(result));
MYSQL
MYSQL_RES
MYSQL_ROW
MYSQL_FIELD
MYSQL_FIELD_OFFSET
my_ulonglong
The MYSQL_FIELD
structure contains the members listed below:
- The name of the field, as a null-terminated string.
-
The name of the table containing this field, if it isn't a calculated field. For calculated fields, the
table
value is an empty string. -
The default value of this field, as a null-terminated string. This is set only if you use
mysql_list_fields()
. -
The type of the field. The
type
value may be one of the following:
You can use theType value Type meaning FIELD_TYPE_TINY
TINYINT
fieldFIELD_TYPE_SHORT
SMALLINT
fieldFIELD_TYPE_LONG
INTEGER
fieldFIELD_TYPE_INT24
MEDIUMINT
fieldFIELD_TYPE_LONGLONG
BIGINT
fieldFIELD_TYPE_DECIMAL
DECIMAL
orNUMERIC
fieldFIELD_TYPE_FLOAT
FLOAT
fieldFIELD_TYPE_DOUBLE
DOUBLE
orREAL
fieldFIELD_TYPE_TIMESTAMP
TIMESTAMP
fieldFIELD_TYPE_DATE
DATE
fieldFIELD_TYPE_TIME
TIME
fieldFIELD_TYPE_DATETIME
DATETIME
fieldFIELD_TYPE_YEAR
YEAR
fieldFIELD_TYPE_STRING
String ( CHAR
orVARCHAR
) fieldFIELD_TYPE_BLOB
BLOB
orTEXT
field (usemax_length
to determine the maximum length)FIELD_TYPE_SET
SET
fieldFIELD_TYPE_ENUM
ENUM
fieldFIELD_TYPE_NULL
NULL
-type fieldFIELD_TYPE_CHAR
Deprecated; use FIELD_TYPE_TINY
insteadIS_NUM()
macro to test whether or not a field has a numeric type. Pass thetype
value toIS_NUM()
and it will evaluate to TRUE if the field is numeric:if (IS_NUM(field->type)) printf("Field is numeric\n");
- The width of the field, as specified in the table definition.
-
The maximum width of the field for the result set (the length of the longest field value for the rows actually in the result set). If you use
mysql_store_result()
ormysql_list_fields()
, this contains the maximum length for the field. If you usemysql_use_result()
, the value of this variable is zero. -
Different bit-flags for the field. The
flags
value may have zero or more of the following bits set:
Use of theFlag value Flag meaning NOT_NULL_FLAG
Field can't be NULL
PRI_KEY_FLAG
Field is part of a primary key UNIQUE_KEY_FLAG
Field is part of a unique key MULTIPLE_KEY_FLAG
Field is part of a non-unique key UNSIGNED_FLAG
Field has the UNSIGNED
attributeZEROFILL_FLAG
Field has the ZEROFILL
attributeBINARY_FLAG
Field has the BINARY
attributeAUTO_INCREMENT_FLAG
Field has the AUTO_INCREMENT
attributeENUM_FLAG
Field is an ENUM
(deprecated)BLOB_FLAG
Field is a BLOB
orTEXT
(deprecated)TIMESTAMP_FLAG
Field is a TIMESTAMP
(deprecated)BLOB_FLAG
,ENUM_FLAG
, andTIMESTAMP_FLAG
flags is deprecated because they indicate the type of a field rather than an attribute of its type. It is preferable to testfield->type
againstFIELD_TYPE_BLOB
,FIELD_TYPE_ENUM
, orFIELD_TYPE_TIMESTAMP
instead. The example below illustrates a typical use of theflags
value:if (field->flags & NOT_NULL_FLAG) printf("Field can't be null\n");
You may use the following convenience macros to determine the boolean status of theflags
value:IS_NOT_NULL(flags)
True if this field is defined as NOT NULL
IS_PRI_KEY(flags)
True if this field is a primary key IS_BLOB(flags)
True if this field is a BLOB
orTEXT
(deprecated; testfield->type
instead) - The number of decimals for numeric fields.
char * name
char * table
char * def
enum enum_field_types type
unsigned int length
unsigned int max_length
unsigned int flags
unsigned int decimals
8.4.2 C API Function Overview
The functions available in the C API are listed below and are described in greater detail in the next section. See section 8.4.3 C API Function Descriptions.
mysql_affected_rows() | Returns the number of rows changed/deleted/inserted by the last UPDATE , DELETE , or INSERT query. |
mysql_close() | Closes a server connection. |
mysql_connect() | Connects to a MySQL server. This function is deprecated; use mysql_real_connect() instead. |
mysql_change_user() | Changes user and database on an open connection. |
mysql_character_set_name() | Returns the name of the default character set for the connection. |
mysql_create_db() | Creates a database. This function is deprecated; use the SQL command CREATE DATABASE instead. |
mysql_data_seek() | Seeks to an arbitrary row in a query result set. |
mysql_debug() | Does a DBUG_PUSH with the given string. |
mysql_drop_db() | Drops a database. This function is deprecated; use the SQL command DROP DATABASE instead. |
mysql_dump_debug_info() | Makes the server write debug information to the log. |
mysql_eof() | Determines whether or not the last row of a result set has been read. This function is deprecated; mysql_errno() or mysql_error() may be used instead. |
mysql_errno() | Returns the error number for the most recently invoked MySQL function. |
mysql_error() | Returns the error message for the most recently invoked MySQL function. |
mysql_real_escape_string() | Escapes special characters in a string for use in a SQL statement taking into account the current charset of the connection. |
mysql_escape_string() | Escapes special characters in a string for use in a SQL statement. |
mysql_fetch_field() | Returns the type of the next table field. |
mysql_fetch_field_direct() | Returns the type of a table field, given a field number. |
mysql_fetch_fields() | Returns an array of all field structures. |
mysql_fetch_lengths() | Returns the lengths of all columns in the current row. |
mysql_fetch_row() | Fetches the next row from the result set. |
mysql_field_seek() | Puts the column cursor on a specified column. |
mysql_field_count() | Returns the number of result columns for the most recent query. |
mysql_field_tell() | Returns the position of the field cursor used for the last mysql_fetch_field() . |
mysql_free_result() | Frees memory used by a result set. |
mysql_get_client_info() | Returns client version information. |
mysql_get_host_info() | Returns a string describing the connection. |
mysql_get_proto_info() | Returns the protocol version used by the connection. |
mysql_get_server_info() | Returns the server version number. |
mysql_info() | Returns information about the most recently executed query. |
mysql_init() | Gets or initializes a MYSQL structure. |
mysql_insert_id() | Returns the ID generated for an AUTO_INCREMENT column by the previous query. |
mysql_kill() | Kills a given thread. |
mysql_list_dbs() | Returns database names matching a simple regular expression. |
mysql_list_fields() | Returns field names matching a simple regular expression. |
mysql_list_processes() | Returns a list of the current server threads. |
mysql_list_tables() | Returns table names matching a simple regular expression. |
mysql_num_fields() | Returns the number of columns in a result set. |
mysql_num_rows() | Returns the number of rows in a result set. |
mysql_options() | Sets connect options for mysql_connect() . |
mysql_ping() | Checks whether or not the connection to the server is working, reconnecting as necessary. |
mysql_query() | Executes a SQL query specified as a null-terminated string. |
mysql_real_connect() | Connects to a MySQL server. |
mysql_real_query() | Executes a SQL query specified as a counted string. |
mysql_reload() | Tells the server to reload the grant tables. |
mysql_row_seek() | Seeks to a row in a result set, using value returned from mysql_row_tell() . |
mysql_row_tell() | Returns the row cursor position. |
mysql_select_db() | Selects a database. |
mysql_shutdown() | Shuts down the database server. |
mysql_stat() | Returns the server status as a string. |
mysql_store_result() | Retrieves a complete result set to the client. |
mysql_thread_id() | Returns the current thread ID. |
mysql_thread_safe() | Returns 1 if the clients are compiled as thread safe. |
mysql_use_result() | Initiates a row-by-row result set retrieval. |
To connect to the server, call mysql_init()
to initialize a connection handler, then call mysql_real_connect()
with that handler (along with other information such as the hostname, user name, and password). Upon connection, mysql_real_connect()
sets the reconnect
flag (part of the MYSQL structure) to a value of 1
. This flag indicates, in the event that a query cannot be performed because of a lost connection, to try reconnecting to the server before giving up. When you are done with the connection, call mysql_close()
to terminate it.
While a connection is active, the client may send SQL queries to the server using mysql_query()
or mysql_real_query()
. The difference between the two is that mysql_query()
expects the query to be specified as a null-terminated string whereas mysql_real_query()
expects a counted string. If the string contains binary data (which may include null bytes), you must use mysql_real_query()
.
For each non-SELECT
query (for example, INSERT
, UPDATE
, DELETE
), you can find out how many rows were changed (affected) by calling mysql_affected_rows()
.
For SELECT
queries, you retrieve the selected rows as a result set. (Note that some statements are SELECT
-like in that they return rows. These include SHOW
, DESCRIBE
, and EXPLAIN
. They should be treated the same way as SELECT
statements.)
There are two ways for a client to process result sets. One way is to retrieve the entire result set all at once by calling mysql_store_result()
. This function acquires from the server all the rows returned by the query and stores them in the client. The second way is for the client to initiate a row-by-row result set retrieval by calling mysql_use_result()
. This function initializes the retrieval, but does not actually get any rows from the server.
In both cases, you access rows by calling mysql_fetch_row()
. With mysql_store_result()
, mysql_fetch_row()
accesses rows that have already been fetched from the server. With mysql_use_result()
, mysql_fetch_row()
actually retrieves the row from the server. Information about the size of the data in each row is available by calling mysql_fetch_lengths()
.
After you are done with a result set, call mysql_free_result()
to free the memory used for it.
The two retrieval mechanisms are complementary. Client programs should choose the approach that is most appropriate for their requirements. In practice, clients tend to use mysql_store_result()
more commonly.
An advantage of mysql_store_result()
is that because the rows have all been fetched to the client, you not only can access rows sequentially, you can move back and forth in the result set using mysql_data_seek()
or mysql_row_seek()
to change the current row position within the result set. You can also find out how many rows there are by calling mysql_num_rows()
. On the other hand, the memory requirements for mysql_store_result()
may be very high for large result sets and you are more likely to encounter out-of-memory conditions.
An advantage of mysql_use_result()
is that the client requires less memory for the result set because it maintains only one row at a time (and because there is less allocation overhead, mysql_use_result()
can be faster). Disadvantages are that you must process each row quickly to avoid tying up the server, you don't have random access to rows within the result set (you can only access rows sequentially), and you don't know how many rows are in the result set until you have retrieved them all. Furthermore, you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for.
The API makes it possible for clients to respond appropriately to queries (retrieving rows only as necessary) without knowing whether or not the query is a SELECT
. You can do this by calling mysql_store_result()
after each mysql_query()
(or mysql_real_query()
). If the result set call succeeds, the query was a SELECT
and you can read the rows. If the result set call fails, call mysql_field_count()
to determine whether or not a result was actually to be expected. If mysql_field_count()
returns zero, the query returned no data (indicating that it was an INSERT
, UPDATE
, DELETE
, etc.), and was not expected to return rows. If mysql_field_count()
is non-zero, the query should have returned rows, but didn't. This indicates that the query was a SELECT
that failed. See the description for mysql_field_count()
for an example of how this can be done.
Both mysql_store_result()
and mysql_use_result()
allow you to obtain information about the fields that make up the result set (the number of fields, their names and types, etc.). You can access field information sequentially within the row by calling mysql_fetch_field()
repeatedly, or by field number within the row by calling mysql_fetch_field_direct()
. The current field cursor position may be changed by calling mysql_field_seek()
. Setting the field cursor affects subsequent calls to mysql_fetch_field()
. You can also get information for fields all at once by calling mysql_fetch_fields()
.
For detecting and reporting errors, MySQL provides access to error information by means of the mysql_errno()
and mysql_error()
functions. These return the error code or error message for the most recently invoked function that can succeed or fail, allowing you to determine when an error occurred and what it was.
8.4.3 C API Function Descriptions
In the descriptions below, a parameter or return value of NULL
means NULL
in the sense of the C programming language, not a MySQL NULL
value.
Functions that return a value generally return a pointer or an integer. Unless specified otherwise, functions returning a pointer return a non-NULL
value to indicate success or a NULL
value to indicate an error, and functions returning an integer return zero to indicate success or non-zero to indicate an error. Note that ``non-zero'' means just that. Unless the function description says otherwise, do not test against a value other than zero:
if (result) /* correct */ ... error ... if (result < 0) /* incorrect */ ... error ... if (result == -1) /* incorrect */ ... error ...
When a function returns an error, the Errors subsection of the function description lists the possible types of errors. You can find out which of these occurred by calling mysql_errno()
. A string representation of the error may be obtained by calling mysql_error()
.
8.4.3.1 mysql_affected_rows()
my_ulonglong mysql_affected_rows(MYSQL *mysql)
8.4.3.2 Description
Returns the number of rows changed by the last UPDATE
, deleted by the last DELETE
or inserted by the last INSERT
statement. May be called immediately after mysql_query()
for UPDATE
, DELETE
, or INSERT
statements. For SELECT
statements, mysql_affected_rows()
works like mysql_num_rows()
.
8.4.3.3 Return Values
An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE
statement, no rows matched the WHERE
clause in the query or that no query has yet been executed. -1 indicates that the query returned an error or that, for a SELECT
query, mysql_affected_rows()
was called prior to calling mysql_store_result()
.
8.4.3.4 Errors
None.
8.4.3.5 Example
mysql_query(&mysql,"UPDATE products SET cost=cost*1.25 WHERE group=10"); printf("%ld products updated",(long) mysql_affected_rows(&mysql));
If one specifies the flag CLIENT_FOUND_ROWS
when connecting to mysqld
, mysql_affected_rows()
will return the number of rows matched by the WHERE
statement for UPDATE
statements.
Note that when one uses a REPLACE
command, mysql_affected_rows()
will return 2 if the new row replaced and old row. This is because in this case one row was inserted and then the duplicate was deleted.
8.4.3.6 mysql_close()
void mysql_close(MYSQL *mysql)
8.4.3.7 Description
Closes a previously opened connection. mysql_close()
also deallocates the connection handle pointed to by mysql
if the handle was allocated automatically by mysql_init()
or mysql_connect()
.
8.4.3.8 Return Values
None.
8.4.3.9 Errors
None.
8.4.3.10 mysql_connect()
MYSQL *mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd)
8.4.3.11 Description
This function is deprecated. It is preferable to use mysql_real_connect()
instead.
mysql_connect()
attempts to establish a connection to a MySQL database engine running on host
. mysql_connect()
must complete successfully before you can execute any of the other API functions, with the exception of mysql_get_client_info()
.
The meanings of the parameters are the same as for the corresponding parameters for mysql_real_connect()
with the difference that the connection parameter may be NULL
. In this case the C API allocates memory for the connection structure automatically and frees it when you call mysql_close()
. The disadvantage of this approach is that you can't retrieve an error message if the connection fails. (To get error information from mysql_errno()
or mysql_error()
, you must provide a valid MYSQL
pointer.)
8.4.3.12 Return Values
Same as for mysql_real_connect()
.
8.4.3.13 Errors
Same as for mysql_real_connect()
.
8.4.3.14 mysql_change_user()
my_bool mysql_change_user(MYSQL *mysql, const char *user, const char *password, const char *db)
8.4.3.15 Description
Changes the user and causes the database specified by db
to become the default (current) database on the connection specified by mysql
. In subsequent queries, this database is the default for table references that do not include an explicit database specifier.
This function was introduced in MySQL Version 3.23.3.
mysql_change_user()
fails unless the connected user can be authenticated or if he doesn't have permission to use the database. In this case the user and database are not changed
The db
parameter may be set to NULL
if you don't want to have a default database.
8.4.3.16 Return values
Zero for success. Non-zero if an error occurred.
8.4.3.17 Errors
The same that you can get from mysql_real_connect()
.
- Commands were executed in an improper order.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
- The MySQL server doesn't implement this command (probably an old server)
- The user or password was wrong.
- The database didn't exist.
- The user did not have access rights to the database.
- The database name was too long.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
ER_UNKNOWN_COM_ERROR
ER_ACCESS_DENIED_ERROR
ER_BAD_DB_ERROR
ER_DBACCESS_DENIED_ERROR
ER_WRONG_DB_NAME
8.4.3.18 Example
if (mysql_change_user(&mysql, "user", "password", "new_database")) { fprintf(stderr, "Failed to change user. Error: %s\n", mysql_error(&mysql)); }
8.4.3.19 mysql_character_set_name()
const char *mysql_character_set_name(MYSQL *mysql)
8.4.3.20 Description
Returns the default character set for the current connection.
8.4.3.21 Return Values
The default character set
8.4.3.22 Errors
None.
8.4.3.23 mysql_create_db()
int mysql_create_db(MYSQL *mysql, const char *db)
8.4.3.24 Description
Creates the database named by the db
parameter.
This function is deprecated. It is preferable to use mysql_query()
to issue a SQL CREATE DATABASE
statement instead.
8.4.3.25 Return Values
Zero if the database was created successfully. Non-zero if an error occurred.
8.4.3.26 Errors
- Commands were executed in an improper order.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.27 Example
if(mysql_create_db(&mysql, "my_database")) { fprintf(stderr, "Failed to create new database. Error: %s\n", mysql_error(&mysql)); }
8.4.3.28 mysql_data_seek()
void mysql_data_seek(MYSQL_RES *result, unsigned long long offset)
8.4.3.29 Description
Seeks to an arbitrary row in a query result set. This requires that the result set structure contains the entire result of the query, so mysql_data_seek()
may be used in conjunction only with mysql_store_result()
, not with mysql_use_result()
.
The offset should be a value in the range from 0 to mysql_num_rows(result)-1
.
8.4.3.30 Return Values
None.
8.4.3.31 Errors
None.
8.4.3.32 mysql_debug()
void mysql_debug(char *debug)
8.4.3.33 Description
Does a DBUG_PUSH
with the given string. mysql_debug()
uses the Fred Fish debug library. To use this function, you must compile the client library to support debugging. See section G.1 Debugging a MySQL server. See section G.2 Debugging a MySQL client.
8.4.3.34 Return Values
None.
8.4.3.35 Errors
None.
8.4.3.36 Example
The call shown below causes the client library to generate a trace file in `/tmp/client.trace' on the client machine:
mysql_debug("d:t:O,/tmp/client.trace");
8.4.3.37 mysql_drop_db()
int mysql_drop_db(MYSQL *mysql, const char *db)
8.4.3.38 Description
Drops the database named by the db
parameter.
This function is deprecated. It is preferable to use mysql_query()
to issue a SQL DROP DATABASE
statement instead.
8.4.3.39 Return Values
Zero if the database was dropped successfully. Non-zero if an error occurred.
8.4.3.40 Errors
- Commands were executed in an improper order.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.41 Example
if(mysql_drop_db(&mysql, "my_database")) fprintf(stderr, "Failed to drop the database: Error: %s\n", mysql_error(&mysql));
8.4.3.42 mysql_dump_debug_info()
int mysql_dump_debug_info(MYSQL *mysql)
8.4.3.43 Description
Instructs the server to write some debug information to the log. The connected user must have the process privilege for this to work.
8.4.3.44 Return values
Zero if the command was successful. Non-zero if an error occurred.
8.4.3.45 Errors
- Commands were executed in an improper order.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.46 mysql_eof()
my_bool mysql_eof(MYSQL_RES *result)
8.4.3.47 Description
This function is deprecated. mysql_errno()
or mysql_error()
may be used instead.
mysql_eof()
determines whether or not the last row of a result set has been read.
If you acquire a result set from a successful call to mysql_store_result()
, the client receives the entire set in one operation. In this case, a NULL
return from mysql_fetch_row()
always means the end of the result set has been reached and it is unnecessary to call mysql_eof()
.
On the other hand, if you use mysql_use_result()
to initiate a result set retrieval, the rows of the set are obtained from the server one by one as you call mysql_fetch_row()
repeatedly. Because an error may occur on the connection during this process, a NULL
return value from mysql_fetch_row()
does not necessarily mean the end of the result set was reached normally. In this case, you can use mysql_eof()
to determine what happened. mysql_eof()
returns a non-zero value if the end of the result set was reached and zero if an error occurred.
Historically, mysql_eof()
predates the standard MySQL error functions mysql_errno()
and mysql_error()
. Because those error functions provide the same information, their use is preferred over mysql_eof()
, which is now deprecated. (In fact, they provide more information, because mysql_eof()
returns only a boolean value whereas the error functions indicate a reason for the error when one occurs.)
8.4.3.48 Return Values
Zero if no error occurred. Non-zero if the end of the result set has been reached.
8.4.3.49 Errors
None.
8.4.3.50 Example
The following example shows how you might use mysql_eof()
:
mysql_query(&mysql,"SELECT * FROM some_table"); result = mysql_use_result(&mysql); while((row = mysql_fetch_row(result))) { // do something with data } if(!mysql_eof(result)) // mysql_fetch_row() failed due to an error { fprintf(stderr, "Error: %s\n", mysql_error(&mysql)); }
However, you can achieve the same effect with the standard MySQL error functions:
mysql_query(&mysql,"SELECT * FROM some_table"); result = mysql_use_result(&mysql); while((row = mysql_fetch_row(result))) { // do something with data } if(mysql_errno(&mysql)) // mysql_fetch_row() failed due to an error { fprintf(stderr, "Error: %s\n", mysql_error(&mysql)); }
8.4.3.51 mysql_errno()
unsigned int mysql_errno(MYSQL *mysql)
8.4.3.52 Description
For the connection specified by mysql
, mysql_errno()
returns the error code for the most recently invoked API function that can succeed or fail. A return value of zero means that no error occurred. Client error message numbers are listed in the MySQL `errmsg.h' header file. Server error message numbers are listed in `mysqld_error.h'. In the MySQL source distribution you can find a complete list of error messages and error numbers in the file `Docs/mysqld_error.txt'.
8.4.3.53 Return Values
An error code value. Zero if no error occurred.
8.4.3.54 Errors
None.
8.4.3.55 mysql_error()
char *mysql_error(MYSQL *mysql)
8.4.3.56 Description
For the connection specified by mysql
, mysql_error()
returns the error message for the most recently invoked API function that can succeed or fail. An empty string (""
) is returned if no error occurred. This means the following two tests are equivalent:
if(mysql_errno(&mysql)) { // an error occurred } if(mysql_error(&mysql)[0] != '\0') { // an error occurred }
The language of the client error messages may be changed by recompiling the MySQL client library. Currently you can choose error messages in several different languages. See section 4.6.2 Non-English Error Messages.
8.4.3.57 Return Values
A character string that describes the error. An empty string if no error occurred.
8.4.3.58 Errors
None.
8.4.3.59 mysql_escape_string()
You should use mysql_real_escape_string()
instead!
This is identical to mysql_real_escape_string()
except that it takes the connection as the first argument. mysql_real_escape_string()
will escape the string according to the current character set while mysql_escape_string()
does not respect the current charset setting.
8.4.3.60 mysql_fetch_field()
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)
8.4.3.61 Description
Returns the definition of one column of a result set as a MYSQL_FIELD
structure. Call this function repeatedly to retrieve information about all columns in the result set. mysql_fetch_field()
returns NULL
when no more fields are left.
mysql_fetch_field()
is reset to return information about the first field each time you execute a new SELECT
query. The field returned by mysql_fetch_field()
is also affected by calls to mysql_field_seek()
.
If you've called mysql_query()
to perform a SELECT
on a table but have not called mysql_store_result()
, MySQL returns the default blob length (8K bytes) if you call mysql_fetch_field()
to ask for the length of a BLOB
field. (The 8K size is chosen because MySQL doesn't know the maximum length for the BLOB
. This should be made configurable sometime.) Once you've retrieved the result set, field->max_length
contains the length of the largest value for this column in the specific query.
8.4.3.62 Return Values
The MYSQL_FIELD
structure for the current column. NULL
if no columns are left.
8.4.3.63 Errors
None.
8.4.3.64 Example
MYSQL_FIELD *field; while((field = mysql_fetch_field(result))) { printf("field name %s\n", field->name); }
8.4.3.65 mysql_fetch_fields()
MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result)
8.4.3.66 Description
Returns an array of all MYSQL_FIELD
structures for a result set. Each structure provides the field definition for one column of the result set.
8.4.3.67 Return Values
An array of MYSQL_FIELD
structures for all columns of a result set.
8.4.3.68 Errors
None.
8.4.3.69 Example
unsigned int num_fields; unsigned int i; MYSQL_FIELD *fields; num_fields = mysql_num_fields(result); fields = mysql_fetch_fields(result); for(i = 0; i < num_fields; i++) { printf("Field %u is %s\n", i, fields[i].name); }
8.4.3.70 mysql_fetch_field_direct()
MYSQL_FIELD *mysql_fetch_field_direct(MYSQL_RES *result, unsigned int fieldnr)
8.4.3.71 Description
Given a field number fieldnr
for a column within a result set, returns that column's field definition as a MYSQL_FIELD
structure. You may use this function to retrieve the definition for an arbitrary column. The value of fieldnr
should be in the range from 0 to mysql_num_fields(result)-1
.
8.4.3.72 Return Values
The MYSQL_FIELD
structure for the specified column.
8.4.3.73 Errors
None.
8.4.3.74 Example
unsigned int num_fields; unsigned int i; MYSQL_FIELD *field; num_fields = mysql_num_fields(result); for(i = 0; i < num_fields; i++) { field = mysql_fetch_field_direct(result, i); printf("Field %u is %s\n", i, field->name); }
8.4.3.75 mysql_fetch_lengths()
unsigned long *mysql_fetch_lengths(MYSQL_RES *result)
8.4.3.76 Description
Returns the lengths of the columns of the current row within a result set. If you plan to copy field values, this length information is also useful for optimization, because you can avoid calling strlen()
. In addition, if the result set contains binary data, you must use this function to determine the size of the data, because strlen()
returns incorrect results for any field containing null characters.
The length for empty columns and for columns containing NULL
values is zero. To see how to distinguish these two cases, see the description for mysql_fetch_row()
.
8.4.3.77 Return Values
An array of unsigned long integers representing the size of each column (not including any terminating null characters). NULL
if an error occurred.
8.4.3.78 Errors
mysql_fetch_lengths()
is valid only for the current row of the result set. It returns NULL
if you call it before calling mysql_fetch_row()
or after retrieving all rows in the result.
8.4.3.79 Example
MYSQL_ROW row; unsigned long *lengths; unsigned int num_fields; unsigned int i; row = mysql_fetch_row(result); if (row) { num_fields = mysql_num_fields(result); lengths = mysql_fetch_lengths(result); for(i = 0; i < num_fields; i++) { printf("Column %u is %lu bytes in length.\n", i, lengths[i]); } }
8.4.3.80 mysql_fetch_row()
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)
8.4.3.81 Description
Retrieves the next row of a result set. When used after mysql_store_result()
, mysql_fetch_row()
returns NULL
when there are no more rows to retrieve. When used after mysql_use_result()
, mysql_fetch_row()
returns NULL
when there are no more rows to retrieve or if an error occurred.
The number of values in the row is given by mysql_num_fields(result)
. If row
holds the return value from a call to mysql_fetch_row()
, pointers to the values are accessed as row[0]
to row[mysql_num_fields(result)-1]
. NULL
values in the row are indicated by NULL
pointers.
The lengths of the field values in the row may be obtained by calling mysql_fetch_lengths()
. Empty fields and fields containing NULL
both have length 0; you can distinguish these by checking the pointer for the field value. If the pointer is NULL
, the field is NULL
; otherwise the field is empty.
8.4.3.82 Return Values
A MYSQL_ROW
structure for the next row. NULL
if there are no more rows to retrieve or if an error occurred.
8.4.3.83 Errors
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.84 Example
MYSQL_ROW row; unsigned int num_fields; unsigned int i; num_fields = mysql_num_fields(result); while ((row = mysql_fetch_row(result))) { unsigned long *lengths; lengths = mysql_fetch_lengths(result); for(i = 0; i < num_fields; i++) { printf("[%.*s] ", (int) lengths[i], row[i] ? row[i] : "NULL"); } printf("\n"); }
8.4.3.85 mysql_field_count()
unsigned int mysql_field_count(MYSQL *mysql)
If you are using a version of MySQL earlier than Version 3.22.24, you should use unsigned int mysql_num_fields(MYSQL *mysql)
instead.
8.4.3.86 Description
Returns the number of columns for the most recent query on the connection.
The normal use of this function is when mysql_store_result()
returned NULL
(and thus you have no result set pointer). In this case, you can call mysql_field_count()
to determine whether or not mysql_store_result()
should have produced a non-empty result. This allows the client program to take proper action without knowing whether or not the query was a SELECT
(or SELECT
-like) statement. The example shown below illustrates how this may be done.
See section 8.4.6.1 Why Is It that After mysql_query()
Returns Success, mysql_store_result()
Sometimes Returns NULL?
.
8.4.3.87 Return Values
An unsigned integer representing the number of fields in a result set.
8.4.3.88 Errors
None.
8.4.3.89 Example
MYSQL_RES *result; unsigned int num_fields; unsigned int num_rows; if (mysql_query(&mysql,query_string)) { // error } else // query succeeded, process any data returned by it { result = mysql_store_result(&mysql); if (result) // there are rows { num_fields = mysql_num_fields(result); // retrieve rows, then call mysql_free_result(result) } else // mysql_store_result() returned nothing; should it have? { if(mysql_field_count(&mysql) == 0) { // query does not return data // (it was not a SELECT) num_rows = mysql_affected_rows(&mysql); } else // mysql_store_result() should have returned data { fprintf(stderr, "Error: %s\n", mysql_error(&mysql)); } } }
An alternative is to replace the mysql_field_count(&mysql)
call with mysql_errno(&mysql)
. In this case, you are checking directly for an error from mysql_store_result()
rather than inferring from the value of mysql_field_count()
whether or not the statement was a SELECT
.
8.4.3.90 mysql_field_seek()
MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result, MYSQL_FIELD_OFFSET offset)
8.4.3.91 Description
Sets the field cursor to the given offset. The next call to mysql_fetch_field()
will retrieve the field definition of the column associated with that offset.
To seek to the beginning of a row, pass an offset
value of zero.
8.4.3.92 Return Values
The previous value of the field cursor.
8.4.3.93 Errors
None.
8.4.3.94 mysql_field_tell()
MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES *result)
8.4.3.95 Description
Returns the position of the field cursor used for the last mysql_fetch_field()
. This value can be used as an argument to mysql_field_seek()
.
8.4.3.96 Return Values
The current offset of the field cursor.
8.4.3.97 Errors
None.
8.4.3.98 mysql_free_result()
void mysql_free_result(MYSQL_RES *result)
8.4.3.99 Description
Frees the memory allocated for a result set by mysql_store_result()
, mysql_use_result()
, mysql_list_dbs()
, etc. When you are done with a result set, you must free the memory it uses by calling mysql_free_result()
.
8.4.3.100 Return Values
None.
8.4.3.101 Errors
None.
8.4.3.102 mysql_get_client_info()
char *mysql_get_client_info(void)
8.4.3.103 Description
Returns a string that represents the client library version.
8.4.3.104 Return Values
A character string that represents the MySQL client library version.
8.4.3.105 Errors
None.
8.4.3.106 mysql_get_host_info()
char *mysql_get_host_info(MYSQL *mysql)
8.4.3.107 Description
Returns a string describing the type of connection in use, including the server host name.
8.4.3.108 Return Values
A character string representing the server host name and the connection type.
8.4.3.109 Errors
None.
8.4.3.110 mysql_get_proto_info()
unsigned int mysql_get_proto_info(MYSQL *mysql)
8.4.3.111 Description
Returns the protocol version used by current connection.
8.4.3.112 Return Values
An unsigned integer representing the protocol version used by the current connection.
8.4.3.113 Errors
None.
8.4.3.114 mysql_get_server_info()
char *mysql_get_server_info(MYSQL *mysql)
8.4.3.115 Description
Returns a string that represents the server version number.
8.4.3.116 Return Values
A character string that represents the server version number.
8.4.3.117 Errors
None.
8.4.3.118 mysql_info()
char *mysql_info(MYSQL *mysql)
8.4.3.119 Description
Retrieves a string providing information about the most recently executed query, but only for the statements listed below. For other statements, mysql_info()
returns NULL
. The format of the string varies depending on the type of query, as described below. The numbers are illustrative only; the string will contain values appropriate for the query.
-
String format:
Records: 100 Duplicates: 0 Warnings: 0
-
String format:
Records: 3 Duplicates: 0 Warnings: 0
-
String format:
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
-
String format:
Records: 3 Duplicates: 0 Warnings: 0
-
String format:
Rows matched: 40 Changed: 40 Warnings: 0
INSERT INTO ... SELECT ...
INSERT INTO ... VALUES (...),(...),(...)...
LOAD DATA INFILE ...
ALTER TABLE
UPDATE
Note that mysql_info()
returns a non-NULL
value for the INSERT ... VALUES
statement only if multiple value lists are specified in the statement.
8.4.3.120 Return Values
A character string representing additional information about the most recently executed query. NULL
if no information is available for the query.
8.4.3.121 Errors
None.
8.4.3.122 mysql_init()
MYSQL *mysql_init(MYSQL *mysql)
8.4.3.123 Description
Allocates or initializes a MYSQL
object suitable for mysql_real_connect()
. If mysql
is a NULL
pointer, the function allocates, initializes, and returns a new object. Otherwise the object is initialized and the address of the object is returned. If mysql_init()
allocates a new object, it will be freed when mysql_close()
is called to close the connection.
8.4.3.124 Return Values
An initialized MYSQL*
handle. NULL
if there was insufficient memory to allocate a new object.
8.4.3.125 Errors
In case of insufficient memory, NULL
is returned.
8.4.3.126 mysql_insert_id()
my_ulonglong mysql_insert_id(MYSQL *mysql)
8.4.3.127 Description
Returns the ID generated for an AUTO_INCREMENT
column by the previous query. Use this function after you have performed an INSERT
query into a table that contains an AUTO_INCREMENT
field.
Note that mysql_insert_id()
returns 0
if the previous query does not generate an AUTO_INCREMENT
value. If you need to save the value for later, be sure to call mysql_insert_id()
immediately after the query that generates the value.
mysql_insert_id()
is updated after INSERT
and UPDATE
statements that generate an AUTO_INCREMENT
value or that set a column value to LAST_INSERT_ID(expr)
. See section 6.3.5.2 Miscellaneous Functions.
Also note that the value of the SQL LAST_INSERT_ID()
function always contains the most recently generated AUTO_INCREMENT
value, and is not reset between queries because the value of that function is maintained in the server.
8.4.3.128 Return Values
The value of the AUTO_INCREMENT
field that was updated by the previous query. Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT
value.
8.4.3.129 Errors
None.
8.4.3.130 mysql_kill()
int mysql_kill(MYSQL *mysql, unsigned long pid)
8.4.3.131 Description
Asks the server to kill the thread specified by pid
.
8.4.3.132 Return Values
Zero for success. Non-zero if an error occurred.
8.4.3.133 Errors
- Commands were executed in an improper order.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.134 mysql_list_dbs()
MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild)
8.4.3.135 Description
Returns a result set consisting of database names on the server that match the simple regular expression specified by the wild
parameter. wild
may contain the wild-card characters `%' or `_', or may be a NULL
pointer to match all databases. Calling mysql_list_dbs()
is similar to executing the query SHOW databases [LIKE wild]
.
You must free the result set with mysql_free_result()
.
8.4.3.136 Return Values
A MYSQL_RES
result set for success. NULL
if an error occurred.
8.4.3.137 Errors
- Commands were executed in an improper order.
- Out of memory.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_OUT_OF_MEMORY
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.138 mysql_list_fields()
MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char *wild)
8.4.3.139 Description
Returns a result set consisting of field names in the given table that match the simple regular expression specified by the wild
parameter. wild
may contain the wild-card characters `%' or `_', or may be a NULL
pointer to match all fields. Calling mysql_list_fields()
is similar to executing the query SHOW COLUMNS FROM tbl_name [LIKE wild]
.
Note that it's recommended that you use SHOW COLUMNS FROM tbl_name
instead of mysql_list_fields()
.
You must free the result set with mysql_free_result()
.
8.4.3.140 Return Values
A MYSQL_RES
result set for success. NULL
if an error occurred.
8.4.3.141 Errors
- Commands were executed in an improper order.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.142 mysql_list_processes()
MYSQL_RES *mysql_list_processes(MYSQL *mysql)
8.4.3.143 Description
Returns a result set describing the current server threads. This is the same kind of information as that reported by mysqladmin processlist
or a SHOW PROCESSLIST
query.
You must free the result set with mysql_free_result()
.
8.4.3.144 Return Values
A MYSQL_RES
result set for success. NULL
if an error occurred.
8.4.3.145 Errors
- Commands were executed in an improper order.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.146 mysql_list_tables()
MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char *wild)
8.4.3.147 Description
Returns a result set consisting of table names in the current database that match the simple regular expression specified by the wild
parameter. wild
may contain the wild-card characters `%' or `_', or may be a NULL
pointer to match all tables. Calling mysql_list_tables()
is similar to executing the query SHOW tables [LIKE wild]
.
You must free the result set with mysql_free_result()
.
8.4.3.148 Return Values
A MYSQL_RES
result set for success. NULL
if an error occurred.
8.4.3.149 Errors
- Commands were executed in an improper order.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.150 mysql_num_fields()
unsigned int mysql_num_fields(MYSQL_RES *result)
or
unsigned int mysql_num_fields(MYSQL *mysql)
The second form doesn't work on MySQL Version 3.22.24 or newer. To pass a MYSQL*
argument, you must use unsigned int mysql_field_count(MYSQL *mysql)
instead.
8.4.3.151 Description
Returns the number of columns in a result set.
Note that you can get the number of columns either from a pointer to a result set or to a connection handle. You would use the connection handle if mysql_store_result()
or mysql_use_result()
returned NULL
(and thus you have no result set pointer). In this case, you can call mysql_field_count()
to determine whether or not mysql_store_result()
should have produced a non-empty result. This allows the client program to take proper action without knowing whether or not the query was a SELECT
(or SELECT
-like) statement. The example shown below illustrates how this may be done.
See section 8.4.6.1 Why Is It that After mysql_query()
Returns Success, mysql_store_result()
Sometimes Returns NULL?
.
8.4.3.152 Return Values
An unsigned integer representing the number of fields in a result set.
8.4.3.153 Errors
None.
8.4.3.154 Example
MYSQL_RES *result; unsigned int num_fields; unsigned int num_rows; if (mysql_query(&mysql,query_string)) { // error } else // query succeeded, process any data returned by it { result = mysql_store_result(&mysql); if (result) // there are rows { num_fields = mysql_num_fields(result); // retrieve rows, then call mysql_free_result(result) } else // mysql_store_result() returned nothing; should it have? { if (mysql_errno(&mysql)) { fprintf(stderr, "Error: %s\n", mysql_error(&mysql)); } else if (mysql_field_count(&mysql) == 0) { // query does not return data // (it was not a SELECT) num_rows = mysql_affected_rows(&mysql); } } }
An alternative (if you KNOW that your query should have returned a result set) is to replace the mysql_errno(&mysql)
call with a check if mysql_field_count(&mysql)
is = 0. This will only happen if something went wrong.
8.4.3.155 mysql_num_rows()
my_ulonglong mysql_num_rows(MYSQL_RES *result)
8.4.3.156 Description
Returns the number of rows in the result set.
The use of mysql_num_rows()
depends on whether you use mysql_store_result()
or mysql_use_result()
to return the result set. If you use mysql_store_result()
, mysql_num_rows()
may be called immediately. If you use mysql_use_result()
, mysql_num_rows()
will not return the correct value until all the rows in the result set have been retrieved.
8.4.3.157 Return Values
The number of rows in the result set.
8.4.3.158 Errors
None.
8.4.3.159 mysql_options()
int mysql_options(MYSQL *mysql, enum mysql_option option, const char *arg)
8.4.3.160 Description
Can be used to set extra connect options and affect behavior for a connection. This function may be called multiple times to set several options.
mysql_options()
should be called after mysql_init()
and before mysql_connect()
or mysql_real_connect()
.
The option
argument is the option that you want to set; the arg
argument is the value for the option. If the option is an integer, then arg
should point to the value of the integer.
Possible options values:
Option | Argument type | Function |
MYSQL_OPT_CONNECT_TIMEOUT | unsigned int * | Connect timeout in seconds. |
MYSQL_OPT_COMPRESS | Not used | Use the compressed client/server protocol. |
MYSQL_OPT_NAMED_PIPE | Not used | Use named pipes to connect to a MySQL server on NT. |
MYSQL_INIT_COMMAND | char * | Command to execute when connecting to the MySQL server. Will automatically be re-executed when reconnecting. |
MYSQL_READ_DEFAULT_FILE | char * | Read options from the named option file instead of from `my.cnf'. |
MYSQL_READ_DEFAULT_GROUP | char * | Read options from the named group from `my.cnf' or the file specified with MYSQL_READ_DEFAULT_FILE . |
Note that the group client
is always read if you use MYSQL_READ_DEFAULT_FILE
or MYSQL_READ_DEFAULT_GROUP
.
The specified group in the option file may contain the following options:
connect_timeout | Connect timeout in seconds. On Linux this timeout is also used for waiting for the first answer from the server. |
compress | Use the compressed client/server protocol. |
database | Connect to this database if no database was specified in the connect command. |
debug | Debug options. |
host | Default host name. |
init-command | Command to execute when connecting to MySQL server. Will automatically be re-executed when reconnecting. |
interactive-timeout | Same as specifying CLIENT_INTERACTIVE to mysql_real_connect() . See section 8.4.3.171 mysql_real_connect() . |
password | Default password. |
pipe | Use named pipes to connect to a MySQL server on NT. |
port | Default port number. |
return-found-rows | Tell mysql_info() to return found rows instead of updated rows when using UPDATE . |
socket | Default socket number. |
user | Default user. |
Note that timeout
has been replaced by connect_timeout
, but timeout
will still work for a while.
For more information about option files, see section 4.1.2 my.cnf Option Files.
8.4.3.161 Return Values
Zero for success. Non-zero if you used an unknown option.
8.4.3.162 Example
MYSQL mysql; mysql_init(&mysql); mysql_options(&mysql,MYSQL_OPT_COMPRESS,0); mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"odbc"); if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0)) { fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&mysql)); }
The above requests the client to use the compressed client/server protocol and read the additional options from the odbc
section in the my.cnf
file.
8.4.3.163 mysql_ping()
int mysql_ping(MYSQL *mysql)
8.4.3.164 Description
Checks whether or not the connection to the server is working. If it has gone down, an automatic reconnection is attempted.
This function can be used by clients that remain idle for a long while, to check whether or not the server has closed the connection and reconnect if necessary.
8.4.3.165 Return Values
Zero if the server is alive. Non-zero if an error occurred.
8.4.3.166 Errors
- Commands were executed in an improper order.
- The MySQL server has gone away.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_UNKNOWN_ERROR
8.4.3.167 mysql_query()
int mysql_query(MYSQL *mysql, const char *query)
8.4.3.168 Description
Executes the SQL query pointed to by the null-terminated string query
. The query must consist of a single SQL statement. You should not add a terminating semicolon (`;') or \g
to the statement.
mysql_query()
cannot be used for queries that contain binary data; you should use mysql_real_query()
instead. (Binary data may contain the `\0' character, which mysql_query()
interprets as the end of the query string.)
If you want to know if the query should return a result set or not, you can use mysql_field_count()
to check for this. See section 8.4.3.85 mysql_field_count()
.
8.4.3.169 Return Values
Zero if the query was successful. Non-zero if an error occurred.
8.4.3.170 Errors
- Commands were executed in an improper order.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.171 mysql_real_connect()
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)
8.4.3.172 Description
mysql_real_connect()
attempts to establish a connection to a MySQL database engine running on host
. mysql_real_connect()
must complete successfully before you can execute any of the other API functions, with the exception of mysql_get_client_info()
.
The parameters are specified as follows:
- The first parameter should be the address of an existing
MYSQL
structure. Before callingmysql_real_connect()
you must callmysql_init()
to initialize theMYSQL
structure. You can change a lot of connect options with themysql_options()
call. See section 8.4.3.159mysql_options()
. - The value of
host
may be either a hostname or an IP address. Ifhost
isNULL
or the string"localhost"
, a connection to the local host is assumed. If the OS supports sockets (Unix) or named pipes (Windows), they are used instead of TCP/IP to connect to the server. - The
user
parameter contains the user's MySQL login ID. Ifuser
isNULL
, the current user is assumed. Under Unix, this is the current login name. Under Windows ODBC, the current user name must be specified explicitly. See section 8.3.2 How to Fill in the Various Fields in the ODBC Administrator Program. - The
passwd
parameter contains the password foruser
. Ifpasswd
isNULL
, only entries in theuser
table for the user that have a blank (empty) password field will be checked for a match. This allows the database administrator to set up the MySQL privilege system in such a way that users get different privileges depending on whether or not they have specified a password. NOTE: Do not attempt to encrypt the password before callingmysql_real_connect()
; password encryption is handled automatically by the client API. db
is the database name. Ifdb
is notNULL
, the connection will set the default database to this value.- If
port
is not 0, the value will be used as the port number for the TCP/IP connection. Note that thehost
parameter determines the type of the connection. - If
unix_socket
is notNULL
, the string specifies the socket or named pipe that should be used. Note that thehost
parameter determines the type of the connection. - The value of
client_flag
is usually 0, but can be set to a combination of the following flags in very special circumstances:Flag name Flag meaning mysqld
to be more ODBC-friendly.CLIENT_COMPRESS
Use compression protocol. CLIENT_FOUND_ROWS
Return the number of found (matched) rows, not the number of affected rows. CLIENT_IGNORE_SPACE
Allow spaces after function names. Makes all functions names reserved words. CLIENT_INTERACTIVE
Allow interactive_timeout
seconds (instead ofwait_timeout
seconds) of inactivity before closing the connection.CLIENT_NO_SCHEMA
Don't allow the db_name.tbl_name.col_name
syntax. This is for ODBC. It causes the parser to generate an error if you use that syntax, which is useful for trapping bugs in some ODBC programs.CLIENT_ODBC
The client is an ODBC client. This changes CLIENT_SSL
Use SSL (encrypted protocol).
8.4.3.173 Return Values
A MYSQL*
connection handle if the connection was successful, NULL
if the connection was unsuccessful. For a successful connection, the return value is the same as the value of the first parameter, unless you pass NULL
for that parameter.
8.4.3.174 Errors
- Failed to connect to the MySQL server.
- Failed to connect to the local MySQL server.
- Failed to create an IP socket.
- Out of memory.
- Failed to create a Unix socket.
- Failed to find the IP address for the hostname.
-
A protocol mismatch resulted from attempting to connect to a server with a client library that uses a different protocol version. This can happen if you use a very old client library to connect to a new server that wasn't started with the
--old-protocol
option. - Failed to create a named pipe on Windows.
- Failed to wait for a named pipe on Windows.
- Failed to get a pipe handler on Windows.
-
If
connect_timeout
> 0 and it took longer thenconnect_timeout
seconds to connect to the server or if the server died while executing theinit-command
.
CR_CONN_HOST_ERROR
CR_CONNECTION_ERROR
CR_IPSOCK_ERROR
CR_OUT_OF_MEMORY
CR_SOCKET_CREATE_ERROR
CR_UNKNOWN_HOST
CR_VERSION_ERROR
CR_NAMEDPIPEOPEN_ERROR
CR_NAMEDPIPEWAIT_ERROR
CR_NAMEDPIPESETSTATE_ERROR
CR_SERVER_LOST
8.4.3.175 Example
MYSQL mysql; mysql_init(&mysql); mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"your_prog_name"); if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0)) { fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&mysql)); }
By using mysql_options()
the MySQL library will read the [client]
and your_prog_name
sections in the my.cnf
file which will ensure that your program will work, even if someone has set up MySQL in some non-standard way.
Note that upon connection, mysql_real_connect()
sets the reconnect
flag (part of the MYSQL structure) to a value of 1
. This flag indicates, in the event that a query cannot be performed because of a lost connection, to try reconnecting to the server before giving up.
8.4.3.176 mysql_real_escape_string()
unsigned int mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned int length)
8.4.3.177 Description
This function is used to create a legal SQL string that you can use in a SQL statement. See section 6.1.1.1 Strings.
The string in from
is encoded to an escaped SQL string, taking into account the current character set of the connection. The result is placed in to
and a terminating null byte is appended. Characters encoded are NUL
(ASCII 0), `\n', `\r', `\', `'', `"', and Control-Z (see section 6.1.1 Literals: How to Write Strings and Numbers).
The string pointed to by from
must be length
bytes long. You must allocate the to
buffer to be at least length*2+1
bytes long. (In the worse case, each character may need to be encoded as using two bytes, and you need room for the terminating null byte.) When mysql_escape_string()
returns, the contents of to
will be a null-terminated string. The return value is the length of the encoded string, not including the terminating null character.
8.4.3.178 Example
char query[1000],*end; end = strmov(query,"INSERT INTO test_table values("); *end++ = '\''; end += mysql_real_escape_string(&mysql, end,"What's this",11); *end++ = '\''; *end++ = ','; *end++ = '\''; end += mysql_real_escape_string(&mysql, end,"binary data: \0\r\n",16); *end++ = '\''; *end++ = ')'; if (mysql_real_query(&mysql,query,(unsigned int) (end - query))) { fprintf(stderr, "Failed to insert row, Error: %s\n", mysql_error(&mysql)); }
The strmov()
function used in the example is included in the mysqlclient
library and works like strcpy()
but returns a pointer to the terminating null of the first parameter.
8.4.3.179 Return Values
The length of the value placed into to
, not including the terminating null character.
8.4.3.180 Errors
None.
8.4.3.181 mysql_real_query()
int mysql_real_query(MYSQL *mysql, const char *query, unsigned int length)
8.4.3.182 Description
Executes the SQL query pointed to by query
, which should be a string length
bytes long. The query must consist of a single SQL statement. You should not add a terminating semicolon (`;') or \g
to the statement.
You must use mysql_real_query()
rather than mysql_query()
for queries that contain binary data, because binary data may contain the `\0' character. In addition, mysql_real_query()
is faster than mysql_query()
because it does not call strlen()
on the query string.
If you want to know if the query should return a result set or not, you can use mysql_field_count()
to check for this. See section 8.4.3.85 mysql_field_count()
.
8.4.3.183 Return Values
Zero if the query was successful. Non-zero if an error occurred.
8.4.3.184 Errors
- Commands were executed in an improper order.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.185 mysql_reload()
int mysql_reload(MYSQL *mysql)
8.4.3.186 Description
Asks the MySQL server to reload the grant tables. The connected user must have the reload privilege.
This function is deprecated. It is preferable to use mysql_query()
to issue a SQL FLUSH PRIVILEGES
statement instead.
8.4.3.187 Return Values
Zero for success. Non-zero if an error occurred.
8.4.3.188 Errors
- Commands were executed in an improper order.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.189 mysql_row_seek()
MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset)
8.4.3.190 Description
Sets the row cursor to an arbitrary row in a query result set. This requires that the result set structure contains the entire result of the query, so mysql_row_seek()
may be used in conjunction only with mysql_store_result()
, not with mysql_use_result()
.
The offset should be a value returned from a call to mysql_row_tell()
or to mysql_row_seek()
. This value is not simply a row number; if you want to seek to a row within a result set using a row number, use mysql_data_seek()
instead.
8.4.3.191 Return Values
The previous value of the row cursor. This value may be passed to a subsequent call to mysql_row_seek()
.
8.4.3.192 Errors
None.
8.4.3.193 mysql_row_tell()
MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result)
8.4.3.194 Description
Returns the current position of the row cursor for the last mysql_fetch_row()
. This value can be used as an argument to mysql_row_seek()
.
You should use mysql_row_tell()
only after mysql_store_result()
, not after mysql_use_result()
.
8.4.3.195 Return Values
The current offset of the row cursor.
8.4.3.196 Errors
None.
8.4.3.197 mysql_select_db()
int mysql_select_db(MYSQL *mysql, const char *db)
8.4.3.198 Description
Causes the database specified by db
to become the default (current) database on the connection specified by mysql
. In subsequent queries, this database is the default for table references that do not include an explicit database specifier.
mysql_select_db()
fails unless the connected user can be authenticated as having permission to use the database.
8.4.3.199 Return Values
Zero for success. Non-zero if an error occurred.
8.4.3.200 Errors
- Commands were executed in an improper order.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.201 mysql_shutdown()
int mysql_shutdown(MYSQL *mysql)
8.4.3.202 Description
Asks the database server to shut down. The connected user must have shutdown privileges.
8.4.3.203 Return Values
Zero for success. Non-zero if an error occurred.
8.4.3.204 Errors
- Commands were executed in an improper order.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.205 mysql_stat()
char *mysql_stat(MYSQL *mysql)
8.4.3.206 Description
Returns a character string containing information similar to that provided by the mysqladmin status
command. This includes uptime in seconds and the number of running threads, questions, reloads, and open tables.
8.4.3.207 Return Values
A character string describing the server status. NULL
if an error occurred.
8.4.3.208 Errors
- Commands were executed in an improper order.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.209 mysql_store_result()
MYSQL_RES *mysql_store_result(MYSQL *mysql)
8.4.3.210 Description
You must call mysql_store_result()
or mysql_use_result()
for every query that successfully retrieves data (SELECT
, SHOW
, DESCRIBE
, EXPLAIN
).
You don't have to call mysql_store_result()
or mysql_use_result()
for other queries, but it will not do any harm or cause any notable performance if you call mysql_store_result()
in all cases. You can detect if the query didn't have a result set by checking if mysql_store_result()
returns 0 (more about this later one).
If you want to know if the query should return a result set or not, you can use mysql_field_count()
to check for this. See section 8.4.3.85 mysql_field_count()
.
mysql_store_result()
reads the entire result of a query to the client, allocates a MYSQL_RES
structure, and places the result into this structure.
mysql_store_results()
returns a null pointer if the query didn't return a result set (if the query was, for example, an INSERT
statement).
mysql_store_results()
also returns a null pointer if reading of the result set failed. You can check if you got an error by checking if mysql_error()
doesn't return a null pointer, if mysql_errno()
returns <> 0, or if mysql_field_count()
returns <> 0.
An empty result set is returned if there are no rows returned. (An empty result set differs from a null pointer as a return value.)
Once you have called mysql_store_result()
and got a result back that isn't a null pointer, you may call mysql_num_rows()
to find out how many rows are in the result set.
You can call mysql_fetch_row()
to fetch rows from the result set, or mysql_row_seek()
and mysql_row_tell()
to obtain or set the current row position within the result set.
You must call mysql_free_result()
once you are done with the result set.
See section 8.4.6.1 Why Is It that After mysql_query()
Returns Success, mysql_store_result()
Sometimes Returns NULL?
.
8.4.3.211 Return Values
A MYSQL_RES
result structure with the results. NULL
if an error occurred.
8.4.3.212 Errors
- Commands were executed in an improper order.
- Out of memory.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_OUT_OF_MEMORY
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.3.213 mysql_thread_id()
unsigned long mysql_thread_id(MYSQL *mysql)
8.4.3.214 Description
Returns the thread ID of the current connection. This value can be used as an argument to mysql_kill()
to kill the thread.
If the connection is lost and you reconnect with mysql_ping()
, the thread ID will change. This means you should not get the thread ID and store it for later. You should get it when you need it.
8.4.3.215 Return Values
The thread ID of the current connection.
8.4.3.216 Errors
None.
8.4.3.217 mysql_use_result()
MYSQL_RES *mysql_use_result(MYSQL *mysql)
8.4.3.218 Description
You must call mysql_store_result()
or mysql_use_result()
for every query that successfully retrieves data (SELECT
, SHOW
, DESCRIBE
, EXPLAIN
).
mysql_use_result()
initiates a result set retrieval but does not actually read the result set into the client like mysql_store_result()
does. Instead, each row must be retrieved individually by making calls to mysql_fetch_row()
. This reads the result of a query directly from the server without storing it in a temporary table or local buffer, which is somewhat faster and uses much less memory than mysql_store_result()
. The client will only allocate memory for the current row and a communication buffer that may grow up to max_allowed_packet
bytes.
On the other hand, you shouldn't use mysql_use_result()
if you are doing a lot of processing for each row on the client side, or if the output is sent to a screen on which the user may type a ^S
(stop scroll). This will tie up the server and prevent other threads from updating any tables from which the data is being fetched.
When using mysql_use_result()
, you must execute mysql_fetch_row()
until a NULL
value is returned, otherwise the unfetched rows will be returned as part of the result set for your next query. The C API will give the error Commands out of sync; You can't run this command now
if you forget to do this!
You may not use mysql_data_seek()
, mysql_row_seek()
, mysql_row_tell()
, mysql_num_rows()
, or mysql_affected_rows()
with a result returned from mysql_use_result()
, nor may you issue other queries until the mysql_use_result()
has finished. (However, after you have fetched all the rows, mysql_num_rows()
will accurately return the number of rows fetched.)
You must call mysql_free_result()
once you are done with the result set.
8.4.3.219 Return Values
A MYSQL_RES
result structure. NULL
if an error occurred.
8.4.3.220 Errors
- Commands were executed in an improper order.
- Out of memory.
- The MySQL server has gone away.
- The connection to the server was lost during the query.
- An unknown error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_OUT_OF_MEMORY
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
8.4.4 C Threaded Function Descriptions
You need to use the following functions when you want to create a threaded client. See section 8.4.8 How to Make a Threaded Client.
8.4.4.1 my_init()
8.4.4.2 Description
This function needs to be called once in the program before calling any MySQL function. This initializes some global variables that MySQL needs. If you are using a thread-safe client library, this will also call mysql_thread_init()
for this thread.
This is automatically called by mysql_init()
, mysql_server_init()
and mysql_connect()
.
8.4.4.3 Return Values
none.
8.4.4.4 mysql_thread_init()
8.4.4.5 Description
This function needs to be called for each created thread to initialize thread specific variables.
This is automatically called by my_init()
and mysql_connect()
.
8.4.4.6 Return Values
none.
8.4.4.7 mysql_thread_end()
8.4.4.8 Description
This function needs to be called before calling pthread_exit()
to free memory allocated by mysql_thread_init()
.
Note that this function is not invoked automatically by the client library. It must be called explicitly to avoid a memory leak.
8.4.4.9 Return Values
none.
8.4.5 C Embedded Server Function Descriptions
You must use the following functions if you want to allow your application to be linked against the embedded MySQL server library. See section 8.4.9 libmysqld, the Embedded MySQL Server Library.
If the program is linked with -lmysqlclient
instead of -lmysqld
, these functions do nothing. This makes it possible to choose between using the embedded MySQL server and a stand-alone server without modifying any code.
8.4.5.1 mysql_server_init()
void mysql_server_init(int argc, const char **argv, const char **groups)
8.4.5.2 Description
This function must be called once in the program before calling any other MySQL function. It starts up the server and initializes any subsystems (mysys
, InnoDB, etc.) that the server uses. If this function is not called, the program will crash.
The argc
and argv
arguments are analogous to the arguments to main()
. The first element of argv
is ignored (it typically contains the program name). For convenience, argc
may be 0
(zero) if there are no command-line arguments for the server.
The NULL
-terminated list of strings in groups
selects which groups in the option files will be active. See section 4.1.2 my.cnf Option Files. For convenience, groups
may be NULL
, in which case the [server]
group will be active.
8.4.5.3 Example
#include <mysql.h> #include <stdlib.h> static char *server_args[] = { "this_program", /* this string is not used */ "--datadir=.", "--set-variable=key_buffer_size=32M" }; static char *server_groups[] = { "server", "this_program_SERVER", (char *)NULL }; int main(void) { mysql_server_init(sizeof(server_args) / sizeof(char *), server_args, server_groups); /* Use any MySQL API functions here */ mysql_server_end(); return EXIT_SUCCESS; }
8.4.5.4 Return Values
none.
8.4.5.5 mysql_server_end()
8.4.5.6 Description
This function must be called once in the program after all other MySQL functions. It shuts down the embedded server.
8.4.5.7 Return Values
none.
8.4.6 Common questions and problems when using the C API
8.4.6.1 Why Is It that After mysql_query()
Returns Success, mysql_store_result()
Sometimes Returns NULL?
It is possible for mysql_store_result()
to return NULL
following a successful call to mysql_query()
. When this happens, it means one of the following conditions occurred:
- There was a
malloc()
failure (for example, if the result set was too large). - The data couldn't be read (an error occurred on the connection).
- The query returned no data (for example, it was an
INSERT
,UPDATE
, orDELETE
).
You can always check whether or not the statement should have produced a non-empty result by calling mysql_field_count()
. If mysql_field_count()
returns zero, the result is empty and the last query was a statement that does not return values (for example, an INSERT
or a DELETE
). If mysql_field_count()
returns a non-zero value, the statement should have produced a non-empty result. See the description of the mysql_field_count()
function for an example.
You can test for an error by calling mysql_error()
or mysql_errno()
.
8.4.6.2 What Results Can I Get From a Query?
In addition to the result set returned by a query, you can also get the following information:
mysql_affected_rows()
returns the number of rows affected by the last query when doing anINSERT
,UPDATE
, orDELETE
. An exception is that ifDELETE
is used without aWHERE
clause, the table is re-created empty, which is much faster! In this case,mysql_affected_rows()
returns zero for the number of records affected.mysql_num_rows()
returns the number of rows in a result set. Withmysql_store_result()
,mysql_num_rows()
may be called as soon asmysql_store_result()
returns. Withmysql_use_result()
,mysql_num_rows()
may be called only after you have fetched all the rows withmysql_fetch_row()
.mysql_insert_id()
returns the ID generated by the last query that inserted a row into a table with anAUTO_INCREMENT
index. See section 8.4.3.126mysql_insert_id()
.- Some queries (
LOAD DATA INFILE ...
,INSERT INTO ... SELECT ...
,UPDATE
) return additional information. The result is returned bymysql_info()
. See the description formysql_info()
for the format of the string that it returns.mysql_info()
returns aNULL
pointer if there is no additional information.
8.4.6.3 How Can I Get the Unique ID for the Last Inserted Row?
If you insert a record in a table containing a column that has the AUTO_INCREMENT
attribute, you can get the most recently generated ID by calling the mysql_insert_id()
function.
You can also retrieve the ID by using the LAST_INSERT_ID()
function in a query string that you pass to mysql_query()
.
You can check if an AUTO_INCREMENT
index is used by executing the following code. This also checks if the query was an INSERT
with an AUTO_INCREMENT
index:
if (mysql_error(&mysql)[0] == 0 && mysql_num_fields(result) == 0 && mysql_insert_id(&mysql) != 0) { used_id = mysql_insert_id(&mysql); }
The most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT
column with a non-magic value (that is, a value that is not NULL
and not 0
).
If you want to use the ID that was generated for one table and insert it into a second table, you can use SQL statements like this:
INSERT INTO foo (auto,text) VALUES(NULL,'text'); # generate ID by inserting NULL INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text'); # use ID in second table
8.4.6.4 Problems Linking with the C API
When linking with the C API, the following errors may occur on some systems:
gcc -g -o client test.o -L/usr/local/lib/mysql -lmysqlclient -lsocket -lnsl Undefined first referenced symbol in file floor /usr/local/lib/mysql/libmysqlclient.a(password.o) ld: fatal: Symbol referencing errors. No output written to client
If this happens on your system, you must include the math library by adding -lm
to the end of the compile/link line.
8.4.7 Building Client Programs
If you compile MySQL clients that you've written yourself or that you obtain from a third party, they must be linked using the -lmysqlclient -lz
option on the link command. You may also need to specify a -L
option to tell the linker where to find the library. For example, if the library is installed in `/usr/local/mysql/lib', use -L/usr/local/mysql/lib -lmysqlclient -lz
on the link command.
For clients that use MySQL header files, you may need to specify a -I
option when you compile them (for example, -I/usr/local/mysql/include
), so the compiler can find the header files.
8.4.8 How to Make a Threaded Client
The client library is almost thread safe. The biggest problem is that the subroutines in `net.c' that read from sockets are not interrupt safe. This was done with the thought that you might want to have your own alarm that can break a long read to a server. If you install interrupt handlers for the SIGPIPE
interrupt, the socket handling should be thread safe.
In the older binaries we distribute on our Web site, the client libraries are not normally compiled with the thread-safe option (the Windows binaries are by default compiled to be thread safe). Newer binary distributions should have both a normal and a thread-safe client library.
To get a threaded client where you can interrupt the client from other threads and set timeouts when talking with the MySQL server, you should use the -lmysys
, -lstring
, and -ldbug
libraries and the net_serv.o
code that the server uses.
If you don't need interrupts or timeouts, you can just compile a thread safe client library (mysqlclient_r)
and use this. See section 8.4 MySQL C API. In this case you don't have to worry about the net_serv.o
object file or the other MySQL libraries.
When using a threaded client and you want to use timeouts and interrupts, you can make great use of the routines in the `thr_alarm.c' file. If you are using routines from the mysys
library, the only thing you must remember is to call my_init()
first! See section 8.4.4 C Threaded Function Descriptions.
All functions except mysql_real_connect()
are by default thread safe. The following notes describe how to compile a thread-safe client library and use it in a thread-safe manner. (The notes below for mysql_real_connect()
actually apply to mysql_connect()
as well, but because mysql_connect()
is deprecated, you should be using mysql_real_connect()
anyway.)
To make mysql_real_connect()
thread safe, you must recompile the client library with this command:
shell> ./configure --enable-thread-safe-client
This will create a thread-safe client library libmysqlclient_r
. --enable-thread-safe-client
. This library is thread safe per connection. You can let two threads share the same connection as long as you do the following:
- Two threads can't send a query to the MySQL at the same time on the same connection. In particular, you have to ensure that between a
mysql_query()
andmysql_store_result()
no other thread is using the same connection. - Many threads can access different result sets that are retrieved with
mysql_store_result()
. - If you use
mysql_use_result
, you have to ensure that no other thread is asking anything on the same connection until the result set is closed. However, it really is best for threaded clients that share the same connection to usemysql_use_result()
. - If you want to use multiple threads on the same connection, you must have a mutex lock around your
mysql_query()
andmysql_store_result()
call combination. Oncemysql_store_result()
is ready, the lock can be released and other threads may query the same connection. - If you program with POSIX threads, you can use
pthread_mutex_lock()
andpthread_mutex_unlock()
to establish and release a mutex lock.
You need to know the following if you have a thread that is calling MySQL functions, but that thread has not created the connection to the MySQL database:
When you call mysql_init()
or mysql_connect()
, MySQL will create a thread specific variable for the thread that is used by the debug library (among other things).
If you have in a thread call a MySQL function, before a thread has called mysql_init()
or mysql_connect()
, the thread will not have the necessary thread specific variables in place and you are likely to end up with a core dump sooner or later. The get things to work smoothly you have to do the following:
- Call
my_init()
at the start of your program if it calls any other MySQL function before callingmysql_real_connect()
. - Call
mysql_thread_init()
in the thread handler before calling any MySQL function. - In the thread, call
mysql_thread_end()
before callingpthread_exit()
. This will free the memory used by MySQL thread specific variables.
You may get some errors because of undefined symbols when linking your client with mysqlclient_r
. In most cases this is because you haven't included the thread libraries on the link/compile line.
8.4.9 libmysqld, the Embedded MySQL Server Library
8.4.9.1 Overview of the Embedded MySQL Server Library
The embedded MySQL server library makes it possible to run a full-featured MySQL server inside the client application. The main benefits are increased speed and more simple management for embedded applications.
8.4.9.2 Compiling Programs with libmysqld
Currently, all of the support libraries must be explicitly listed when linking with -lmysqld
. In the future, mysql_config --libmysqld-libs
will name the libraries to make this easier. Also, all of the supporting libraries will probably be incorporated into libmysqld at some time to simplify this even more.
The correct flags for compiling and linking a threaded program must be used, even if you do not directly call any thread functions in your code.
8.4.9.3 A Simple Embedded Server Example
This example program and makefile should work without any changes on a Linux or FreeBSD system. For other operating systems, minor changes will be needed. This example is designed to give enough details to understand the problem, without the clutter that is a necessary part of a real application.
To try out the example, create an `example' directory at the same level as the mysql-4.0 source directory. Save the `example.c' source and the `GNUmakefile' in the directory, and run GNU `make' from inside the `example' directory.
`example.c'
/* * A simple example client, using the embedded MySQL server library */ #include <mysql.h> #include <stdarg.h> #include <stdio.h> #include <stdlib.h> enum on_error { E_okay, E_warn, E_fail }; static void die(MYSQL *db, char *fmt, ...); MYSQL *db_connect(const char *dbname); void db_disconnect(MYSQL *db); void db_do_query(MYSQL *db, const char *query, enum on_error on_error); const char *server_groups[] = { "test_client_SERVER", "server", NULL }; int main(int argc, char **argv) { MYSQL *one, *two; /* This must be called before any other mysql functions. * * You can use mysql_server_init(0, NULL, NULL), and it will * initialize the server using groups = { "server", NULL }. * * In your $HOME/.my.cnf file, you probably want to put: [test_client_SERVER] language = /path/to/source/of/mysql/sql/share/english * You could, of course, modify argc and argv before passing * them to this function. Or you could create new ones in any * way you like. But all of the arguments in argv (except for * argv[0], which is the program name) should be valid options * for the MySQL server. * * If you link this client against the normal mysqlclient * library, this function is just a stub that does nothing. */ mysql_server_init(argc, argv, server_groups); one = db_connect("test"); two = db_connect(NULL); db_do_query(one, "show table status", E_fail); db_do_query(two, "show databases", E_fail); mysql_close(two); mysql_close(one); /* This must be called after all other mysql functions */ mysql_server_end(); exit(EXIT_SUCCESS); } void die(MYSQL *db, char *fmt, ...) { va_list ap; va_start(ap, fmt); vfprintf(stderr, fmt, ap); va_end(ap); putc('\n', stderr); if (db) db_disconnect(db); exit(EXIT_FAILURE); } MYSQL * db_connect(const char *dbname) { MYSQL *db = mysql_init(NULL); if (!db) die(db, "mysql_init failed: no memory"); mysql_options(db, MYSQL_READ_DEFAULT_GROUP, "simple"); if (!mysql_real_connect(db, NULL, NULL, NULL, dbname, 0, NULL, 0)) die(db, "mysql_real_connect failed: %s", mysql_error(db)); return db; } void db_disconnect(MYSQL *db) { mysql_close(db); } /* * show_query: this code is snagged from mysql.cc; this function * is intended to be used internally to db_do_query() */ static char * show_query(MYSQL *db) { MYSQL_RES *res; MYSQL_FIELD *field; MYSQL_ROW row; char sep[256], *psep = sep; char *is_num = 0; char *err = 0; unsigned int length = 1; /* initial "|" */ unsigned int off; if (!(res = mysql_store_result(db))) return mysql_error(db); if (!(is_num = malloc(mysql_num_fields(res)))) { err = "out of memory"; goto err; } /* set up */ *psep++ = '+'; while ((field = mysql_fetch_field(res))) { unsigned int len = strlen(field->name); if (len < field->max_length) len = field->max_length; if (len < 2 && !IS_NOT_NULL(field->flags)) len = 2; /* \N */ field->max_length = len + 1; /* bending the API... */ len += 2; length += len + 1; /* " " before, " |" after */ if (length >= 255) { err = "row too long"; goto err; } memset(psep, '-', len); psep += len; *psep++ = '+'; *psep = '\0'; } /* column headings */ puts(sep); mysql_field_seek(res,0); fputc('|',stdout); for (off=0; (field = mysql_fetch_field(res)) ; off++) { printf(" %-*s|",field->max_length, field->name); is_num[off]= IS_NUM(field->type); } fputc('\n',stdout); puts(sep); /* rows */ while ((row = mysql_fetch_row(res))) { (void) fputs("|",stdout); mysql_field_seek(res,0); for (off=0 ; off < mysql_num_fields(res); off++) { field = mysql_fetch_field(res); printf(is_num[off] ? "%*s |" : " %-*s|", field->max_length, row[off] ? (char*) row[off] : "NULL"); } (void) fputc('\n',stdout); } puts(sep); err: if (is_num) free(is_num); mysql_free_result(res); return err; } void db_do_query(MYSQL *db, const char *query, enum on_error on_error) { char *err = 0; if (mysql_query(db, query) != 0) goto err; if (mysql_field_count(db) > 0) { if ((err = show_query(db))) goto err; } else if (mysql_affected_rows(db)) printf("Affected rows: %lld [%s]\n", mysql_affected_rows(db), query); return; err: switch (on_error) { case E_okay: break; case E_warn: fprintf(stderr, "db_do_query failed: %s [%s]\n", err ? err : mysql_error(db), query); break; case E_fail: die(db, "db_do_query failed: %s [%s]", err ? err : mysql_error(db), query); break; } }
`GNUmakefile'
# Set this to your mysql source directory m := ../mysql-4.0 CC := cc CPPFLAGS := -I$m/include -D_THREAD_SAFE -D_REENTRANT CFLAGS := -g -W -Wall LDFLAGS := -static LDLIBS = $(embed_libs) -lz -lm -lcrypt ifneq (,$(shell grep FreeBSD /COPYRIGHT 2>/dev/null)) # FreeBSD LDFLAGS += -pthread else # Assume Linux LDLIBS += -lpthread endif # Standard libraries embed_libs := \ $m/libmysqld/.libs/libmysqld.a \ $m/isam/libnisam.a \ $m/myisam/libmyisam.a \ $m/heap/libheap.a \ $m/merge/libmerge.a \ $m/myisammrg/libmyisammrg.a # Optionally-built libraries ifneq (,$(shell test -r $m/innobase/usr/libusr.a && echo "yes")) embed_libs += \ $m/innobase/usr/libusr.a \ $m/innobase/odbc/libodbc.a \ $m/innobase/srv/libsrv.a \ $m/innobase/que/libque.a \ $m/innobase/srv/libsrv.a \ $m/innobase/dict/libdict.a \ $m/innobase/ibuf/libibuf.a \ $m/innobase/row/librow.a \ $m/innobase/pars/libpars.a \ $m/innobase/btr/libbtr.a \ $m/innobase/trx/libtrx.a \ $m/innobase/read/libread.a \ $m/innobase/usr/libusr.a \ $m/innobase/buf/libbuf.a \ $m/innobase/ibuf/libibuf.a \ $m/innobase/eval/libeval.a \ $m/innobase/log/liblog.a \ $m/innobase/fsp/libfsp.a \ $m/innobase/fut/libfut.a \ $m/innobase/fil/libfil.a \ $m/innobase/lock/liblock.a \ $m/innobase/mtr/libmtr.a \ $m/innobase/page/libpage.a \ $m/innobase/rem/librem.a \ $m/innobase/thr/libthr.a \ $m/innobase/com/libcom.a \ $m/innobase/sync/libsync.a \ $m/innobase/data/libdata.a \ $m/innobase/mach/libmach.a \ $m/innobase/ha/libha.a \ $m/innobase/dyn/libdyn.a \ $m/innobase/mem/libmem.a \ $m/innobase/sync/libsync.a \ $m/innobase/ut/libut.a \ $m/innobase/os/libos.a \ $m/innobase/ut/libut.a endif ifneq (,$(shell test -r $m/bdb/build_unix/libdb.a && echo "yes")) embed_libs += $m/bdb/build_unix/libdb.a endif # Support libraries embed_libs += \ $m/mysys/libmysys.a \ $m/strings/libmystrings.a \ $m/dbug/libdbug.a \ $m/regex/libregex.a # Optionally built support libraries ifneq (,$(shell test -r $m/readline/libreadline.a && echo "yes")) embed_libs += $m/readline/libreadline.a endif # This works for simple one-file test programs sources := $(wildcard *.c) objects := $(patsubst %c,%o,$(sources)) targets := $(basename $(sources)) all: $(targets) clean: rm -f $(targets) $(objects) *.core
8.4.9.4 Licensing the Embedded Server
The MySQL source code is covered by the GNU GPL license (see section K GNU GENERAL PUBLIC LICENSE). One result of this is that any program which includes, by linking with libmysqld
, the MySQL source code must be released as free software (under a license compatible with the GPL).
We encourage everyone to promote free software by releasing code under the GPL or a compatible license. For those who are not able to do this, another option is to purchase the MySQL code from MySQL AB under a looser license. For details concerning this issue, please see section 1.3.1 MySQL Licensing Policy.
8.5 MySQL C++ APIs
Two APIs are available in the MySQL Contrib directory.
8.5.1 Borland C++
You can compile the MySQL Windows source with Borland C++ 5.02. (The Windows source includes only projects for Microsoft VC++, for Borland C++ you have to do the project files yourself).
One known problem with Borland C++ is that it uses a different structure alignment than VC++. This means that you will run into problems if you try to use the default libmysql.dll
libraries (that was compiled with VC++) with Borland C++. You can do one of the following to avoid this problem.
- You can use the static MySQL libraries for Borland C++ that you can find on http://www.mysql.com/downloads/os-win32.html.
- Only call
mysql_init()
withNULL
as an argument, not a pre-allocated MYSQL struct.
8.6 MySQL Java Connectivity (JDBC)
There are 2 supported JDBC drivers for MySQL (the mm driver and the Reisin JDBC driver). You can find a copy of the mm driver at http://mmmysql.sourceforge.net/ or http://www.mysql.com/Downloads/Contrib/ and the Reisin driver at http://www.caucho.com/projects/jdbc-mysql/index.xtp For documentation consult any JDBC documentation and the driver's own documentation for MySQL-specific features.
8.7 MySQL Python APIs
The MySQL Contrib directory contains a Python interface written by Joseph Skinner.
You can also use the Python interface to iODBC to access a MySQL server. mxODBC
8.8 MySQL Tcl APIs
Tcl at binevolve. The Contrib directory contains a Tcl interface that is based on msqltcl 1.50.
8.9 MySQL Eiffel wrapper
The MySQL Contrib directory contains an Eiffel wrapper written by Michael Ravits.
Go to the first, previous, next, last section, table of contents.