Query input parameters

PreviousDynamic Query NextStored Procedure

Last updated 5 months ago

Query input parameters

Refer to values provided to a query at runtime to dynamically customize its behavior. They are placeholders in a query that get substituted with actual data during execution. These parameters help avoid hardcoding values in SQL statements, improve query flexibility, and enhance security by preventing SQL injection when used correctly.

Query parameters should be surrounded by curly brackets.

select * from "User" where "firstName"={firstName} and "secondName"={secondName}

There are two input parameters in the example above:

firstName
secondName

Parameter names can differ from SQL table column names; this is valid:

select * from "User" where "firstName"={fName} and "secondName"={sName}

In this case, fName and sName will be visible in the connector builder view.

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

Data types

The parameter type should be defined. Default system type is: . The system automatically converts the selected type to the corresponding DB data type. For example, can be automatically converted to VARCHAR, TEXT, CHAR, and other types. If you need to cast to a specific DB type, the should be used.

Possible types

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

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

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

Cast functions

Type Casts converts a value from one data type to another specified data type. This can be done in two ways:

Explicitly using the CAST() function in PostgreSQL and Oracle
Use :: operator, this works only in PostgreSQL

Example:

insert into "application" ("applicationAsJson") values ({application}::json)

insert into "application" ("applicationAsJson") values (CAST({application} as json))

Cast operation will transform string to json.

PostgreSQL cast operators

Cast operator

The platform type

PostgreSQL Type

::timestamp

TIMESTAMP

::timestamptz

TIMESTAMPZ

::date

DATE

::time

TIME

::bit

BIT

::bit(n)

BIT VARYING(N)

::cidr

CIDR

::inet

INET

::json

JSON

::jsonb

JSONB

::macaddr

MACADDR

::macaddr8

MACADDR8

::pg_lsn

PG_LSN

::tsquery

TSQUERY

::tsvector

TSVECTOR

::txid_snapshot

TXID_SNAPSHOT

::uuid

UUID

::xml

XML

...and others

Oracle cast operations

Types

B O O L E A N

S M A L L I N T

I N T E G E R

B I G I N T

D E C I M A L

R E A L

D O U B L E

F L O A T

C H A R

V A R C H A R

L O N G V A R C H A R

C H A R F O R B I T D A T A

V A R C H A R F O R B I T D A T A

L O N G V A R C H A R F O R B I T D A T A

C L O B

B L O B

D A T E

T I M E

T I M E S T A M P

X M L

BOOLEAN

SMALLINT

INTEGER

BIGINT

DECIMAL

REAL

DOUBLE

FLOAT

CHAR

VARCHAR

LONG VARCHAR

CHAR FOR BIT DATA

VARCHAR FOR BIT DATA

LONG VARCHAR FOR BIT DATA

CLOB

BLOB

DATE

TIME

TIMESTAMP

XML

PreviousDynamic Query NextStored Procedure

Last updated 5 months ago