mysqld

A D wrapper for the MySQL C API (libmysql.lib) - MySQLD. Source file mysqld.d.

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.

It does not aim to be comprehensive. The MySQL C API documentation is excellent, and it should be straightforward for advanced users to derive a class from Command where native mysql_xxx_xxx() calls can be used.

It has currently only been compiled and unit tested on Ubuntu with D2.055, and the latest MySQL client library. The required MySQL header files were translated the hard way, since I don't have the benefit of htod ln my development environment.

there are numerous examples of usage in the unittest sections.

The file mysqld.sql can be used to generate the tables requires by the unit tests.

class MySQLDException: object.Exception;
An exception type for MySQLD

If the module encounters a problem it will throw an exception of type MySQLDException. information.

this(MYSQL* h, string file, int line);
An exception constructor from MySQL API error information.

this(string msg, string file, int line);
An exception constructor from a string - used when MySQLD sees a problem.

alias MyX;
MySQLDException AKA MyX

class Connection;
A class to encapsulate struct MYSQL and its associated functions.

The character set information stuff is currently omitted, as converting the header files for that to D turned out to be a nightmare, and will have to be done slowly later.

this();
Default constructor - Initialize Connection object

this(string connectionString);
Constructor to create an opened Connection

Params:
string connectionString <host=hostname/IP>;user=username;pwd=password;db=database>

MYSQL* handle();
Get connection handle

Returns:
Pointer to a MYSQL struct - used by many mysql_xxx_xx() calls.

void switchUser(string user, string password, string database = null);
Change user.

Params:
string user user name
string password password
databse database to use (optional)

string stats();
Get a string showing the current server statistics

Returns:
A string like "Uptime: 6646 Threads: 2 Questions: 282 Slow queries: 0 Opens: 42 Flush tables: 1 Open tables: 1 Queries * per second avg: 0.42"

string currentDB();
Query for the current database

Returns:
The name of the current database.

void skipResult();
Get rid of a query result.

Pulls any data from the server, then frees it. Use if you have executed a query but are only interested in the status.

bool serverOK();
Ping the server to make sure it is still there

Returns:
true if all is well.

string clientVersion();
Get the client version as a string.

Returns:
Version string.

uint clientVersionNumber();
Get the client version as a number

Returns:
Version number.

string hostInfo();
Determine what host we are connected to.

Returns:
A sring like "localhost via TCP/IP".

string serverVersion();
Get the server version as a string.

Returns:
Version string.

uint serverVersionNumber();
Get the server version as a number

Returns:
Version number.

uint protocol();
Get the protocol that is in use

Returns:
? not documented

void open(string cs);
Open a connection to a specified host.

Params:
string cs A connection string like <host=hostname/IP>;user=username;pwd=password;db=database>

void open(string host, string user, string pwd, string db);
Open a connection to a specified host/database.

Params:
string host Host name or IP address
string user User name
string pwd password
string db Database name

void close();
Close the connection

bool autoCommit();
Determine the auto-commit mode

Returns:
true if auto-commit is on.

bool autoCommit(bool mode);
Set the auto-commit mode.

Params:

Returns:
The previous mode.

void initialCommand(string sql);
Set a SQL command string to be executed on connection.

See the MySQL documentation for mysql_options() for details of options that can be set.

Params:

void compress();
Tell the client to compress its communications.

void connectTimeout(uint seconds);
Modify the connection time-out period.

Params:

void readTimeout(uint seconds);
Modify the time-out period for reads.

Params:

void writeTimeout(uint seconds);
Modify the time-out period for writes.

Params:

void useNamedPipe();
Tell the client to use a named pipe.

A Windows option - named pipe is default for Linux

void reconnect(bool tryReconnect);
Tell the client to attempt to reconnect if the connection is lost.

void reportTruncation(bool report);
Tell the server to report truncation if the buffer provided for an out parameter is not big enough.

class Command;
A class to encapsulate struct MYSQL_STMT and its associated functions.

Methods execXXX are provided to execute simple SQL queries/commands, to execute prepared statements, and to return a reader for a result set. There are also ancillary methods to create parameters for prepared statements, etc.

this(Connection con);
Constructor to produce a Command object with no SQL

Params:
Connection con Reference to a Connection

this(Connection con, string sql);
Constructor to produce a Command object with a current SQL command string

Params:
Connection con Reference to a Connection.
string sql A SQL string.

int prepare();
Prepare a command.

Prepare is optional. You only call it in cases where you are going to bind D variables as input sources or output targets.

You don't need to call prepare befor you call execScalar() - it will do it for you.

Returns:
The number of input parameters that were found in the SQL

void close();
Put the Command object back into an initial state, with no associated SQL.

string sql();
Set the SQL for the Command object.

Returns:
The SQL string currently set for the Command.

