Oc-windows.ru

IT Новости из мира ПК
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Java sql sqlexception invalid column index

How to fix java.sql.SQLException: Inval >

java.sql.SQLException: Invalid column index

An example of «java.sql.SQLException: Invalid column index»

19 comments :

The colum index begin 1.
For example :ResultSet rs=ms.execute(«SELECT id, description FROM news»);

May 11, 2012 at 12:20 AM Anonymous said.

I am facing problem of invalid column index plz sombody help me

PreparedStatement ps=con.prepareStatement(«insert into ABC values(ID.nextval. )»);

ps.setInt(1,ID);
System.out.println(ID);
ps.setString(2,textField.getText());
ps.setString(3,textField_1.getText());
ps.setString(4,textField_2.getText());

if(rdbtnM.isSelected())
<
ps.setString(5, rdbtnM.getText());
>
if(rdbtnF.isSelected())
<
ps.setString(5, rdbtnF.getText());
>

plz fix the error of invalid column index

Hi Anonymous, Can you please post full exception stacktrace? From your code it looks like you are setting fifth position ps.setString(5) but I can only see 4 placeholders (?) in your prepared statement. This could be the cause of your SQLException, invalid column index.

August 2, 2013 at 10:26 PM Anonymous said.

final JButton btnRegister = new JButton(«Register»);
btnRegister.addActionListener(new ActionListener() <
public void actionPerformed(ActionEvent me) <
if(me.getSource()==btnRegister)
<
//Connection >try
<

Connection con=ConnectionClass.getCon();
PreparedStatement ps=con.prepareStatement(«insert into ABC values(ID.nextval. )»);

ps.setInt(1,ID);
System.out.println(ID);
ps.setString(2,textField.getText());
ps.setString(3,textField_1.getText());
ps.setString(4,textField_2.getText());

if(rdbtnM.isSelected())
<
ps.setString(5, rdbtnM.getText());
>
if(rdbtnF.isSelected())
<
ps.setString(5, rdbtnF.getText());
>

catch(SQLException sqle)
<
sqle.printStackTrace();
>

btnRegister.setBounds(202, 308, 89, 23);
contentPane.add(btnRegister);

my connection class is connected with oracle 10g database in which a table is created ABC having columns as (ID number(10) primary key,Fname varchar2(20),Lname varchar2(20),Username varchar2(20),Gender char(1))

above is the order in the table

I have to dynamically auto-increament the value of ID and it is giving me the error of INVALID COLUMN INDEX

PLZ SOLVE THE PROBLEM .

Try removing ps.setInt(1) call and reduce subsequent numbers till 4 e.g. ps.setString(2,textField.getText()); should be ps.setString(1,textField.getText());, change all of them so that your last index should be 4. I hope this will solve your invalid index problem.

August 3, 2013 at 4:22 AM Anonymous said.

thanks I have solved my problem 🙂

one problem is also there i.e. I have to apply the constraints on respective text feilds as per their requirement..

so plz solve this problem I am not able to apply the constraint it gives lots of error when I apply it.

August 3, 2013 at 9:47 PM Anonymous said.

best way to solve this type of problem is put a sop before the starting of sql and then compile the program.you will get error in system out log and now check the failed query.try to resolve the query and put it back into program.now compile it.its done

February 15, 2014 at 2:09 AM Anonymous said.

Given below is my Java source code for Issuing a book. I want to enter the date of Issue of book in database and I want to pick up that date from the desktop/laptop shows in corner of bottom of screen.

public class Issue extends JFrame <

protected static final String String = null;
private JPanel contentPane;
private JTextField textField;
private JTextField textField_1;

/**
* Launch the application.
*/
public static void main(String[] args) <
EventQueue.invokeLater(new Runnable() <
public void run() <
try <
Issue frame = new Issue();
frame.setVisible(true);
> catch (Exception e) <
e.printStackTrace();
>
>
>);
>

/**
* Create the frame.
*/
public Issue() <

setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
setBounds(100, 100, 509, 467);
contentPane = new JPanel();
contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
setContentPane(contentPane);
contentPane.setLayout(null);

JLabel lblNewLabel = new JLabel(«Book Id»);
lblNewLabel.setHorizontalAlignment(SwingConstants.CENTER);
lblNewLabel.setBounds(177, 128, 92, 14);
contentPane.add(lblNewLabel);

/* JLabel lblNewLabel_1 = new JLabel(«Date of Issue»);
lblNewLabel_1.setHorizontalAlignment(SwingConstants.CENTER);
lblNewLabel_1.setBounds(192, 196, 77, 17);
contentPane.add(lblNewLabel_1);*/

Читать еще:  Код ошибки 0xc000007b

textField = new JTextField();
textField.setBounds(309, 125, 86, 20);
contentPane.add(textField);
textField.setColumns(10);

/*textField_1 = new JTextField();
textField_1.setBounds(309, 194, 86, 20);
contentPane.add(textField_1);
textField_1.setColumns(10);*/

final JButton btnIssue = new JButton(«Issue»);
btnIssue.addMouseListener(new MouseAdapter() <
@Override
public void mouseClicked(MouseEvent me)
<

if(me.getSource()==btnIssue)
<
try
<
**********************Calendar currentDate = Calendar.getInstance();
SimpleDateFormat formatter=
new SimpleDateFormat(«yyyy/MMM/dd»);
String dateNow = formatter.format(currentDate.getTime());
System.out.println(«Now the date is :=> » + dateNow);*******************

PreparedStatement ps;
Connection con;
con = ConnectionClass.getCon();
ps=con.prepareStatement(«insert into Book_Issue values(. )»);

ps.setInt(1,Integer.parseInt(textField.getText()));
++++++++ps.setDate(2, «dateNow»);++++++++++++
// ps.executeUpdate();

ps.executeUpdate();
JOptionPane.showMessageDialog(null, «THIS BOOK IS ISSUED TO YOU «);
// ps.executeUpdate();

>
catch(Exception sqle)
<
sqle.printStackTrace();
>

>);
btnIssue.setBounds(180, 297, 89, 23);
contentPane.add(btnIssue);

JButton btnNewButton_1 = new JButton(«Reset»);
btnNewButton_1.addMouseListener(new MouseAdapter() <
@Override
public void mouseClicked(MouseEvent me1) <
textField.setText(«»);
textField_1.setText(«»);
>
>);
btnNewButton_1.setBounds(316, 297, 89, 23);
contentPane.add(btnNewButton_1);

JButton btnNewButton_2 = new JButton(«Back»);
btnNewButton_2.addMouseListener(new MouseAdapter() <
@Override
public void mouseClicked(MouseEvent arg) <
new Confirm().show();
>
>);
btnNewButton_2.setBounds(253, 380, 89, 23);
contentPane.add(btnNewButton_2);
>
>

here above the code present in between ***** is of obtaning code from the laptop/desktop and the code betweeen ++++ is the place where I have to use it to display it in database, without writing the date in textfiled.

I hope you would be able to uderstand my query.

March 18, 2014 at 9:41 AM Anonymous said.

We can see similar issue when it mismatches in number of ‘?’ in the given query string.

May 9, 2014 at 12:43 AM Anonymous said.

package org.java.com;
import java.sql.*;
public class PrepareState <

public static void main(String[] args)throws Exception <
try<
Class.forName(«oracle.jdbc.driver.OracleDriver»);
Connection con=DriverManager.getConnection(«jdbc:oracle:thin:@localhost:1521:xe»,»system»,»jayabhargav38″);
//PreparedStatement stmt=con.prepareStatement(«insert into emp values(. )»);
PreparedStatement stmt=con.prepareStatement(«update emp set name=?, >stmt.setInt(5,161);
stmt.setString(6,»karan»);
int i=stmt.executeUpdate();
System.out.println(i+ «records inserted»);
con.close();
>catch(Exception e)<

output:invalid column index

Hi
I have very simple query
Select ipaddress, logontime, logofftime from usersssion.
It has some other fields as well but we are looking only for ipadddress, logogg and logon time

But when we run this query through arcsight smart connector it is giving invalid index coloum error.
Can you please advise

May 12, 2015 at 9:54 AM Anonymous said.

pls guys I need help on how to retrieve data from java embedded database

@Anonymous, you need to write the Java program using JDBC to connect to Java embedded database to get the data. You also make sure to use the relevant JDBC drivers.

///services
String sname=request.getParameter(«sname»);
String );
String deluxe=request.getParameter(«deluxe»);
String tax=request.getParameter(«tax»);
//category
String cat=request.getParameter(«category»);

Connection connect = null;
PreparedStatement ps=null;
PreparedStatement ps1=null;

try <
Class.forName(«com.mysql.jdbc.Driver»);
// Setup the connection with the DB
connect = DriverManager
.getConnection(«jdbc:mysql://localhost:3306/tariff»,»root»,»123456″);

ps=connect.prepareStatement(
«insert into services(service_name,classic,deluxe,tax) values(. )»);
ps.setString(1,sname);
ps.setString(2,classic);
ps.setString(3,deluxe);
ps.setString(4,tax);
ps.executeUpdate();
ps.close();

ps1=connect.prepareStatement(«insert into category(category) values(?)»);
ps1.setString(1,cat);

>
catch(Exception e) <
System.out.println(e);
>

showing error: incorrect integer value; ‘ ‘ for column at row 1

@Shiva, because you are setting String «ps.setString(1,sname);» and I guess your database column is of integer type. Whatever you enter must match schema of table, check your service table.

I try to create a login page using jsp, but it is showing invalid column index. While I run the same code in db. It runs smoothly.
please someone help me.
Here is my code,
Class.forName(«oracle.jdbc.driver.OracleDriver»);
Connection con = DriverManager.getConnection(«jdbc:oracle:thin:@localhost:1521:xe»,»ksrce_ceats»,»ceats»);
PreparedStatement pre=null;
ResultSet rs;
pre = con.prepareStatement(«select * from login_tbl where emp «);
pre.setString(1,request.getParameter(user));
pre.setString(2,request.getParameter(pass));
rs = pre.executeQuery();
if(rs.next()) <
response.sendRedirect(«user.html»);
session.setAttribute(«empid», user);
>else <
out.println(«Invalid password try again»);
>
>

Читать еще:  Регулярное выражение для email javascript

If that’s a SQLException thrown by Java, it’s most likely because you are trying to get or set a value from a ResultSet, but the index you are using isn’t within the range.

For example, you might be trying to get the column at index 3 from the result set, but you only have two columns being returned from the SQL query.

or may be a column doesn’t exist

I am facing problem of invalid column index error.please help me
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JdbcResultSet <

/**
* @param args
*/
public static void main(String[] args) <
// TODO Auto-generated method stub
Connection con;
PreparedStatement prp;
String sql = «select * from s >ResultSet rs;

Java sql sqlexception invalid column index

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

We have a use case to pull non-PII info from our HR system and get in into an active list. The Oracle database for the HR system is overwritten with new information once a day, so our connector frequency will be set for 24 hours.

I am using the when I look at the agent.log file.

query=SELECT EmployeeId, LAST_NAME FROM HR.HR_COMN_EMPL

max > where regexp_like( emplid, ‘^[[:digit:]]*$’ )

event.sourceUser >event.deviceCustomString1=LAST_NAME
event.deviceCustomString1Label=__stringConstant(«Last Name»)
event.deviceVendor=__getVendor(«HR»)
event.deviceProduct=__stringConstant(«HRProduct»)

Here is a sample of the EmployeeId field:

Here is the detailed error:

[2015-11-19 23:16:25,129][ERROR][default.com.arcsight.agent.loadable.flexagent._FlexIdBasedDatabaseAgent][setDeviceConnectionState] Device connection to [jdbc:oracle:thin:@servername:0000/databasename|user] down.(Invalid column index)

java.sql.SQLException: Invalid column index

[2015-11-19 23:16:25,130][FATAL][default.com.arcsight.agent.sdk.d.e.i][processQuery()] Failed to process query [[SELECT EmployeeId, LAST_NAME FROM HR.HR_COMN_EMPL] for [Wed Dec 31 18:02:15 CST 1969]/[135010]] for [jdbc:oracle:thin:@servername:0000/databasename]

We do not have the ability to use a separate process to export the data and use the arcsight command to import it directly into an activelist. We also don’t have the ability to insert another column into the table.

Has anyone run into this error before? Do you know what data the connector is expecting for the maxid query?

FlexConnector

Smart Connector

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

I just realized I’m missing devcieEventClassId, is that a mandatory field? I have that populated on all my other flexconnectors.

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

Hello!
Are you sure that your JDBC connection string is correct, or you just made it hidden like that?

for [jdbc:oracle:thin:@ servername:0000 /databasename]

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

Hi, thanks for the reply. I replaced the confidential info with bogus info. The connection to the database is working fine.

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

Run you maxid.query in some SQL Client and post the results here. Might as well make it bogus also.

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

I had to get the DBA’s to run the query for me, but here is the output:

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
Читать еще:  Java found set required collection

Well, even though you’re using loopingenabled, I think you still have to pass the ID variable to the query, so your query should be looking like this:

query=SELECT EmployeeId , LAST_NAME FROM HR.HR_COMN_EMPL

ORDER BY EmployeeId

Please try and post the results.

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

Thanks for the suggestion, I think I made it further. I am now getting the error below. I am having the DBA’s run this SQL directly against the database to try to find the invalid number.

[2015-11-23 14:57:32,800][ERROR][default.com.arcsight.agent.loadable.flexagent._FlexIdBasedDatabaseAgent][setDeviceConnectionState] Device connection to [jdbc:oracle:thin:@servername:0000/databasename|user] down.(ORA-01722: invalid number)

[2015-11-23 14:57:32,808][INFO ][default.com.arcsight.agent.Agent][instantiateAgents] Agent [HR] started.

java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

[2015-11-23 14:57:32,828][FATAL][default.com.arcsight.agent.sdk.d.e.i][processQuery()] Failed to process query [[SELECT (to_number (EmployeeId)), LAST_NAME FROM HR.HR_TAR_COMN_EMPL WHERE EmployeeId > ? ORDER BY EmployeeId] for [Wed Dec 31 18:02:15 CST 1969]/[135049]] for [jdbc:oracle:thin:@servername:0000/databasename].

I also tried this for the query but got the same error:

query=SELECT (to_number (EmployeeId)), LAST_NAME FROM HR.HR_TAR_COMN_EMPL

WHERE regexp_like( EmployeeId, ‘^[[:digit:]]*$’ )

ORDER BY EmployeeId

[2015-11-23 14:50:14,833][ERROR][default.com.arcsight.agent.loadable.flexagent._FlexIdBasedDatabaseAgent][setDeviceConnectionState] Device connection to [jdbc:oracle:thin:@servername:0000/|databasename|user] down.(ORA-01722: invalid number

java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

[2015-11-23 14:50:14,851][INFO ][default.com.arcsight.agent.Agent][instantiateAgents] Agent [HR] started.

[2015-11-23 14:50:14,883][FATAL][default.com.arcsight.agent.sdk.d.e.i][processQuery()] Failed to process query [[SELECT (to_number (EmployeeId)), LAST_NAME FROM HR.HR_TAR_COMN_EMPL WHERE regexp_like( EmployeeId, ‘^[[:digit:]]*$’ ) AND EmployeeId > ? ORDER BY EmployeeId] for [Wed Dec 31 18:02:15 CST 1969]/[135049]] for [jdbc:oracle:thin:@servername:0000/databasename].

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

I found a workaround to my problem, but wanted to report what I had found.

When troubleshooting, I came across this error:

Thread: java.sql.SQLException: Invalid column name

LinkBack
Thread Tools
Search Thread
Display
  • Linear Mode
  • Switch to Hybrid Mode
  • Switch to Threaded Mode

java.sql.SQLException: Inval >

I have checked the same below mentioned query in oracle sql developer, it is fetching the output correctly but when i tried to run the query through java. it is giving me java.sql.SQLException: Invalid column name
..
please help me

Query:
select collection from delete_queue where QUEUE_STATUS_ group by collection

programmatically running the query:

String selectString=»select collection from delete_queue where queue_status_ group by collection «;

Statement selectStatement = this.conn.createStatement();

//Execute query and handle ResultSet
ResultSet results = selectStatement.executeQuery(selectString);

What driver are you using?
Is that the full exception?
Are you sure you’re looking at the same db?

java.sql.SQLException: Inval >

driver name : oracle.jdbc.driver.OracleDriver

here is the full exception

at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:227)
at oracle.jdbc.driver.OracleStatement.get_column_inde x(OracleStatement.java:3086)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn( OracleResultSetImpl.java:1854)
at oracle.jdbc.driver.OracleResultSet.getInt(OracleRe sultSet.java:1605)

That’s not the full error.
Anyway:
at oracle.jdbc.driver.OracleResultSet.getInt(OracleRe sultSet.java:1605)

It’s being thrown when calling getInt() on the result set. You haven’t given us that code.

String selectString=»select collection from delete_queue where queue_status_ group by collection «;

Statement selectStatement = this.conn.createStatement();

//Execute query and handle ResultSet
ResultSet results = selectStatement.executeQuery(selectString);

/*—handleresultsetMultiple method—-*/
private List handleResultSetMultiple( ResultSet rs )
throws SQLException <
List deleteQueueList = new Vector ();

DeleteQueue deleteQueue = handleResultSetSingle( rs );
while( deleteQueue != null ) <
deleteQueueList.add( deleteQueue );
deleteQueue = handleResultSetSingle( rs );
>

private DeleteQueue handleResultSetSingle( ResultSet rs )
throws SQLException <
if( ! rs.next() )
return( null );

DeleteQueue deleteQueue = new DeleteQueue();

Ссылка на основную публикацию
Adblock
detector
×
×