Results 1 to 9 of 9
  1. #1
    bikkerss is offline Member
    Join Date
    Dec 2009
    Posts
    20
    Rep Power
    0

    Default problem with java --> sql

    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

  2. #2
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,651
    Blog Entries
    7
    Rep Power
    21

    Default

    Quote Originally Posted by bikkerss View Post
    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

  3. #3
    iluxa is offline Senior Member
    Join Date
    Mar 2010
    Posts
    266
    Rep Power
    5

    Default

    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

  4. #4
    bikkerss is offline Member
    Join Date
    Dec 2009
    Posts
    20
    Rep Power
    0

    Default

    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 ?

    Quote Originally Posted by iluxa View Post
    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
    Last edited by bikkerss; 04-13-2010 at 09:22 PM.

  5. #5
    iluxa is offline Senior Member
    Join Date
    Mar 2010
    Posts
    266
    Rep Power
    5

    Default

    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

  6. #6
    j2me64's Avatar
    j2me64 is offline Senior Member
    Join Date
    Sep 2009
    Location
    Zurich, Switzerland
    Posts
    962
    Rep Power
    6

    Default

    Quote Originally Posted by iluxa View Post
    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

    Java Code:
    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.
    Last edited by j2me64; 04-13-2010 at 10:14 PM.

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,086
    Rep Power
    20

    Default

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

  8. #8
    bikkerss is offline Member
    Join Date
    Dec 2009
    Posts
    20
    Rep Power
    0

    Default

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


    Quote Originally Posted by j2me64 View Post
    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

    Java Code:
    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.

  9. #9
    bikkerss is offline Member
    Join Date
    Dec 2009
    Posts
    20
    Rep Power
    0

Similar Threads

  1. Problem with Java...
    By mc6415 in forum New To Java
    Replies: 10
    Last Post: 02-22-2010, 12:24 AM
  2. java problem
    By Mj Shine in forum New To Java
    Replies: 5
    Last Post: 08-15-2009, 05:09 AM
  3. Java Problem. Need Help!
    By bob101 in forum New To Java
    Replies: 6
    Last Post: 03-19-2009, 04:34 AM
  4. Help, java problem
    By Suriman in forum New To Java
    Replies: 4
    Last Post: 03-09-2009, 04:26 PM
  5. problem with java nio
    By andrei stoiculescu in forum Networking
    Replies: 3
    Last Post: 02-02-2009, 03:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •