A Native D Client API for MySQL.

mysqlN is a native D driver for the MySQL database system. Principal source file mysql.d. You can find the source at github.

This module attempts to provide composite objects and methods that will allow a wide range of common database operations, but be relatively easy to use. The design is a first attempt to illustrate the structure of a set of modules to cover popular database systems and ODBC.

It has no dependecies on GPL header files or libraries, instead communicating directly with the server via the published client/server protocol.

http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol
http://forge.mysql.com/w/index.php?title=MySQL_Internals_ClientServer_Protocol&diff=5078&oldid=4374

This version is not by any means comprehensive, and there is still a good deal of work to do. As a general design position it avoids providing wrappers for operations that can be accomplished by simple SQL sommands, unless the command produces a result set. There are some instances of the latter category to provide simple meta-data for the database,

Its primary objects are:

It has currently only been compiled and unit tested on Ubuntu with D2.055 using a TCP loopback connection to a server on the local machine.

There are numerous examples of usage in the unittest sections.

The file mysqld.sql, included with the module source code, can be used to generate the tables required by the unit tests.

There is an outstanding issue with Connections. Normally MySQL clients sonnect to a server on the same machine via a Unix socket on *nix systems, and through a named pipe on Windows. Neither of these conventions is currently supported. TCP must be used for all connections.

Since there is currently no SHA1 support on Phobos, a simple translation of the NIST example C code for SHA1 is also included with this module.

License:
www.boost.org/LICENSE_1_0.txt, Boost License 1.0.

Author:
Steve Teale

class MySQLException: object.Exception;
An exception type to distinguish exceptions thrown by this module.

struct TimeDiff;
A simple struct to represent time difference.

D's std.datetime does not have a type that is closely compatible with the MySQL interpretation of a time difference, so we define a struct here to hold such values.

TimeDiff toTimeDiff(ubyte[] a);
Function to extract a time difference from a binary encoded row.

Time/date structures are packed by the server into a byte sub-packet with a leading length byte, and a minimal number of bytes to embody the data.

Params:
ubyte[] a slice of a protocol packet beginning at the length byte for a chunk of time data

Returns:
A populated or default initialized TimeDiff struct.

TimeDiff toTimeDiff(string s);
Function to extract a time difference from a text encoded column value.

Text representations of a time difference are like -750:12:02 - 750 hours 12 minutes and two seconds ago.

Params:
string s A string representation of the time difference.

Returns:
A populated or default initialized TimeDiff struct.

TimeOfDay toTimeOfDay(ubyte[] a);
Function to extract a TimeOfDay from a binary encoded row.

Time/date structures are packed by the server into a byte sub-packet with a leading length byte, and a minimal number of bytes to embody the data.

Params:
ubyte[] a slice of a protocol packet beginning at the length byte for a chunk of time data

Returns:
A populated or default initialized std.datetime.TimeOfDay struct.

TimeOfDay toTimeOfDay(string s);
Function to extract a TimeOfDay from a text encoded column value.

Text representations of a time of day are as in 14:22:02

Params:
string s A string representation of the time.

Returns:
A populated or default initialized std.datetine.TimeOfDay struct.

ubyte[] pack(TimeOfDay tod);
Function to pack a TimeOfDay into a binary encoding for transmission to the server.

Time/date structures are packed into a string of bytes with a leading length byte, and a minimal number of bytes to embody the data.

Params:
TimeOfDay tod TimeOfDay struct.

Returns:
Packed ubyte[].

Date toDate(ubyte[] a);
Function to extract a Date from a binary encoded row.

Time/date structures are packed by the server into a byte sub-packet with a leading length byte, and a minimal number of bytes to embody the data.

Params:
ubyte[] a slice of a protocol packet beginning at the length byte for a chunk of Date data

Returns:
A populated or default initialized std.datetime.Date struct.

Date toDate(string s);
Function to extract a Date from a text encoded column value.

Text representations of a Date are as in 2011-11-11

Params:
a A string representation of the time difference.

Returns:
A populated or default initialized std.datetime.Date struct.

ubyte[] pack(Date dt);
Function to pack a Date into a binary encoding for transmission to the server.

Time/date structures are packed into a string of bytes with a leading length byte, and a minimal number of bytes to embody the data.

Params:
Date dt std.datetime.Date struct.

