Re: execute multiple queries with mysql c++ connector

On 07/03/17 17:41, Lew Pitcher wrote:
> On Tuesday March 7 2017 12:19, in comp.databases.mysql, "The Natural
> Philosopher" <tnp_at_invalid.invalid> wrote:
>
>> On 07/03/17 16:46, Axel Schwenke wrote:
>>> On 07.03.2017 11:56, kushal bhattacharya wrote:
>>>> Cant i use semicolon in c++ mysql connector too ?
>>>
>>> Obviously not. Haven't you just got an error message for that?
>>>
>>>> i Have to run the above queries simultaneously not as a separate query
>>>> statement
>>>
>>> 1. what you have shown before was a single statement ending with a
>>> semicolon, not multiple statements. You're blabbing incoherently.
>>>
>>> 2. no, you cannot run two (or more) statements simultaneously in one
>>> connection. It's impossible. You might be able to *send* multiple
>>> statements at once [1] but it's highly discouraged. But even in that case
>>> the statements will be executed one after the other.
>>>
>>> 3. whenever you think you have to run two (or more) statements
>>> simultaneously to achieve a certain result, you're doing it wrong.
>>>
>>
>> How can you therefore create an atomic unit of two or more queries?
>>
>> I.e. lest suppose you want to read a number, like a bank balance, and
>> subtract something from it and write it back, but you dont want some
>> other asynchronous process to be doing the same?
>>
>> No alternatives to locks?
>
> Such things are called "transactions", and are delimited by the SQL statements
> START TRANSACTION;
> or
> BEGIN;
> to initiate the transaction, and
> COMMIT;
> or
> ROLLBACK;
> to terminate it.
>
> The
> START TRANSACTION;
> or
> BEGIN;
> statement tells MySQL where the transaction begins. MySQL will "remember" the
> state of the database at this point.
>
> The
> COMMIT;
> statement tells MySQL to make permanent all changes made by the SQL statements
> executed between the START TRANSACTION (or BEGIN) and this statement.
> The
> ROLLBACK;
> statement tells MySQL to undo all changes made by the SQL statements executed
> between the START TRANSACTION (or BEGIN) and this statement.
>
> Any sql executed between these delimiters will be treated as a single unit of
> work, with databases and database records locked and data guaranteed
> accordingly.
>
> For example:
> START TRANSACTION;
> SELECT _at_A:=SUM(salary) FROM table1 WHERE type=1;
> UPDATE table2 SET summary=_at_A WHERE type=1;
> COMMIT;
>
> Reference: https://dev.mysql.com/doc/refman/5.7/en/commit.html
>
> [snip]
>
>
Ok, that stirs memories with me, BUT given that Axel has just stated that the semicolon stuff doesnt work with C API, presumably these are [Quoted] all separate commands sent via the Mysql socket, so how does it know that they COMMIT is coming from the same person who sent the TRANSACTION ???

-- 
"I guess a rattlesnake ain't risponsible fer bein' a rattlesnake, but ah 
puts mah heel on um jess the same if'n I catches him around mah chillun".