Summary

This operator imports data from MSSQL, ORACLE, PostgreSQL or ODBC data sources storing the query into a TIS File. 


(warning) Please note that from V 7.0, due to security reason the query files have to be uploaded to a specific TIS Files folder, namely \TIS\$SysFiles\SQLQueries.

Configuration

Settings

Name

Value

Opt.

Description

Example

Select query file

System.String

-

Select a file you want to import

-

Database Management System

System.String

-

Select which DMS you want to use

  • MS SQL Server
  • Oracle
  • Postgres
  • ODBC

ODBC

System.String

-

Maximum execution time of querry

  • 30 sec
  • 1 min 
  • 2 min
  • 5 min
  • 10 min
  • 30 min
  • No timeout

30 sec

Connection string


opt.

Database-specific connection string

-

Text fields content for blank line

System.String

opt.

If the filter operator creates a blank line, then this text will be displayed in all text columns.

-

Generate blank row if nothing has been found

System.Boolean

opt.

Must a blank line be created if filtering does not find anything? Zero will be entered in the numerical columns or the data columns, the text located in the parameter "Content text fields in case of blank line" will be displayed in the text columns.

-

Overview


Screenshot


Query file reference

  • Create a new file
  • Select an existing file (warning) Note that the file must have extension .sql
  • Select an existing file by providing path which may include parameters in syntax #XI.TISPar('par')#

Want to learn more?

This operator imports data from MSSQL, ORACLE or ODBC data sources

Troubleshooting

ORACLE:

Version 6.9 and higher uses the odp.net managed dataaccess client from ORACLE.

The managed dataaccess client does not use the registry settings any longer. 

This means, that no oracle-client ist needed anymore and settings in tnsnames.ora are not used any longer.


ORACLE - Datasources:

  • For Version 6.9 and higher it is necessary to change the connection string to a fully qualified tnsnames.ora like connection string.
  • For Version 6.9 consider using the culture parameter in the functions to_number and to_date if you import data from another culture than the current managed dataaccess client culture.
to_date
select to_date('01DEC2022', 'ddMonYYYY', 'NLS_DATE_LANGUAGE = American') from dual;
returns
TO_DATE('01DEC2022'
-------------------
2022-12-01 00:00:00
to_number
select to_number('1,125.14', '9G999D99', ' NLS_NUMERIC_CHARACTERS = ''.,''') from dual;
returns
TO_NUMBER('1,125.14','9G999D99','NLS_NUMERIC_CHARACTERS=''.,''')
----------------------------------------------------------------
1125.14
 
-- The first character in NLS_NUMERIC_CHARACTERS is the decimalpoint
-- The second character in NLS_NUMERIC_CHARACTERS is the grouping character (thousand separator).


Valid language names


Related topics