Vely logo Empower C
install  tutorials  examples
documentation  license  about

12.1.0 released on Sep 19, 2022

Prepared statements



DESCRIPTION:



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
end-query

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
end-query

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:


Performance


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:


PostgreSQL


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:


Database ( begin-transaction   commit-transaction   current-row   database_config_file   database_queries   on-error   prepared_statements   query-result   rollback-transaction   run-query  )  SEE ALL (documentation)



Copyright (c) 2022 DaSoftver LLC. Vely is a trademark of Dasoftver LLC. The software and information herein are provided "AS IS" and without any warranties or guarantees of any kind. Icons copyright PaweĊ‚ Kuna licensed under MIT. This web page is licensed under CC-BY-SA-4.0.