0

This is my function in MySQL I am trying to call "GetData()" within the java code below. This is simply supposed to call the following function and print all 3 tables.

delimiter //
CREATE PROCEDURE GetData () BEGIN
SELECT * FROM Class;
SELECT * FROM Student;
SELECT * FROM ClassStudent;
END //
delimiter ;

CALL GetData();

This is my java method for the GetData call: 

public static void GetData(Connection conn){

    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
        stmt = conn.prepareStatement("Call GetData()");
        rs = stmt.executeQuery();
        // Now do something with the ResultSet ....
        boolean rowsLeft = true;
        rs.beforeFirst();
        while (rs.next()) {
            System.out.println(rs.getInt(1) 
                    + ":" + rs.getString(2) 
                    + ":" + rs.getString(3) 
                    + ":" + rs.getString(4)
                    + ":" + rs.getString(5)
                    + ":" + rs.getString(6)
                    + ":" + rs.getString(7)
                    + ":" + rs.getString(8));
        }

When I run this over onyx I get the following error: SQLException: Column Index out of range, 6 > 5.

New edited code:

public static void GetData(Connection conn){

    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
        rs = conn.prepareStatement("select * from Class").executeQuery();
        rs = conn.prepareStatement("select * fromClassStudent").executeQuery();
        rs = conn.prepareStatement("select * from Student").executeQuery();
        // Now do something with the ResultSet ....
        boolean rowsLeft = true;
        rs.beforeFirst();
        while (rs.next()) {
            System.out.println(rs.getInt(1) 
                    + ":" + rs.getString(2) 
                    + ":" + rs.getString(3) 
                    + ":" + rs.getString(4));

Output: 

Getting Data...
48:Ross:Geller:1998-10-10
49:Pheobe:Buffay:1998-09-10
50:Hope:Diamond:1996-08-07
51:Perry:Ellis:1991-08-02
52:Hermann:Cain:1987-07-10
53:Lily:Aldrin:1994-04-04
54:Endora:Bewitched:1995-09-03
55:Darwin:Moore:1992-06-08
56:Thatcher:Charles:1992-04-04
57:Sage:Seven:null

Is this an incorrect way to do this? I now have 3 Queries but it seems to only being executing the first one. This may seem trivial but I am very new to this.

Nick
  • 138,499
  • 22
  • 57
  • 95
Poggers99
  • 11
  • 3
  • Can you include your full error stack trace? As well as example data from the tables you're fetching data from. – Johan Oct 18 '18 at 21:42
  • 1
    For some reason you only have 5 columns. `rs.getString(6)` is getting a sixth which isn't valid. Also don't create procedures for fetching like this, just run 3 queries. – danblack Oct 18 '18 at 21:43
  • Likely, the first resultset returned only has five columns in it. Calling `getString(6)` is throwing an error because there is no sixth column. – spencer7593 Oct 18 '18 at 21:44
  • Did you expect one resultset to contain rows from 3 different tables? And moreover to mix their columns? – forpas Oct 18 '18 at 21:48
  • Thank you for all the responses! I will try to edit my code right now and get rid of the getString(6) on down. I will post an update soon. – Poggers99 Oct 18 '18 at 21:52
  • Found the answer to my question here: [link]https://stackoverflow.com/questions/9696572/queries-returning-multiple-result-sets Thank you guys for helping me understand what I needed to do. – Poggers99 Oct 18 '18 at 22:39

0 Answers0