execute multiple queries with mysql c++ connector

[kushal bhattacharya]

Is it possible to run multiple queries using mysql c++ connector .If so how ?
Actually when i run multiple queries with ';' i am getting error from mysql saying to refer to manual but when i run it in mysql directly it runs properly.I have seen on stackoverflow that i have to set option of multiple statements to true but this really doesn't work.

[Jerry Stuckle]

On 2/22/2017 11:50 PM, kushal bhattacharya wrote:
> Is it possible to run multiple queries using mysql c++ connector .If so how ?
> Actually when i run multiple queries with ';' i am getting error from mysql saying to refer to manual but when i run it in mysql directly it runs properly.I have seen on stackoverflow that i have to set option of multiple statements to true but this really doesn't work.
>

As I suggested in comp.lang.c, please show the code you're trying to
execute and what error messages you receive.

--
==================
Remove the "x" from my email address
Jerry Stuckle
jstu...@attglobal.net
==================

[kushal bhattacharya]

suppose code is
insert into table set val1=?,val2=?,val3,?;select last_insert_id() as id ;

[Kees Nuyt]

And what were the error messages?
--
Kees Nuyt

[Jerry Stuckle]

As well as the actual code. The above is not valid code - it's just a
partial SQL statement.

But it seems obvious the OP isn't interested in knowing what the answer
to his problem is, since he won't provide full information.

[kushal bhattacharya]

error message is error near the part where ';' is provided and its saying to refer to the manual of mysql but when i run it on phpmyadmin it runs propewrly without showing any arror

[kushal bhattacharya]

@Jeery Stuckle i can't provide here the actual query because its really very large and yes i am interested in knowing the answer but i am trying to keep my question short and precise which can be understood by you

[Jürgen Exner]

On Fri, 24 Feb 2017 21:14:42 -0800 (PST), kushal bhattacharya
<bhattachar...@gmail.com> wrote in comp.databases.mysql:

>error message is error near the part where ';' is provided and its saying to refer to the manual of mysql but when i run it on phpmyadmin it runs propewrly without showing any arror

Your postings are unreadable.
Please learn how to use Usenet!

Limit your line lenght to somewhere around 75 characters.
And quote sufficient context such that people know what you are talking
about.

The fact that you have elected an unsuitable interface (Google Groups)
to link into Usenet does not exempt you from following basic Usenet
ettiquette which has been a proven custom for 30 years.

jue

[Luuk]

On 25-02-17 06:14, kushal bhattacharya wrote:
> error message is error near the part where ';' is provided and its saying to refer to the manual of mysql but when i run it on phpmyadmin it runs propewrly without showing any arror
>

Do not describe what is going wrong, but copy/paste the error.

