>mydb.lockConnection() does create a new connection if it needs to. And that WILL throw an exception if there's a problem connecting to the DB server. So your code above WILL catch an exception if the connection information (server address/port/login/etc) is wrong.

But it does not. I am getting Access Violation instead of the exception if connection credentials is wrong:

Authentication failure: Access denied for user 'root'@'111.111.111.111' (using password: YES)

object.Error@(0): Access Violation
----------------
0x004436C0 in void database.Database.connect() at D:\code\CMS\source\database.d(34)
0x00403130 in _Dmain at D:\code\CMS\source\app.d(17)
0x00593C6F in D2rt6dmain211_d_run_mainUiPPaPUAAaZiZ6runAllMFZ9__lambda1MFZv
0x00593C33 in void rt.dmain2._d_run_main(int, char**, extern (C) int function(char[][])*).runAll()
0x00593B34 in _d_run_main
0x004433CC in main at D:\code\CMS\source\app.d(7)
0x005F0929 in mainCRTStartup
0x769262C4 in BaseThreadInitThunk
0x774D0FD9 in RtlSubscribeWnfStateChangeNotification
0x774D0FA4 in RtlSubscribeWnfStateChangeNotification
Program exited with code 1

On Thursday, 2 February 2017 at 04:04:15 UTC, Nick Sabalausky wrote:
> On 02/01/2017 01:54 PM, Suliman wrote:
>> Also I can't understand what is SQL Command and what exec is doing if
>> it's returning ulong?
>>
>>
>
> "struct Command" should not be used. It is old, and a bad design. This new release attempts to replace it with a better design. Hopefully, "struct Command" will be deleted in a later release.
>
> The "exec" functions are for commands like INSERT, UPDATE, DELETE, CREATE, etc (it is *not* for SELECT). It is for things that do NOT return actual rows of data. The "exec" functions return "rows affected" - the number of rows that were affected by your INSERT, or UPDATE, etc. Usually people ignore that number, but it's information the server sends back, and is sometimes useful to some people. For example, SQL administration tools usually tell you "# rows affected" after you run an INSERT/UPDATE/etc.
>
> If you are doing a SELECT, then you do NOT use "exec", you use "query" for SELECT. "query" returns a set of rows.
>
> Summary:
> ---------
>
> SELECT: Use query() or querySet() or queryRow(), etc.
>
> INSERT/UPDATE/DELETE/CREATE/DROP: Use exec(). Return value tells you how many rows were added/changed/deleted/etc.

Plz add this mention to readme.

On 02/02/2017 03:23 AM, Suliman wrote:
>> mydb.lockConnection() does create a new connection if it needs to. And
>> that WILL throw an exception if there's a problem connecting to the DB
>> server. So your code above WILL catch an exception if the connection
>> information (server address/port/login/etc) is wrong.
>
> But it does not. I am getting Access Violation instead of the exception
> if connection credentials is wrong:
>
> Authentication failure: Access denied for user 'root'@'111.111.111.111'
> (using password: YES)
>
> object.Error@(0): Access Violation
> ----------------
> 0x004436C0 in void database.Database.connect() at
> D:\code\CMS\source\database.d(34)
> 0x00403130 in _Dmain at D:\code\CMS\source\app.d(17)
> 0x00593C6F in D2rt6dmain211_d_run_mainUiPPaPUAAaZiZ6runAllMFZ9__lambda1MFZv
> 0x00593C33 in void rt.dmain2._d_run_main(int, char**, extern (C) int
> function(char[][])*).runAll()
> 0x00593B34 in _d_run_main
> 0x004433CC in main at D:\code\CMS\source\app.d(7)
> 0x005F0929 in mainCRTStartup
> 0x769262C4 in BaseThreadInitThunk
> 0x774D0FD9 in RtlSubscribeWnfStateChangeNotification
> 0x774D0FA4 in RtlSubscribeWnfStateChangeNotification
> Program exited with code 1
>

Ahh, ok, that's a bug. Will fix.

ResultSet querySet(Connection conn, string sql, ColumnSpecialization[] csa = null)

Could you explain last parameter?

`ColumnSpecialization[] csa = null`. I can't understand how to use it.

Could you give me your skype?

Also I think it's better to remove old deprecated methods at all, because it's add only mess.

