Cứ thỉnh thoảng , 1 hoặc n bảng trong DB của tôi bị lỗi này mà không rõ nguyên nhân . Đó là lỗi "This exceeds the maximum allowable table row size, 8060." khi tôi muốn thêm vào 1 trường mới .
Nhưng bảng của tôi rất , rất là bé [ vài trường Char[64] , vài trường datetime và số ] . Tôi tính rowsize cho vài bảng thì maximum không bao giờ vượt quá 500 bytes .
Mỗi khi bị lỗi như vậy , sau 1 hồi hết cách tôi chỉ còn cách tạo ra bảng mới [để có đủ default ..v..v] , đổ dữ liệu ở bảng lỗi vào bảng mới , rồi drop bảng lỗi , rename bảng mới , sau đó thêm cột mới [ trước đó vì thêm cột mới gây lỗi ] , mọi việc bình thường .
Bảng của tôi là bảng Heap . Tôi cũng thử bằng cách tạo clustered index rồi drop để rebuild heap , hoặc dùng hàm dbcc checktable , dbcc dbrepair , DBCC INDEXDEFRAG .
Hi all, I'm trying to make insertion in Oracle Db using prepared statement but it is giving me this exception : " java.sql.SQLException: Invalid column index", i searched in the forum all doing select or update no one making insertion .so i cant find what's wrong
Here is my code
Any suggestions ?
[edited to avoid horizontal scrolling on smaller screens]
"java.sql.SQLException: Invalid column index" is a frequent error while working in Java Database Connectivity [JDBC]. As the name suggests "Invalid column index" its related to accessing or setting column in your SQL Query using prepared statement in Java. I have seen "java.sql.SQLException: Invalid column index" coming mostly due to two reason:
- Setting column data using setXXXX[int coloumIndex] e.g. setInt[0] setString[0]
- Getting column data using getXXX[int columnIndex] e.g. getInt[0] getString[0]
The most common cause of "java.sql.SQLException: Invalid column index" is a misconception that column index started with "0" like array or String index but that's not true instead column index starts with "1" so whenever you try to get or Set column data with column index "0" you will get "java.sql.SQLException: Invalid column index".
java.sql.SQLException: Invalid column index
That's was the one case another case was you are simply putting wrong column index, like if your query is returning 3 columns in ResultSet and you are trying to access 4th column, JDBC will throw "java.sql.SQLException: Invalid column index" or if your PreparedStatement has 3 place holders but you are trying to set data on 4th column index you will be greeted by "java.sql.SQLException: Invalid column index".
In the next section, we will see a real-life example of "java.sql.SQLException: Invalid column index" while writing JDBC code.
An example of "java.sql.SQLException: Invalid column index"
here is a sample code example of getting some data from the PreparedStatement SELECT SQL query. here we have put just one placeholder for passing order_id. if you pass place holder anything other than "1" like "0" or "2" you will get "java.sql.SQLException: Invalid column index", just try it on your end and you will get hold of it. The same is true while you are reading data from ResultSet.
Actually, ResultSet offers two ways to access column data either by column name or column index.
If you access column data using an incorrect column name, JDBC will throw "java.sql.SQLException: Invalid column name" while if the index is incorrect JDBC will throw "java.sql.SQLException: Invalid column index". I prefer accessing column data using the name because it's more readable in code.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class InvalidColumnIndexExample {
public static void main[String args[]] throws SQLException {
Connection conn = DriverManager.getConnection["jdbc:oracle:thin:@localhost:1526:TESTID", "root", "root"];
PreparedStatement preStatement = conn.prepareStatement["select distinct item from Order where order_id=?"];
preStatement.setString[0, "123456"]; //this will throw "java.sql.SQLException: Invalid column index" because "0" is not valid colum index
ResultSet result = preStatement.executeQuery[];
while[result.next[]]{
System.out.println["Item: " + result.getString[2]]; //this will also throw "java.sql.SQLException: Invalid column index" because resultset has only one column
}
}
}
Output:
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal[OraclePreparedStatement.java:7700]
at oracle.jdbc.driver.OraclePreparedStatement.setString[OraclePreparedStatement.java:7654]
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString[OraclePreparedStatementWrapper.java:910]
That’s all on how to fix “Exception in thread "main" java.sql.SQLException: Invalid column index” Just beware that column index on ResultSet and PreparedStatement parametric query starts with 1 and not 0 and accessing column with index 0 will result in invalid column index error.