This is because it will be hard, bases on your description, to
find out which one of the errors described in the docs is talked about.
( https://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html )

like this:
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

or this:
ERROR 1366 (22007): Incorrect integer value: 'A' for column 'i' at row 1

Try to create a simple example with te same result, when you can not, or
will not provide the real SQL statement.

The code that gives above errors is:
[root@test]> DESC test;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| i | int(11) | NO | PRI | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

[root@test]> insert into test(i) values('5');select last_insert_id() as id ;
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
+----+
| id |
+----+
| 0 |
+----+
1 row in set (0.00 sec)

[Jerry Stuckle]

On 2/25/2017 12:16 AM, kushal bhattacharya wrote:
> @Jeery Stuckle i can't provide here the actual query because its really very large and yes i am interested in knowing the answer but i am trying to keep my question short and precise which can be understood by you
>

Then post an example query that shows the problem. But I've never seen
a "real query" that would be "too long".

Without accurate information we can't help you. And you seem to refuse
to provide accurate information. Only paraphrasing.

[Axel Schwenke]

Then *why* can't you just copy and paste your query and the error message?

That error you made is a simple one: there does not belong a semicolon (";")
at the end of the query - not if you send it through any connector. The ; is
only for the command line client (mysql or phpAdmin) to determine where the
query actually ends. This is necessary because a query can span multiple
lines. It's however not part of the query and is thus not sent to the MySQL
server.

[kushal bhattacharya]

example is as follows :-
insert into table values(value to provide here);select last_insert_id() as id;
i cant run this query in c++ library of mysql it says
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; ' but it runs properly well in phpmyadmin sql editor

[kushal bhattacharya]

hi,
Am i missing some elaborations now?

[Axel Schwenke]

On 05.03.2017 05:40, kushal bhattacharya wrote:
> Am i missing some elaborations now?

Maybe you read my post again.
I already guessed your problem and answered it.

[kushal bhattacharya]

Cant i use semicolon in c++ mysql connector too ? i Have to run the above queries simultaneously not as a separate query statement

[Jerry Stuckle]

On 3/7/2017 5:56 AM, kushal bhattacharya wrote:
> Cant i use semicolon in c++ mysql connector too ? i Have to run the above queries simultaneously not as a separate query statement
>

Why?

And you still haven't shown us the code. You claim you have a problem
with C++ mysql connector, but you haven't provided any C++ code.

You aren't going to get good answers to any of your questions without
providing *accurate* code. This means if you are asking about C++ mysql
connector, the C++ code you are using.

[Axel Schwenke]

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.


[1] https://dev.mysql.com/doc/refman/5.7/en/c-api-multiple-queries.html
but I don't think Connector/C++ exposes that functionality

[The Natural Philosopher]

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?

>
> [1] https://dev.mysql.com/doc/refman/5.7/en/c-api-multiple-queries.html
> but I don't think Connector/C++ exposes that functionality
>

--
"I am inclined to tell the truth and dislike people who lie consistently.
This makes me unfit for the company of people of a Left persuasion, and
all women"

[Lew Pitcher]

On Tuesday March 7 2017 12:19, in comp.databases.mysql, "The Natural

Philosopher" <t...@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 @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

Reference: https://dev.mysql.com/doc/refman/5.7/en/commit.html

[snip]


--
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request

[The Natural Philosopher]

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 ???


--
"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".

[Lew Pitcher]

On Tuesday March 7 2017 12:49, in comp.databases.mysql, "The Natural

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.

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.

[The Natural Philosopher]

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."

[Axel Schwenke]

On 07.03.2017 18:19, The Natural Philosopher wrote:
> On 07/03/17 16:46, Axel Schwenke wrote:

>> 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?

Maybe you should check your dictionary at "atomic" vs. "simultaneous" first

> 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?

It's called transaction. MySQL supports this for more than 10 years now.

> how does it know that they COMMIT is coming from the same person who sent
the TRANSACTION

Because transaction boundaries are tied to the context of a connection
(sometimes and IMHO better called "session"). And unlike HTTP, the MySQL
protocol does not require cookies or such. A session and the (TCP)
connection between client and server are essentially the same. There is no
need for additional tokens to recognize a certain communication partner as
the one who had been calling earlier.

Even more: if the connection should break, i.e. due to a crash of the client
machine or a network split, MySQL will close the session and roll back any
open connection.

There are a lot of things that are tied to the session: session variables,
temporary tables, prepared statements, the LAST_INSERT_ID() etc. pp.
Actually MySQL would be pretty useless without all that.

Now, /if/ you want to run statements concurrently from your application, you
can of course do that. Just open multiple connections. The reason for doing
that is however never a functional one. But you might leverage multiple
worker threads inside the MySQL server that way. I.e. you could dump or load
multiple tables in parallel to speedup the overall performance.

Oh, look - this already exists: https://github.com/maxbube/mydumper

[Axel Schwenke]

On 07.03.2017 23:31, Axel Schwenke wrote:
> if the connection should break, i.e. due to a crash of the client
> machine or a network split, MySQL will close the session and roll back any
> open connection.

Oops.

...if the connection should break, i.e. due to a crash of the client machine

or a network split, MySQL will close the session and roll back any open

_transaction_.

[kushal bhattacharya]

the thing is i am using those queries as separate statement it is working fine but i heard that this feature is avaialble in jdbc mysql

[Peter H. Coffin]

On Wed, 8 Mar 2017 02:22:45 -0800 (PST), kushal bhattacharya wrote:

> the thing is i am using those queries as separate statement it is
> working fine but i heard that this feature is avaialble in jdbc mysql

Okay, it works fine one way but not ther other. Why is it important to
you to do it the way that doesn't work? You must be assuming some kind
of consequence from issuing multiple statements, and if we know what you
want to actually accomplish, we can show you how to get the right
results.

--
I think it's a beautiful day to go to the zoo and feed the ducks.
To the lions.
-- Brian Kantor

[kushal bhattacharya]

hi,
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

[Peter H. Coffin]

Go back and read the stuff other people posted about transactions.
That's how you get that consistency to happen. Submitting multiple
statements with semicolons runs the statements sequentially and DOES NOT
guarantee consistency. The database can change between running the first
and the second statements. If you use the transaction properly, you've
told it NOT to change between statements, so it won't.

--
"25 grams of wafers and 20 ml of wine undergo transubstantiation and
become the flesh and blood of our Lord. How many Joules of heat are
released by the transformation?" --Theological Physics exam, 1997

[Thomas 'PointedEars' Lahn]

J�rgen Exner wrote:

> On Fri, 24 Feb 2017 21:14:42 -0800 (PST), kushal bhattacharya
> <bhattachar...@gmail.com> wrote in comp.databases.mysql:

It’s called attribution _line_, not attribution novel.

>>error message is error near the part where ';' is provided and its saying
>>to refer to the manual of mysql but when i run it on phpmyadmin it runs
>>propewrly without showing any arror
>
> Your postings are unreadable.
> Please learn how to use Usenet!

I wager that they do not even know that they are posting to Usenet.

They are using Google Groups, and the technical flaws in the message are the
result of

(1) Google Groups being buggy when it comes to newsgroups;

(2) Google not teaching their users about Usenet, the archive of which
Google inherited when buying DejaNews (and continuing to making it
worse since).

> Limit your line lenght to somewhere around 75 characters.
> And quote sufficient context such that people know what you are talking
> about.
>
> The fact that you have elected an unsuitable interface (Google Groups)
> to link into Usenet does not exempt you from following basic Usenet
> ettiquette which has been a proven custom for 30 years.

True, but you cannot blame them for not knowing. Proper information about
Usenet *outside* of Usenet is hard to come by. Even the Wikipedia articles
about Usenet fail to provide it on fundamental levels.

Hopefully there is going to be a better Web application than Google Groups
for posting to Usenet, one that also provides guidance to newbies on how to
post. I am working on it; the server-side part is going to be written in
PHP.

Finally, it is hypocritical of you to post without proper encoding (the
*plain* umlaut in your From header field value does _not_ belong there), and
with an attribution novel; corrigible flaws for which your newsreader, Forté
Agent, of which you are using a *four-times outdated* version, is known.

<http://www.forteinc.com/>

--
PointedEars

Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

[Axel Schwenke]

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.

[kushal bhattacharya]

ok thanks a lot i will use transaction in my query now :)

