Getting unexpected Token error in sql query

Question

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: TOP near line 1, column 8 [SELECT TOP 10 IServe.ID FROM TopUp.dbo.IServe WHERE ExpireDate >= '2019-10-03' AND TelcoID = '2' AND ProductID = '2' AND RechargeAmt = '100.0' AND Available = 1 ORDER BY ExpireDate, SN]

String query3 = "SELECT TOP " + importStockList.getOrderQuantity() +" IServe.ID FROM IServe WHERE "
            + " ExpireDate >= '" + sqlDate + "' " + " AND TelcoID = '" + importStockList.getTelcoId()
            + "' AND ProductID = '" + importStockList.getProductId() + "' AND " + "RechargeAmt = '"
            + importStockList.getRechargeAmt() + "' AND Available = 1 ORDER BY ExpireDate, SN" ;

    Session hbsessionSQL = HibernateUtilSQL.getSessionFactory().openSession();
    List<Iserve> iserve = hbsessionSQL.createQuery(query3).list();

Can you please help me this error. I am stuck here

HQL doesn't support "TOP" (or general SQL syntax - that's why it's called HQL and not SQL). — Elliott Frisch
– Elliott Frisch, Commented Oct 3, 2019 at 2:08
Have you looked into using LIMIT 10 at the end of the statement rather than TOP 10 at the start? — Jason
– Jason, Commented Oct 3, 2019 at 2:09
I am still getting the same error even after using LIMIT. FYI, I am using SQL database — Manish Patel
– Manish Patel, Commented Oct 4, 2019 at 10:00

Tim Biegeleisen · Accepted Answer · 2019-10-03 02:14:45Z

While your query is hard to read, and you should be using a prepared statement, I don't see anything wrong per se about the syntax. So the error is probably happening because TOP is not valid HQL syntax. TOP is really only supported on Microsoft databases, such as SQL Server or Access. Try using LIMIT instead:

try {
    Session session = HibernateUtilSQL.getSessionFactory().openSession();
    Connection conn = session.connection();
    String sql = "SELECT ID FROM IServe WHERE ExpireDate >= ? AND TelcoID = ? AND ProductID = ? AND RechargeAmt = ? AND Available = 1 ORDER BY ExpireDate, SN LIMIT ?";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setDate(1, sqlDate);
    ps.setInt(2, importStockList.getTelcoId());
    ps.setInt(3, importStockList.getProductId());
    ps.setInt(4, importStockList.getRechargeAmt());
    ps.setInt(5, importStockList.getOrderQuantity());
    ResultSet rs = ps.executeQuery();

    while(rs.next()) {
        // process result set here
    }
}
catch(HibernateException e) {
    e.printStackTrace();
}

Rena · Accepted Answer · 2019-10-03 06:09:46Z

0

Since its not understood what type your variables are, try to see the data by yourself. If there is an option string values contain special characters, remove them first.

edited Oct 3, 2019 at 6:09

answered Oct 3, 2019 at 5:53

Rena

718 bronze badges

Collectives™ on Stack Overflow

Getting unexpected Token error in sql query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related