java.lang.ArrayIndexOutOfBoundsException: 1

[Reshma Rangarajan]

Hi,

I am trying to do an insert into table using PreparedStatement. I get a java.lang.ArrayIndexOutOfBoundsException: 1 exception at pStatement.setString(2, id);. Here's the code and stack trace. Can someone please let me know whats the bug in this code. Thanks in advance

PreparedStatement pStatement = null;
            ResultSet resultSet = null;
            StringBuffer billsNotFound = new StringBuffer();
            ArrayList<Long> billsFound = new ArrayList<Long>();
            String queryString = "select count(*) " +
            			"from customer c " +
            			"where c.cust_number = ?";

            String insertString = "INSERT into staging_c(bill_num, id, dtatus) VALUES(?,?,?)";
            String id = requestVO.getId();
            for(int loop = 0; loop < billNumbers.length ; loop++)
            {
            	pStatement = conn.prepareStatement(queryString);
               	pStatement.setString(1, ("" + billNumbers[loop]));
            	resultSet = pStatement.executeQuery();

            	while(resultSet.next())
            	{
            		if(resultSet.getInt(1) != 1)
            			billsNotFound.append(billNumbers[loop] + ",");
            		else
            		{
            			billsFound.add(billNumbers[loop]);
            			
            			conn.prepareStatement(insertString);
            			String billNumber = "" + billNumbers[loop]; 
            			pStatement.setString(1, billNumber);
               			pStatement.setString(2, id);
            			pStatement.setString(3, "P");
            			
            			pStatement.executeUpdate();
            		}
            	}
            }

[/code][/code]



java.lang.ArrayIndexOutOfBoundsException: 1
at oracle.jdbc.dbaccess.DBDataSetImpl._getDBItem(DBDataSetImpl.java:378)

at oracle.jdbc.dbaccess.DBDataSetImpl._createOrGetDBItem(DBDataSetImpl.j
ava:781)
at oracle.jdbc.dbaccess.DBDataSetImpl.setBytesBindItem(DBDataSetImpl.jav
a:2449)
at oracle.jdbc.driver.OraclePreparedStatement.setItem(OraclePreparedStat
ement.java:1190)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedSt
atement.java:1610)
at com.comp.sample.datalayer.SampleInsert.testInsert(Unknown Sourc
e)

[sri ramvaithiyanathan]

can you please tell me what is billNumbers which you have used in for loop.
whether you checked in the line number 378.
put some logger or sop to check the value of id inside the else part.

Regards,
Sriram.V

[Kuldip Shetty]

Both the select and Insert query statements are using the same preparedStatement variable pStatement. While creating the preparedStatement instance for insert query guess you forgot to intialise to a new preparedStatement variable (LHS is missing)

conn.prepareStatement(insertString); // LHS is missing. Prepared Statement created is not intialised to a variable

Kuldip

[Sudheer Bhat]

A general suggestion. It would be better if you Prepare your SELECT and INSERT statements outside the loop. If you are preparing the statements inside the loop, then you are soft parsing the query for each iteration of the loop and there by loosing the primary advantage of prepared statement - prepare once, execute many times.

[Jan Cumps]

Summarizing the great advise from the replies you received:

...
            String queryString = "select count(*) " +
...
            String insertString = "INSERT into staging_c(bill_num, id, dtatus) VALUES(?,?,?)";
 
           PreparedStatement insertStmt = conn.prepareStatement(insertString);  // initialize before loops
           PreparedStatement queryStmt = conn.prepareStatement(queryString);  // initialize before loops
...
            for(int loop = 0; loop < billNumbers.length ; loop++)
            {
               // MOVED out of loop pStatement = conn.prepareStatement(queryString);
               	queryStmt.setString(1, ("" + billNumbers[loop]));
            	resultSet = queryStmt.executeQuery();

            	while(resultSet.next())
            	{
 ...
           			
            			// MOVED OUT OF LOOP, and was wrong    conn.prepareStatement(insertString);
...
            			insertStmt.setString(1, billNumber); // used correct statement object
               			insertStmt.setString(2, id); // used correct statement object
            			insertStmt.setString(3, "P"); // used correct statement object
            			
            			insertStmt.executeUpdate(); // used correct statement object
            		}
            	}
            }

[Reshma Rangarajan]

that was spot on. Initializing the second prepareStatement solved my issue.

iStatement = conn.prepareStatement(insertString);

I have used two different prepared statements now for query and insert.

PS: billNumbers was an array of 'Long' values.

Thanks a lot to all of you for the help.