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]