Returns:
Packed ubyte[].

DateTime toDateTime(ubyte[] a);
Function to extract a DateTime from a binary encoded row.

Time/date structures are packed by the server into a byte sub-packet with a leading length byte, and a minimal number of bytes to embody the data.

Params:
ubyte[] a slice of a protocol packet beginning at the length byte for a chunk of DateTime data

Returns:
A populated or default initialized std.datetime.DateTime struct.

DateTime toDateTime(string s);
Function to extract a DateTime from a text encoded column value.

Text representations of a DateTime are as in 2011-11-11 12:20:02

Params:
a A string representation of the time difference.

Returns:
A populated or default initialized std.datetime.DateTime struct.

DateTime toDateTime(ulong x);
Function to extract a DateTime from a ulong.

This is used to support the TimeStamp struct.

Params:
ulong x A ulong e.g. 20111111122002UL.

Returns:
A populated std.datetime.DateTime struct.

ubyte[] pack(DateTime dt);
Function to pack a DateTime into a binary encoding for transmission to the server.

Time/date structures are packed into a string of bytes with a leading length byte, and a minimal number of bytes to embody the data.

Params:
DateTime dt std.datetime.DateTime struct.

Returns:
Packed ubyte[].

struct Timestamp;
A D struct to stand for a TIMESTAMP

It is assumed that insertion of TIMESTAMP values will not be common, since in general, such columns are used for recording the time of a row insertion, and are filled in automatically by the server. If you want to force a timestamp value in a prepared insert, set it into a timestamp struct as an unsigned long in the format YYYYMMDDHHMMSS and use that for the approriate parameter. When TIMESTAMPs are retrieved as part of a result set it will be as DateTime structs.

enum SvrCapFlags;
Server capability flags.

During the connection handshake process, the server sends a uint of flags describing its capabilities

SECURE_PWD
Long passwords

FOUND_NOT_AFFECTED
Report rows found rather than rows affected

ALL_COLUMN_FLAGS
Send all column flags

WITH_DB
Can take database as part of login

NO_SCHEMA
Can disallow database name as part of column name database.table.column

CAN_COMPRESS
Can compress packets

ODBC
Can handle ODBC

LOCAL_FILES
Can use LOAD DATA LOCAL

IGNORE_SPACE
Can ignore spaces before '('

PROTOCOL41
Can use 4.1+ protocol

INTERACTIVE
Interactive client?

SSL
Can switch to SSL after handshake

IGNORE_SIGPIPE
Ignore sigpipes?

TRANSACTIONS
Transaction support

SECURE_CONNECTION
4.1+ authentication

MULTI_STATEMENTS
Multiple statement support

MULTI_RESULTS
Multiple result set support

enum SQLType;
Column type codes

DEFAULT means infer parameter type or column type from D variable type.

enum RefreshFlags;
Server refresh flags

struct OKPacket;
A struct representing an OK or Error packet

OK packets begin with a zero byte - Error packets with 0xff

struct FieldDescription;
A struct representing a field (column) description packet

These packets, one for each column are sent before the data of a result set, followed by an EOF packet.

this(ubyte[] packet);
Construct a FieldDescription from the raw data packet

Parameters:
packet = The packet contents excluding the 4 byte packet header

@property string db();
Database name for column as string

@property string table();
Table name for column as string - this could be an alias as in 'from tablename as foo'

@property string originalTable();
Real table name for column as string

@property string name();
Column name as string - this could be an alias

@property string originalName();
Real column name as string

@property ushort charSet();
The character set in force

@property uint length();
The 'length' of the column as defined at table creation

@property ushort type();
The type of the column hopefully (but not always) corresponding to enum SQLType. Only the low byte currently used

@property ushort flags();
Column flags - unsigned, binary, null and so on

@property ubyte scale();
Precision for floating point values

@property bool notNull();
NotNull from flags

@property bool unsigned();
Unsigned from flags

@property bool binary();
Binary from flags

@property bool isenum();
Is-enum from flags

@property bool isset();
Is-set (a SET column that is) from flags

struct ParamDescription;
A struct representing a prepared statement parameter description packet

These packets, one for each parameter are sent in response to the prepare command, followed by an EOF packet.

Sadly it seems that this facility is only a stub. The correct number of packets is sent, but they contain no useful information and are all the same.

