I've been grinding my teeth in this issue since yesterday. I wanted to retrieve a record in the database using a view and here is my code:
try {
Class.forName("org.postgresql.Driver").newInstance();
Connection Conn = DriverManager.getConnection("jdbc:postgresql://{ipaddress}/database?user=postgres&password=password");
Statement Stmt = Conn.createStatement();
String sqlCommand = "SELECT num, ip_address, ftp_user, ftp_password, ftp_location, site FROM table_view WHERE site = 'test' ";
ResultSet RS = Stmt.executeQuery(sqlCommand);
while (RS.next()) {
data.add(RS.getInt("num")
+ "=>" + RS.getString("ip_address")
+ "=>" + RS.getString("ftp_user")
+ "=>" + RS.getString("ftp_password")
+ "=>" + RS.getString("ftp_location"));
}
// Clean up after ourselves
RS.close();
Stmt.close();
Conn.close();
}
catch (SQLException E) {
System.out.println("SQLException: " + E.getMessage());
System.out.println("SQLState: " + E.getSQLState());
System.out.println("VendorError: " + E.getErrorCode());
}
catch(Exception ex)
{
System.out.println(ex.getMessage());
}
I know that the query used above returns a row because I tried to run it in the pgAdmin and it does. But when I do it with that code, it does not go inside the while(RS.next())
loop where this is when you extract the data, right?
I have been using these lines of code and it works fine. Somehow it does not respond to this. The view is owned by the user I used in the connection. Also, I tried to use the query used in the view, the select query before it is created as a view, and it returned 1 row but still the same when running the code, it does not return any data.
Is there something I did wrong? or something I lacked? is the view an issue? Please help. Thanks.
I also tried to change the query something like this:
String sqlCommand = "SELECT num, ip_address, ftp_user, ftp_password, ftp_location, site FROM table_view WHERE site = 'test' "
+ " UNION "
+ " SELECT 0, '', '', '', '', '' ";
and goes inside the while loop but only takes the added union select. I am very sure that the first select returns a row.