Multiple insert statements in single ODBC ExecuteNonQuery (C#)
I'm inserting multiple rows into a DB, and joining them together in an attempt to improve performance.
I get an ODBCException telling me my SQL syntax is wrong. But when I try it in the mysql commandline client, it works just fine..
I ran a simplified test to describe the process.
Command Line Client:
mysql> create table test (`id` int, `name` text);
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO test(id, name) VALUES ('1', 'Foo');INSERT INTO test(id, name) VALUES ('2', 'bar');
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql>
After that I ran this code on the same DB:
comm.CommandText = "INSERT INTO test(id, name) VALUES ('1', 'Foo');INSERT INTO test(id, name) VALUES ('2', 'bar');";
comm.ExecuteNonQuery();
which gives me the following error:
+ base {"ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.51-community]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 'INSERT INTO test(id, name) VALUES ('2', 'bar')' at line 1"} System.Data.Common.DbException {System.Data.Odbc.OdbcException}
c# mysql odbc
asked Nov 10 '10 at 13:29
pvelpvel
2813
add a comment |
I'm inserting multiple rows into a DB, and joining them together in an attempt to improve performance.
I get an ODBCException telling me my SQL syntax is wrong. But when I try it in the mysql commandline client, it works just fine..
I ran a simplified test to describe the process.
Command Line Client:
mysql> create table test (`id` int, `name` text);
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO test(id, name) VALUES ('1', 'Foo');INSERT INTO test(id, name) VALUES ('2', 'bar');
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql>
After that I ran this code on the same DB:
comm.CommandText = "INSERT INTO test(id, name) VALUES ('1', 'Foo');INSERT INTO test(id, name) VALUES ('2', 'bar');";
comm.ExecuteNonQuery();
which gives me the following error:
+ base {"ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.51-community]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 'INSERT INTO test(id, name) VALUES ('2', 'bar')' at line 1"} System.Data.Common.DbException {System.Data.Odbc.OdbcException}
c# mysql odbc
asked Nov 10 '10 at 13:29
pvelpvel
2813
add a comment |
I'm inserting multiple rows into a DB, and joining them together in an attempt to improve performance.
I get an ODBCException telling me my SQL syntax is wrong. But when I try it in the mysql commandline client, it works just fine..
I ran a simplified test to describe the process.
Command Line Client:
mysql> create table test (`id` int, `name` text);
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO test(id, name) VALUES ('1', 'Foo');INSERT INTO test(id, name) VALUES ('2', 'bar');
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql>
After that I ran this code on the same DB:
comm.CommandText = "INSERT INTO test(id, name) VALUES ('1', 'Foo');INSERT INTO test(id, name) VALUES ('2', 'bar');";
comm.ExecuteNonQuery();
which gives me the following error:
+ base {"ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.51-community]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 'INSERT INTO test(id, name) VALUES ('2', 'bar')' at line 1"} System.Data.Common.DbException {System.Data.Odbc.OdbcException}
c# mysql odbc
asked Nov 10 '10 at 13:29
pvelpvel
2813
I'm inserting multiple rows into a DB, and joining them together in an attempt to improve performance.
I get an ODBCException telling me my SQL syntax is wrong. But when I try it in the mysql commandline client, it works just fine..
I ran a simplified test to describe the process.
Command Line Client:
mysql> create table test (`id` int, `name` text);
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO test(id, name) VALUES ('1', 'Foo');INSERT INTO test(id, name) VALUES ('2', 'bar');
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql>
After that I ran this code on the same DB:
comm.CommandText = "INSERT INTO test(id, name) VALUES ('1', 'Foo');INSERT INTO test(id, name) VALUES ('2', 'bar');";
comm.ExecuteNonQuery();
which gives me the following error:
+ base {"ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.51-community]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 'INSERT INTO test(id, name) VALUES ('2', 'bar')' at line 1"} System.Data.Common.DbException {System.Data.Odbc.OdbcException}
c# mysql odbc
c# mysql odbc
asked Nov 10 '10 at 13:29
pvelpvel
2813
asked Nov 10 '10 at 13:29
pvelpvel
2813
asked Nov 10 '10 at 13:29
pvelpvel
2813
asked Nov 10 '10 at 13:29
pvelpvel
2813
asked Nov 10 '10 at 13:29
pvelpvel
2813
2813
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
Yes, ODBC does NOT support batch processing. (EDIT: See @Jean-Do's answer for a more up to date solution.)
But there is another option:
- Use the MySQL .NET Connector instead of ODBC.
- Then use the MySQL alternative INSERT statement:
INSERT INTO test(id, name) VALUES ('1', 'Foo'), ('2', 'bar');.
answered Nov 10 '10 at 13:39
rsennarsenna
9,04014051
This is not correct. See below answers.
– Robert Penridge
Nov 19 '18 at 23:29
@RobertPenridge The only thing currently wrong is the first phrase, and it was right at the time it was written (2010).
– rsenna
Nov 20 '18 at 9:41
add a comment |
Batching is actually supported by MySQL ODBC driver v5+, you just need to click on the Details button of the ODBC control panel (if on Windows) and check the "Allow multiple statements" checkbox.
Alternatively, uses OPTIONS=67108864 on you odbc connection string.
More information here : http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html
answered Mar 15 '13 at 18:51
Jean-DoJean-Do
53547
This saved my ass, but just a small correction, I found it is 'option' with no 's', i.e. option=67108864; Also an updated link: dev.mysql.com/doc/connector-odbc/en/…
– padraigf
Oct 19 '18 at 15:51
add a comment |
It cannot handle batching (using ; to separate multiple statements) since this would require two way communication. I am afraid you have to do it in a loop and go to database multiple times.
In fact I have never been able to use batching with any managed provider.
answered Nov 10 '10 at 13:33
AliostadAliostad
69.4k14134190
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f4144961%2fmultiple-insert-statements-in-single-odbc-executenonquery-c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Yes, ODBC does NOT support batch processing. (EDIT: See @Jean-Do's answer for a more up to date solution.)
But there is another option:
- Use the MySQL .NET Connector instead of ODBC.
- Then use the MySQL alternative INSERT statement:
INSERT INTO test(id, name) VALUES ('1', 'Foo'), ('2', 'bar');.
answered Nov 10 '10 at 13:39
rsennarsenna
9,04014051
This is not correct. See below answers.
– Robert Penridge
Nov 19 '18 at 23:29
@RobertPenridge The only thing currently wrong is the first phrase, and it was right at the time it was written (2010).
– rsenna
Nov 20 '18 at 9:41
add a comment |
Yes, ODBC does NOT support batch processing. (EDIT: See @Jean-Do's answer for a more up to date solution.)
But there is another option:
- Use the MySQL .NET Connector instead of ODBC.
- Then use the MySQL alternative INSERT statement:
INSERT INTO test(id, name) VALUES ('1', 'Foo'), ('2', 'bar');.
answered Nov 10 '10 at 13:39
rsennarsenna
9,04014051
This is not correct. See below answers.
– Robert Penridge
Nov 19 '18 at 23:29
@RobertPenridge The only thing currently wrong is the first phrase, and it was right at the time it was written (2010).
– rsenna
Nov 20 '18 at 9:41
add a comment |
Yes, ODBC does NOT support batch processing. (EDIT: See @Jean-Do's answer for a more up to date solution.)
But there is another option:
- Use the MySQL .NET Connector instead of ODBC.
- Then use the MySQL alternative INSERT statement:
INSERT INTO test(id, name) VALUES ('1', 'Foo'), ('2', 'bar');.
answered Nov 10 '10 at 13:39
rsennarsenna
9,04014051
Yes, ODBC does NOT support batch processing. (EDIT: See @Jean-Do's answer for a more up to date solution.)
But there is another option:
- Use the MySQL .NET Connector instead of ODBC.
- Then use the MySQL alternative INSERT statement:
INSERT INTO test(id, name) VALUES ('1', 'Foo'), ('2', 'bar');.
answered Nov 10 '10 at 13:39
rsennarsenna
9,04014051
edited Nov 20 '18 at 9:45
answered Nov 10 '10 at 13:39
rsennarsenna
9,04014051
answered Nov 10 '10 at 13:39
rsennarsenna
9,04014051
answered Nov 10 '10 at 13:39
rsennarsenna
9,04014051
9,04014051
This is not correct. See below answers.
– Robert Penridge
Nov 19 '18 at 23:29
@RobertPenridge The only thing currently wrong is the first phrase, and it was right at the time it was written (2010).
– rsenna
Nov 20 '18 at 9:41
add a comment |
This is not correct. See below answers.
– Robert Penridge
Nov 19 '18 at 23:29
@RobertPenridge The only thing currently wrong is the first phrase, and it was right at the time it was written (2010).
– rsenna
Nov 20 '18 at 9:41
This is not correct. See below answers.
– Robert Penridge
Nov 19 '18 at 23:29
This is not correct. See below answers.
– Robert Penridge
Nov 19 '18 at 23:29
@RobertPenridge The only thing currently wrong is the first phrase, and it was right at the time it was written (2010).
– rsenna
Nov 20 '18 at 9:41
@RobertPenridge The only thing currently wrong is the first phrase, and it was right at the time it was written (2010).
– rsenna
Nov 20 '18 at 9:41
add a comment |
Batching is actually supported by MySQL ODBC driver v5+, you just need to click on the Details button of the ODBC control panel (if on Windows) and check the "Allow multiple statements" checkbox.
Alternatively, uses OPTIONS=67108864 on you odbc connection string.
More information here : http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html
answered Mar 15 '13 at 18:51
Jean-DoJean-Do
53547
This saved my ass, but just a small correction, I found it is 'option' with no 's', i.e. option=67108864; Also an updated link: dev.mysql.com/doc/connector-odbc/en/…
– padraigf
Oct 19 '18 at 15:51
add a comment |
Batching is actually supported by MySQL ODBC driver v5+, you just need to click on the Details button of the ODBC control panel (if on Windows) and check the "Allow multiple statements" checkbox.
Alternatively, uses OPTIONS=67108864 on you odbc connection string.
More information here : http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html
answered Mar 15 '13 at 18:51
Jean-DoJean-Do
53547
This saved my ass, but just a small correction, I found it is 'option' with no 's', i.e. option=67108864; Also an updated link: dev.mysql.com/doc/connector-odbc/en/…
– padraigf
Oct 19 '18 at 15:51
add a comment |
Batching is actually supported by MySQL ODBC driver v5+, you just need to click on the Details button of the ODBC control panel (if on Windows) and check the "Allow multiple statements" checkbox.
Alternatively, uses OPTIONS=67108864 on you odbc connection string.
More information here : http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html
answered Mar 15 '13 at 18:51
Jean-DoJean-Do
53547
Batching is actually supported by MySQL ODBC driver v5+, you just need to click on the Details button of the ODBC control panel (if on Windows) and check the "Allow multiple statements" checkbox.
Alternatively, uses OPTIONS=67108864 on you odbc connection string.
More information here : http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html
answered Mar 15 '13 at 18:51
Jean-DoJean-Do
53547
answered Mar 15 '13 at 18:51
Jean-DoJean-Do
53547
answered Mar 15 '13 at 18:51
Jean-DoJean-Do
53547
answered Mar 15 '13 at 18:51
Jean-DoJean-Do
53547
53547
This saved my ass, but just a small correction, I found it is 'option' with no 's', i.e. option=67108864; Also an updated link: dev.mysql.com/doc/connector-odbc/en/…
– padraigf
Oct 19 '18 at 15:51
add a comment |
This saved my ass, but just a small correction, I found it is 'option' with no 's', i.e. option=67108864; Also an updated link: dev.mysql.com/doc/connector-odbc/en/…
– padraigf
Oct 19 '18 at 15:51
This saved my ass, but just a small correction, I found it is 'option' with no 's', i.e. option=67108864; Also an updated link: dev.mysql.com/doc/connector-odbc/en/…
– padraigf
Oct 19 '18 at 15:51
This saved my ass, but just a small correction, I found it is 'option' with no 's', i.e. option=67108864; Also an updated link: dev.mysql.com/doc/connector-odbc/en/…
– padraigf
Oct 19 '18 at 15:51
add a comment |
It cannot handle batching (using ; to separate multiple statements) since this would require two way communication. I am afraid you have to do it in a loop and go to database multiple times.
In fact I have never been able to use batching with any managed provider.
answered Nov 10 '10 at 13:33
AliostadAliostad
69.4k14134190
add a comment |
It cannot handle batching (using ; to separate multiple statements) since this would require two way communication. I am afraid you have to do it in a loop and go to database multiple times.
In fact I have never been able to use batching with any managed provider.
answered Nov 10 '10 at 13:33
AliostadAliostad
69.4k14134190
add a comment |
It cannot handle batching (using ; to separate multiple statements) since this would require two way communication. I am afraid you have to do it in a loop and go to database multiple times.
In fact I have never been able to use batching with any managed provider.
answered Nov 10 '10 at 13:33
AliostadAliostad
69.4k14134190
It cannot handle batching (using ; to separate multiple statements) since this would require two way communication. I am afraid you have to do it in a loop and go to database multiple times.
In fact I have never been able to use batching with any managed provider.
answered Nov 10 '10 at 13:33
AliostadAliostad
69.4k14134190
answered Nov 10 '10 at 13:33
AliostadAliostad
69.4k14134190
answered Nov 10 '10 at 13:33
AliostadAliostad
69.4k14134190
answered Nov 10 '10 at 13:33
AliostadAliostad
69.4k14134190
69.4k14134190
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f4144961%2fmultiple-insert-statements-in-single-odbc-executenonquery-c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