struct EOFPacket;
A struct representing an EOF packet

an EOF packet is sent after each sequence of field description and parameter description packets, and after a sequence of result set row packets.

These EOF packets contain a server status and a warning count.

this(ubyte[] packet);
Construct an EOFPacket struct from the raw data packet

Parameters:
packet = The packet contents excluding the 4 byte packet header

@property ushort warnings();
Retrieve the warning count

@property ushort serverStatus();
Retrieve the server status

struct ResultSetHeaders;
A struct representing the collation of a sequence of FieldDescription packets.

This data gets filled in after a query (prepared or otherwise) that creates a result set completes. All the FD packets, and an EOF packet must be eaten before the row data packets can be read.

this(Connection con, uint fieldCount);
Construct a ResultSetHeaders struct from a sequence of FieldDescription packets and an EOF packet.

Parameters:
con = A Connection via which the packets are read fieldCount = the number of fields/columns generated by the query

void addSpecializations(ColumnSpecialization[] csa);
Add specialization information to one or more field descriptions.

Currently the only specialization supported is the capability to deal with long data e.g. BLOB or TEXT data in chunks by stipulating a chunkSize and a delegate to sink the data.

Parameters:
csa = An array of ColumnSpecialization structs

FieldDescription opIndex(size_t i);
Index into the set of field descriptions

@property auto fieldCount();
Get the number of fields in a result row.

@property ushort warnings();
Get the warning count as per the EOF packet

@property string[] fieldNames();
Get an array of strings representing the column names

struct PreparedStmtHeaders;
A struct representing the collation of a prepared statement parameter description sequence

As noted above - parameter descriptions are not fully implemented by MySQL.

class Connection;
A struct representing a database connection.

The Connection is responsible for handshaking with the server to establish authentication. It then passes client preferences to the server, and subsequently is the channel for all command packets that are sent, and all response packets received.

Uncompressed packets consist of a 4 byte header - 3 bytes of length, and one byte as a packet number. Connection deals with the headers and ensures that packet numbers are sequential.

The initial packet is sent by the server - esentially a 'hello' packet inviting login. That packet has a sequence number of zero. That sequence number is the incremented by cliemt and server packets thruogh the handshake sequence.

After login all further sequences are initialized by the client sending a command packet with a zero sequence number, to which the server replies with zero or more packets with sequential sequence numbers.

this(string host, string user, string pwd, string db, uint capFlags = defaultClientFlags);
Construct opened connection.

After the connection is created, and the initial invitation is received from the server client preferences can be set, and authentication can then be attempted.

Parameters:
host = An IP address in numeric dotted form, or as a host name. user = The user name to authenticate. password = Users password. db = Desired initial database. capFlags = The set of flag bits from the server's capabilities that the client requires

this(string cs, uint capFlags = defaultClientFlags);
Construct opened connection.

After the connection is created, and the initial invitation is received from the server client preferences are set, and authentication can then be attempted.

TBD The connection string needs work to allow for semicolons in its parts!

Parameters:
cs = A connetion string of the form "host=localhost;user=user;pwd=password;db=mysqld" capFlags = The set of flag bits from the server's capabilities that the client requires

void close();
Explicitly close the connection.

This is a two-stage process. First tell the server we are quitting this connection, and then close the socket.

Idiomatic use as follows is suggested:
{
   auto con = Connection("localhost:user:password:mysqld");
   scope(exit) con.close();
   // Use the connection
   ...
}


void selectDB(string dbName);
Select a current database.

Params:
string dbName Name of the requested database

Throws:
MySQLEcception

OKPacket pingServer();
Check the server status

Returns:
An OKPacket from which server status can be determined

Throws:
MySQLEcception

OKPacket refreshServer(int flags);
Refresh some feature[s] of he server.

Returns:
An OKPacket from which server status can be determined

Throws:
MySQLEcception

string serverStats();
Get a textual report on the servr status.

void enableMultiStatements(bool on);
Enable multiple statement commands

This can be used later if this feature was not requested in the client capability flags.

Params:
bool on Boolean value to turn th capability on or off.

@property ubyte protocol();
Return the in-force protocol number

@property string serverVersion();
Server version

@property uint serverCapabilities();
Server capability flags

@property ushort serverStatus();
Server status

@property ubyte charSet();
Current character set

