I did a little deeper reading and the MySQL C API (probably the same API they wrote the ODBC driver with) does not support multiple statements through most (if not all) of its "querying" interfaces:
http://dev.mysql.com/doc/mysql/en/mysql_query.html http://dev.mysql.com/doc/mysql/en/mysql_real_query.html http://dev.mysql.com/doc/mysql/en/mysql_stmt_prepare.html So the odds are looking rather slim that you will be able to execute more than one statement at a time. Since you are getting syntax errors I assume that you are forced into single-statement execution. Well, at least we were able to help you to use server variables and temp tables :-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Jeff Burgoon" <[EMAIL PROTECTED]> wrote on 09/21/2004 10:01:29 AM: > Hey. Thanks. > > First off, I meant to say semicolon (;) in my first post but I mistyped and > said comma by accident. I am trying to use semicolon as my delimiter. This > method works if I issue statements from PHPMyAdmin, but not through my ODBC > app. > > However, your pseudocode made me realize something I didn't know was true. > I had just assumed a temporary table would not persist between issued > statements but they do if you have not closed the connection, as you stated. > I don't know why I never tried this, but it works for me both with temporary > tables and variables which is great. > > However, the syntax of your open statement still does not work for me. I > get a SQL syntax error any time I try combining statements with Semicolons. > Since I've been programming using SQL Server for a while now I feel like I > SHOULD be able to issue commands in this way but somewhere I heard that > mySQL prevents batch queries on purpose. > > > <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > For multiple statements in one submission, have you tried using a > > semicolon ; not a comma , ? > > (http://dev.mysql.com/doc/mysql/en/Entering_queries.html) > > > > Each command may return a recordset of its own. Be prepared to either > > cycle through the returned recordsets or ignore them as they arrive. > > > > When you create a connection with a MySQL database, you establish an > > environment where variables and temporary tables can exist that is > > specifically yours. Just issue your commands in multiple statements > > WITHOUT CLOSING YOUR CONNECTION and you will be able to use those items. > > Here is some VB/ADO-like pseudocode to illustrate: > > > > set oConn = new Connection > > set oRS = new Recordset > > set oRSTimer = new Recordset > > oConn.open "connection string", sUser, sPassword > > > > oConn.execute "SET @Start_time = NOW()" > > oConn.execute "CREATE TEMPORARY TABLE tmpResults SELECT .... " > > oRS.Open "SELECT * FROM tmpResults where Col2='bluegills'; SET > > @End_Time=Now()", oConn, 0,1,1 > > oRStimer.Open "[EMAIL PROTECTED], @end_time", oConn, 0,1,1 > > > > oConn.Execute "DROP TABLE tmpResults" > > oConn.Close > > > > > > What I did was open a connection, set a variable, create a temp table by > > populating it with the results of a SELECT query, get some records from > > that temp table AND set another variable, then finally get another record > > that contained the values of both temporary variables. After all that, I > > dropped my temporary table and closed the connection. I am nearly 100% > > certain that the combined statements in the "oRS.Open..." line will work > > for you. Let us know if it does or doesn't, OK?. > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > > > "Jeff Burgoon" <[EMAIL PROTECTED]> wrote on 09/21/2004 08:54:27 AM: > > > > > I'm writing my first MySQL app in VB.net using myODBC. However I think > > this > > > question applies to all languages using MySQL. From what I understand, > > I > > > am unable to issue a batch statement of commands separated by commas to > > > mySQL. I receive an error whenever I try to do so from my app. For > > this > > > reason, I am unable to make use of SQL variables and temporary tables. I > > > must instead use persistant tables. > > > > > > Can anyone tell me if this is in fact the case and if so, any > > suggestions on > > > how to get over this hurdle? > > > > > > Thanks, > > > Jeff > > > > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
