CPP Driver Part 2: Prepared Statements

In the previous lesson, we introduced the C++ driver and saw how to install it and how to use it to connect to a ScyllaDB cluster and perform some basic operations. 

To improve performance, it’s almost always recommended to use Prepared Statements. 

In this lesson, we will see how to do this with the C++ driver. To run the code in this lesson you should have the driver installed, a three-node cluster running with the ks.mutant_data table, and some data in it. This is covered in the lesson CPP Driver – Part 1.  Prepared Statements are routinely employed to improve the speed of query execution. In short, they are a kind of parameterized CQL query, which are “compiled” and cached by ScyllaDB. When reused, they save ScyllaDB from parsing CQL on every request. 

The C/C++ driver supports them with the following syntax. You can find the code in  /scylla-code-samples/cpp/part2/prepared_statement.cpp.

Edit the file prepared_statement.cpp and change the IP according to the setup of your cluster. Now compile and run the code:

g++ prepared_statements.cpp -lscylla-cpp-driver -o prepared_statement

./prepared_statement 

The execution may produce some output on your console due to the negotiation of the protocol version. Unless the connection failed, it’s nothing to worry about.

If you want to see that the row was actually inserted you can query the cluster to see that it’s there, for example by using the “iterator” from the lesson CPP Driver – Part 1. 

Because it’s easy to make a mistake in the position of a particular argument in a row, the C/C++ driver supports a feature called “binding by column name”. This is an example of how it would be used:

If you want to test this you can replace the line:

In the file prepared_statements.cpp with the above line and rerun it. It’s that simple to bind by column name. 

Similarly, parameters can be also given explicit names, indicated by a colon:

If you read the above example carefully you might have observed another benefit of prepared statements: no need to put string literals in single quotes (no need to escape literals). That’s because the driver sends the parameterized query separately from the parameters themselves. We can do a similar trick with simple statements, that is, parameterize them – either by question marks or by names. You can find the code in  /scylla-code-samples/cpp/part2/param_simple.cpp.

Edit the file param_simple.cpp and change the IP according to the setup of your cluster. Now compile and run the code:

g++ param_simple.cpp -lscylla-cpp-driver -o param_simple 

./param_simple

Careful though not to confuse parameterized simple statements with prepared statements. Prepared Statements are “compiled” on a node once and from then onwards are referenced from the client app by a unique ID. On the other hand, simple statements are sent in text form every time they are executed. The only thing they have in common is that they both send their parameters separately and thus don’t need escaping.

Summary 

In this lesson, we saw an example of how to use prepared statements with the CPP driver. Using prepared statements improves performance as the preprocessing of the statement is done only once. Another advantage is that prepared statements also provide better security—they protect against SQL (CQL) injection. We also saw simple statements and an example of how to use them.