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 BLOB
s 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)
void mysql_close(MYSQL *mysql)
MYSQL *mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd)
int mysql_create_db(MYSQL *mysql, const char *db)
void mysql_data_seek(MYSQL_RES *res, uint offset)
int mysql_drop_db(MYSQL *mysql, const char *db)
int mysql_eof(MYSQL_RES *)
char *mysql_error(MYSQL *mysql)
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *handle)
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)
MYSQL_ROW mysql_fetch_row(MYSQL_RES *mysql)
void mysql_field_seek(MYSQL_RES *result, int field)
void mysql_free_result(MYSQL_RES *result)
char *mysql_get_client_info(void)
char *mysql_get_host_info(MYSQL *mysql)
int mysql_get_proto_info(MYSQL *mysql)
char *mysql_get_server_info(MYSQL *mysql)
int mysql_insert_id(MYSQL *mysql)
MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild)
MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char *wild)
MYSQL_RES *mysql_list_processes(MYSQL *mysql)
MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char *wild)
int mysql_num_fields(MYSQL_RES *result)
int mysql_num_rows(MYSQL_RES *result)
int mysql_query(MYSQL *mysql, const char *query)
int mysql_real_query(MYSQL *mysql, const char *query, uint length)
int mysql_reload(MYSQL *mysql)
int mysql_select_db(MYSQL *mysql, const char *db)
int mysql_shutdown(MYSQL *mysql)
char *mysql_stat(MYSQL *mysql)
MYSQL_RES *mysql_store_result(MYSQL *mysql)
MYSQL_RES *mysql_use_result(MYSQL *mysql)
mysql_query()
returns success, mysql_store_result()
sometimes returns NULL?It means one of the following:
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!
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.
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).
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
.
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.
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!
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!
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.
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 |
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
$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
($sth)
which invokes the execute
method. Example:
$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
do
$rc = $dbh->do($statement) or die "Can't execute $statement: $dbh- >errstr\n";
disconnect
$rc = $dbh->disconnect;
quote
$sql = $dbh->quote($string)
execute
fetch_*
methods below to retrieve the data. Example:
$rv = $sth->execute or die "can't execute the query: $sth->errstr;
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
$rv = $sth->rows;
NULLABLE
$null_possible = $sth->{NULLABLE};
NUM_OF_FIELDS
$nr_of_fields = $sth->{NUM_OF_FIELDS};
data_sources
@dbs = DBI->data_sources("mysql");
ChopBlanks
$sth->{'ChopBlanks') =1;
insertid
$new_id = $sth->{insertid};
is_blob
$keys = $sth->{is_blob};
is_key
$keys = $sth->{is_key};
is_num
$nums = $sth->{is_num};
is_pri_key
$pri_keys = $sth->{is_pri_key};
is_not_null
$not_nulls = $sth->{is_not_null};
max_length
length
$max_lengts = $sth->{max_length}; $lengts = $sth->{length};
NAME
$names = $sth->{NAME};
table
$tables = $sth->{table};
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
.
Insert pointers/descriptions of JDBC.
Insert pointers/descriptions for C++.
Insert pointers/descriptions for Python.
Insert pointers/descriptions for TCL.
Go to the first, previous, next, last section, table of contents.