Pipes
2025.9 Pipes
2025.9 Pipes
  • Introduction
  • Configuration
  • Valves
    • API
      • HTTP Response Format Valve
    • Azure
      • Get User
      • Get User Role
      • Is User in Group
    • BankID
      • BidOperation
      • BidToItems
    • Cef event
    • Codecs
      • Base64 Encode
      • Base64 Decode
      • Base64 Converter
    • DateTime
      • Instant Generator
      • Instant Transformer
      • MS Date to Instant Transformer
      • Instant to MS Date Transformer
    • Debug
      • Dump Exports
      • Dump Request
      • Dump Session
      • Dump State
      • Wait
    • Delivery
      • Clean Mobile Valve
      • SMS Valve
      • Voice Valve
      • SMTP Valve
    • EntraID
      • Users
        • Create User
        • Update User
        • Delete User
        • Get User
        • List Users
        • Get Groups
        • List Groups
        • List Direct Reports
        • List Owned Objects
        • Reset Password
      • Groups
        • Create Group
        • Update Group
        • Delete Group
        • List Groups
        • Add Group To Users
        • Remove Group From Users
        • Add User To Groups
        • Remove User From Groups
        • Add Group Owner
        • Remove Group Owner
      • Directory
        • Restore Deleted Item
    • Exports
      • Exports Put
      • Exports Remove
    • Flow
      • Flow Fail
      • Flow State Add
      • Assert Value
      • Pipe Exec
      • Pipe Call
    • Freja
    • HTTP
      • GET
      • PUT
      • POST
      • DELETE
    • Item
      • Item Create
      • Item Merge
      • Item Match Merge
      • Item Remove
      • Items Remove
      • Item Property Add
      • Item Property Copy
      • Item Property Replace
      • Item Property Split
      • Item Property Rename
      • Item Property Hash
      • Item Property Token Replace
      • MV Property To Items
      • JSON To Items
      • MV Property Join
    • JDBC Query
    • JSON
      • JsonObjectCreate
    • JWT
      • CreateJwt
      • ParseJwt
    • LDAP
      • LDAP Search
      • LDAP Group Filter
      • LDAP Bind
      • LDAP Add
      • LDAP Delete
      • LDAP Modify
      • LDAP Move
      • DN Parse
    • Microsoft AD
      • Add Member To Groups
      • Remove Member From Groups
      • Add Group To Members
      • Remove Group From Members
      • GUID to string
    • Misc
      • Basic Auth
    • OTP
      • OTP Generation
      • OTP Validation
    • PKI
      • X509 Certificate Extractor
      • X509 Certificate Validator
      • Passcode Generator
    • PDF
      • Html2Pdf
      • PDFMerge
    • Request
      • RequestParameterExist
      • RequestParameterRename
    • ScriptEval
    • MobilSITHS
    • Session
      • Session Put
      • Session Create
      • CopyFromSession
    • Tokens
      • Token Authentication
    • WorkOrders
      • WorkOrderCreate
Powered by GitBook
On this page
  • Introduction
  • Prerequisites
  • Configuration
  • Query parameters
  • Examples
  • Troubleshooting
  • Invalid query parameter type configuration (Instant)
  • Invalid Instant format
  1. Valves

JDBC Query

Valve performing JDBC queries

PreviousMV Property JoinNextJSON

Last updated 3 days ago

Introduction

Valve for performing database (RDBMS) queries using JDBC.

Supports any JDBC compatible statements (SELECT, INSERT, UPDATE, DELETE, etc.) and the use of query parameters.

Rows returned by the statement are added/merged to the current flows item set using a configurable identifier. Native JSON types are preserved other types are converted to strings.

If a statement just modifies rows in the DB (i.e. INSERT, UPDATE, DELETE) item set will not be modified.

If a statement does neither return or modify rows, valve will fail ("No data") unless "allow_no_data": true is set.

Prerequisites

Before using this valve the module must be configured and deployed.

Configuration

Valve name: JdbcQuery | JDBCQuery

Name
Description
Default value
Mandatory
Expanded

destination

JdbcClient instance to use.

"default"

query

The JDBC query/statement.

row_id

Name of column containing the row identifier. Value of this column is used as item id when rows are added/merged to item set.

"id"

query_params

Query parameters descriptor (See below)

allow_no_data

Flag for allowing no data queries. By default this valve fails if a query returns/affects no rows. Use this flag to turn off this behaviour.

false

{
  "name" : "JdbcQuery",
  "config" : {
    "destination" : "default",
    "query" : "SELECT * FROM users WHERE username=?",
    "row_id" : "username",
    "query_params" : "username"
  }
}

Query parameters

Query parameters are represented as ? in the query:

SELECT * FROM users WHERE username=? AND type=?

Before the query is executed each parameter is replaced with an actual value based on the query parameter descriptor.

The query parameter description can be either a simple string containing a comma separated list of parameters, an array of strings containing parameters or an array of parameter objects.

