Member Avatar for jahanruhi3@gmai 
package com.batch;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import DBConnection.DBConnect;
public class Batch {
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public ArrayList BatchAction(String ik) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs;
        ResultSet rs1;
        ArrayList al = new ArrayList();
        conn = DBConnect.getConnection();
        final SimpleDateFormat monthDayYearformatter = new SimpleDateFormat(
                "dd MM yyyy HH:mm:ss");
        System.out.print("is it really working....");
        try {
            st = conn.createStatement();
            rs=st.executeQuery("select * from batch order by start_date DESC");
            System.out.println("select * from batch order by start_date DESC");
            while (rs.next()) {
                ArrayList<String> aL = new ArrayList<String>();
                aL.add(Integer.toString(rs.getInt(1)));
                rs1=st.executeQuery("select * from project_stage");
                {
                    ArrayList aL1 = new ArrayList();
                    while(rs1.next()){
                        aL1.add(rs1.getString(5));
                    }aL.addAll(aL1);
                }

                aL.add(Integer.toString(rs.getInt(2)));
                java.util.Date d = rs.getTimestamp(3);
                if (d != null) {
                    String date = monthDayYearformatter.format(d);
                    aL.add(date);
                }
                else{
                    aL.add("No Start Date Found ");
                }
                java.util.Date d1 = rs.getTimestamp(4);
                if (d1 != null) {
                    String date1 = monthDayYearformatter.format(d1);
                    aL.add(date1);
                }
                else{
                    aL.add("No End Date Found");
                }
                String stageType = rs.getString(5);
                if(stageType != null)

                {
                    aL.add(stageType);
                }
                else
                {

                aL.add("No Stage Type Data Found ");
                }
                aL.add(Integer.toString(rs.getInt(6)));
                al.add(aL);
            }

            conn.setAutoCommit(true);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return al;

    }
}
Edited by Dani because: Formatting fixed
  • 2 Contributors
  • 3 Replies
  • 219 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by stultuske

Dani AI

Generated

As noted, "Invalid column index" usually means the code asked for a column number that does not exist in the current ResultSet. The thread shows the PROJECT_STAGE DDL was posted but not the BATCH table layout, so the most reliable first step is to confirm exactly what columns the batch query is returning and whether the code’s numeric indexes match those columns.

A small diagnostic that always helps is to print the ResultSet metadata before using numeric indexes:

ResultSetMetaData md = rs.getMetaData();
int cols = md.getColumnCount();
for (int i = 1; i <= cols; i++) {
  System.out.println(i + " -> " + md.getColumnLabel(i));
}

If the column count is less than the highest index used, change to column names or adjust indexes. Prefer explicit column lists in SELECT instead of SELECT * so the order and presence of columns is predictable.

Another common cause in this pattern is reusing the same Statement for a nested query. Executing a second executeQuery on the same Statement can close or change the first ResultSet on some JDBC drivers. Two safe alternatives: (a) run the project_stage query once before the loop and cache its results, or (b) use a separate Statement/PreparedStatement for the inner query and close its ResultSet promptly. Example pattern:

List<String> stages = new ArrayList<>();
try (Statement st2 = conn.createStatement();
     ResultSet tmp = st2.executeQuery("SELECT Name FROM project_stage")) {
  while (tmp.next()) stages.add(tmp.getString(1));
}

Also make sure getInt/getTimestamp/getString calls match the actual column types, and always close inner ResultSets (rs1) after use. Confirm the JDBC driver and DB (Oracle vs SQL Server) since driver behavior for multiple open ResultSets can vary. These steps resolve the vast majority of "invalid column index" cases.

Recommended Answers

All 3 Replies

Member Avatar for stultuske

nice code.
1. add code tags
2. do understand you did not provide us of a schedule of your DB, which is where your problem lies.

an invalid column index is something what may occur if you ask for the contents of the fourth column, if your table only has three columns.

Member Avatar for jahanruhi3@gmai

CREATE TABLE PROJECT_STAGE(IK int NOT NULL UNIQUE,
P6_TRANSACTION_TYPE nvarchar2(2) NULL,
ObjectID int NOT NULL,
Id nvarchar2(40) NULL,
Name nvarchar2(120) NULL,
StartDate TIMESTAMP NULL,
FinishDate TIMESTAMP NULL,
SCHED_ITEM_ID int NULL,
CREATE_DATE TIMESTAMP NULL,
LAST_UPDATE_DATE TIMESTAMP NULL,
ACTIVE int NULL,PRIMARY KEY(Id));

CREATE TABLE SCHEDULE(IK int NOT NULL PRIMARY KEY,
DESCRIPTION nvarchar2(200) NULL,
RUN_DATE TIMESTAMP NULL,
TYPE_TASKS_FK varchar2(50) NOT NULL,
RUNNING int NULL,
USER_SCHEDULED int NULL,
CONSTRAINT tblSchAct_fk FOREIGN KEY (IK,TYPE_TASKS_FK) REFERENCES TASKS1(IK,TYPE_TASKS));

Member Avatar for stultuske

mm ... I think you're not always reading your data into the correct datatype, maybe that's the problem.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.