From N1QL to JavaScript and Back – Part 5: Prepared statements

In previous blogs, we covered executing N1QL (SQL++) from JavaScript functions, processing documents through iterators, manipulating data. and handling errors.

We now move to executing dynamic statements.

Prepared statements

JavaScript functions can prepare and execute prepared statements, much like any request would be able to, for example:

Before we delve into the details of preparing and executing prepared statements, we should probably mention a few things about N1QL prepared statements:

• • N1QL prepared statements are not private to a request, but are shared among the whole node. It is definitely possible, nay, encouraged, to use statements previously prepared by somebody else.
  • This means that the scope of the prepared statement name is the query_context used at the time it was created. You cannot have two statements with the same name under the same query_context.
  • If letting N1QL assign names at prepare time, prepared statements are really no different than in any relational engine.
  • If assigning names yourself, bear in mind that you have to keep them unique within the query_context set by your request. It is a good idea to use a unique naming scheme, such as an application prefix.
  • Also, if you are preparing a statement that already exists, the statement text has to match the text of the existing statement. This is to prevent changing the meaning of a prepared statement behind somebody else’s back.

Back to prepared statements in JavaScript functions!

You can:

• • prepare and execute a statement
  • prepare a statement for some other function/request to use
  • execute an existing statement
  • even execute a statement passed as a function parameter (although it’s possibly risky to do so, e.g., code injection)

Prepare and execute

We have already seen preparing and executing a named statement in the previous example, we’ll now explore how to prepare and execute an anonymous prepared statement.

In the main, you have to process the results of the PREPARE statement, extract the name, and construct an EXECUTE statement, like so:

The example above also demonstrates how to use iterators. If you find that ugly, you could loop over the prepare results to get the name:

The example above works because PREPARE only returns one document, the prepared statement plan, and the scope of the variable prep declared in the for loop is the actual function, so it is visible past the end of the loop itself.

To be fair, neither example looks particularly elegant, but still.

The thing to note is that EXECUTE does not accept parameters, so you have to construct a statement string, which means using the N1QL() function.

While this is not an issue in this particular example, it’s best to include the statement name in quotes, on one hand to be able to handle statement names specified in distributed format, but more importantly to avoid any risk of N1QL injection: say that the statement name was passed as a parameter to the function:

A nefarious user could very well execute:

If you had not embedded the name in quotes, he would be executing a potentially dangerous function, not a prepared statement!

This avoids the risk:

Passing placeholder values to EXECUTE statements

More likely than not, you will have to pass placeholder values to the prepared statement as you are executing it.

For positional placeholders, this is really not different from non prepared statements: you have to use the N1QL() function, for example

When it comes to named parameters, the points to consider are that:

• • The transpiler doesn’t have visibility of the prepared statement text, what it is parsing and rewriting is just the EXECUTE statement. So, it doesn’t really know what variable names it should be using to construct the N1QL() call.
  • The EXECUTE … USING statement only allows static values in the USING clause, the reason for this being to avoid ambiguity between parameters defined in the request body and parameters defined in the USING clause.

The net result is that unless you want to use static values (strings, numbers, etc.), where EXECUTE … USING would be a viable option (for named parameters too), currently the only viable option is the N1QL() function:

Later releases will see the transpiler be extended to handle the EXECUTE statement.

Prepared statements and loops

Consider the following function:

What this function does is execute an INSERT statement for each document retrieved.

This requires the JavaScript worker to ask the N1QL service to parse and plan the INSERT statement for each document retrieved.

A better way is to use prepared statements:

Conclusion

We have now covered all possible ways to gather and manipulate data by executing N1QL statements within JavaScript functions.

Next, we’ll cover some advanced topics like nested function calls, security and transactions.