Sql type

KUMA supports multiple types of databases.

When creating a connector, you must specify general connector settings and specific database connection settings.

On the Basic settings tab, you must specify the following values for the connector:

Name (required)—unique name of the resource. Must contain from 1 to 128 Unicode characters.
Type (required)—connector type, sql.
Tenant (required)—name of the tenant that owns the resource.
Default query (required)—SQL query that is executed when connecting to the database.
Poll interval, sec —interval for executing SQL queries. This value is specified in seconds.
The default value is 10 seconds.
Description—up to 256 Unicode characters describing the resource.

To connect to the database, you need to define the values of the following settings on the Basic settings tab:

URL (required)—secret that stores a list of URLs for connecting to the database.
If necessary, you can edit or create a secret.
1. Click the button.
  The secret window is displayed.
2. Define the values for the following settings:
  1. Name—the name of the added secret.
  2. Type—urls.
    This value is set by default and cannot be changed.
  3. URL—URL of the database.
    You must keep in mind that each type of database uses its own URL format for connections.
    
    Available URL formats are as follows:
    - For SQLite:
      sqlite3://file:<file_path>
      A question mark (?) is used as a placeholder.
    - For MSSQL:
      sqlserver://<user>:<password>@<server:port>/<instance_name>?database=<database> (recommended)
      sqlserver://<user>:<password>@<server>?database=<database>&encrypt=disable
      The characters @p1 are used as a placeholder.
    - For MySQL:
      mysql://<user>:<password>@tcp(<server>:<port>)/<database>
      The characters %s are used as a placeholder.
    - For PostgreSQL:
      postgres://<user>:<password>@<server>/<database>?sslmode=disable
      The characters $1 are used as a placeholder.
    - For Cockroach:
      postgres://<user>:<password>@<server>:<port>/<database>?sslmode=disable
      The characters $1 are used as a placeholder.
    - For Firebird:
      firebirdsql://<user>:<password>@<server>:<port>/<database>
      A question mark (?) is used as a placeholder.
  4. Description—any additional information.
3. If necessary, click Add and specify an additional URL.
  In this case, if one URL is not available, the program connects to the next URL specified in the list of addresses.
4. Click the Save button.
1. Click the button.
  The secret window is displayed.
2. Specify the values for the settings that you want to change.
  You can change the following values:
  1. Name—the name of the added secret.
  2. URL—URL of the database.
    You must keep in mind that each type of database uses its own URL format for connections.
    
    Available URL formats are as follows:
    - For SQLite:
      sqlite3://file:<file_path>
      A question mark (?) is used as a placeholder.
    - For MSSQL:
      sqlserver://<user>:<password>@<server:port>/<instance_name>?database=<database> (recommended)
      sqlserver://<user>:<password>@<server>?database=<database>&encrypt=disable
      The characters @p1 are used as a placeholder.
    - For MySQL:
      mysql://<user>:<password>@tcp(<server>:<port>)/<database>
      The characters ? are used as placeholders.
    - For PostgreSQL:
      postgres://<user>:<password>@<server>/<database>?sslmode=disable
      The characters $1 are used as a placeholder.
    - For Cockroach:
      postgres://<user>:<password>@<server>:<port>/<database>?sslmode=disable
      The characters $1 are used as a placeholder.
    - For Firebird:
      firebirdsql://<user>:<password>@<server>:<port>/<database>
      A question mark (?) is used as a placeholder.
  3. Description—any additional information.
3. If necessary, click Add and specify an additional URL.
  In this case, if one URL is not available, the program connects to the next URL specified in the list of addresses.
4. Click the Save button.
When creating connections, strings containing account credentials with special characters may be incorrectly processed. If an error occurs when creating a connection but you are sure that the settings are correct, enter the special characters in percent encoding.

Codes of special characters

!

#

$

%

&

'

(

)

*

+

%21

%23

%24

%25

%26

%27

%28

%29

%2A

%2B

,

/

:

;

=

?

@

[

]

\

%2C

%2F

%3A

%3B

%3D

%3F

%40

%5B

%5D

%5C

