PowerShell: multiple queries in ODBC DSN connection

Question

I want to execute multiple queries in an ODBC DSN MySql Connection. If I execute just one query it works fine. But if I try to execute multiple I always get this error:

"ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.84-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 'UPDATE TABLE SET ID = '5' WHERE TYPE = '500' AND ID' 
at line 2"

My script:

$qrys = @"
UPDATE TABLE SET ID = '5' WHERE TYPE = '500' AND ID = '0';
UPDATE TABLE SET ID = '6' WHERE TYPE = '600' AND ID = '0';
UPDATE TABLE SET ID = '7' WHERE TYPE = '700' AND ID = '0';
UPDATE TABLE SET ID = '8' WHERE TYPE = '800' AND ID = '0';
"@


$conn = new-object System.Data.Odbc.OdbcConnection
$conn.connectionstring = "DSN=MYDB"
$conn.open()

foreach ($sqlCommand in $qrys) {

    $cmd = New-object System.Data.Odbc.OdbcCommand($sqlCommand,$conn)
    $dataset = New-Object System.Data.DataSet
    (New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($dataSet) 

}
$conn.Close()

Any ideas? Thanks in advance

Guessing really, but is it maybe objecting to the ; at the end of the strings? — Mark Wragg
– Mark Wragg, Commented Mar 6, 2017 at 15:22
Is the table you're trying to update really called TABLE, or do you need to replace that with a table name? — Mike Shepard
– Mike Shepard, Commented Mar 6, 2017 at 16:27

erg · Accepted Answer · 2017-03-06 16:43:04Z

2

MySql does not enable multiple queries by default. Either add OPTIONS=67108864 to a connection-string, or enable the checkbox Allow multiple statements in the DSN configuration dialog:

see: https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html

answered Mar 6, 2017 at 16:43

erg

1,6521 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

aston_zh Over a year ago

How should my connectionstring look like?

erg Over a year ago

Something like

Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=myDataBase; User=myUsername;Password=myPassword;Option=67108864;

Replace all property values with values matching your system (driver-name, server, etc.)

Collectives™ on Stack Overflow

PowerShell: multiple queries in ODBC DSN connection

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related