Re: execute multiple queries with mysql c++ connector

On 09.03.2017 18:52, kushal bhattacharya wrote:
>
> I want the queries to be written just as if i am writing it in sql editor
> with semicolons in between so that it runs on the same context and
there is
> no chance that due to multi threading there may be interference
from other
> connections if i do it as a seperate query statement which i am
observing sometimes

If you think you observed this then either your observation is wrong or something weird happened. I.e. you you could have experienced a loss of connection and then a (silent) reconnect. Also phpMyAdmin behaves differently in that aspect: it does not retain the connection between page reloads.

But as said before:

1. queries run over the same connection (in connector/C++: from the same sql::Connection object) are guaranteed to use the same context. Except for connection loss and reconnect. I suggest to turn reconnect off.
2. if you type multiple queries, separated by semicolons, into the MySQL command line client, they will be separated by the command line client and sent to the MySQL server separately. They will hence also be executed separately. And the server will never see the semicolons you have typed. This is a feature of the MySQL command line client and if you want to have it in your own program, you must implement it yourselves (but why?)
3. in most cases it makes no sense to send multiple queries at once. This is so for several reasons:

3a) all statements require the status to be read, many statements return a result set. It's much easier to read the status and the result set after each single statement, as to first send all statements and then check all the results at once.

3b) often the program flow depends on the outcome of a statement. Depending on the success of one query your program might send one or the other query or it might even stop completely on a failure. Note: the MySQL command line client implements some logic too: if a query fails, it stops the execution (ignores all subsequent queries)

3c) even if a connector allows to send multiple queries, it's a good thing to disable it. Because by disabling it, the whole class of SQL injection attacks becomes useless for the attacker.

TL;DR: if you use connector/C++, send your queries one by one and implement proper status checks. Read up on transactions if you need atomicity. Turn off automatic reconnects. Read up on SQL injection attacks. Received on Fri Mar 10 2017 - 09:02:21 CET