MyODBC 3.51 executing multiple SQL statements

[Tim Hastings]

Hello,

I am using MyODBC from VB and I want to submit a batch of insert statements
in one call from my App. This is more efficient than making multiple calls
from code because of the communication overhead.

If I send a batch multiple statements separated by ; or ; + newline I get
syntax errors pointing at the start of the next statement..
If I fire each statement one at a time with the ; at the end there's no
problem.

I cannot be the first person with this problem. Is it common practice to
make multiple calls from you application or is there a magic trick I am
missing?

Code and error message below, thanks in advance!

Cheers,

Tim H


My execute function is like this:

Public Function ExecuteSQL(ByVal strSQL As String) As Long

Dim lngAffected As Long

mobjConn.Execute strSQL, lngAffected, adCmdText + adExecuteNoRecords
ExecuteSQL = lngAffected

End Function


And I am performing this operations:

objDB.ExecuteSQL "DELETE FROM table1;" -- works fine

objDB.ExecuteSQL "DELETE FROM table1; DELETE FROM table1;" -- error

Error message:
[MySQL][ODBC 3.51 Driver][mysqld-4.0.14-max-nt]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 '; DELETE FROM `in(

[swdev1]

** following is my opinion, not really the answer to your question **
INSERTS? why not switch to VFP and use the remote views and handle it all
with the tableupdate() command ?

VB really sucks at doing data, and sucks even more at doing REMOTE data.
if you have Visual Studio EnterPrise Edition Version 6, VFP is on CD 1.
IF you have the MSDN Universal subscription, you can find VFP version 6,7,8
there .

here are some notes about how to do it:
http://fox.wikis.com/wc.dll?Wiki~VFPmySQLLinuxP1~VFP

hth - mondo regards [Bill]
ps - your example here was for DELETE, btw....
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
"Tim Hastings" <so***@nospam.com> wrote in message
news:bi*******************@news.demon.co.uk...

Hello,

I am using MyODBC from VB and I want to submit a batch of insert statements in one call from my App. This is more efficient than making multiple calls
from code because of the communication overhead.

If I send a batch multiple statements separated by ; or ; + newline I get
syntax errors pointing at the start of the next statement..
If I fire each statement one at a time with the ; at the end there's no
problem.

I cannot be the first person with this problem. Is it common practice to
make multiple calls from you application or is there a magic trick I am
missing?

Code and error message below, thanks in advance!

Cheers,

Tim H


My execute function is like this:

Public Function ExecuteSQL(ByVal strSQL As String) As Long

Dim lngAffected As Long

mobjConn.Execute strSQL, lngAffected, adCmdText + adExecuteNoRecords ExecuteSQL = lngAffected

End Function


And I am performing this operations:

objDB.ExecuteSQL "DELETE FROM table1;" -- works fine

objDB.ExecuteSQL "DELETE FROM table1; DELETE FROM table1;" -- error

Error message:
[MySQL][ODBC 3.51 Driver][mysqld-4.0.14-max-nt]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 '; DELETE FROM `in(

[swdev1]

** following is my opinion, not really the answer to your question **
INSERTS? why not switch to VFP and use the remote views and handle it all
with the tableupdate() command ?

VB really sucks at doing data, and sucks even more at doing REMOTE data.
if you have Visual Studio EnterPrise Edition Version 6, VFP is on CD 1.
IF you have the MSDN Universal subscription, you can find VFP version 6,7,8
there .

here are some notes about how to do it:
http://fox.wikis.com/wc.dll?Wiki~VFPmySQLLinuxP1~VFP

hth - mondo regards [Bill]
ps - your example here was for DELETE, btw....
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
"Tim Hastings" <so***@nospam.com> wrote in message
news:bi*******************@news.demon.co.uk...

Hello,

I am using MyODBC from VB and I want to submit a batch of insert statements in one call from my App. This is more efficient than making multiple calls
from code because of the communication overhead.

If I send a batch multiple statements separated by ; or ; + newline I get
syntax errors pointing at the start of the next statement..
If I fire each statement one at a time with the ; at the end there's no
problem.

I cannot be the first person with this problem. Is it common practice to
make multiple calls from you application or is there a magic trick I am
missing?

Code and error message below, thanks in advance!

Cheers,

Tim H


My execute function is like this:

Public Function ExecuteSQL(ByVal strSQL As String) As Long

Dim lngAffected As Long

mobjConn.Execute strSQL, lngAffected, adCmdText + adExecuteNoRecords ExecuteSQL = lngAffected

End Function


And I am performing this operations:

objDB.ExecuteSQL "DELETE FROM table1;" -- works fine

objDB.ExecuteSQL "DELETE FROM table1; DELETE FROM table1;" -- error

Error message:
[MySQL][ODBC 3.51 Driver][mysqld-4.0.14-max-nt]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 '; DELETE FROM `in(

[swdev1]

** following is my opinion, not really the answer to your question **
INSERTS? why not switch to VFP and use the remote views and handle it all
with the tableupdate() command ?

VB really sucks at doing data, and sucks even more at doing REMOTE data.
if you have Visual Studio EnterPrise Edition Version 6, VFP is on CD 1.
IF you have the MSDN Universal subscription, you can find VFP version 6,7,8
there .

here are some notes about how to do it:
http://fox.wikis.com/wc.dll?Wiki~VFPmySQLLinuxP1~VFP

hth - mondo regards [Bill]
ps - your example here was for DELETE, btw....
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
"Tim Hastings" <so***@nospam.com> wrote in message
news:bi*******************@news.demon.co.uk...

Hello,

I am using MyODBC from VB and I want to submit a batch of insert statements in one call from my App. This is more efficient than making multiple calls
from code because of the communication overhead.

If I send a batch multiple statements separated by ; or ; + newline I get
syntax errors pointing at the start of the next statement..
If I fire each statement one at a time with the ; at the end there's no
problem.

I cannot be the first person with this problem. Is it common practice to
make multiple calls from you application or is there a magic trick I am
missing?

Code and error message below, thanks in advance!

Cheers,

Tim H


My execute function is like this:

Public Function ExecuteSQL(ByVal strSQL As String) As Long

Dim lngAffected As Long

mobjConn.Execute strSQL, lngAffected, adCmdText + adExecuteNoRecords ExecuteSQL = lngAffected

End Function


And I am performing this operations:

objDB.ExecuteSQL "DELETE FROM table1;" -- works fine

objDB.ExecuteSQL "DELETE FROM table1; DELETE FROM table1;" -- error

Error message:
[MySQL][ODBC 3.51 Driver][mysqld-4.0.14-max-nt]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 '; DELETE FROM `in(