Re: execute multiple queries with mysql c++ connector

On 07/03/17 17:58, Lew Pitcher wrote:
> 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.
>
Ok. That makes total sense. A token to make what is fundamentally a stateless connectiu9on into a stateful one, like cookies in a web browser.

> 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.
>
Thansk. I learnt something today.

Do transactions work with ISAM or is it just innodb - no I'll look that up myself. :-)

Ah. Doesnt work on ISAM files.

-- 
"Women actually are capable of being far more than the feminists will 
let them."