Run SQL commands on connection

[langabi]

I'm trying to run specific SQL queries at connection time, eg. SET time_zone = '+03:00';.

Is there a way to do so that will re-run this query every time the connection is re-established? As far as I can see, no, because if one chains a ->then() onto the original LazyConnection or Connection, this will NOT be rerun if the connection closes and is reopened.

Alternatively, one could use an 'open' or 'connect' event, but it doesn't look like there is one.

Any good ideas? Thanks!

[clue]

@langabi Thanks for reporting, makes a lot of sense and is indeed something we've already toyed around with!

Similar requests have come up (perhaps indirectly) in #133 and others.

Here's my concept for a future MysqlClient API as discussed in #147:

$db = new React\Mysql\MysqlClient('alice:secret@localhost/bookstore');

$db->on('open', fn () => $db->exec("SET time_zone = '+03:00'"));

$db->query('SELECT 1')->then(fn (QueryResult $result) => var_dump($result));

The idea is to fire the open event immediately after the database is ready to accept commands, right before the query passed to query() would actually be executed.

What do you think about this?

[clue]

The same API can also be used to set SQL modes:

$db = new React\Mysql\MysqlClient('alice:secret@localhost/bookstore');

$db->on('open', fn () => $db->exec("SET SQL_MODE='NO_BACKSLASH_ESCAPES'"));

$db->query('SELECT "foo\nbar"')->then(fn (QueryResult $result) => var_dump($result));

See also #139

[clue]

I believe this has been answered, so I'm closing this for now. Please come back with more details if this problem persists and we can always reopen this 👍

[langabi]

Thanks! I don’t know how I missed your earlier comment, sorry!

This looks perfect, and solves my use case. Thanks!

[pfk84]

Any news/progress on the 'open' event? I'd also need to SET the time_zone (to UTC) for any MySQL connection...

[clue]

Any news/progress on the 'open' event? I'd also need to SET the time_zone (to UTC) for any MySQL connection...

@pfk84 The open event will be implemented as part of the API discussed in #147.