# 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.

<pre class="language-sql"><code class="lang-sql"><strong>select * from "User" where "firstName"={firstName} and "secondName"={secondName} 
</strong></code></pre>

There are two input parameters in the example above:

* `firstName`
* `secondName`

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

<pre class="language-sql"><code class="lang-sql"><strong>select * from "User" where "firstName"={fName} and "secondName"={sName} 
</strong></code></pre>

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: [pdk.core.String](../../../structures/pdk.core#string). The system automatically converts the selected type to the corresponding DB data type. For example, [pdk.core.String](../../../structures/pdk.core#string) can be automatically converted to `VARCHAR`, `TEXT`, `CHAR`, and other types. If you need to cast to a specific DB type, the [cast function](#undefined) should be used.

### Possible types

* [pdk.core.String](../../../structures/pdk.core#string) and [pdk.core.Array\<String>](../../../structures/pdk.core#array)
* [pdk.core.Integer](../../../structures/pdk.core#integer) and [pdk.core.Array\<Integer>](../../../structures/pdk.core#array)
* [pdk.core.Boolean](../../../structures/pdk.core#boolean) and [pdk.core.Array\<Boolean>](../../../structures/pdk.core#array)
* [pdk.core.Float](../../../structures/pdk.core#float) and [pdk.core.Array\<Float>](../../../structures/pdk.core#array)
* [pdk.core.Array\<Byte>](../../../structures/pdk.core#array) - for BLOB data type

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

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

`ids` parameter type is [pdk.core.Array\<Integer>](../../../structures/pdk.core#array) and contains values `[1,2,3]`. SQL will be transformed into:

```sql
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:

1. Explicitly using the `CAST()` function in PostgreSQL and Oracle&#x20;
2. Use `::` operator, this works only in PostgreSQL

Example:

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

or

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

`{application}` is an input parameter with the selected type [pdk.core.String](../../../structures/pdk.core#string).

Cast operation will transform string to json.

#### PostgreSQL cast operators

| Cast operator    | The platform type                                      | PostgreSQL Type |
| ---------------- | ------------------------------------------------------ | --------------- |
| ::timestamp      | [pdk.core.String](../../../structures/pdk.core#string) | TIMESTAMP       |
| ::timestamptz    | [pdk.core.String](../../../structures/pdk.core#string) | TIMESTAMPZ      |
| ::date           | [pdk.core.String](../../../structures/pdk.core#string) | DATE            |
| ::time           | [pdk.core.String](../../../structures/pdk.core#string) | TIME            |
| ::bit            | [pdk.core.String](../../../structures/pdk.core#string) | BIT             |
| ::bit(n)         | [pdk.core.String](../../../structures/pdk.core#string) | BIT VARYING(N)  |
| ::cidr           | [pdk.core.String](../../../structures/pdk.core#string) | CIDR            |
| ::inet           | [pdk.core.String](../../../structures/pdk.core#string) | INET            |
| ::json           | [pdk.core.String](../../../structures/pdk.core#string) | JSON            |
| ::jsonb          | [pdk.core.String](../../../structures/pdk.core#string) | JSONB           |
| ::macaddr        | [pdk.core.String](../../../structures/pdk.core#string) | MACADDR         |
| ::macaddr8       | [pdk.core.String](../../../structures/pdk.core#string) | MACADDR8        |
| ::pg\_lsn        | [pdk.core.String](../../../structures/pdk.core#string) | PG\_LSN         |
| ::tsquery        | [pdk.core.String](../../../structures/pdk.core#string) | TSQUERY         |
| ::tsvector       | [pdk.core.String](../../../structures/pdk.core#string) | TSVECTOR        |
| ::txid\_snapshot | [pdk.core.String](../../../structures/pdk.core#string) | TXID\_SNAPSHOT  |
| ::uuid           | [pdk.core.String](../../../structures/pdk.core#string) | UUID            |
| ::xml            | [pdk.core.String](../../../structures/pdk.core#string) | XML             |
| ...and others    |                                                        |                 |

#### Oracle cast operations

You can find more information on the official website: [https://docs.oracle.com/javadb/10.8.1.2/ref/rrefsqlj33562.html](https://docs.oracle.com/javadb/10.8.1.2/ref/rrefsqlj33562.html)

| Types                     | <p>B<br>O<br>O<br>L<br>E<br>A<br>N</p> | <p>S<br>M<br>A<br>L<br>L<br>I<br>N<br>T</p> | <p>I<br>N<br>T<br>E<br>G<br>E<br>R</p> | <p>B<br>I<br>G<br>I<br>N<br>T</p> | <p>D<br>E<br>C<br>I<br>M<br>A<br>L</p> | <p>R<br>E<br>A<br>L</p> | <p>D<br>O<br>U<br>B<br>L<br>E</p> | <p>F<br>L<br>O<br>A<br>T</p> | <p>C<br>H<br>A<br>R</p> | <p>V<br>A<br>R<br>C<br>H<br>A<br>R</p> | <p>L<br>O<br>N<br>G<br><br>V<br>A<br>R<br>C<br>H<br>A<br>R</p> | <p>C<br>H<br>A<br>R<br><br>F<br>O<br>R<br><br>B<br>I<br>T<br><br>D<br>A<br>T<br>A</p> | <p>V<br>A<br>R<br>C<br>H<br>A<br>R<br><br>F<br>O<br>R<br><br>B<br>I<br>T<br><br>D<br>A<br>T<br>A</p> | <p>L<br>O<br>N<br>G<br><br>V<br>A<br>R<br>C<br>H<br>A<br>R<br><br>F<br>O<br>R<br><br>B<br>I<br>T<br><br>D<br>A<br>T<br>A</p> | <p>C<br>L<br>O<br>B</p> | <p>B<br>L<br>O<br>B</p> | <p>D<br>A<br>T<br>E</p> | <p>T<br>I<br>M<br>E</p> | <p>T<br>I<br>M<br>E<br>S<br>T<br>A<br>M<br>P</p> | <p>X<br>M<br>L</p> |
| ------------------------- | -------------------------------------- | ------------------------------------------- | -------------------------------------- | --------------------------------- | -------------------------------------- | ----------------------- | --------------------------------- | ---------------------------- | ----------------------- | -------------------------------------- | -------------------------------------------------------------- | ------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------- | ----------------------- | ----------------------- | ----------------------- | ----------------------- | ------------------------------------------------ | ------------------ |
| BOOLEAN                   | Y                                      | -                                           | -                                      | -                                 | -                                      | -                       | -                                 | -                            | Y                       | Y                                      | Y                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | Y                       | -                       | -                       | -                       | -                                                | -                  |
| SMALLINT                  | -                                      | Y                                           | Y                                      | Y                                 | Y                                      | Y                       | Y                                 | Y                            | Y                       | -                                      | -                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | -                       | -                       | -                       | -                       | -                                                | -                  |
| INTEGER                   | -                                      | Y                                           | Y                                      | Y                                 | Y                                      | Y                       | Y                                 | Y                            | Y                       | -                                      | -                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | -                       | -                       | -                       | -                       | -                                                | -                  |
| BIGINT                    | -                                      | Y                                           | Y                                      | Y                                 | Y                                      | Y                       | Y                                 | Y                            | Y                       | -                                      | -                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | -                       | -                       | -                       | -                       | -                                                | -                  |
| DECIMAL                   | -                                      | Y                                           | Y                                      | Y                                 | Y                                      | Y                       | Y                                 | Y                            | Y                       | -                                      | -                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | -                       | -                       | -                       | -                       | -                                                | -                  |
| REAL                      | -                                      | Y                                           | Y                                      | Y                                 | Y                                      | Y                       | Y                                 | Y                            | -                       | -                                      | -                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | -                       | -                       | -                       | -                       | -                                                | -                  |
| DOUBLE                    | -                                      | Y                                           | Y                                      | Y                                 | Y                                      | Y                       | Y                                 | Y                            | -                       | -                                      | -                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | -                       | -                       | -                       | -                       | -                                                | -                  |
| FLOAT                     | -                                      | Y                                           | Y                                      | Y                                 | Y                                      | Y                       | Y                                 | Y                            | -                       | -                                      | -                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | -                       | -                       | -                       | -                       | -                                                | -                  |
| CHAR                      | Y                                      | Y                                           | Y                                      | Y                                 | Y                                      | -                       | -                                 | -                            | Y                       | Y                                      | Y                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | Y                       | -                       | Y                       | Y                       | Y                                                | -                  |
| VARCHAR                   | Y                                      | Y                                           | Y                                      | Y                                 | Y                                      | -                       | -                                 | -                            | Y                       | Y                                      | Y                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | Y                       | -                       | Y                       | Y                       | Y                                                | -                  |
| LONG VARCHAR              | Y                                      | -                                           | -                                      | -                                 | -                                      | -                       | -                                 | -                            | Y                       | Y                                      | Y                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | Y                       | -                       | -                       | -                       | -                                                | -                  |
| CHAR FOR BIT DATA         | -                                      | -                                           | -                                      | -                                 | -                                      | -                       | -                                 | -                            | -                       | -                                      | -                                                              | Y                                                                                     | Y                                                                                                    | Y                                                                                                                            | Y                       | Y                       | -                       | -                       | -                                                | -                  |
| VARCHAR FOR BIT DATA      | -                                      | -                                           | -                                      | -                                 | -                                      | -                       | -                                 | -                            | -                       | -                                      | -                                                              | Y                                                                                     | Y                                                                                                    | Y                                                                                                                            | Y                       | Y                       | -                       | -                       | -                                                | -                  |
| LONG VARCHAR FOR BIT DATA | -                                      | -                                           | -                                      | -                                 | -                                      | -                       | -                                 | -                            | -                       | -                                      | -                                                              | Y                                                                                     | Y                                                                                                    | Y                                                                                                                            | Y                       | Y                       | -                       | -                       | -                                                | -                  |
| CLOB                      | Y                                      | -                                           | -                                      | -                                 | -                                      | -                       | -                                 | -                            | Y                       | Y                                      | Y                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | Y                       | -                       | -                       | -                       | -                                                | -                  |
| BLOB                      | -                                      | -                                           | -                                      | -                                 | -                                      | -                       | -                                 | -                            | -                       | -                                      | -                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | -                       | Y                       | -                       | -                       | -                                                | -                  |
| DATE                      | -                                      | -                                           | -                                      | -                                 | -                                      | -                       | -                                 | -                            | Y                       | Y                                      | -                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | -                       | -                       | Y                       | -                       | -                                                | -                  |
| TIME                      | -                                      | -                                           | -                                      | -                                 | -                                      | -                       | -                                 | -                            | Y                       | Y                                      | -                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | -                       | -                       | -                       | Y                       | -                                                | -                  |
| TIMESTAMP                 | -                                      | -                                           | -                                      | -                                 | -                                      | -                       | -                                 | -                            | Y                       | Y                                      | -                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | -                       | -                       | Y                       | Y                       | Y                                                | -                  |
| XML                       | -                                      | -                                           | -                                      | -                                 | -                                      | -                       | -                                 | -                            | -                       | -                                      | -                                                              | -                                                                                     | -                                                                                                    | -                                                                                                                            | -                       | -                       | -                       | -                       | -                                                | Y                  |

\
