Java: INSERT INTO inside a loop using a test result

Question

I'm having a little problem when I try to insert some data into my database (MS-SQL). I use this code (I just copy-paste it all, so someone can understand something that I don't know)

try { Connection connection = null; Statement Statement = null; ResultSet ResultSet = null; String query = ""; // jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]] String host = "jdbc:sqlserver://server;databaseName=db"; String username = "user"; String password = "pass"; Statement = connection.createStatement(); DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Calendar cal = Calendar.getInstance(); DateFormat dateFormat1 = new SimpleDateFormat("dd.MM.yyyy"); Calendar cal1 = Calendar.getInstance(); for (int k = 0; k < idArray.length ; k ++ ) { query = "INSERT INTO Companies (ID,Company,Company2,Company3,Country,ZIP,City,Street," + "Phone,Fax,Email,Internet,CustomerNo,AccountMngr,Icon,AddressSource," + "UserDefined1,UserDefined2,CreatedOn,CreatedBy,ColectedInformation) " + " VALUES ('" + UUID.randomUUID() + "','" + company_nameArray[k] + "','" + company_name2Array[k] + "','" + company_name3Array[k] + "','DE','" + zipArray[k] + "','" + cityArray[k] + "','" + streetArray[k] + "','" + phone_Array[k] + "','" + faxArray[k] + "','" + emailArray[k] + "','" + internetArray[k] + "','" + customer_noArray[k] + "','d','131','60','Baufinder Import','Import Datum " + dateFormat1.format(cal1.getTime()) + "','" + dateFormat1.format(cal1.getTime()) + "','d','" + "cxcxvcx" + "')"; ResultSet = Statement.executeQuery(query); } connection.close(); } catch(NullPointerException e) { System.out.println("NullPointerException"); } catch ( SQLException err ) { System.out.println( "SQL Exception:" ) ; // Loop through the SQL Exceptions while( err != null ) { System.out.println( "State : " + err.getSQLState() ) ; System.out.println( "Message: " + err.getMessage() ) ; System.out.println( "Error : " + err.getErrorCode() ) ; err = err.getNextException() ; } } catch( Exception e ) { System.out.println( "There is a problem with " + e ) ; } } 

and I get this error:

SQL Exception: State: null Message: There was no result returned by the statement. Error: 0

I do not understand the problem with the test result. I use it a lot since I am trying to read data from another database, and now I am trying to insert them into another. In addition, all of these tables do not contain null values.

Thanks in advance:)

Answer 1

Use Statement # executeUpdate (String) instead of Statement # execute (query);

 for (int k = 0; k < idArray.length ; k ++ ) { query = "INSERT INTO Companies (ID,Company,Company2,Company3,Country,ZIP,City,Street," + "Phone,Fax,Email,Internet,CustomerNo,AccountMngr,Icon,AddressSource," + "UserDefined1,UserDefined2,CreatedOn,CreatedBy,ColectedInformation) " + " VALUES ('" + UUID.randomUUID() + "','" + company_nameArray[k] + "','" + company_name2Array[k] + "','" + company_name3Array[k] + "','DE','" + zipArray[k] + "','" + cityArray[k] + "','" + streetArray[k] + "','" + phone_Array[k] + "','" + faxArray[k] + "','" + emailArray[k] + "','" + internetArray[k] + "','" + customer_noArray[k] + "','d','131','60','Baufinder Import','Import Datum " + dateFormat1.format(cal1.getTime()) + "','" + dateFormat1.format(cal1.getTime()) + "','d','" + "cxcxvcx" + "')"; int updatedRowCount = Statement.executeUpdate(query); } 

Notes:

• Please use PreparedStatement to prevent the possibility of SQL injection if some data is directly entered by the user.
• Try using batch insert if you need to improve insert performance.