Sql -> Any tuto ?

[Gary Willoughby]

On Thursday, 8 August 2013 at 11:18:16 UTC, Larry wrote:
> https://github.com/rejectedsoftware/mysql-native/blob/master/README.md
>
> the native D mysql driver.
>
> But then, what to do with it ?

I've used this quite a bit and works quite nicely. I've found a 
few bugs while using it and they have been promptly fixed, so if 
you find any bugs just file an issue on github. With that said 
here's how i use it:

Inserting:

	auto connection = new Connection("host", "user", "password", 
"database");
	auto command    = Command(connection);
	command.sql     = "INSERT IGNORE INTO rule (id, severity, 
description) VALUES (?, ?, ?)";
	command.prepare();

	ulong rowsAffected;

	foreach (Rule rule; rules)
	{
		command.bindParameter(rule.id, 0);
		command.bindParameter(cast(ubyte)rule.severity, 1);
		command.bindParameter(rule.description, 2);
		command.execPrepared(rowsAffected);
	}

	command.releaseStatement();

It's quite simple really but there are a few things to remember. 
First *always* call command.releaseStatement(); when you have 
finished with a prepared statement, it leaks memory if you don't. 
Second the bound parameters must be lvalues. i.e. variables that 
can be access by reference. For example, you can't do this as it 
will crash.:

	command.bindParameter(123, 0);

Third, rowsAffected is required by the execPrepared() method 
whether you use it later or not.

There are other bindParameter overloads that allow binding from 
tuples or variants but this was not as intuitive as using the 
above way.

Selecting:

	auto connection = new Connection("host", "user", "password", 
"database");
	auto command    = Command(connection);
	command.sql     = "SELECT id, severity, statusId FROM rule WHERE 
id = ?";

	command.bindParameter(rule.id, 0);
	auto results = command.execPreparedResult();

	auto id       = results[0][0].coerce!uint; // ugghh!
	auto severity = results[0][1].coerce!uint; // ugghh!
	auto statusId = results[0][2].coerce!uint; // ugghh!

The results array is a funky iterable collection of variants. 
This is the only bit i hate, dealing with these is horrible but i 
can't think of a better solution at the minute. There is a method 
that takes a structure to fill from the results but i couldn't 
get it to work.

Stored procedures:

I've not used these but there is two methods available:

	execFunction() - execute a stored function with D variables as 
input and output.
	execProcedure() - execute a stored procedure with D variables as 
input.

I assume it's something like this:

	auto connection = new Connection("host", "user", "password", 
"database");
	auto command    = Command(connection);
	bool success    = command.execProcedure("procedureName", arg1, 
arg2, ...);
	bool success    = command.execFunction("procedureName", 
resultStruct, arg1, arg2, ...);

Conclusion:

Look at the connection.d file in the library and especially at 
the Connection class' methods. Read the comments in there and try 
to figure out how it works. :)