Go to the first, previous, next, last section, table of contents.


MySQL client tools and API's

MySQL C API

The C API is distributed with MySQL. It is included in the libmysqlclinet library. It allows C programs to access a database.

Most of the other client APIs (all except Java) use this library to connect. So for example you can use the same environment variables.

The client has a maximum communication buffer size. This is automatically increased up to the maximum size (the default for this is 512Kb). As buffers are increased (but not decreased until close) on demand, it will not take any resources if one increases this. This size check is mostly a check for erroneous queries and communication packets.

The communication buffer must be big enough to contain a single SQL statement and one row of returned data (but of course not at the same time). Each thread's communication buffer is dynamically enlarged to handle any row or query up to the imposed limit.

So if you have BLOBs that contains data up to 16M you must have at least 16M as your communication buffer limit. See section How does one change the size of MySQL buffers?.

MySQL shrinks each communication buffer to net_buffer_length after each query.

mysql_affected_rows Retrieves the number of affected rows by the last UPDATE, DELETE or INSERT.
mysql_close Closes a server connection.
mysql_connect Establish a connection to a MySQL server.
mysql_create_db Create a database.
mysql_data_seek Seeks to an arbitrary row in a query result set.
mysql_drop_db Drop a database.
mysql_eof Determine if last row has been read.
mysql_error The error message from last MySQL function.
mysql_fetch_field Find out what type a table field is.
mysql_fetch_lengths Returns the length of all columns in a query result set.
mysql_fetch_row Fetch the 'next' row in the query result.
mysql_field_seek Put the column cursor on column number field.
mysql_free_result Free memory used to store a query result.
mysql_get_client_info Return version information for the current client library.
mysql_get_host_info Returns name of server host.
mysql_get_proto_info Get protocol version used by connection.
mysql_get_server_info Returns the version number of the server.
mysql_insert_id Returns ID generated for a AUTO_INCREMENT field.
mysql_list_dbs Return matching database names.
mysql_list_fields Return matching field names.
mysql_list_processes Get a list of the current server threads.
mysql_list_tables Return matching table names.
mysql_num_fields Return the number of columns in a result set.
mysql_num_rows Returns the number of rows in result set.
mysql_query Executes a SQL query.
mysql_real_query Executes a SQL query with length information.
mysql_reload Reload the user permissions table in the server.
mysql_select_db Connect to a database.
mysql_shutdown Shut down the database server.
mysql_stat Return server status in a string.
mysql_store_result Reads a result set to the client.
mysql_use_result Initiate a dynamic result set for each row.
int mysql_affected_rows(MYSQL *mysql)
Retrieves the number of affected rows by the last UPDATE, DELETE or INSERT.
void mysql_close(MYSQL *mysql)
Closes a server connection.
MYSQL *mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd)
Establish a connection to a MySQL server.
int mysql_create_db(MYSQL *mysql, const char *db)
Create a database.
void mysql_data_seek(MYSQL_RES *res, uint offset)
Seeks to an arbitrary row in a query result set.
int mysql_drop_db(MYSQL *mysql, const char *db)
Drop a database.
int mysql_eof(MYSQL_RES *)
Determine if last row has been read.
char *mysql_error(MYSQL *mysql)
The error message from last MySQL function.
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *handle)
Find out what type a table field is. When querying for the length of a BLOB without retreving a result, MySQL returns the 'default blob length', which is 8192, when doing a select on the table. When one retrevies a result, column_object->max_length contains the real max_length for the specific query. The 8192 size is chosen because MySQL doesn't know the max length for the BLOB. This should be made configurable sometime.
unsigned int *mysql_fetch_lengths(MYSQL_RES *mysql)
Returns the length of all columns in a query result set.
MYSQL_ROW mysql_fetch_row(MYSQL_RES *mysql)
Fetch the 'next' row in the query result.
void mysql_field_seek(MYSQL_RES *result, int field)
Put the column cursor on column number field.
void mysql_free_result(MYSQL_RES *result)
Free memory used to store a query result.
char *mysql_get_client_info(void)
Return version information for the current client library.
char *mysql_get_host_info(MYSQL *mysql)
Returns name of server host.
int mysql_get_proto_info(MYSQL *mysql)
Get protocol version used by connection.
char *mysql_get_server_info(MYSQL *mysql)
Returns the version number of the server.
int mysql_insert_id(MYSQL *mysql)
Returns ID generated for AUTO_INCREMENT field.
MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild)
Return matching database names.
MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char *wild)
Return matching field names.
MYSQL_RES *mysql_list_processes(MYSQL *mysql)
Get a list of the current server threads.
MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char *wild)
Return matching table names.
int mysql_num_fields(MYSQL_RES *result)
Return the number of columns in a result set.
int mysql_num_rows(MYSQL_RES *result)
Returns the number of rows in result set.
int mysql_query(MYSQL *mysql, const char *query)
Executes a SQL query.
int mysql_real_query(MYSQL *mysql, const char *query, uint length)
Executes a SQL query with length information.
int mysql_reload(MYSQL *mysql)
Reload the user permissions table in the server.
int mysql_select_db(MYSQL *mysql, const char *db)
Connect to a database.
int mysql_shutdown(MYSQL *mysql)
Shut down the database server.
char *mysql_stat(MYSQL *mysql)
Return server status in a string.
MYSQL_RES *mysql_store_result(MYSQL *mysql)
Reads a result set to the client.
MYSQL_RES *mysql_use_result(MYSQL *mysql)
Initiate a dynamic result set for each row.

