19.0.0 released Nov 08, 2023
Prepared statements

Prepared queries
A prepared query differs only in that it is natively prepared for the database you are using. It is pre-compiled and (among other things) its execution plan is created once, instead of each time a query executes. The statement is cached going forward for the life of the process (with the rare exception of re-establishing a lost database connection). It means effectively an unlimited number of requests will be reusing the query statement.

In practicality it means the execution of a query may be faster due to being prepared only once and executed many times.

Note that databases do not allow prepared queries for DDL (Data Definition Language), as there is not much benefit in general, hence only DML queries (such as INSERT, DELETE etc.) and SELECT can be prepared.
Caching of queries
In order to cache a query statement, Vely will save query text that actually executes the very first time it runs. Then, regardless of what query text you supply in the following executions, it will not mutate anymore. It means from that moment onward, the query will always execute that very same query text, just with different input parameters.

In general, majority of queries are static and not dynamic, so this works just fine. When you use prepared statements with dynamic queries though, you should be aware that query text is immutable. For instance, code in a request that is called many times may look like:
char *qry;
static char is_query_built = 0;

if (!is_query_built) {
    qry = create_query();
    is_query_built = 1;

run-prepared-query @mydb = qry output col1, col2, col3 : inp1, inp2

In the above example, query text "qry" is built only once (by using static boolean "is_query_built"), because the actual query text is needed only for the very first execution of query "myquery", even when connection is lost and re-established. This improves performance beyond what prepared statement may already do. You can, of course, build it every time (i.e. not use the boolean or a similar mechanism), but the query text that actually executes will not change after the very first run. The input parameter variables (such as "inp1" and "inp2" in this example) may change, of course.

In a majority of code, a query may be static as in:
run-prepared-query @mydb = "select col1 from test where someID='%s'" output col1 : id_value

then clearly the query text is a string literal and will never change. All that changes is the input parameters (in this case "id_value"), and of course the output (in this case "col1"). Most queries are like this.
SQL injections
Note that regardless of whether you use prepared statements or not, the execution of your queries is guarded against SQL injections:
In most cases, prepared statement will exhibit better performance, and this is particularly true in Vely, where Vely FCGI processes (see vely-architecture and how-vely-works) keep a single database connection (and thus a single session) open for the life of the process (re-creating it only when the connection is lost). Because of this, a prepared statement is done so once; and then reused and re-run many times over afterwards.

In some cases, you might not want to use prepared statements. Some reasons may be:
You may get an error like:
could not determine data type of parameter $N

when preparing statements for PostgreSQL. This is an issue with Postgres server, and has nothing to do with Vely, for example in statement:
select col1 from test where someId>='%s' and col1 like concat( '%s' ,'%')

you might get an error "could not determine data type of parameter $2". An issue like this may be that Postgres cannot determine the type, or it may be a bug in Postgres; regardless, this is not a Vely issue. In this case $2 is the second '%s' input parameters and you should specify the type manually for Postgres, generally in form of
...  '%s'::<type> ...

In this case, the type in question is "text", so your statement would be:
select col1 from test where someId>='%s' and col1 like concat( '%s'::text ,'%')

This solution generally works for any Postgres client, not just Vely, regardless of how is the positional input parameter specified.
See also
See all

You are free to copy, redistribute and adapt this web page (even commercially), as long as you give credit and provide a dofollow link back to this page - see full license at CC-BY-4.0. Copyright (c) 2019-2023 Dasoftver LLC. Vely and elephant logo are trademarks of Dasoftver LLC. The software and information on this web site are provided "AS IS" and without any warranties or guarantees of any kind. Icons from table-icons.io copyright PaweĊ‚ Kuna, licensed under MIT license.