problem with java --> sql

[bikkerss]

hey, i'm trying to learn some java and started my own project where i'm trying to filter from dates but i get the following error with this sql statement.
what is wrong here ?

SQL="SELECT * from info where firstdate between'" + Startdate.getText() +"'" + "and'" + Enddate.getText() +"'" + ",count(topic) FROM INFO GROUP BY topic'" ;


Caused by: java.sql.SQLException: near ",": syntax error

[JosAH]

hey, i'm trying to learn some java and started my own project where i'm trying to filter from dates but i get the following error with this sql statement.
what is wrong here ?

SQL="SELECT * from info where firstdate between'" + Startdate.getText() +"'" + "and'" + Enddate.getText() +"'" + ",count(topic) FROM INFO GROUP BY topic'" ;


Caused by: java.sql.SQLException: near ",": syntax error

I assume your SQL variable is of type String; print it to the console and see what your sql compiler is complaining about.

kind regards,

Jos

[iluxa]

here's your SQL statement:

SELECT * from info where firstdate between'01/01/01'and'02/02/02',count(topic) FROM INFO GROUP BY topic'


more than one problem with it... First make sure the SQL query works by itself, then put it into Java

[bikkerss]

yes, it's a string
good idea

@iluxa

but i need to get the data you put satic in the sql statment '01/01/01'and'02/02/02' dynamic from textfields so i need to concatinate the strings into the sql statement or am i wrong ?

here's your SQL statement:

SELECT * from info where firstdate between'01/01/01'and'02/02/02',count(topic) FROM INFO GROUP BY topic'


more than one problem with it... First make sure the SQL query works by itself, then put it into Java

[iluxa]

what I'm saying is this: the problem is not Java but the SQL query.

- the query is not well-formatted (seems to me you need spaces in the '01/01/01'and'02/02/02' part; extra ' symbol at the end)
- when you have re-formatted it, you aren't using the GROUP BY correctly:

SELECT first_name,count(id) FROM people GROUP BY id [that's what you're doing, incorrect]
SELECT first_name,count(id) FROM people GROUP BY first_name [correct]

I'm not sure it's even possible to use SELECT * and GROUP BY in the same query.

- when you've fixed all that, Java part will work

[j2me64]

I'm not sure it's even possible to use SELECT * and GROUP BY in the same query.

your sql in the first post was wrong. the syntax is

SELECT
FROM
WHERE
GROUP BY

you can use SELECT * and GROUP BY in the same query, no problem. and the date-format is in most cases YYYY-MM-DD but look at your db manual. which db are you using?

the sql-statement

SELECT * from info where firstdate between'01/01/01'and'02/02/02',count(topic) FROM INFO GROUP BY topic'

will never work. you can't use FROM after WHERE in this way. if you want to know how many rows per topic are your table then use

select topic, count(*) from info where firstdate between startdate and enddate group by topic. try it out and check if the count is ok.

[Tolls]

Can I also say this ought to be a PreparedStatement, and binding the two dates into it?

[bikkerss]

if i use this in sql itself i get an near ",": syntax error

your sql in the first post was wrong. the syntax is

SELECT
FROM
WHERE
GROUP BY

you can use SELECT * and GROUP BY in the same query, no problem. and the date-format is in most cases YYYY-MM-DD but look at your db manual. which db are you using?

the sql-statement

SELECT * from info where firstdate between'01/01/01'and'02/02/02',count(topic) FROM INFO GROUP BY topic'

will never work. you can't use FROM after WHERE in this way. if you want to know how many rows per topic are your table then use

select topic, count(*) from info where firstdate between startdate and enddate group by topic. try it out and check if the count is ok.

[bikkerss]

great it works

thanks all of you