Why is it that after mysql_query() returns success, mysql_store_result() sometimes returns NULL?

It means one of the following:

  1. Malloc failure.
  2. The data couldn't be read (Error on connection).
  3. The statement was a statement which never returns data (INSERT or UPDATE or DELETE).

You can always check if the statement should have given a result by checking that mysql_num_fields(MYSQL *) isn't 0. If this is 0 the last query was a statement that does not return values. For example a INSERT or a DELETE.

You have got an error if mysql_error(MYSQL *) isn't empty!

What results can I get from a query?

mysql_affected_rows(MYSQL *) returns the number of affected rows in the last query when doing an INSERT, UPDATE or DELETE. Except, if DELETE is used without a WHERE clause then the table is truncated which is much faster! In this case it returns records affected as zero.

mysql_insert_id(MYSQL *) returns the given ID of the last query when inserting a row into a table with an AUTO_INCREMENT index. See section How can I get the unique ID for the last inserted row?

Some queries, LOAD DATA INFILE... and INSERT INTO ... SELECT ..., return additional info. The result is returned in mysql_info(MYSQL *). mysql_info() returns a null pointer if there is no additional information.

How can I get the unique ID for the last inserted row?

If you insert a record that has a AUTO_INCREMENT index then you can get the given id with mysql_insert_id(MYSQL *).

The last value is also stored in the server and can be retrieved with the LAST_INSERT_ID() function.

You can check if an auto_increment index is used by 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(MYSQL_RESULT) == 0 &&
    mysql_insert_id(MYSQL) != 0)
  used_id = mysql_insert_id(MYSQL);

The id that LAST_INSERT_ID() returns is maintained in the server per connection. It will not be botched by another client. It will not even be changed if you update another auto_increment column with a non magic value (that is a not null or 0).

What is the difference between mysql_use_result() and mysql_store_result() modes?

mysql_use_results reads the result directly from the server without storing it in a local buffer. This is somewhat faster and uses much less memory thatn mysql_store_result. One shouldn't use mysql_use_results if there is a lot of processing being done for each row at the client side, or if the output is sent to a screen on which the user may do a ^S (stop scroll). Doing this would tie up the server and then other threads couldn't update the used tables. One can't use mysql_data_seek or issue other queries while using mysql_use_result.

Problems linking with the C API.

When linking with the C API you can get the following errors 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

This means that on your system you have to include the math library (-lm) last in the compile/link line.

How to make a threadsafe client