@property string currentDB();
Current database

struct ParameterSpecialization;
A struct to represent specializations of prepared statement parameters.

There are two specializations. First you can set an isNull flag to indicate that the parameter is to have the SQL NULL value.

Second, if you need to send large objects to the database it might be convenient to send them in pieces. These two variables allow for this. If both are provided then the corresponding column will be populated by calling the delegate repeatedly. the source should fill the indicated slice with data and arrange for the delegate to return the length of the data supplied. Af that is less than the chunkSize then the chunk will be assumed to be the last one.

struct ColumnSpecialization;
A struct to represent specializations of prepared statement parameters.

If you are executing a query that will include result columns that are large objects it may be expedient to deal with the data as it is received rather than first buffering it to some sort of byte array. These two variables allow for this. If both are provided then the corresponding column will be fed to the stipulated delegate in chunks of chunkSize, with the possible exception of the last chunk, which may be smaller. The 'finished' argument will be set to true when the last chunk is set.

Be aware when specifying types for column specializations that for some reason the field descriptions returned for a resultset have all of the types TINYTEXT, MEDIUMTEXT, TEXT, LONGTEXT, TINYBLOB, MEDIUMBLOB, BLOB, and LONGBLOB lumped as type 0xfc contrary to what it says in the protocol documentation.

struct Row;
A struct to represent a single row of a result set.

The row struct is used for both 'traditional' and 'prepared' result sets. It consists of parallel arrays of Variant and bool, with the bool array indicating which of the result set columns are NULL.

I have been agitating for some kind of null indicator that can be set for a Variant without destroying its inherent type information. If this were the case, then the bool array could disappear.

this(Connection con, ubyte[] packet, ResultSetHeaders rh, bool binary);
A constructor to extract the column data from a row data packet.

If the data for the row exceeds the server's maximum packet size, then several packets will be sent for the row that taken together constitute a logical row data packet. The logic of the data recovery for a Row attempts to minimize the quantity of data that is bufferred. Users can assist in this by specifying chunked data transfer in cases where results sets can include long column values.

The row struct is used for both 'traditional' and 'prepared' result sets. It consists of parallel arrays of Variant and bool, with the bool array indicating which of the result set columns are NULL.

I have been agitating for some kind of null indicator that can be set for a Variant without destroying its inherent type information. If this were the case, then the bool array could disappear.

Variant opIndex(uint i);
Simplify retrieval of a column value by index.

If the table you are working with does not allow NULL columns, this may be all you need. Otherwise you will have to use isNull(i) as well.

Params:
uint i the zero based index of the column whose value is required.

Returns:
A Variant holding the column value.

@property bool isNull(uint i);
Check if a column in the result row was NULL

Params:
uint i The zero based column index.

void toStruct(S)(ref S s);
Move the content of the row into a compatible struct

This method takes no account of NULL column values. If a column was NULL, the corresponding Variant value would be unchanged in those cases.

The method will throw if the type of the Variant is not implicitly convertible to the corresponding struct member

Params:
S a struct type.
s an ref instance of the type

struct Column;
Composite representation of a column value

Another case where a null flag on Variant would simplify matters.

struct ResultSet;
A Random access range of Rows.

This is the entity that is returned by the Command methods execSQLResult and execPreparedResult

MySQL result sets can be up to 2^^64 rows, and the 32 bit implementation of the MySQL C API accomodates such potential massive result sets by storing the rows in a doubly linked list. I have taken the view that users who have a need for result sets up to this size should be working with a 64 bit system, and as such the 32 bit implementation will throw if the number of rows exceeds the 32 bit size_t.max.

@property bool empty();
Make the ResultSet behave as a random access range - empty

@property ResultSet save();
Make the ResultSet behave as a random access range - save

@property Row front();
Make the ResultSet behave as a random access range - front

Gets the first row in whatever remains of the Range.

@property Row back();
Make the ResultSet behave as a random access range - back

Gets the last row in whatever remains of the Range.

void popFront();
Make the ResultSet behave as a random access range - popFront()

void popBack();
Make the ResultSet behave as a random access range - popBack

Row opIndex(size_t i);
Make the ResultSet behave as a random access range - opIndex

Gets the i'th row of whatever remains of the range

@property size_t length();
Make the ResultSet behave as a random access range - length

