Sql type

The sql is used for SQL communications. Connector settings are divided into three blocks:

• General connector settings.
• Settings of a specific SQL connection. One connector may have more than one of these connections.
• Advanced settings of a connector.

General connector settings

General connector settings are located on the Main settings tab:

• Name (required)—a unique name for this type of resource. Must contain from 1 to 128 Unicode characters.
• Tenant (required)—name of the tenant that owns the resource.
• Type (required)—connector type.
• Default query (required)—field for an SQL query that is common to all connections of the connector.
• Poll interval, sec - time between SQL queries in seconds. The default value is 10 seconds.
• Description—up to 256 Unicode characters describing the resource.

If an individual connection (see below) has its own defined query and/or query interval, this connection will use the values specifically defined for its own query and/or query interval.

Settings of a specific SQL connection

General connector settings are located on the Main settings tab. You can create multiple connections in one connector by using the Add connection button to add new ones. You can delete connections by using the button.

Connection settings:

• URL (required)—drop-down list for selecting the secret resource that stores a list of query strings for SQL connections. The string format depends on the specific database (see the supported SQL types below).

  When creating connections, strings containing account credentials with special characters may be incorrectly processed. If a connection is not being created even though you are sure that your 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, [, ], :, /, #, %, \.

  Available formats for server addresses: hostname:port, IPv4:port, IPv6:port.

  If required, a secret can be created in the connector creation window using the button. The selected secret can be changed by clicking on the button.

• Identity column (required)—name of the column that will serve as the identity column.
• 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 SQL queries. If a query is indicated for a specific connection in a connector, this query will be used instead of the query specified in the Default query field.
• Poll interval, sec - time between SQL queries in seconds. Dafault value – 10 seconds. If a query interval is indicated for a specific connection in a connector, this interval will be used instead of the connector's general query interval.

One SQL connection is defined by using the URL, Identity column, and Identity seed parameters. The last line where data was read from the SQL table is saved in the KUMA collector that generated the query sent to the SQL database. This allows the program to start from the last read line when reading data from the SQL table. The ID of the last read line does not change when a different URL or query is indicated in the connector. To change the starting line where data acquisition from the SQL table will begin, you must change the value of the Identity seed and/or Identity column fields.

Advanced settings of a connector

Additional connector settings are located on the Advanced settings tab:

• Character encoding setting specifies character encoding. The default value is UTF-8.

  KUMA can process SQL responses in UTF-8 character encoding. Either ensure that the SQL server sends messages in UTF-8 or use the Character encoding drop-down list in the Connector settings to convert incoming messages to UTF-8.

• Debug—a drop-down list where you can specify whether resource logging should be enabled. By default it is Disabled.

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 character ? is used as a placeholder 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