JDBC - SQLITE Select to variable

Question

I am trying to run a query / select statement and save it in a variable. I know how to get something specific from a specific column but not from counting rows.

This is working as I getting MYID specifically.

ResultSet MYIDrs = stmtCFG.executeQuery( "SELECT rowid, MYID from MYINDEX order by rowid desc limit 1;" );
MYID = MYIDrs.getString("MYID");

Now I am trying to count the rows that works in SQLite client but not in the jdbc as I can't figure out what to request.

this is what I have but is not resulting in what I am expecting.

ResultSet FILE_COUNTrs = stmtCFG.executeQuery( "SELECT count(*) from TABLE where MYID = '"+MYID+"';");
FILE_COUNT = FILE_COUNTrs.getString(?????);

problem or question is: What do I put in the ????? as I already tried everything.

I am expecting to see a number.

score 1 · Accepted Answer · answered Oct 27 '16 at 14:06

1

I am really sorry I found what I was looking for by assigning a name TOTAL

This is my code and it works...

ResultSet FILE_COUNTrs = stmtCFG.executeQuery( "SELECT count(*) AS TOTAL from TABLE where MYID = '"+MYID+"';");
FILE_COUNT = FILE_COUNTrs.getString("TOTAL");

answered Oct 27 '16 at 14:06

That should not work, you need to call `FILE_COUNTrs.next()` before retrieving your value. – Mark Rotteveel Oct 27 '16 at 18:15

score 1 · Answer 2 · answered Oct 27 '16 at 14:42

1

You use wrong data type. COUNT(*) returns Integer type, Not String.

You can do like this without assigning a label for COUNT(*)

int FILE_COUNT = FILE_COUNTrs.getInt(1); // 1: is the column index of COUNT(*)

answered Oct 27 '16 at 14:42

LHA

9,398
8
46
85

JDBC - SQLITE Select to variable

2 Answers2