void revert();
Restore the range to its original span.

Since the range is just a view of the data, we can easily revert to the initial state.

Column[string] asAA();
Get a row as an associative array by column name

The row in question will be that which was the most recent subject of front, back, or opIndex. If there have been no such references it will be front.

struct ResultSequence;
An input range of Rows.

This is the entity that is returned by the Command methods execSQLSequence and execPreparedSequence

MySQL result sets can be up to 2^^64 rows. This interface allows for iteration through a result set of that size.

@property bool empty();
Make the ResultSequence behave as an input range - empty

@property Row front();
Make the ResultSequence behave as an input range - front

Gets the current row

void popFront();
Make the ResultSequence behave as am input range - popFront()

Progresses to the next row of the result set - that will then be 'front'

Column[string] asAA();
Get the current row as an associative array by column name

void close();
Explicitly clean up the MySQL resources and cancel pending results

@property ulong rowCount();
Get the number of currently retrieved.

Note that this is not neccessarlly the same as the length of the range.

struct Command;
Encapsulation of an SQL command or query.

A Command be be either a one-off SQL query, or may use a prepared statement. Commands that are expected to return a result set - queries - have distinctive methods that are enforced. That is it will be an error to call such a method with an SQL command that does not produce a result set.

this(Connection con);
Construct a naked Command object

Params:
Connection con A Connection object to communicate with the server

this(Connection con, string sql);
Construct a Command object complete with SQL

Params:
Connection con A Connection object to communicate with the server
string sql SQL command string.

@property string sql();
Get the current SQL for the Command

@property string sql(string sql);
Set a new SQL command.

This can have quite profound side effects. It resets the Command to an initial state. If a query has been issued on the Command that produced a result set, then all of the result set packets - field description sequence, EOF packet, result rows sequence, EOF packet must be flushed from the server before any further operation can be performed on the Connection. If you want to write speedy and efficient MySQL programs, you should bear this in mind when designing your queries so that you are not requesting many rows when one would do.

Params:
string sql SQL command string.

void prepare();
Submit an SQL command to the server to be compiled into a prepared statement.

The result of a successful outcome will be a statement handle - an ID - for the prepared statement, a count of the parameters required for excution of the statement, and a count of the columns that will be present in any result set that the command generates. Thes values will be stored in in the Command struct.

The server will then proceed to send prepared statement headers, including parameter descriptions, and result set field descriptions, followed by an EOF packet.

If there is an existing statement handle in the Command struct, that prepared statement is released.

Throws:
MySQLEXception if there are pending result set items, or if the server has a problem.

void releaseStatement();
Release a prepared statement.

This method tells the server that it can dispose of the information it holds about the current prepared statement, and resets the Command object to an initial state in that respect.

ulong purgeResult();
Flush any outstanding result set elements.

When the server responds to a command that produces a result set, it queues the whole set of corresponding packets over the current connection. Before that Connection can embark on any new command, it must receive all of those packets and junk them.

http:
//www.mysqlperformanceblog.com/2007/07/08/mysql-net_write_timeout-vs-wait_timeout-and-protocol-notes/

void bindParameter(T)(ref T val, uint pIndex, ParameterSpecialization psn = PSN(0,false,SQLType.DEFAULT,0,null,true));
Bind a D variable to a prepared statement parameter.

In this implementation, binding comprises setting a value into the appropriate element of an array of Variants which represent the parameters, and setting any required specializations.

To bind to some D variable, we set the corrsponding variant with its address, so there is no need to rebind between calls to execPreparedXXX.

void bindParameterTuple(T...)(ref T args);
Bind a tuple of D variables to the parameters of a prepared statement.

You can use this method to bind a set of variables if you don't need any specialization, that is there will be no null values, and chunked transfer is not neccessary.

The tuple must match the required number of parameters, and it is the programmer's responsibility to ensure that they are of appropriate types.

void bindParameters(VariantN!(maxSize)[] va, ParameterSpecialization[] psnList = null);
Bind a Variant[] as the parameters of a prepared statement.

You can use this method to bind a set of variables in Variant form to the parameters of a prepared statement.