The following special characters are not supported in passwords used to access SQL databases: space, [, ], :, /, #, %, \.
Identity column (required)—name of the column that contains the ID for each row of the table.
Identity seed (required)—identity column value that will be used to determine the specific line to start reading data from the SQL table.
Query—field for an additional SQL query. The query indicated in this field is performed instead of the default query.
Poll interval, sec —interval for executing SQL queries. The interval defined in this field replaces the default interval for the connector.
This value is specified in seconds. The default value is 10 seconds.

On the Advanced settings tab, you need to specify the following settings for the connector:

Character encoding—the specific encoding of the characters. The default value is UTF-8.
KUMA converts SQL responses to UTF-8 encoding. You can configure the SQL server to send responses in UTF-8 encoding or change the encoding of incoming messages on the KUMA side.
Debug—a drop-down list where you can specify whether resource logging should be enabled. By default it is Disabled.

Within a single connector, you can create a connection for multiple supported databases.

Supported SQL types and their specific usage features

The UNION operator is not supported by the SQL Connector resources.

The following SQL types are supported:

MSSQL
Example URLs:
- sqlserver://{user}:{password}@{server:port}/{instance_name}?database={database} – (recommended option)
- sqlserver://{user}:{password}@{server}?database={database}
The characters @p1 are used as a placeholder in the SQL query.

If you need to connect using domain account credentials, specify the account name in <domain>%5C<user> format. For example: sqlserver://domain%5Cuser:password@ksc.example.com:1433/SQLEXPRESS?database=KAV.
MySQL
Example URL: mysql://{user}:{password}@tcp({server}:{port})/{database}

The characters ? are used as placeholders in the SQL query.
PostgreSQL
Example URL: postgres://{user}:{password}@{server}/{database}?sslmode=disable

The characters $1 are used as a placeholder in the SQL query.
CockroachDB
Example URL: postgres://{user}:{password}@{server}:{port}/{database}?sslmode=disable

The characters $1 are used as a placeholder in the SQL query.
SQLite3
Example URL: sqlite3://file:{file_path}

A question mark (?) is used as a placeholder in the SQL query.
Oracle DB
Example URL: oracle://{user}/{password}@{server}:{port}/{service_name}

Easy Connect syntax is used. The characters :val are used as a placeholder in the SQL query.

When querying the Oracle DB, if the initial value of the ID is in datetime format, the Oracle to_timestamp_tz function should be used to add the date conversion to the SQL query. For example, select * from connections where login_time > to_timestamp_tz(:val, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM'). In this example, Connections is the Oracle DB table and the :val variable is taken from the Identity seed field, therefore it must be indicated in a format with the timezone (for example, 2021-01-01T00:10:00+03:00).

To access the Oracle DB, the libaio1 package must be installed.
Firebird® SQL
Example URL: firebirdsql://{user}:{password}@{server}:{port}/{database}

A question mark (?) is used as a placeholder in the SQL query.

A sequential request for database information is supported in SQL queries. For example, if you type select * from <name of data table> where id > <placeholder> in the Query field, the Identity seed field value will be used as the placeholder value the first time you query the table. In addition, the service that utilizes the SQL connector saves the ID of the last read entry, and the ID of this entry will be used as the placeholder value in the next query to the database.

Examples of SQL requests

Page top

!	#	$	%	&	'	(	)	*	+
%21	%23	%24	%25	%26	%27	%28	%29	%2A	%2B
,	/	:	;	=	?	@	[	]	\
%2C	%2F	%3A	%3B	%3D	%3F	%40	%5B	%5D	%5C

!	#	$	%	&	'	(	)	*	+
%21	%23	%24	%25	%26	%27	%28	%29	%2A	%2B
,	/	:	;	=	?	@	[	]	\
%2C	%2F	%3A	%3B	%3D	%3F	%40	%5B	%5D	%5C

!	#	$	%	&	'	(	)	*	+
%21	%23	%24	%25	%26	%27	%28	%29	%2A	%2B
,	/	:	;	=	?	@	[	]	\
%2C	%2F	%3A	%3B	%3D	%3F	%40	%5B	%5D	%5C