This section describes functions which access MySQL(R) relational databases using the MySQL client library.
This documentation assumes you have a basic knowledge of SQL. Even if you do not, the examples should help you to get started. On the client computer, you should have at least the client part of the MySQL software installed, and a working MySQL database with enough access authorization to connect and issue at least queries.
The following functions are defined.
| Function | Purpose |
|---|---|
| mysql_affectedrows | Number of affected rows in the last command |
| mysql_autocommit | Set or reset autocommit mode |
| mysql_close | Close connection to a MySQL database |
| mysql_commit | Commit changes |
| mysql_connect | Connect to a MySQL server |
| mysql_createdb | Create a new database |
| mysql_dataseek | Set the next row to be fetched |
| mysql_fetchfield | Get the description of a column |
| mysql_fetchrow | Fetch a row in a query result as a list of fields |
| mysql_fetchstruct | Fetch a row in a query result as a structure |
| mysql_freeresult | Free the data structure allocated for a result |
| mysql_getclientinfo | Get version of the MySQL client |
| mysql_gethostinfo | Get information about the server host |
| mysql_getserverinfo | Get information about the MySQL server |
| mysql_info | Get information about the client |
| mysql_numfields | Number of fields |
| mysql_numrows | Number of rows |
| mysql_query | Execute an SQL query |
| mysql_rollback | Cancel changes |
| mysql_selectdb | Change current database |
MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries. The LME part of the LME extension source code has been developed by Calerga Sarl. It is provided as a convenience to users of LME-based products.
The extension is provided as both compiled library (ready to use) and source code. If you want, you can compile it and link it with the latest release of the client MySQL library. The steps below show the simplest way to do it.
gcc -Imysqlinc -Lmysqllib -Iextdevel \ -shared -o myext.so LMEMyExt.c -lmysqlclient
SQRLoadExtension extpath/myext.so
Number of affected rows in the last command.
n = mysql_affectedrows(c)
mysql_affectedrows(c) gives the number of affected rows in the last UPDATE, DELETE, or INSERT command.
mysql_affected_rows
Set or reset autocommit mode.
mysql_autocommit(c, b)
mysql_autocommit(c,b) enables the autocommit mode of connection c if b is true, or disables it is false. By default, autocommit is enabled.
When autocommit is enabled, SQL transactions which modify the database, such as insert, update and delete, are committed immediately. When it is disabled, they must be committed explicitly with mysql_ccommit, or canceled with mysql_rollback.
mysql_autocommit
MySQL 4.1 or later.
Close connection to a MySQL database.
mysql_close(c)
mysql_close(c) closes the connection c.
mysql_close
Commit changes.
mysql_commit(c)
mysql_commit(c) commits last changes made to connection c in an atomic operation. It is not required if autocommit mode is enabled.
mysql_commit
MySQL 4.1 or later.
mysql_rollback, mysql_autocommit
Connect to a MySQL server.
c = mysql_connect(server) c = mysql_connect(server, username) c = mysql_connect(server, username, password)
mysql_connect(server,username,password) establishes a connection to a database server. All input arguments are strings. If the username and password are omitted, empty values are used. The result is an identifier which should be used in all other MySQL calls. The connection is closed with mysql_close.
c = mysql_connect('test')
c =
0
r = mysql_query(c, 'select * from person;')
r =
1
count = mysql_numrows(r)
count =
17
nfields = mysql_numfields(r)
nfields =
3
field1 = mysql_fetchfield(r)
field1 =
name: 'name'
table: 'person'
length: 30
max_length: 12
type: 'VARCHAR'
not_null: 0
primary_key: 0
unique_key: 0
multiple_key: 0
numeric: 0
unsigned: 0
blob: 0
zerofill: 0
decimals: 0
row = mysql_fetchrow(f)
row =
{'Joe', [1949,3,24], 'UK'}
row = mysql_fetchstruct(f)
row =
name: 'Marco'
birthdate: [1967,10,9]
country: 'Italy'
mysql_freeresult(r);
mysql_close(c);
mysql_init, mysql_real_connect
Create a new database.
mysql_createdb(c, db)
mysql_createdb(c,db) creates a new database named db with connection c.
mysql_real_query
mysql_fetchrow, mysql_fetchstruct
Set the next row to be fetched.
mysql_dataseek(r, i)
mysql_dataseek(c,i) sets the next row to be fetched by mysql_fetchrow or mysql_fetchstruct for the query result r. The row index i is 0-based (first row for i=0), like fseek.
mysql_data_seek
mysql_fetchrow, mysql_fetchstruct
Get the description of a column.
strct = mysql_fetchfield(r, i) list = mysql_fetchfield(r)
mysql_fetchfield(r,i) gets the description of column i (first=1) for the query result r. The result is a structure with the following fields:
| Field | Value |
|---|---|
| name | column name |
| table | table name |
| length | column width |
| max_length | max width in the current set |
| type | data type |
| not_null | true if cannot be the null value |
| primary_key | true for primary key |
| unique_key | true for unique key |
| multiple_key | true for multiple key |
| numeric | true for numeric type |
| unsigned | true for unsigned numeric type |
| blob | true for blob type |
| zerofill | true for zerofill field |
| decimals | number of decimals |
Data type is one of the following strings: 'CHAR', 'VARCHAR', 'DECIMAL', 'NUMERIC', 'SMALLINT', 'INTEGER', 'FLOAT', 'DOUBLE', 'BIGINT', 'DATE', 'TIME', 'TIMESTAMP', BLOB, ENUM, SET.
mysql_fetchfield(s) gets the description of all columns as a list of structures.
mysql_fetch_fields, mysql_num_fields
Get numeric columns as a matrix.
matrix = mysql_fetchnumtable(s, columns)
mysql_fetchnumtable(s,columns) gets the value of the numeric columns specified in vector columns (1=first). All rows are fetched. The result is a double array.
(TBD)
Fetch a row in the result of a query as a list of fields.
list = mysql_fetchrow(r)
mysql_fetchrow(r) fetches the next row in the result of mysql_query identified by r. The result is a list of field values. Numbers are mapped to double real, dates to 1x3 double real matrices [year, month, day], times to 1x3 double real matrices [hour, minute, second], datetime to 1x6 double real matrices [year, month, day, hour, minute, second], blobs to row vectors of type uint8, and everything else to strings.
mysql_fetch_row, mysql_fetch_lengths
mysql_query, mysql_fetchstruct
Fetch a row in the result of a query as a structure.
struct = mysql_fetchstruct(r)
mysql_fetchstruct(r) fetches the next row in the result of mysql_query identified by r. The result is a structure whose fields correspond to the columns of the query result.
mysql_fetch_row, mysql_fetch_lengths, mysql_fetch_fields
Free the data structure allocated for a result.
mysql_freeresult(r)
mysql_freeresult(r) releases the data structures retained by mysql_query. Afterwards, r is invalid.
mysql_free_result
Get information about the MySQL client.
str = mysql_getclientinfo
mysql_getclientinfo gives the version of the MySQL client. No connection is required.
mysql_get_client_info
mysql_info, mysql_getserverinfo, mysql_gethostinfo
Get information about the server host.
str = mysql_gethostinfo(c)
mysql_gethostinfo(c) gives information about the server host and the connection type for connection c.
mysql_get_host_info
mysql_getserverinfo, mysql_getclientinfo, mysql_info
Get information about the MySQL server.
str = mysql_getserverinfo(c)
mysql_getserverinfo(c) gives information about the MySQL server for connection c.
None; definitions MYSQL_CHARSET and MYSQL_VERSION_ID.
mysql_getclientinfo, mysql_info, mysql_gethostinfo
Information about the MySQL client.
str = mysql_info
mysql_info gives information about the MySQL client in a string. No connection is required. The information contains the client version, the character set used, and a trademark acknowledgment.
mysql_get_client_info
mysql_getserverinfo, mysql_gethostinfo
Number of fields.
n = mysql_numfields(r)
mysql_numfields(r) gives the number of fields (columns) in result r.
mysql_num_fields
Number of rows.
n = mysql_numrows(r)
mysql_numrows(r) gives the number of rows in result r.
mysql_num_rows
Execute an SQL query.
mysql_query(c, cmd) r = mysql_query(c, cmd)
mysql_query(c,cmd) executes the SQL query given by the string cmd, using connection c. With an output argument, it keeps the data structures necessary to retrieve data, for instance the table which results from a SELECT statement, until a call to mysql_freeresult.
mysql_real_query, mysql_store_result
mysql_fetchrow, mysql_fetchstruct, mysql_freeresult
Cancel changes.
mysql_rollback(c)
In non-autocommit mode, mysql_rollback(c) cancels all the changes made with connection c since the previous mysql_commit or mysql_rollback command. In autocommit mode, it has no effect.
mysql_rollback
MySQL 4.1 or later.
mysql_commit, mysql_autocommit
Change current database.
mysql_selectdb(c, db)
mysql_selectdb(c,db) selects db as the current database for connection c. db is given as a string. All queries which follow are addressed to this database.
mysql_select_db