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