CODEWITHSUNDEEP
mysqlvb.netodbc
Poat
Poat

Reputation: 376

Run Delimiter Statements over ODBC connection .NET

I have an application with a scripting engine which has ability to run queries over ODBC connections.

We recently modified it to allow delimiter statements

This allows the user to pass in a query as so: 

DELIMITER //
CREATE PROCEDURE HelloWorld()
SELECT "Hello World!";
//
DELIMITER ;
CALL HelloWorld();

Normally ExecuteDataset would throw an exception, as the delimiters are not handled properly - and MySqlScript handles these perfectly.

Now the issue comes when using an ODBC connection. A user can specify something like DSN=testDB; and it will run the query on that DB - this works perfect at the moment, however the issue comes when wanting to run the above block using delimiter statements - they aren't handled properly, here's a snippet showing how we run over ODBC:

Case ScriptIssuedDatabaseQuery.QueryTypes.OdbcQuery
Try
    Using odbcConn As New Data.Odbc.OdbcConnection(qtw.ScriptQuery.OdbcConnectionString)
        odbcConn.Open()
        If odbcConn.State = Data.ConnectionState.Open Then
            Using oc As New Data.Odbc.OdbcCommand(qtw.ScriptQuery.SqlQueryData, odbcConn)
                qtw.ScriptQuery.QueryScalarResult = oc.ExecuteScalar().ToString() ' this fails, using ExecuteNonQuery same
                qtw.ScriptQuery.IsResultGood = True  ' If didn't throw exception - assume good
            End Using
        End If
        odbcConn.Close() 'redundant?
    End Using
Catch ex As Exception
    myHost.LogErrorMessage("Script Engine: Error in ODBC query: " & ex.Message)
End Try

Anything I'm missing? I've scoured the web and came up empty handed, tried a few different ways w/ Odbc class, tried using Transaction class, etc

Upvotes: 0

Views: 331

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179304

DELIMITER is never sent to the server.

The purpose of DELIMITER is to tell the client code what the current statement delimiter is, so that the client code can split the script into individual statements and send the statements to the server, individually, for execution.

You either need to write code to parse this content for DELIMITER statements and split on delimiters... or just separate each statement yourself and send to the server individually.

You can, for example, send the entire procedure body to the server as a single statement. The END at the end will be recognized as ending the procedure declaration -- no // or other delimiter is needed.

It's a common misconception that the server understands delimiters. In fact, if a normal/simple query ends in ; the server just ignores it. There's not actually a need to end any statement with a delimiter.

Upvotes: 1

Related Questions