Limit Keyword on MySQL With Prepared Statement

LIMIT keyword on MySQL with prepared statement

Here's the problem:

$comments = $db->prepare($query); 
/* where $db is the PDO object */ 
$comments->execute(array($post, $min, $max));

The manual page for PDOStatement::execute() says (emphasis mine):

Parameters

input_parameters An array of values with as many elements as there are
bound parameters in the SQL statement being executed. All values are
treated as PDO::PARAM_STR.

Thus your parameters are getting inserted as strings, so the final SQL code looks like this:

LIMIT '0', '10'

This is a particular case where MySQL will not cast to number but trigger a parse error:

mysql> SELECT 1 LIMIT 0, 10;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> SELECT 1 LIMIT '0', '10';
ERROR 1064 (42000): 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 ''0', '10'' at line 1

What docs have to say:

The LIMIT clause can be used to constrain the number of rows
returned by the SELECT statement. LIMIT takes one or two numeric
arguments, which must both be nonnegative integer constants, with
these exceptions:

• Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

• Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

Your choices include:

• Bind parameters one by one so you can set a type:

  $comments->bindParam(1, $post, PDO::PARAM_STR);
  $comments->bindParam(2, $min, PDO::PARAM_INT);
  $comments->bindParam(3, $min, PDO::PARAM_INT);
  

• Do not pass those values as parameters:

  $query = sprintf('SELECT id, content, date
      FROM comment
      WHERE post = ?
      ORDER BY date DESC
      LIMIT %d, %d', $min, $max);
  

• Disable emulated prepares (the MySQL driver has a bug/feature that will make it quote numeric arguments):

  $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
  

MySQL syntax error using LIMIT command with Prepared Statement in Java

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

JDBC mysql does not support placeholder of LIMIT in PreparedStatement?

Your problem is not about the syntax or MySQL support for LIMIT since it's supported. The problem is about the way you are executing the PreparedStatement.

When using PreparedStatement you may not use the executeQuery(String sql), because you've prepared the SQL string formerly for the execution, and no need to pass it again in the executeQuery() method. So do this

ResultSet rs = pStmt.executeQuery();

instead of

ResultSet rs = pStmt.executeQuery(selectLimitSql);

With passing again the selectLimitSql (like above line), you are ignoring the following lines:

pStmt.setInt(1, pageNo * PAGESIZE);
pStmt.setInt(2, PAGESIZE);

and it is like executing your primitive pure sql which contains '?, ?' place holders and you get that exception.

Limit max prepared statement count

You need to close the statement inside upsertProjectSkuCosts() (or re-use it - see the end of this post).

When you call db.Prepare(), a connection is taken from the internal connection pool (or a new connection is created, if there aren't any free connections). The statement is then prepared on that connection (if that connection isn't free when stmt.Exec() is called, the statement is then also prepared on another connection).
So this creates a statement inside your database for that connection. This statement will not magically disappear - having multiple prepared statements in a connection is perfectly valid. Golang could see that stmt goes out of scope, see it requires some sort of cleanup and then do that cleanup, but Golang doesn't (just like it doesn't close files for you and things like that). So you'll need to do that yourself using stmt.Close(). When you call stmt.Close(), the driver will send a command to the database server, telling it the statement is no longer needed.

The easiest way to do this is by adding defer stmt.Close() after the err check following db.Prepare().

What you can also do, is prepare the statement once and make that available for upsertProjectSkuCosts (either by passing the stmt into upsertProjectSkuCosts or by making upsertProjectSkuCosts a func of a struct, so the struct can have a property for the stmt). If you do this, you should not call stmt.Close() - because you aren't creating new statements anymore, you are re-using an existing statement.

Also see Should we also close DB's .Prepare() in Golang? and https://groups.google.com/forum/#!topic/golang-nuts/ISh22XXze-s

Prepared SQL query, UNION and LIMIT - syntax

strangely enough, this seems to work... i execute the query directly, with no preparation.

$reponse = $bdd->query("SELECT id_dossier, type_sortie, date_incident, no_train, commentaire FROM sortie_prevue
        UNION 
        SELECT id_dossier, type_sortie, date_incident, no_train, commentaire FROM sortie_non_prevue
        ORDER BY date_incident DESC LIMIT $resultatsParPage");

if someone has a comment to explain this, he would be very welcome !

Error while using PDO prepared statements and LIMIT in query

Regarding to post LIMIT keyword on MySQL with prepared statement , the code below could solve my problem.

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

Thanks Álvaro G. Vicario and Maerlyn

using limit with like keyword in mysql query gives 0 rows

Meaning of

limit 1, 5 

is,
select 2nd row onwards, a total of 5 records, if found.

But in your case, for the search criteria, as there exists only one record, and
you want from 2nd to 6th record, a total of 5 records, the result was empty.

Change:

limit 1,5;

To:

limit 0, 5; -- to fetch first 5 records from the found

Or

limit 5; -- to fetch first 5 records from the found

To find total number of found records, You need to execute

select found_rows()

when used with SQL_CALC_FOUND_ROWS.

Sequence of operations should be:

SELECT SQL_CALC_FOUND_ROWS * FROM books where keyword like '%java%' limit 5;
SELECT FOUND_ROWS();

The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.

Refer to: MySQL: Found_Rows()

limiting prepared statement to execute one query at a time

I think i was using the prepared statement in a wrong way. here is an example of select used in prepared statement.

String selectSQL = "SELECT USER_ID, USERNAME FROM DBUSER WHERE USER_ID = ?";
PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL);
preparedStatement.setInt(1, 1001);
ResultSet rs = preparedStatement.executeQuery(selectSQL )

PHP PDO Prepared Statement parameter causing error

Your $limit parameter is being escaped as one parameter, where it should be escaped as two. Your sql will currently look something like "limit '0, 8';" where it should look like "limit 0, 8";

To solve this, you should split your limit parameter into two. Edit the end of your SQL to look like:

LIMIT :offset, :limit

And your parameter list to look like:

$query_params = array (
    ':offset' => ($pagenum - 1) * $page_rows,
    ':limit' => $page_rows
);

As Mr Smith mentioned, you'll also have to add the line:

$db->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

To ensure the limit parameters are correctly handled as integers rather than strings.

How to apply bindValue method in LIMIT clause?

I remember having this problem before. Cast the value to an integer before passing it to the bind function. I think this solves it.

$fetchPictures->bindValue(':skip', (int) trim($_GET['skip']), PDO::PARAM_INT);

---

Related Topics