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 ?

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.

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;

You can still use the alias

select count(*) as c from tablename;

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

heh , I just put this into code now :

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 ...

Hmm, I don't know. besides checking to make sure there IS a next, I don't see anything wrong.

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.

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 ?

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();

%>

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