www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.learn - Sql -> Any tuto ?

reply "Larry" <deco33 hotmail.fr> writes:
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
Aug 08 2013
parent reply "Gary Willoughby" <dev nomad.so> writes:
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. :)
Aug 08 2013
next sibling parent reply "Larry" <deco33 hotmail.fr> writes:
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
Aug 09 2013
parent reply "Gary Willoughby" <dev nomad.so> writes:
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. :)
Aug 09 2013
parent "Larry" <deco33 hotmail.fr> writes:
:)

You are perfectly right !
Aug 09 2013
prev sibling parent reply "H. S. Teoh" <hsteoh quickfur.ath.cx> writes:
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.
Aug 08 2013
parent reply "Gary Willoughby" <dev nomad.so> writes:
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.
Oct 25 2013
parent "simendsjo" <simendsjo gmail.com> writes:
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
Oct 25 2013