On Thursday, 2 February 2017 at 08:38:32 UTC, Nick Sabalausky wrote:
> On 02/02/2017 03:23 AM, Suliman wrote:
>>> [...]
>>
>> But it does not. I am getting Access Violation instead of the exception
>> if connection credentials is wrong:
>>
>> Authentication failure: Access denied for user 'root'@'111.111.111.111'
>> (using password: YES)
>>
>> object.Error@(0): Access Violation
>> ----------------
>> 0x004436C0 in void database.Database.connect() at
>> D:\code\CMS\source\database.d(34)
>> 0x00403130 in _Dmain at D:\code\CMS\source\app.d(17)
>> 0x00593C6F in D2rt6dmain211_d_run_mainUiPPaPUAAaZiZ6runAllMFZ9__lambda1MFZv
>> 0x00593C33 in void rt.dmain2._d_run_main(int, char**, extern (C) int
>> function(char[][])*).runAll()
>> 0x00593B34 in _d_run_main
>> 0x004433CC in main at D:\code\CMS\source\app.d(7)
>> 0x005F0929 in mainCRTStartup
>> 0x769262C4 in BaseThreadInitThunk
>> 0x774D0FD9 in RtlSubscribeWnfStateChangeNotification
>> 0x774D0FA4 in RtlSubscribeWnfStateChangeNotification
>> Program exited with code 1
>>
>
> Ahh, ok, that's a bug. Will fix.

* Moreover, how do I close a connection or does it auto close?

* Does it support mysql_real_escape_string() like in php? This factor-in the db encoding to do he appriate encoding for '/\" ...

On 2/2/17 1:50 AM, Suliman wrote:
> On Thursday, 2 February 2017 at 05:28:10 UTC, Nick Sabalausky wrote:
>> Made a couple more long-needed changes while I'm at it:
>>
>> https://github.com/Abscissa/mysql-native-experimental
>> Tag: v0.2.0-preview2
>>
>> - For better clarity, renamed `mysql.db.MysqlDB` to
>> `mysql.pool.MySqlPool`.
>>
>> - Package mysql.connection no longer acts as a package.d, publicly
>> importing other modules. To import all of mysql-native, use `import
>> mysql;`
>
> Thanks! Could you explain about pool. I googled about it, and still
> can't understand when it up new connections?
>
> How can I imagine what connection is? Because without it hard to
> understand what difference between connections with and without pool.
>
> Am I right understand that if I use pool I can create connection
> instance one time in DB class constructor end every new connection will
> be created on demand?

Just to answer some of this, because I had questions about the pool as well.

The ConnectionPool is a mechanism to allow a limited resource (or a resource you want to limit, depending how you look at it) to be pooled for use. It's basically a free-list. It also abstracts away the details of opening a connection (i.e. server ip, username, password). It's nice because it makes opening a connection cleaner and straightforward.

The resulting connection can only be used in one fiber at a time, and the connection pool itself can only be used in one thread ever (there is no sharing of connection pools between threads). The resulting connection struct is reference counted, so it automatically releases back to the pool when it goes out of scope.

One issue I had with MysqlDB (now MySqlPool) is that it doesn't allow you to actually put a limit on the connections. In other words, you can't say "only allow 50 simultaneous connections". The effect is that you save the connection initialization, but you can't put a hard cap on connections used in your thread. If you have 10,000 fibers running at once, then you may get 10,000 connections to the database that are left open. If each of those is a socket, you are consuming a lot of resources during down times.

Not to mention that the allocation of the 10,000th connection has to first go on a linear search through the 9,999 other connections to find an open one (this seems like it could be solved better with a linked-list freelist?).

See here: https://github.com/mysql-d/mysql-native/issues/74

BTW, I solved this by copying MysqlDB into my local project and adding the appropriate parameter to the constructor :)

-Steve

On Thursday, 2 February 2017 at 14:08:19 UTC, Steven Schveighoffer wrote:
> On 2/2/17 1:50 AM, Suliman wrote:
>> On Thursday, 2 February 2017 at 05:28:10 UTC, Nick Sabalausky wrote:
>>> Made a couple more long-needed changes while I'm at it:
>>>
>>> https://github.com/Abscissa/mysql-native-experimental
>>> Tag: v0.2.0-preview2
>>>
>>> - For better clarity, renamed `mysql.db.MysqlDB` to
>>> `mysql.pool.MySqlPool`.
>>>
>>> - Package mysql.connection no longer acts as a package.d, publicly
>>> importing other modules. To import all of mysql-native, use `import
>>> mysql;`
>>
>> Thanks! Could you explain about pool. I googled about it, and still
>> can't understand when it up new connections?
>>
>> How can I imagine what connection is? Because without it hard to
>> understand what difference between connections with and without pool.
>>
>> Am I right understand that if I use pool I can create connection
>> instance one time in DB class constructor end every new connection will
>> be created on demand?
>
> Just to answer some of this, because I had questions about the pool as well.
>
> The ConnectionPool is a mechanism to allow a limited resource (or a resource you want to limit, depending how you look at it) to be pooled for use. It's basically a free-list. It also abstracts away the details of opening a connection (i.e. server ip, username, password). It's nice because it makes opening a connection cleaner and straightforward.
>
> The resulting connection can only be used in one fiber at a time, and the connection pool itself can only be used in one thread ever (there is no sharing of connection pools between threads). The resulting connection struct is reference counted, so it automatically releases back to the pool when it goes out of scope.
>
> One issue I had with MysqlDB (now MySqlPool) is that it doesn't allow you to actually put a limit on the connections. In other words, you can't say "only allow 50 simultaneous connections". The effect is that you save the connection initialization, but you can't put a hard cap on connections used in your thread. If you have 10,000 fibers running at once, then you may get 10,000 connections to the database that are left open. If each of those is a socket, you are consuming a lot of resources during down times.
>
> Not to mention that the allocation of the 10,000th connection has to first go on a linear search through the 9,999 other connections to find an open one (this seems like it could be solved better with a linked-list freelist?).
>
> See here: https://github.com/mysql-d/mysql-native/issues/74
>
> BTW, I solved this by copying MysqlDB into my local project and adding the appropriate parameter to the constructor :)
>
> -Steve

