Sending multiple commands to MySql backend through TEXT...ENDTEXT 2

[zazzi]

I am successfully using TEXT....ENDTEXT to send single commands to a MySql backend. My question is whether it is possible to send multiple commands with one TEXT...ENDTEXT construct (probably not?). Example :

** connection to MySql already verified and labelled as "nH_LS"

The following works:

TEXT TO cSql NOSHOW
UPDATE table1 SET column1 = 'aaa'
ENDTEXT
nSql = SQLEXEC(nH_LS, cSql)
TEXT TO cSql NOSHOW
UPDATE table2 SET column1 = 'bbb'
ENDTEXT
nSql = SQLEXEC(nH_LS, cSql)


The following does not work:

TEXT TO cSql NOSHOW
UPDATE table1 SET column1 = 'aaa';
UPDATE table2 SET column1 = 'bbb'
ENDTEXT
nSql = SQLEXEC(nH_LS, cSql)

 

[Joe Crescenzi]

Try inserting GO between each command.

 

[zazzi]

Thanks Joe,

I tried but it did not work, I see GO is a command specific to MS SQL server but not allowed for MySql.

 

[tomk3]

I usually write the script in studio (MS-SQL) and when it works I copy it into Text/Endtext.
I am not sure on the ; in your sample

 

[zazzi]

Tom,

If I write this outside VFP (in an HeidiSql query window), it works (and definitely needs the ; )

UPDATE table1 SET column1 = 'aaa';
UPDATE table2 SET column1 = 'bbb';

but it does not work when sent by VFP within TEXT...ENDTEXT

 

[Joe Crescenzi]

Just to be clear here, does it give you an error, or is it only executing the 1st part?

In you example, are all the values saying aaa, instead of bbb... or is it not running either?

 

[Chris Miller]

MSSQL also allows the semicolon as terminator, but overall dialects differ, for example the GO is very MSSQL specific.

I think everything worth knowing is said here: https://www.sqlservercentral.com/articles/the-go-command-and-the-semicolon-terminator

MySQL allows multiple commands, see https://dev.mysql.com/doc/c-api/9.0/en/c-api-multiple-queries.html
Search (CTRL+F) for "MULTI_STATEMENTS" within https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html

Either your ODBC connection string has an OPTIONS=x part, where x is a number resulting from ORing the option values documented, or you specify options by their name,i.e. for this option add a section in the connectionstring with "MULTI_STATEMENTS=1".

Your example hopefully is not what you really execute, without a WHERE clause an UPDATE would set a field in all records, there is no such concept as the current record in SQL. Batch updates should rather not be done by SQLEXEC, but by a TABLEUPDATE of a buffered workarea with several CURSORSETPROPS set to allow using TABLEUPDATE for an updatable SQL Passthrough workarea that you got from SQLExec in the first place.

 

[zazzi]

Just to be clear here, does it give you an error, or is it only executing the 1st part?

In you example, are all the values saying aaa, instead of bbb... or is it not running either?

It does not execute anything. It throws an error "[MySQL][ODBC 8.0(a) Driver][mysqld-11.5.2-MariaDB]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE table2`". So it seems it is reading the two commands as if it were one.

 

[zazzi]

MSSQL also allows the semicolon as terminator, but overall dialects differ, for example the GO is very MSSQL specific.

I think everything worth knowing is said here: https://www.sqlservercentral.com/articles/the-go-command-and-the-semicolon-terminator

MySQL allows multiple commands, see https://dev.mysql.com/doc/c-api/9.0/en/c-api-multiple-queries.html
Search (CTRL+F) for "MULTI_STATEMENTS" within https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html

Either your ODBC connection string has an OPTIONS=x part, where x is a number resulting from ORing the option values documented, or you specify options by their name,i.e. for this option add a section in the connectionstring with "MULTI_STATEMENTS=1".

Your example hopefully is not what you really execute, without a WHERE clause an UPDATE would set a field in all records, there is no such concept as the current record in SQL. Batch updates should rather not be done by SQLEXEC, but by a TABLEUPDATE of a buffered workarea with several CURSORSETPROPS set to allow using TABLEUPDATE for an updatable SQL Passthrough workarea that you got from SQLExec in the first place.

Thanks Chris,

The example was just an example, my commands are totally different but I used a couple of UPDATE statements just to make it clear to read... It is about dropping a table and building a new one as part of a routine maintenance.

I added option 67108864 (MULTI_STATEMENTS) in the connection string OPTION parameter and now I receive error 1050 = unhandled error from mysql_next_result()

 

[zazzi]

Solved!

It seems adding OPTION 67108864 (MULTI_STATEMENTS) in the connection string OPTION parameter did the trick. In my last post I mentioned error 1050 but that was my fault (forgot to drop the fake table manually).

To summarize for all those who may benefit;

To enable multiple commands within the TEXT...ENDTEXT construct just add OPTION 67108864 (or combine it with other options just adding the values) in the connection string. This is my case on my dev server:

cConnectionString = "DRIVER={MySQL ODBC 8.0 ANSI Driver};" ;
+ "SERVER=127.0.0.1;" ;
+ "PORT=3306;" ;
+ "OPTION=67108864;" ;
+ "UID=root;" ;
+ "PWD=***;" ;
+ "database=lab;" ;
+ "ENABLE_LOCAL_INFILE=1;"

Thanks everybody and particularly to Chris (marked as Great post).

 

[Chris Miller]

Thanks for the feedback, zazzi.

I'd add to it that other options are usually helpful for VFP clients, NO_BIGINTis almost mandatory, as VFP doesn't support bigints. It has the disadvantage that values bigger than 2^32 will still not work, which means you could ask "so what?" But you don't want values in the usual 32bit integer range to come back as bigints, too. Ideally the MySQL database has no bigint fields, anyway.

Doesn't matter much, as you pointed out yourself the options can be combined. With many options that's making the connection string shorter, On the other hand the setting names make it much clearer what options are used and how.