#1
  1. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12

    Arrow how to get a result with JDBC that's not a column


    Hey there .

    What i'm trying to do is to get the result of

    select currval('some_sequence_id') with JDBC however
    rs.next();
    rs.getInt("currval");
    throws a wierd exception : postgresql.stat.result

    any ideeas ?
    FreeBSD , dooing more with less since 10 years ago
  2. #2
  3. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    117
    Hmm, I assume currval is some built in database function? Try using an alias for the function. Something like:

    select currval('some_sequence_id') as val




    Then you can use "val" as the column name:

    rs.getInt("val");

    Barring that, use the getInt(int) for an index instead of the column name.
  4. #3
  5. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12
    that works for the built in function . However I also needed this , and I thought it would work the same but it doesn't :

    select count(*) from tablename;
  6. #4
  7. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    117
    You can still use the alias

    select count(*) as c from tablename;
  8. #5
  9. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12
    duh , i was writting :
    select count(*) from contacts as c;

    it's hard to be a noob sometimes

    thanks again for putting up with some of my less inteligent problems
  10. #6
  11. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12
    heh , I just put this into code now :
    Code:
    ResultSet rs = stmt.executeQuery("Select count(*) as c from table");
                rs.next();
                currval = rs.getInt("c");
    The SQL exception that was thrown by this code is : postgresql.stat.result .
    So how can I get that result into JDBC . This is the same problem Exception I was thrown before when i was not using aliasing ...
  12. #7
  13. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    117
    Hmm, I don't know. besides checking to make sure there IS a next, I don't see anything wrong.
    Code:
    ResultSet rs = stmt.executeQuery("Select count(*) as c from table");
    if(rs.next())
                currval = rs.getInt("c");
    Can you get a command prompt for postgresql and type that query in manually? This will remove java/the web server as a potential source of the problem.

    The mailing list archives on the postgresql site are down. I would search in there for some anwers too.
  14. #8
  15. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12
    yea , I was playing with those commands on the pg terminal before coding . I allwais tend to run the queries in the terminal at first . They work just fine in the terminal . The same exception was thrown when using 3 different ways of extracting data :
    rs.getInt("c"); < - alias
    rs.getInt("count"); < - what postgres shows as a "column name" for the result
    rs.getInt(1); < - column id

    all of them trew the same exception . I don't think this is a postgreSQL fault ... hmmm , I wonder if it could be the driver , but i doubt it ...

    are you saying that this is normally the correct JDBC syntax ?
  16. #9
  17. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    538
    This is what I'm using (on MySQL, but I don't think that the problem is in the db):

    <%@ page language="java" import="java.sql.*" %>
    <%
    // define variables
    String Count;

    // define database parameters
    String host="localhost";
    String user="tbd";
    String pass="tbd";
    String db="db";
    String conn;
    %>
    <%

    Class.forName("org.gjt.mm.mysql.Driver");

    // create connection string
    conn = "jdbc:mysql://" + host + "/" + db + "?user=" + user + "&password=" + pass;

    // pass database parameters to JDBC driver
    Connection Conn = DriverManager.getConnection(conn);

    // query statement
    Statement SQLStatement = Conn.createStatement();

    // generate query
    String Query = "select count(*) as c from table";

    // get result
    ResultSet SQLResult = SQLStatement.executeQuery(Query);

    while(SQLResult.next())
    {
    Count = SQLResult.getString("c");
    out.println("<tr><td>" + Count + "</td></tr>");
    }

    // close connection
    SQLResult.close();
    SQLStatement.close();
    Conn.close();

    %>
  18. #10
  19. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12
    heh ...
    thanks pabloj ... the problem was that I was trying to retreive an integer and I was getting a String of some sort . I noticed you were using strings in your code . So I tried that and it worked ...

    I find it a bit odd that u'd want to retreive strings for things as :

    count() or max() or sum() , but at least it's not working ...

    thanks once again to u guys , for offering me very good help

IMN logo majestic logo threadwatch logo seochat tools logo