Hello,

I got

https://github.com/rejectedsoftware/mysql-native/blob/master/README.md

the native D mysql driver.

But then, what to do with it ?

Do someone use it ?
I just sent a mail to the maintainer.

I would use it for prepared statements/stored procedures but no clue on how to use it.

Thanks,

Larry

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. :)

So many thanks !

Yes it is not very polished yet.

But now I see how I can manage it, I will follow your lead and try things. :)

Thanks again,

Larry

On Friday, 9 August 2013 at 07:31:09 UTC, Larry wrote:
> So many thanks !
>
> Yes it is not very polished yet.
>
> But now I see how I can manage it, I will follow your lead and try things. :)
>
> Thanks again,
>
> Larry

Great! Remember if you have any issues or find any bugs raise an issue on github. I've found the developer there to be very responsive and welcoming of reports or pull requests. After all, if we all contribute it helps us all out. :)

:)

You are perfectly right !

On Thu, Aug 08, 2013 at 08:28:21PM +0200, Gary Willoughby wrote:
> 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.

In that case, it should be written like this:

	...
	command.prepare();
	scope(exit) command.releaseStatement();

	ulong rowsAffected;
	foreach ...

This is exactly the kind of situation scope guards are designed for. So use them! :-)


T

-- 
If a person can't communicate, the very least he could do is to shut up. -- Tom Lehrer, on people who bemoan their communication woes with their loved ones.

On Saturday, 10 August 2013 at 18:28:31 UTC, H. S. Teoh wrote:
> In that case, it should be written like this:
>
> 	...
> 	command.prepare();
> 	scope(exit) command.releaseStatement();
>
> 	ulong rowsAffected;
> 	foreach ...
>
> This is exactly the kind of situation scope guards are designed for. So
> use them! :-)
>
>
> T

It's sometimes not that simple if you are within try/catch blocks or re-using the command for other queries.

On Friday, 25 October 2013 at 08:07:02 UTC, Gary Willoughby wrote:
> On Saturday, 10 August 2013 at 18:28:31 UTC, H. S. Teoh wrote:
>> In that case, it should be written like this:
>>
>> 	...
>> 	command.prepare();
>> 	scope(exit) command.releaseStatement();
>>
>> 	ulong rowsAffected;
>> 	foreach ...
>>
>> This is exactly the kind of situation scope guards are designed for. So
>> use them! :-)
>>
>>
>> T
>
> It's sometimes not that simple if you are within try/catch blocks or re-using the command for other queries.

Just a side-note: In the current version, the parameter packet is rebuilt on each request, so some of the advantages of reusing the statement might be lost if you only rebind a couple of the parameters.
https://github.com/rejectedsoftware/mysql-native/blob/master/source/mysql/connection.d#L4169

In my rewrite, I'm modifying the packet in-place, so it might be more performant.
https://github.com/simendsjo/mysqln/blob/master/source/mysql/protocol/packets.d#L782