MySQL syntax error using LIMIT command with Prepared Statement in Java

Question

I am writing code in Java and I want to take every time I run this code the next line from a MySQL table.The second time I run this code is this.

String timh1 = "1";
String timh2 = "2";
PreparedStatement st = null;
String sqlGrammes = "SELECT SURNAME ,KATHGORIA, AFM , NAME FROM EMPLOYEE LIMIT ?,? ";
try {
    st = connection.prepareStatement(sqlGrammes);
    st.setString(1, timh1);
    st.setString(2, timh2);

But it shows me this error :

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''1','2'' at line 1

Looks like you can't parametrize the limit values. – juergen d Jul 11 '15 at 17:47 — juergen d, Jul 11 '15 at 17:47

score 7 · Accepted Answer · answered Jul 11 '15 at 17:49

limit accepts integer parameters, so you should use ints, not Strings:

int timh1 = 1;
int timh2 = 2;
PreparedStatement st = null;
String sqlGrammes = "SELECT SURNAME ,KATHGORIA, AFM , NAME FROM EMPLOYEE LIMIT ?,? ";
try {
    st = connection.prepareStatement(sqlGrammes);
    st.setInt(1, timh1); // notice the setInt
    st.setInt(2, timh2); // here too

Bhavik Patel · Answer 2 · 2016-10-31T16:51:14.237

-3

I was able to do it without prepared statement

int i = 0;
    int j = 1;
    sql = "Select SURNAME ,KATHGORIA, AFM , NAME FROM EMPLOYEE limit "+i+ ","+j;

got first row as output.

edited Oct 31 '16 at 16:51

answered Jul 11 '15 at 17:58

Bhavik Patel

1,044
1
15
33

2

Be-aware of SQL Injection – mumair Nov 11 '19 at 12:20

MySQL syntax error using LIMIT command with Prepared Statement in Java

2 Answers2

Linked

Related