The client is 'almost' thread-safe. The biggest problem is that net.c (the subroutines that read from sockets) are not interrupt safe. This was done with the thought that one may want to have one's own alarm that can break a long read to a server.

The standard client libraries are not compiled with the thread options.

To get a thread safe client use the -lmysys, -lstring and -ldbug libraries and net_serv.o that the server uses.

When using a threaded client I think one can have great use of the thr_alarm.c routine. If you are using the mysys routines, the only thing one has to remember is to call my_init() first!

Making a threadsafe client

All functions except mysql_connect() are currently thread safe.

To get connect thread_safe you have to do the following:

Recompile the client with:

CPPFLAGS=-DTHREAD_SAFE_CLIENT ./configure ...

You may get some errors because of undefined symbols when linking the standard client as the pthread libraries are not included by default.

The resulting libmysqld.a library is now thread safe.

Two threads can't use the same handle (returned by mysql_connect()) at the same time, even if two threads can use different MYSQL_RES handles that were created with mysql_store_result().

When using a threaded client one can have great use of the thr_alarm.c routine. If you are using the mysys routines, the only thing one has to remember is to call my_init() first!

MySQL Perl API's

Since DBI/DBD now is the recommended perl interface mysqlperl is not documented here.

DBI with DBD::mysql

DBI is a generic interface for many databases. That means that you can write a script what 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 DBIs web page and read the documentation. For more information on Object Oriented Programming (OOP) as defined in Perl5, see the perl OOP page.

The DBI interface

Portable DBI methods.

connect Establish a connection to a database server
prepare Get a SQL statement ready for execution
do Prepares and executes a SQL statement
disconnect Disconnect from the database server
quote Quote strings/blobs to be inserted
execute Executes prepared statements
fetchrow_array fetch the next row as an array of fields.
fetchrow_arrayref fetch next row as a reference array of fields
fetchrow_hashref fetch next row as a reference to a hashtable
fetchall_arrayref Get all data as a array of arrays
finish finish a statment and let the system free resources
rows Returns the number of rows affected
data_sources Return an array of databases available on localhost
ChopBlanks Shall fetchrow trim spaces
NUM_OF_PARAMS Number of placeholders in the prepared statement
NULLABLE Which columns can be NULL
MySQL specific methods.
insertid The latest auto_increment value
is_blob Which column ar BLOBs
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 can NOT be NULL. See NULLABLE
length Maximum theoretically possible column sizes
max_length Maximum physical present column sizes
NAME Column names
NUM_OF_FIELDS Number of fields returned.
table Table names in returned set
type All coulumn types
_CreateDB Create a database
_DropDB Drop a database. THIS IS DANGEROUS
connect
You use the connect method to make a database connection to the data source. The $data_source value should begin with DBI:driver_name:. Example connect methods with the DBD::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 username and/or password are undefined, then the DBI will use the values of the DBI_USER, DBI_PASS environment variables respectively. If you don't specify a hostname, then it will default to "localhost". If you don't specify a port, then it defaults to the default mysql port (3306).
prepare
Prepare gets a SQL statement ready for execution by the database engine and returns a statement handle ($sth) which invokes the execute method. Example:
$sth = $dbh->prepare($statement) or die "Can't prepare $statement: 
$dbh->errstr\n";
do
The "do" method prepares and executes a SQL statement and returns the number of rows effected. This method is generally used for "non-select" statements which can not be prepared in advance (driver limitation) or which do not need to executed more than once (inserts, deletes, etc.). Examples:
$rc = $dbh->do($statement) or
        die "Can't execute $statement: $dbh- >errstr\n";