string sql(string s);
Set the SQL for the Command object.

Params:
sql An SQL string.

Returns:
The previously set string.

Connection connection();
Gets a reference to the connection that was used to create this object.

void cancel();
Attempts to cancel an ongoing command

void reset();
Put the Command object back into the state before prepare() and execXXX().

The current SQL is retained.

void clearParams();
Clears the arrays of parameters/bind structs created for this object.

void createParam(T)(ref T target, int direction = ParamIn, enum_field_types tt = enum_field_types.MYSQL_TYPE_DATETIME);
Create a parameter - in, inout, or out.

The new parameter is appended to the appropriate array of parameters (in or out or both) associated with the Command object

Params:
T The type for the parameter.
target A value of that type from which input will be taken or into which output will be placed.
direction ParamIn, ParamInOut, or ParamOut - defaults to ParamIn
tt Temporal values only - exactly what type, MYSQL_TYPE_TIME/..DATE/..DATETIME/..TIMESTAMP.

void createChunkedParam(T)(ref T target, uint chunkSize, int direction = ParamIn, InChunkDelegate inCD = null, OutChunkDelegate outCD = null);
Create a parameter for chunked transfer - in, inout, or out.

The new parameter is appended to the appropriate array of parameters (in or out or both) associated with the Command object.

The method can be used in various ways. The chunking either way can be done moving chunks from or to the target object, or a delegate can be specified to source or sink the data. The delegate signature for input parameters is:
void[] delegate(ref uint)
That for an out parameter is:
uint delegate(void[], uint)

Params:
T The type for the parameter.
target A value of that type from which input may be taken or into which output may be placed.
direction ParamIn, ParamInOut, or ParamOut - defaults to ParamIn
inCD An InChunkDelegate to source the data.
outCD An OutChunkDelegate to sink the data.

void updateIP(T)(ref T target, T newValue);
Update an input parameter prior to a further execution of execPrepared()

This method is required because the in bind properties of string or byte[] parameters need to be adjusted to take into account the length of the new value.

It's effect is equivalent to a simple assignment of a new value in the case of non-array variables.

There is a caveat. You can't do this trick with a chunked input parameter that is tied to a delegate to source the data.

Params:
T the variable type.
target the variable that will source the data for a parameter.
newValue the value to be set for the target variable.

int execSQL(out ulong ra);
Execute a plain SQL command.

You can use this method to execute any SQL statement.

The method will provide information about the outcome in addition to the return value. If it was a non-query - something like INSERT, DELETE, or UPDATE, the Command object's disposition property will be set to NON_QUERY, and the out parameter value should be the number of rows affected.

If the statement was something such as a SELECT, with a potential (though possibly empty) result set, then disposition will be set to RESULT, in which case the getReader method will give you access if, and the Command object's fields property will be set to the number of columns in the result set. The out parameter ra will be set to the number of rows selected.

Alternatively, it may be set to RESULT_MISSING, which means the result was missing, in which case you can take whatever action is appropriate.

If this method generates a result set, and you subsequently call another execXXX method, the saved result set will be wasted.

Returns:
The result disposition.

Params:
ulong ra out - the number of rows affected or in the result set.
target the variable that will source the data for a parameter.
newValue the value to be set for the target variable.

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

Almost any SQL statement can be prepared, with input/output parameters represented by '?' in the SQL statement. If you have such placemarkers, you must bind them to variables in your program using the createParam method as many times as the are ? placeholders, and in the corresponding order. Do this before you call prepare().

If you prepare a SELECT, or something else that returns a result set, you can bind output parameters to receive column values from the result set, also using createParam. Do this before you call execPrepared();

If this has been done, after a call to execPrepared, the values for the first returned row will have been populated into the bound variables. You can then get the values from subsequent rows by calling getNextBoundRow(). In this case the Command object's disposition property is set to BOUND_OUTPUT, and the fields property is set to the number of bound output variables.

If the statement sql is a CALL to a stored procedure with explicit out param(s), in theory this should result in a single-row result set containing the out parameter values, and these should be transferred to the bound variables. However, according to the MYSQL documentation, this will not work until version 6. Better make your SP return a result set.

If you have not bound any input or output parameters then you could just execSQL for things like INSERT, UPDATE, and DELETE, and unless you plan to use the same statement many times in a session, this may be more efficient.

If you use execReader for SELECTs, or anything else that returns a result set, then you must create output parameters to bind the returned column data. Otherwise execPrepared will throw an exception, as the resulting data will not be accessible.

execPrepared will accept both input and output parameters created by createChunkedParam, with or without a delegate to source or sink the data.

If there are no chunked out parameters, execPrepared buffers the result set to the client.

Returns:
The result disposition, with the number of rows affected or selected in the out parameter.

