while (rs.next) not returning all values saved in DB

 
1
0
-1

Hi all

I would like to call all Teacher names saved in a table against a ID. However the below code returns only one record whereas there are 3 records against one id. Please help

 

import org.joget.apps.app.service.*;
import org.joget.apps.form.model.*;
import org.joget.apps.form.service.*;
import java.sql.*;
import java.util.*;

public FormRowSet getData() {

FormRowSet results = null;

if (results == null) {
results = new FormRowSet();

Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql://127.5.232.123:3306/jwdb?characterEncoding=UTF-8", "", ");

if(!con.isClosed()){
String pId = "#form.MLA_Main.studentID#";
String sql = "SELECT c_instructors FROM app_fd_Std_Ins WHERE c_studentId = ?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setString(1, pId);

ResultSet rs = stmt.executeQuery();

while (rs.next()) {
FormRow row = new FormRow();
row.put("instructorNames", (rs.getString(1) != null)?rs.getString(1):"");
//row.put("email", (rs.getString(3) != null)?rs.getString(3):"");
results.add(row);

}

}

} catch(Exception ex) {
System.err.println("Exception: " + ex.getMessage());
} finally {
try {
if(con != null)
con.close();
} catch(SQLException e) {}
}
}
//------------------------------------------------------------------------------------

return results;
}

return getData();
  1. Eric

    Have you tried adding the error messages to the joget log and see wherever there is any indication what might go wrong?

  2. Kamran Raza

    Thanks Eric. There is no error messages. The problem is that one row is returning however there has three rows of data against one 'Student ID'

CommentAdd your comment...

3 answers

  1.  
    2
    1
    0
    String sql = "SELECT c_instructors FROM app_fd_Std_Ins WHERE c_studentId = ?";

    Your SQL statement only returns one column. Please test it out and change the select query in your SQL client first.

    1. Kamran Raza

      Thank you Bastiana for your response. Yes, i have called one column. But, that column has three rows of data however only one row is returning. Looking forward to hear from you back Thank you again Kamran

    CommentAdd your comment...
  2.  
    1
    0
    -1

    The form will typically make use of the first item in FormRowSet unless you put these codes into those form elements that expect multiple objects in FormRowSet such as the FormGrid / Grid elements.

    1. Kamran Raza

      Thank you Walter for your response. Can you please elaborate? I have tried by putting these codes into 'Grid' under Bean shell load binder but nothing is returning. Best regards Kamran

    CommentAdd your comment...
  3.  
    1
    0
    -1

    Dear Bastiana,

    I have run the SQL Select statement on SQL client.

    The result has two records what i am expecting however in the Joget app only one one row is returning: I have attached below two screen shots instances for your reference:

     

    SQL statement:

     

     

     

    Joget App:

     

     

    Your response will be highly appreciated. 

     

      CommentAdd your comment...