Using binding variables

To declare a binding variable, you need to create an instance of PreparedStatement. An instance of PreparedStatement can be created through the KVStore.prepare() method.

You can specify zero or more variable declarations. The syntax for a variable is:

DECLARE $varname vartype; 

If the DML statement contains external variables, the PreparedStatement can be executed multiple times by creating an instance of BoundStatement. The external variables must be bound to specific values before the statement can be executed. To allow for the potentially concurrent execution of the same PreparedStatement multiple times with different bind values each time, binding of external variables must be done through one or more instances of BoundStatement. Such instances are created using the createBoundStatement() method.

This instance can then be executed multiple times using the KVStore.execute() or KVStore.executeSync() methods.

For example:

// store handle creation omitted.

...

// Compile the statement.
PreparedStatement pStmt = store.prepare(
    "DECLARE $minAge integer; $maxAge integer;  " +
    "SELECT id, firstName FROM Users WHERE
    age >= $minAge and age < $maxAge "
);

// Iterate decades
for( int age = 0; age <= 100; age = age + 10 ) {
    int maxAge = age + ( age < 100 ? 10 : 1000 );
    System.out.println("Persons with ages between " + age +
        " and " + maxAge + ".");
    
    // Bind variables, reuse the same pStmt
    BoundStatement bStmt = pStmt.createBoundStatement();
    bStmt.setVariable("$minAge", age);
    bStmt.setVariable("$maxAge", maxAge);

    // Execute the statement
    StatementResult result = store.executeSync(bStmt);

    // Get the results in the current decade
    for( RecordValue record : result ) {
        System.out.println("id: " +
            record.get("id").asInteger().get() );
        System.out.println("firstName: " +
            record.get("firstName").asString().get());
    }
}