Understanding Prepare Statements (PS) and Cursors in DBLE

Prepare Statement (PS) Overview

In DBLE, a prepared statement (abbreviated PS) is a pre‑compiled SQL statement that can be executed multiple times with different parameters, helping prevent SQL injection, reducing parsing overhead, and offering more flexible invocation.

Advantages of PS

Prevents SQL injection vulnerabilities.

Standard PS can be compiled once and executed many times, saving parsing and optimization time.

Provides more flexible calling patterns.

Disadvantages of PS

Requires at least two network round‑trips (prepare and execute), increasing latency compared to immediate SQL.

Consumes memory to cache the compiled statement structure.

Limited support for IN clauses.

Classification

Server‑side PS : Client sends PS protocol packets to the server, which assembles parameters, optimizes, and executes.

Client‑side PS : Client assembles parameters locally and sends a single immediate SQL to the server; this is essentially a pseudo‑prepare and does not gain the compile‑once benefit.

Requirements for Using DBLE‑Side PS

DBLE side : No special configuration needed.

Client side : For JDBC, enable useServerPrepStmts to use server‑side prepare; otherwise client‑side prepare is used.

Verification

PreparedStatement preparedStatement = con.prepareStatement("select t1.id from no_sharding_t1 t1 where t1.id=?");
// Verify DBLE side prepare
assert preparedStatement instanceof ServerPreparedStatement;

Protocol Commands

COM_STMT_PREPARE : Send SQL to create a prepared statement; response includes statement ID and column/argument metadata.

COM_STMT_EXECUTE : Send statement ID, bound values, and optional cursor flag; executes the SQL and returns OK or result set.

COM_STMT_FETCH : Send statement ID and desired row count; fetches a batch of rows (used when cursor is enabled).

COM_STMT_SEND_LONG_DATA : Sends large BLOB data for a placeholder.

COM_STMT_RESET : Resets long‑data values (rarely used).

COM_STMT_CLOSE : Sends statement ID to close the prepared statement and release resources.

Flow Diagram

The client ↔ DBLE communication uses server‑side prepare, while DBLE ↔ MySQL uses client‑side prepare, meaning backend communication is similar to ordinary immediate queries with additional protocol translation.

Principle

Prepare phase stores the statement without full compilation.

Execute phase assembles parameters, sends the statement, receives results, and converts them to the PS protocol.

Cursor Support

Advantages

Client‑friendly; prevents OOM on large queries.

Disadvantages

Slower performance.

Resources may not be released promptly.

Cursor Classification

Server‑side cursor : Server stores the result set and maintains a cursor; client fetches rows as needed.

Client‑side cursor (type 1) : Client controls TCP flow, pausing reads when data is excessive (not recommended).

Client‑side cursor (type 2) : Client caches the entire result set locally and reads pages from the cache (not recommended for large data).

DBLE supports only the first type (server‑side cursor).

Enabling Cursors in DBLE

DBLE side: Versions < 3.21.02 do not support cursors; version = 3.21.02 requires no setting; version > 3.21.02 needs -DenableCursor=false in bootstrap.cnf .

Client side requirements

Use a driver that supports cursors (e.g., MySQL official JDBC driver).

Enable useServerPrepStmts and useCursorFetch for JDBC.

Set fetchSize > 0 on the prepared statement.

Execute the statement.

After these steps, the result set is fetched in batches according to fetchSize .

Cursor Verification

final ResultSet resultSet = preparedStatement.executeQuery();
// Verify server‑side cursor usage
Method method = com.mysql.cj.jdbc.StatementImpl.class.getDeclaredMethod("useServerFetch");
method.setAccessible(true);
Boolean useServerFetch = (Boolean) method.invoke(preparedStatement);
assert useServerFetch == true;

Cursor Flow Diagram

Cursor Principle

Prepare phase sends a special statement to compute column count, a prerequisite for cursor activation.

Execute phase stores the result set in a temporary file.

Fetch phase retrieves the result set in batches.

Related Configuration Parameters

maxHeapTableSize

heapTableBufferChunkSize

Refer to the DBLE documentation for detailed settings.