SQL syntax error on WHERE clause using apache Derby

Question

For the following SQL select statement ; -

resultSet = statement.executeQuery("SELECT SS100.ORDERS.ORDER_ID, SS100.ORDERS.ORDER_NUMBER," +" SS100.ORDERS.PERSON_ID, SS100.PERSON.FIRST_NAME"+
"FROM  PERSON, ORDERS " +
"WHERE SS100.PERSON.PERSON_ID = SS100.ORDERS.PERSON_ID " );

I'm getting a syntax error on the WHERE keyword - dump as follows :-

 java.sql.SQLSyntaxErrorException: Syntax error: Encountered "WHERE" at line 1, column 126.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source)
    at Database.orderQuery(Database.java:146)
    at MainApp.main(MainApp.java:19)

Caused by: java.sql.SQLException: Syntax error: Encountered "WHERE" at line 1, column 126.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)... 10 more

I've tried playing around with the alias statement, but then get an error on the '.' dot operator. I'm obviously doing something fundamentally wrong but I can't see it at the moment, can anyone help?

Check the SQL you generate here. If you print it to the console, you'll notice, that you are missing a space before the FROM. — Sirko
– Sirko, Commented Mar 17, 2014 at 15:26
thanks guys, you're absolutely right, that sorted out the problem, now I've just got to sort out the rest!! — Martin Clifford
– Martin Clifford, Commented Mar 17, 2014 at 17:05

Luiggi Mendoza · Accepted Answer · 2014-03-17 15:26:45Z

2

Look here:

resultSet = statement.executeQuery("SELECT ..." +... + " SS100.ORDERS.PERSON_ID, SS100.PERSON.FIRST_NAME"+
    "FROM  PERSON, ORDERS " +

There's no space between "SS100.PERSON.FIRST_NAME" and "FROM".

This can be easily solved by always starting your parts with an space:

resultSet = statement.executeQuery("SELECT ..." +... + " SS100.ORDERS.PERSON_ID, SS100.PERSON.FIRST_NAME"+
    " FROM  PERSON, ORDERS " +
    " WHERE ..."

answered Mar 17, 2014 at 15:26

Luiggi Mendoza

85.9k16 gold badges159 silver badges354 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Martin Clifford Over a year ago

Hi Luiggi, as per the above, problem sorted, and thank you for your excellent suggestion as to starting parts with a space, I will be doing this from now on!!

Luiggi Mendoza Over a year ago

@MartinClifford you're welcome. If you find this answer as the most useful for your problem, please mark the green check below the rep :).

Martin Clifford Over a year ago

Hi Luiggi, I'm sorry, I don't understand 'green check below' what's that all about?

Ash Burlaczenko · Accepted Answer · 2014-03-17 15:27:52Z

0

This concatenation of strings will turn into

"SELECT SS100.ORDERS.ORDER_ID, SS100.ORDERS.ORDER_NUMBER," +" SS100.ORDERS.PERSON_ID, SS100.PERSON.FIRST_NAME"+

"FROM PERSON, ORDERS " + "WHERE SS100.PERSON.PERSON_ID = SS100.ORDERS.PERSON_ID "

SELECT SS100.ORDERS.ORDER_ID, SS100.ORDERS.ORDER_NUMBER, SS100.ORDERS.PERSON_ID,
SS100.PERSON.FIRST_NAMEFROM  PERSON, ORDERS WHERE SS100.PERSON.PERSON_ID =
SS100.ORDERS.PERSON_ID

The issue is where FIRST_NAME and FROM join. Add a space after FIRST_NAME and the sql should run fine.

However, you should really be asking yourself why you are doing this. If you know the values to concatenate, why not have one string to start with.

answered Mar 17, 2014 at 15:27

Ash Burlaczenko

25.6k16 gold badges70 silver badges103 bronze badges

1 Comment

Martin Clifford Over a year ago

Hi Ash, the space was the problem, which I have now sorted, thanks. However I don't understand your last comment about concatenate and one string, could you explain?

Collectives™ on Stack Overflow

SQL syntax error on WHERE clause using apache Derby

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related