Parameter specializations can be added if required. This method could be used to add records from a data entry form along the lines of
auto c = Command(con, "insert into table42 values(?, ?, ?)");
c.prepare();
Variant[] va;
va.length = 3;
c.bindParameters(va);
DataRecord dr;    // Some data input facility
ulong ra;
do
{
   dr.get();
   va[0] = dr("Name");
   va[1] = dr("City");
   va[2] = dr("Whatever");
   c.execPrepared(ra);
} while(tod < "17:30");


Params:
VariantN!(maxSize)[] va External list of Variants to be used as parameters
ParameterSpecialization[] psnList any required specializations

ref Variant param(uint index);
Access a prepared statement parameter for update.

Another style of usage would simply update the parameter Variant directly

c.param(0) = 42;
c.param(1) = "The answer";


Params:
uint index The zero based index

bool execSQL(out ulong ra);
Execute a one-off SQL command.

Use this method when you are not going to be using the same command repeatedly. It can be used with commands that don't produce a result set, or those that do. If there is a result set its existence will be indicated by the return value.

Any result set can be accessed vis getNextRow(), but you should really be using execSQLResult() or execSQLSequence() for such queries.

Params:
ulong ra An out parameter to receive the number of rows affected.

Returns:
true if there was a (possibly empty) result set.

ResultSet execSQLResult(ColumnSpecialization[] csa = null);
Execute a one-off SQL command for the case where you expect a result set, and want it all at once.

Use this method when you are not going to be using the same command repeatedly. This method will throw if the SQL command does not produce a result set.

If there are long data items among the expected result columns you can specify that they are to be subject to chunked transfer via a delegate.

Params:
ColumnSpecialization[] csa An optional array of ColumnSpecialization structs.

Returns:
A (possibly empty) ResultSet.

ResultSequence execSQLSequence(ColumnSpecialization[] csa = null);
Execute a one-off SQL command for the case where you expect a result set, and want to deal with it a row at a time.

Use this method when you are not going to be using the same command repeatedly. This method will throw if the SQL command does not produce a result set.

If there are long data items among the expected result columns you can specify that they are to be subject to chunked transfer via a delegate.

Params:
ColumnSpecialization[] csa An optional array of ColumnSpecialization structs.

Returns:
A (possibly empty) ResultSequence.

void execSQLTuple(T...)(ref T args);
Execute a one-off SQL command to place result values into a set of D variables.

Use this method when you are not going to be using the same command repeatedly. It will throw if the specified command does not produce a result set, or if any column type is incompatible with the corresponding D variable

Params:
args A tuple of D variables to receive the results.

Returns:
true if there was a (possibly empty) result set.

bool execPrepared(out ulong ra);
Execute a prepared command.

Use this method when you will use the same SQL command repeatedly. It can be used with commands that don't produce a result set, or those that do. If there is a result set its existence will be indicated by the return value.

Any result set can be accessed vis getNextRow(), but you should really be using execPreparedResult() or execPreparedSequence() for such queries.

Params:
ulong ra An out parameter to receive the number of rows affected.

Returns:
true if there was a (possibly empty) result set.

ResultSet execPreparedResult(ColumnSpecialization[] csa = null);
Execute a prepared SQL command for the case where you expect a result set, and want it all at once.

Use this method when you will use the same command repeatedly. This method will throw if the SQL command does not produce a result set.

If there are long data items among the expected result columns you can specify that they are to be subject to chunked transfer via a delegate.

Params:
ColumnSpecialization[] csa An optional array of ColumnSpecialization structs.

Returns:
A (possibly empty) ResultSet.

ResultSequence execPreparedSequence(ColumnSpecialization[] csa = null);
Execute a prepared SQL command for the case where you expect a result set, and want to deal with it one row at a time.

Use this method when you will use the same command repeatedly. This method will throw if the SQL command does not produce a result set.

If there are long data items among the expected result columns you can specify that they are to be subject to chunked transfer via a delegate.

Params:
ColumnSpecialization[] csa An optional array of ColumnSpecialization structs.

Returns:
A (possibly empty) ResultSequence.

void execPreparedTuple(T...)(ref T args);
Execute a prepared SQL command to place result values into a set of D variables.

Use this method when you will use the same command repeatedly. It will throw if the specified command does not produce a result set, or if any column type is incompatible with the corresponding D variable

Params:
args A tuple of D variables to receive the results.

Returns:
true if there was a (possibly empty) result set.

Row getNextRow();
Get the next Row of a pending result set.