Params:
ulong ra out - the number of rows affected or in the result set.
target the variable that will source the data for a parameter.
newValue the value to be set for the target variable.

bool outValueNull(int out_param_number);
Test for null output values

After calling execPrepared you may need to check if some of the out columns were NULL. Call this method with the out param number - as per expected output '?' placeholders in the SQL.

Params:
int out_param_number The out parameter number - zero based.

Returns:
true if the column value was NULL.

bool getNextBoundRow();
Get the next row of a result set and populate the values into bound variables.

After a call to execPrepared, the values for the first returned row will have been populated into the bound variables. You then get the values from subsequent rows by calling getNextBoundRow(). If there were chunked output parameters, this will involve a fetch from the server.

Returns:
false if there are no more rows.

Reader getReader();
Get a Reader objectcorresponding to the result set of a previous execSQL.

This method gives access to the results of a SELECT or some other SQL statement that generates a result set. You should probably have used used execReader in the first place for such cases.

The result set is buffered to the client.

Returns:
A Reader object for the result set.

Reader execReader();
Performs a query, and returns a Reader object for the result set

This method is to be used for ad-hoc SELECTs and other queries that return a result set. The returned reader can be used to iterate through the resulting rows and access column data. The result set is buffered to the client.

Returns:
A Reader object.

void execScalar(T)(ref T target, uint chunkSize = 0, enum_field_types tv = enum_field_types.MYSQL_TYPE_DATE);
Perform a query, and populate the value of a single column value into a D variable.

If the query does not produce a result set, or produces a result set that has more than one column then execScalar will throw. If it produces a result set with multiple rows, only the first row will be considered. If it is not possible to convert the column value to type T, then execScalar will throw.

If your target column is a string or binary string type, you can ask that it be fetched in chunks, by specifying a non-zero chunkSize. This may be easier on the network, but more importantly it means that you don't have to size the receiving array before you call execScalar - it will size it appropriately. It will also call prepare(), and it will be an error if you do so too.

Params:
T A variable type.
target An instance of that type, if it is a string type, the length should be set to what can be accepted.
chunkSize The size of chunk to use for transfer. If zero the transfer is not chunked.
tv One of the MySQL time types to allow correct generation of the result.

Returns:
A value of the requested type.

class Reader;
A class to give convenient access to a result set, as described by MYSQL_RES.

At the current state of development, a the result set in question will always have been buffered to the client.

this(MYSQL_RES* pres);
Constructor to tie the reader to a MYSQL_RES struct.

You will not normally need to use this constructor in your code, as usually you will be handed a Reader by Command.getReader() or Command execReader().

Params:

void indexFields();
Create an index so you can refer to columns by name.

This will slow things down, so access to column values by name is optional. Use an integer index to access row data if you want the best performance.

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

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

@property Reader front();
Make the reader behave as a random access range - front

You don't get anything useful back from these methods - they just return a reference to the reader object, but the current row will have been adjusted accordingly, and the remaining range trimmed down in the case of popFront and popBack.

Afterwards you need to call getRow to populate the values into a matching struct.

@property Reader back();
Make the reader behave as a random access range - back

Reader popFront();
Make the reader behave as a random access range - popFront()

Reader popBack();
Make the reader behave as a random access range - popBack

Reader opIndex(size_t i);
Make the reader behave as a random access range - opIndex

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

void getRow(S)(ref S s);
Populate a matching struct with the values from the current row.

The struct must have field names corresponding one to one - order and name - with the column names and types in the result set. The only wiggle room is that integer types in the struct can be bigger than necessary - e.g. long for a column that is byte. However, signed/unsigned mismatches are not tolerated. If the same struct was used for the previous row the name and type checking is skipped.

Parameters:
s - a struct instance of a suitable type

T getValue(T)(out T target, int index, out bool isnull, bool getString = false, out string os = _dummy);
Get a column value by index into a D variable from the current row

Params:
T The type of the target variable.
target An instance of that type.
index The column number within the result set starting at zero.
isnull An out variable that will be set to true if the column was NULL.
getString An indicator that getValue should stop short and return the string value of the column.
os An out string variable for that purpose.

Returns:
The value populated.

T getValue(T)(out T target, string colName, out bool isnull);
Get a column value by column name into a D variable.

You must first call indexFields() or this method will throw.

Params:
T The type of the target variable.
target An instance of that type.
colName The column name.
isnull An out variable that will be set to true if the column was NULL.

Returns:
The value populated.

string asString(int index);
Get a column value as a string by column index.

Params:
int index The column index within the result set.

Returns:
The string value for the column "(NULL)" if NULL.

string asString(string colName);
Get a column value as a string by column name.

You must first call indexFields() or this method will throw.

Params:
index The column index within the result set.

Returns:
The string value for the column "(NULL)" if NULL.


Page generated by Ddoc.