disconnect
Disconnect will disconnect the database handle from the database. This is typically called right before you exit from the program. Example:
$rc = $dbh->disconnect;
quote
The quote method is used to "escape" any special characters contained in the string and to add the required outer quotation marks.
$sql = $dbh->quote($string)
execute
This method executes the prepared statement. For non-select satements, it returns the number of rows affected. For select statements, execute only starts the SQL query in the database. You need to use one of the fetch_* methods below to retrieve the data. Example:
$rv = $sth->execute or die "can't execute the query: $sth->errstr;
fetchrow_array
This method "fetches" the next row of data and returns it as an array of field values. Example:
while(@row = $sth->fetchrow_array) {
        print qw($row[0]\t$row[1]\t$row[2]\n); 
}
fetchrow_arrayref
This method "fetches" the next row of data and returns it as a reference to an array of field values. Example:
while($row_ref = $sth->fetchrow_arrayref) {
        print qw($row_ref->[0]\t$row_ref->[1]\t$row_ref->[2]\n);
}
fetchrow_hashref
This method fetches a row of data and returns a reference to a hash table containing field name/value pairs. This method is not nearly as efficient as using array references as demonstrated above. Example:
while($hash_ref = $sth->fetchrow_hashref) {
        print qw($hash_ref->{firstname}\t$hash_ref->{lastname}\t\
                $hash_ref- > title}\n);
}
fetchall_arrayref
This method is used to get all the data (rows) to be returned from the SQL statement. It returns a reference to an array of arrays of references to each row. You access/print the data by using a nested loop. Example:
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
Indicates that no more data will be fetched from this statement handle. You call this method to free up the statement handle and any system resources it may be holding. Example:
$rc = $sth->finish;
rows
Returns the number of rows affected (updated, deleted, etc.) from the last command. This is usually used after a do() or non-select execute() statement.
$rv = $sth->rows;
NULLABLE
A reference to an array of boolean values; TRUE indicates that this column may contain NULLs.
$null_possible = $sth->{NULLABLE}; 
NUM_OF_FIELDS
Number of fields returned by a SELECT or LISTFIELDS 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.
$nr_of_fields = $sth->{NUM_OF_FIELDS};
data_sources
This method returns an array of databases available to the mysql daemon on localhost.
@dbs = DBI->data_sources("mysql"); 
ChopBlanks
This determines whether a fetchrow will chop preceding and trailing blanks off the returned values.
$sth->{'ChopBlanks') =1; 
MySQL specific methods.
insertid
If you use the auto-increment feature of mysql, the new auto-incremented values will be stored here.
$new_id = $sth->{insertid};
is_blob
Reference to an array of boolean values; TRUE indicates that the respective column is a blob.
$keys = $sth->{is_blob};
is_key
Reference to an array of boolean values; TRUE indicates, that the respective column is a key.
$keys = $sth->{is_key};
is_num
Reference to an array of boolean values; TRUE indicates, that the respective column contains numeric values.
$nums = $sth->{is_num};
is_pri_key
Reference to an array of boolean values; TRUE indicates, that the respective column is a primary key.
$pri_keys = $sth->{is_pri_key};
is_not_null
A reference to an array of boolean values; FALSE indicates that this column may contain NULLs. You should better use the NULLABLE attribute above which is a DBI standard.
$not_nulls = $sth->{is_not_null};
max_length
length
A reference to an array of maximum column sizes. The max_length is the maximum physically present in the result table, length gives the theoretically possible maximum.
$max_lengts = $sth->{max_length};
$lengts = $sth->{length};
NAME
A reference to an array of column names.
$names = $sth->{NAME};
table
Returns a reference to an array of table names.
$tables = $sth->{table};

More DBI/DBD information

You can use the perldoc command to get more information about DBI.

perldoc DBI
perldoc DBI::FAQ
perldoc mysql

You can also use the pod2man, pod2html, etc.. tools to translate to other formats.

And of course you can find the latest DBI information at the DBI web page
.

MySQL Java connectivity (JDBC)

Insert pointers/descriptions of JDBC.

MySQL PHP API's

PHP documentation

MySQL C++ API's

Insert pointers/descriptions for C++.

MySQL Python API's

Insert pointers/descriptions for Python.

MySQL TCL API's

Insert pointers/descriptions for TCL.


Go to the first, previous, next, last section, table of contents.


Casa de Bender