ProcessMIX Guide
  • Platform Overview
    • Introduction
    • Platform Purpose
    • Main Concepts
      • Organization Structure
      • Visual Language
      • Project Development Environment (IDE)
      • Deployment and Integration
      • Administration and Troubleshooting
    • First Steps
    • Change Log
      • 5.0.0
      • 5.0.5
      • 5.0.5.1
      • 5.0.5.2
      • 5.0.5.3
      • 5.0.5.4
  • Language Reference Guide
    • Overview
    • Data Structures and Types
      • Core Types (pdk.core)
      • Dictionary (pdk.asset.dictionary)
      • Scorecard (pdk.asset.scorecard)
      • Blockchain (pdk.blockchain)
      • Exceptions (pdk.ex)
      • Input/Output (pdk.io)
      • JSON Web Token (pdk.jwt)
      • Networking (pdk.net)
      • HTTP (pdk.net.http)
      • SOAP/Web Services (pdk.net.soap)
      • XML/DOM (pdk.net.xml)
      • S3 (pdk.s3)
      • SQL (pdk.sql)
      • Postgres (pdk.db.postgre)
      • Util (pdk.util)
      • Default Data Structure Field Values
    • Flows
      • Variables
        • Local Flow Variables
        • Node Variables
      • Flow Node Types
        • Core Nodes
          • In
          • Out
          • Assign
          • If
          • Switch
          • Iterator
          • Function Call
          • Subflow
          • Raise Exception
          • Exception Handler
          • Validator
        • Connector Nodes
          • Database Begin Transaction
          • Database Commit Transaction
          • Database Rollback Transaction
          • Database Query Executor
          • EVM Blockchain Functions
          • EVM Blockchain Smart Contract Functions
          • REST Service Executor
          • SOAP Service Executor
          • S3 Connector
          • SMB Connector
      • Expressions
      • Built-In Functions
        • Expression functions
          • pdk.util.Any
          • pdk.util.Array
          • pdk.util.Blockchain
          • pdk.util.Cast
          • pdk.util.Codec
          • pdk.util.Crypto
          • pdk.util.Date
          • pdk.util.File
          • pdk.util.Json
          • pdk.util.JWT
          • pdk.util.Map
          • pdk.util.Math
          • pdk.util.String
          • pdk.util.Util
        • Node functions
          • pdk.flow.Any
          • pdk.flow.Array
          • pdk.flow.File
          • pdk.flow.JsonArray
          • pdk.flow.JsonObject
          • pdk.flow.HttpRequest
          • pdk.flow.HttpResponse
          • pdk.flow.Logger
          • pdk.flow.Map
          • pdk.flow.Xml
            • pdk.flow.xml.XmlAttr
            • pdk.flow.xml.XmlElement
            • pdk.flow.xml.XmlNode
            • pdk.flow.xml.XmlDocument
            • pdk.flow.xml.XmlDocumentType
            • pdk.flow.xml.XmlDOMConfiguration
            • pdk.flow.xml.XmlDOMImplementation
            • pdk.flow.xml.XmlProcessingInstruction
            • pdk.flow.xml.XmlTypeInfo
            • pdk.flow.xml.XmlCharacterData
            • pdk.flow.xml.XmlText
            • pdk.flow.xml.XmlNamedNodeMap
          • pdk.node.BlockchainEth
          • pdk.node.S3
          • pdk.node.Smb
      • Exceptions
        • Checked Exceptions
        • Runtime Exceptions
    • Assets
      • Dictionary
      • Decision Table
      • Scorecard
      • PMML
    • Connectors
      • DB Connector
        • Prepared Query
        • Dynamic Query
        • Query input parameters
        • Stored Procedure
      • REST Connector
      • SOAP Connector
      • S3 Connector
      • SMB Connector
      • EVM Blockchain Connector
    • Global Variables
    • Appendices
      • Overview of Database Transactions
      • Reserved Words
  • Project Development Environment (IDE)
    • Project Explorer Panel
    • Flow Builder
    • Flow Node Editor
    • Data Structure Builder
    • Connector Builders
      • REST Connector Builder
      • SOAP Connector Builder
      • DB (Database) Connector Builder
      • EVM Blockchain Connectors Builder
      • S3 Connector Builder
    • Asset Builders
      • Dictionary Builder
      • Decision Table Builder
      • Scorecard Builder
      • PMML Asset Builder
    • Global Variables Panel
    • Expression Editor
    • Project Deployment and Execution
    • Cron Expression Generator
    • Test Helper
    • Debugging the Project
    • DB transactions
    • Team Collaboration Tools
      • Version Control
      • Conflict Resolver
    • Selectors
    • Error Panel
    • Local History
  • Home and Administration Guide
    • Organization and Subscription
    • Organization Team
    • Repositories and Projects
    • Environments
    • Deployments
    • Database Provisioning
    • Roles and Permissions
  • Logs and Troubleshooting
    • Request/Call Logs
    • Deployment Logs
    • Application Logs
  • Appendix: Example Project
    • Risk Mitigation Solution
Powered by GitBook
On this page
  • Data types
  • Possible types
  • Cast functions
  1. Language Reference Guide
  2. Connectors
  3. DB Connector

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: pdk.core.String. The system automatically converts the selected type to the corresponding DB data type. For example, 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 should be used.

Possible types

  • 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 ({ids})

ids 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)

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

  2. Use :: operator, this works only in PostgreSQL

Example:

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

or

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

{application} is an input parameter with the selected type pdk.core.String.

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

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

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

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

PreviousDynamic QueryNextStored Procedure

Last updated 3 months ago

pdk.core.String
pdk.core.String
pdk.core.String
pdk.core.String
pdk.core.String
pdk.core.String
pdk.core.String
pdk.core.String
pdk.core.String
pdk.core.String
pdk.core.String
pdk.core.String
pdk.core.String
pdk.core.String
pdk.core.String
pdk.core.String
pdk.core.String
pdk.core.String