[The Natural Philosopher]

On 10/03/17 12:42, kushal bhattacharya wrote:
> ok thanks a lot i will use transaction in my query now :)
>

Further to this do transactions have ANY effect on C-ISAM style files?


--
"Anyone who believes that the laws of physics are mere social
conventions is invited to try transgressing those conventions from the
windows of my apartment. (I live on the twenty-first floor.) "

Alan Sokal

[Lew Pitcher]

On Friday March 10 2017 10:12, in comp.databases.mysql, "The Natural

Philosopher" <t...@invalid.invalid> wrote:

> On 10/03/17 12:42, kushal bhattacharya wrote:
>> ok thanks a lot i will use transaction in my query now :)
>>
> Further to this do transactions have ANY effect on C-ISAM style files?

mu

AFAIK, MySQL's myisam database storage is something different from IBM's C-
ISAM indexed file storage system.

The proper question would be, "Do transactions have any affect on MyISAM
databases?", and the answer to that would be "No. MySQL does not support
transactions on MyISAM databases."

HTH

[Axel Schwenke]

On 10.03.2017 18:26, Lew Pitcher wrote:

> <t...@invalid.invalid> wrote:
>
>> Further to this do transactions have ANY effect on C-ISAM style files?
>

> The proper question would be, "Do transactions have any affect on MyISAM
> databases?", and the answer to that would be "No. MySQL does not support
> transactions on MyISAM databases."

Another way to phrase the answer would be: in MySQL transaction support is
implemented at the storage engine layer. InnoDB is the only shipped engine
in MySQL that supports transactions. There are other (third-party) engines
with transaction support (i.e. TokuDB). The MyISAM engine in MySQL does not
support transactions (and never will).

[kushal bhattacharya]

hi,
I am using inodb storage engine in mysql so definitely i dont have to worry about issues if transaction now :)