Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Possible to execute several SQL Statements at one time with MYSQL?

Status
Not open for further replies.

dand11

Programmer
Jun 24, 2008
63
US
I need to be able to run the following SQL Statements at the same time from an ASP.NET application. The query is built on the fly and passed to the MYSQL Database. When I execute each individually in MYSQL Query Browser it runs fine but when I try the web app I get an error:

The Error:
Code:
Event Type:	Error
Description:
The description for Event ID ( 0 ) in Source ( WSInterop ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Reference Num: 94315-3132
Application Name: NA

Message:
OdbcException - SearchForVehicle
ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.51b-community-nt]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 ';

						 
										
							Create TABLE tmp_Veh (ROWNUM INT AUTO_INCREMENT' at line 1
Stack Trace:    at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
   at CTS.BASE.DBObject.RunDBCommandFillDataSet(String commandText, CommandType commandType, DbParameter[] parameters, String tableName, DataSet dataSet)
   at CTS.DBOBJECT.MARIANNA.BaseQuery`1.FillDataTable(String key, DataSet ds, DataTable dt, T desc, String UserName, String userID)
   at CTS.DBOBJECT.MARIANNA.VehicleSearch.SearchForVehicle(VehicleDescription oVeh, Int32 startRow, Int32 endRow, String UserID, String UserName)
   at CTS.DBOBJECT.MARIANNA.DBLogic.SearchForVehicle(VehicleDescription oVehicle, String UserID, String UserName, Int32 StartRow, Int32 EndRow, String PageID, String PageDir)
   at CTS.WS.DATASEARCH.BIZLogic.SearchForVehicle(VehicleDescription oVeh, RequestorSecurityLevel RequestorInfo, UsernameToken uToken, Int32 pageNum, String pageID, String pageDir)
   at CTS.WS.DATASEARCH.SEARCH.SearchForVehicle(String VTag, String VTagState, String VVIN, String VYear, String VMake, String VModel, String VColor, RequestorSecurityLevel RequestorInfo, Int32 pageNum, String pageID, String pageDir)
.


My Query:
Code:
Create TABLE tmp_Veh (ROWNUM INT AUTO_INCREMENT, TblSource cHAR(20), UNIQUEKEY INT,
 								VIN CHAR(100), VYEAR varchar(10), MAKE CHAR(20), VModelText CHAR(20),
 								TAG CHAR(20), STATE CHAR(3),  COLORSOLID CHAR(20), ORIGINATINGORG CHAR(3), TotalCount INT, PRIMARY KEY(ROWNUM));

						 
									
							LOCK TABLE tmp_Veh WRITE;

						 
							INSERT INTO tmp_Veh(TblSource, UNIQUEKEY, VIN, VYEAR, MAKE, VModelText, TAG, STATE,  COLORSOLID, ORIGINATINGORG, TotalCount)
							SELECT  'OFFENSE'  as TblSource
								, v.Primary as UNIQUEKEY
								, v.VIN as VIN
								, v.YEAR as VYEAR
								, v.MAKE as MAKE
								, v.Model as VModelText
								, v.License as TAG
								, v.Lic_Stat as	STATE
								, v.Top_clr as COLORSOLID
							 	, ' ' as ORIGINATINGORG
								, 1 as TotalCount
							 FROM Vehicle v inner join incident i on v.CN = i.CN
                 					 INNER JOIN charges c on v.CN = c.CN 
						 WHERE UPPER(v.License) = 'ABC123'  
							UNION
							SELECT  'TRAFFIC'  as TblSource
								, v.Primary	as UNIQUEKEY
								, v.VIN as	VIN
								, v.YEAR as 	VYEAR
								, v.MAKE as	MAKE
								, v.Model as	VModelText
								, v.License as	TAG
								, v.Lic_Stat as	STATE
								, v.Top_clr as COLORSOLID
							 	, ' ' as ORIGINATINGORG
								, 1 as TotalCount
							 
							 FROM Vehicle v inner join incident i on v.CN = i.CN
                 					 INNER JOIN accident c on v.CN = c.ComplaintNumber
						 WHERE LTRIM(v.License) = 'ABC123'   
							UNION
							SELECT  'UCM'  as TblSource
								, v.Primary as UNIQUEKEY
								, v.VIN as VIN
								, v.YEAR as VYEAR
								, v.MAKE as MAKE
								, v.Model as VModelText
								, v.License as TAG
								, v.Lic_Stat as	STATE
								, v.Top_clr  as COLORSOLID
							 	, ' ' as ORIGINATINGORG
								, 1 as TotalCount
							 
							 FROM Vehicle v inner join incident i on v.CN = i.CN  
                 					 INNER JOIN ticket t on i.CN = t.CaseNumber 
						 WHERE LTRIM(v.License) = 'ABC123' ;   
							
					

							Select * from tmp_Veh where RowNum > 0 and RowNum < 10;
					
							

							DROP TABLE tmp_Veh;
					

							UNLOCK TABLES;[\code]
 
i don't think you can submit multiple queries in one shot in mysql. you can do that using sql server though. I think that how mysql api works.

Thanks,
Murali Bala
 
Thx s2001, I was afraid of that. I'm trying to take care of it on the C# side.
 
I think that depends on what library you use. the mysqli library has a function for multiple SQL statements as far as I know.

Be careful to double and triple-checks your input though. Multiple SQL statements are great for hackers to abuse. Most dangerous SQL injections just don't work with just one query.

AND off course the user you log in with should have minimal rights.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)
 
I use multiple statements via ODBC 5.x driver (over ADO/MDAC) and it works. I must say these are update statements only (so no selects).


/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top