This method can be used after either execSQL() or execPrepared() have returned true to retrieve result set rows sequentially.

Similar functionality is available via execSQLSequence() and execPreparedSequence() in which case the interface is presented as a forward range of Rows.

This method allows you to deal with very large result sets either a row at a time, or by feeding the rows into some suitable container such as a linked list.

Returns:
A Row object.

bool execFunction(T, U...)(string name, ref T target, U args);
Execute a stored function, with any required input variables, and store the return value into a D variable.

For this method, no query string is to be provided. The required one is of the form "select foo(?, ? ...)". The method generates it and the appropriate bindings - in, and out. Chunked transfers are not supported in either direction. If you need them, create the parameters separately, then use execPreparedResult() to get a one-row, one-column result set.

If it is not possible to convert the column value to the type of target, then execFunction will throw. If the result is NULL, that is indicated by a false return value, and target is unchanged.

In the interest of performance, this method assumes that the user has the required information about the number and types of IN parameters and the type of the output variable. In the same interest, if the method is called repeatedly for the same stored function, prepare() is omitted after the first call.

Params:
T The type of the variable to receive the return result.
U type tuple of arguments
name The name of the stored function.
target the D variable to receive the stored function return result.
args The list of D variables to act as IN arguments to the stored function.

bool execProcedure(T...)(string name, ref T args);
Execute a stored procedure, with any required input variables.

For this method, no query string is to be provided. The required one is of the form "call proc(?, ? ...)". The method generates it and the appropriate in bindings. Chunked transfers are not supported. If you need them, create the parameters separately, then use execPrepared() or execPreparedResult().

In the interest of performance, this method assumes that the user has the required information about the number and types of IN parameters. In the same interest, if the method is called repeatedly for the same stored function, prepare() and other redundant operations are omitted after the first call.

OUT parameters are not currently supported. It should generally be possible with MySQL to present them as a result set.

Params:
T Type tuple
name The name of the stored procedure.
args Tuple of args

Returns:
True if the SP created a result set.

@property ulong lastInsertID();
After a command that inserted a row into a table with an auto-increment ID column, this method allows you to retrieve the last insert ID.

struct MySQLColumn;
A struct to hold column metadata

string schema;
The database that the table having this column belongs to.

string table;
The table that this column belongs to.

string name;
The name of the column.

uint index;
Zero based index of the column within a table row.

bool defaultNull;
Is the default value NULL?

string defaultValue;
The default value as a string if not NULL

bool nullable;
Can the column value be set to NULL

string type;
What type is the column - tinyint, char, varchar, blob, date etc

long charsMax;
Capacity in characters, -1L if not applicable

long octetsMax;
Capacity in bytes - same as chars if not a unicode table definition, -1L if not applicable.

short numericPrecision;
Presentation information for numerics, -1L if not applicable.

short numericScale;
Scale information for numerics or NULL, -1L if not applicable.

string charSet;
Character set, "" if not applicable.

string collation;
Collation, "" if not applicable.

string colType;
More detail about the column type, e.g. "int(10) unsigned".

string key;
Information about the column's key status, blank if none.

string extra;
Extra information.

string privileges;
Privileges for logged in user.

string comment;
Any comment that was set at table definition time.

struct MySQLProcedure;
A struct to hold stored function metadata

struct MetaData;
Facilities to recover meta-data from a connection

It is important to bear in mind that the methods provided will only return the information that is available to the connected user. This may well be quite limited.

string[] databases();
List the available databases

Note that if you have connected using the credentials of a user with limited permissions you may not get many results.

Returns:
An array of strings

string[] tables();
List the tables in the current database

Returns:
An array of strings

MySQLColumn[] columns(string table);
Get column metadata for a table in the current database

Params:
string table The table name

Returns:
An array of MySQLColumn structs

MySQLProcedure[] functions();
Get list of stored functions in the current database, and their properties

MySQLProcedure[] procedures();
Get list of stored procedures in the current database, and their properties


Also in this Directory

  • mysql.d Translated mysql.h
  • mysqln.cbp Code::Blocks project file
  • mysqln.depend Code::Blocks project file
  • mysqln.layout Code::Blocks project file
  • mysqld.sql SQL for test tables.
  • sha1.d Translated mysql.h

  • Page mostly generated by Ddoc.