Prepared query

parameterized query with predefined SQL

A prepared query, also known as a prepared statement or parameterized query, is a SQL statement that is precompiled and stored in a database in a form that allows efficient execution multiple times with different parameters.

When a prepared query is used, the database server parses, compiles, and optimizes the SQL statement once and creates a prepared execution plan. This prepared execution plan is stored in memory or in the database server's cache, ready to be executed multiple times with different parameter values.

---

SQL

SQL should be a valid statement. It is possible to add parameters that can be used for passing values from variables. Use curly brackets { } to define input parameters.

Example:

select * from "mySchema"."users" where "id" = {id}

id - an input parameter that can be configured within the properties of the db connector node.

!Important. Ensure to enclose table, column, and schema names in double quotes; otherwise, any uppercase letters will be automatically converted to lowercase. SQL (assume id=1):

select * from mySchema.users where id = {id} 

will trigger an exception because it will be transformed into

select * from myschema.users where id = 1 

and myschema does not exist.

The schema name can be defined within the database URL. In this scenario, it can be omitted.

select * from "users" where "id" = {id}

Will lead to the same result as:

select * from "mySchema"."users" where "id" = {id}

---

Input parameters

The parameter name is indicated by the text enclosed within curly brackets { }. The name should consist only of letters, digits, and underscore characters, , and must start with a letter.

{myParam1_} - valid
{1myParam} - not valid
{_myParam} - not valid
{myParam@$#^%&*!-+=} - not valid 

The parameter type should be defined. Default type is: pdk.core.String

Possible types are:

• pdk.core.String and pdk.core.Array<String>

• pdk.core.Integer and pdk.core.Array<Integer>

• pdk.core.Boolean and pdk.core.Array<Boolean>

• pdk.core.Float and pdk.core.Array<Float>

• pdk.core.Array<Byte> - for BLOB data type

Arrays are processed as lists of values with a comma as the delimiter.

select * from "users" where "id" in ({id})

id parameter type is pdk.core.Array<Integer> and contains values [1,2,3]. SQL will be transformed into:

select * from "users" where "id" in (1,2,3)