Do you mean that every new fiber open new connection? And when fiber is yield it's return connection to the pool?

Could you explain real case if rangification of ResultSet

http://semitwist.com/mysql-native-docs/v0.2.0-preview1/mysql/result/ResultSet.html

Does it's mean that I can write
foreach(x;result.empty) ? Or how to use it?

On Thu, 02 Feb 2017 06:50:34 +0000, Suliman wrote:

> On Thursday, 2 February 2017 at 05:28:10 UTC, Nick Sabalausky wrote:
>> Made a couple more long-needed changes while I'm at it:
>>
>> https://github.com/Abscissa/mysql-native-experimental Tag: v0.2.0-preview2
>>
>> - For better clarity, renamed `mysql.db.MysqlDB` to `mysql.pool.MySqlPool`.
>>
>> - Package mysql.connection no longer acts as a package.d,
>> publicly importing other modules. To import all of mysql-native, use
>> `import mysql;`
> 
> Thanks! Could you explain about pool. I googled about it, and still can't understand when it up new connections?

A database connection is just a socket.

Problem 1: database connections are expensive to set up. (Network connections in general are expensive -- in my testing, an AWS EC2 t2.micro instance can handle ~80 connections per second. I was primarily testing RPC / REST API systems, which is a fair bit more than 'open a socket, send a byte, receive a byte, close the socket'.)

Problem 2: database connections only offer one stream of data each direction.

Problem 3: my application needs to handle a bunch of requests that might occur at the same time, each of which talks to the database.

Solution: we'll have an object that manages a set of connections. It can reuse a connection, and it guarantees a connection is not reused when a previous user is still using it. It might automatically close a connection after enough time without that connection being used. It will automatically open a new connection when it has no free connections to hand out.

On 02/02/2017 07:10 AM, aberba wrote:
>
> * Moreover, how do I close a connection or does it auto close?
>

It closes in its destructor (although AIUI there are times when dtors don't get run). But it can be closed manually with Connection.close();

> * Does it support mysql_real_escape_string() like in php? This factor-in
> the db encoding to do he appriate encoding for '/\" ...

There is a function like that one that was contributed, mysql.escape.mysql_escape:

https://github.com/Abscissa/mysql-native-experimental/blob/master/source/mysql/escape.d#L4

But, it's better to use prepared statements, because then no escaping is needed, and there's no worry about forgetting to escape:

Prepared prepared = prepare(connection, "SELECT * FROM `someTable` WHERE i=? AND s=?");
prepared.setArgs(7, "hello");
ResultRange results = prepared.query();

The prepared statement is reference counted and will automatically be released when there are no references to it left.

On 02/02/2017 03:23 AM, Suliman wrote:
>> mydb.lockConnection() does create a new connection if it needs to. And
>> that WILL throw an exception if there's a problem connecting to the DB
>> server. So your code above WILL catch an exception if the connection
>> information (server address/port/login/etc) is wrong.
>
> But it does not. I am getting Access Violation instead of the exception
> if connection credentials is wrong:
>
> Authentication failure: Access denied for user 'root'@'111.111.111.111'
> (using password: YES)
>
> object.Error@(0): Access Violation
> ----------------
> 0x004436C0 in void database.Database.connect() at
> D:\code\CMS\source\database.d(34)
> 0x00403130 in _Dmain at D:\code\CMS\source\app.d(17)
> 0x00593C6F in D2rt6dmain211_d_run_mainUiPPaPUAAaZiZ6runAllMFZ9__lambda1MFZv
> 0x00593C33 in void rt.dmain2._d_run_main(int, char**, extern (C) int
> function(char[][])*).runAll()
> 0x00593B34 in _d_run_main
> 0x004433CC in main at D:\code\CMS\source\app.d(7)
> 0x005F0929 in mainCRTStartup
> 0x769262C4 in BaseThreadInitThunk
> 0x774D0FD9 in RtlSubscribeWnfStateChangeNotification
> 0x774D0FA4 in RtlSubscribeWnfStateChangeNotification
> Program exited with code 1
>

I'm unable to reproduce this problem. Are you trying to use the connection after it fails to connect? It looks like that's what's happening, you're using a null reference.