Re: execute multiple queries with mysql c++ connector
Date: Tue, 07 Mar 2017 12:58:24 -0500
Message-ID: <o9ms6p$1uj$1_at_dont-email.me>
[Quoted] On Tuesday March 7 2017 12:49, in comp.databases.mysql, "The Natural Philosopher" <tnp_at_invalid.invalid> wrote:
> 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 > 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 ???
AFAIK, MySQL ties everything together by connection. I don't know the C++ API (I've only used the C and PHP APIs), so the details may differ, but in essence, MySQL treats the "handle" that it gives you when you establish a "connection" as the element to tie activities together. All the SQL piped to MySQL through a single unique connection handle (perhaps a connection instance in C++) are treated as coming from the same source, and executed sequentially.
[Quoted] The MySQL Connector/C++ documentation contains this example code...
sql::Driver* driver = get_driver_instance(); std::auto_ptr<sql::Connection> con(driver->connect(url, user, pass)); In this case, con() would be the connection instance, the "handle", if you will, and MySQL would treat all SQL sent through that handle as coming from the same source, serially.
-- Lew Pitcher "In Skills, We Trust" PGP public key available upon requestReceived on Tue Mar 07 2017 - 18:58:24 CET