Elements of the descriptor must match the order and the number of parameters in the query.

When simple strings are used the parameters are assumed to be pipe input parameter names. Values are extracted from the request and added to the statement as is and in the order the params are defined.

"query_params" : "username"
"query_params" : ["id", "username", "created"]

When parameter objects are used, parameters can be expanded and type converted. By default parameters are not expanded ("expand" : false) and uses the supplied type/no conversion ("type": "NONE").

Type conversion are used for non-native JSON types that are encoded into strings.

Name
Description

value

Parameter value. This could be the name of or path to a pipe request parameter containing the actual value or an expansion expression evaluating to the actual value. (Mandatory)

expand

Flag turning on property expansion on value. (Default: false)

type

(Default: "NONE")

"query_params" : [ 
    {
        "value" : "username",
        "expand" : false,
        "type": "NONE"
    },
    {
        "value" : "created",
        "expand" : false,
        "type": "INSTANT"
    }
]

Note: that expansion requires an expansion expression and that expansion always produces strings.

Examples

Examples below use the following table:

CREATE TABLE users (
    id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    created TIMESTAMP WITH TIME ZONE NOT NULL,
    PRIMARY KEY (id)
)

A simple SELECT with a single parameter

Valve with statement to select a user from the users table.

{
    "name" : "JdbcQuery",
    "config" : {
        "destination" : "default",
        "query" : "SELECT * FROM users WHERE id=?",
        "row_id" : "username",
        "query_params" : [ {
            "value" : "id",
            "expand" : false
        } ]
    }
}

A SELECT using query parameter type conversion

Valve with statement to select all users created in a specific time interval.

Since JSON doesn't support instants, type conversion must be used to convert the instant string value to an actual Instant object (that can be converted to a JDBC TIMESTAMP by the JdbcClient).

{
  "name" : "JdbcQuery",
  "config" : {
    "destination" : "default",
    "query" : "SELECT * FROM users WHERE created BETWEEN ? AND ?",
    "row_id" : "username",
    "query_params" : [ {
      "value" : "not_before",
      "expand" : false,
      "type" : "INSTANT"
    }, {
      "value" : "${now}",
      "expand" : true,
      "type" : "INSTANT"
    } ]
  }
}

An authentication SELECT with username and password

Valve with statement to authenticate a user using username and password.

This is an example of how to verify that a user exists and that the user password is correct in a single statement not returning the stored password (like the LDAP bind operation). If user doesn't exist or an incorrect password is used, statement will return an empty result which should be treated as an error by the pipe. If user exist and password is correct, one row/item is returned (success).

Example also demonstrates the use of expansion for query params.

Expansion is not necessary for request parameters since query params defaults to request parameters when expansion is off (default). Skipping expansion also leads to better performance. Use expansion to access values in session or item.

To detect invalid username/password combinations allow_no_data must be turned on and pipe must validate item set (no row/item if incorrect username or password is supplied).

Never store passwords in clear text.

{
    "name" : "JdbcQuery",
    "config" : {
      "destination" : "default",
      "query" : "SELECT id,username,lastLogin FROM users WHERE username=? AND password=?",
      "query_params" : [ {
        "value" : "username",
        "expand" : false
      }, {
        "value" : "${request.password}",
        "expand" : true
      } ],
      "row_id": "username",
      "allow_no_data" : true
    }
}

An INSERT

Valve with statement to insert a new user into the users table.

Never store passwords in clear text.

{
  "name" : "JdbcQuery",
  "config" : {
    "destination" : "default",
    "query" : "INSERT INTO users (id,username,password,created) VALUES (?,?,?,?)",
    "query_params" : [ {
      "value" : "id",
      "expand" : false
    }, {
      "value" : "username",
      "expand" : false
    }, {
      "value" : "password",
      "expand" : false
    }, {
      "value" : "created",
      "expand" : false,
      "type" : "INSTANT"
    } ]
  }
}

An UPDATE

Valve with statement to update the password of a user in the users table.

Never store passwords in clear text.

{
  "name" : "JdbcQuery",
  "config" : {
    "destination" : "default",
    "query" : "UPDATE users SET password=? WHERE id=?",
    "query_params" : [ {
      "value" : "password",
      "expand" : false
    }, {
      "value" : "id",
      "expand" : false
    } ]
  }
}

Troubleshooting

Invalid query parameter type configuration (Instant)

Since Instants are not natively supported in JSON they are handled as ISO 8601 date time strings. These strings cannot be handled by JDBC but need to be converted back to Instants that can be converted to JDBC TIMESTAMPs.

A missing or invalid query parameter type will cause the following pipe failure:

Pipe failure: JDBC query failed: data exception: invalid datetime format

Invalid Instant format

Type conversion from String to Instant only support ISO 8601 ("2011-12-03T10:15:30Z"). Other formats will cause the following pipe failure::

Pipe failure: Text '2022-08-10 09:05:19.285' could not be parsed at index 10

JdbcClient