This operator imports data from MSSQL, ORACLE, PostgreSQL or ODBC data sources
Works with the keyword dal.
Understand different keywords – e.g. for the use of parameters.
Masked password in connection string
SQL editor with syntax highlighting and Intellisense
An SQL Query allows importing data from a data base of your choice (e.g. Access) or files (Excel, Text, CSV) available for the server.
Screenshot and main steps
Step 1: How to access data-base tables?
The easy way
If you want to connect to a TIS-Table or a table within the schema [TIS]Editor runs within – which is the most probable case – then it is enough to select the field data source and enter dal as information on connection.
Enter username and password
Username and password for the connection to the data source. These fields have to be filled in only if the data source requires authentication with username and password (e.g. databases typically do but files typically don't).
Select the Data Source
Choose from the different connection methods<br>
OPTIONAL fetch additional information
Define the Connection String (if entering dal is not sufficient)
Parameter may be used in the form of
NOTE: This is the only relevant information for the establishment of the connection. The string for connection must comply with the conventions for connection strings. The button Create … combines a valid connection string on the basis of the information User, Password, Data source, etc…
Check Table names and Column Name
As soon as the connection string is defined, on can update the list of tables that can be accessed:
Step 2: How to select data from such a table?
The SQL queries are sent directly to the data base and (provided you have the necessary rights) and applied. 'All SQL-language elements which are supported by the selected data source are allowed.
Define the max. query time
Define, when the operator shall stop the query (if not successfully completed). This avoids erroneous queries paralysing the server.
Notes including how to use Parameters in Tablename and where- statements
You can use Parameters and access TIS-Tables directly – see the [TIS]Board-Manual for a detailed description.
Getting database independent datetime
Please consider #XI.SysDate()# to get the current Date&Time as it is independent from the database where your query will run.
Examples where you build up the SQL Statement with parameters using TISParSQL
Placeholder can be used as part of the connection string and also in the query string.
Please note that the parameter's data type must match e.g. the column's data type in a WHERE clause.
|Special placeholder for partial SQL queries.|
|Placeholder for TIS Table which is replaced by the internal database table name.|
Examples for query elements
Good examples can be found at: http://www.w3schools.com/Sql/default.asp
% is used as wild-Card for strings of any length
Filter for NULL
Not in 2nd table
Group by & order by
Select only different names
Calculate Sums and Mittelwert (avg)
Select and count different values
Looking for many values
If you need to search for empty strings in a TIS-Table use the following statement:
Select data from different tables
Join Data from two Sources
DBMS differences: MS-SQL, Oracle, and PostgreSQL
If you build SQL queries in the Formula operator, you may use things, like
Some examples are provided here. A longer list can be found at: http://dba-oracle.com/oracle_news/2005_12_16_sql_syntax_differences.htm
Get the current Date & Time
Please use instead the following command which is provided by the [TIS]
Please use instead the following command
Cast (INCLOCK as timestamp)
Select first records
Select * From Data limit 100
Get substring from string
EXTRACT(EPOCH from (C6 - C5)) as
|Get the result's data type|
pg_typeof( EXTRACT(EPOCH from (C6 - C5)) )::text
|Convert strings to float numbers|
cast(replace(replace('1.234,00','.',''),',','.') as float8
Select C0 as "Betrieb"
Import Data from ODBC sources
Example for Import Definition
Example for Creating a Statement
Select top 100 Firma, Beruf from PEP_Dateiname.txt
File Format for Text Files
ODBC works with out problems, if the file has a UTF-8 format and has no real UTF 8 symbols.
|Note||Check if the right ODBC driver is installed. Corresponding your operating system the driver must be either 32 or 64 bit.|
|MySQL type mapping|
Initial Statement in ODBC Driver set SQL-Mode to ANSI:
How to understand the structure of a database
|Dealing with formats in Postgresql|
Avoid Empty Tables
Avoiding empty Tables
As of version 5.8 empty tables can be avoided by selecting the option. In case an empty table would be returned a defined warning appears instead of the table.
Access to TIS system's database
Use keyword "
|Dynamically support different DBMS|
Provide scripts in folder C:\inetpub\wwwroot\TIS\TISBoard\TISSQLScripts
in der Query field enter the script name
XimesDAL.SQLImportConnectionInternal - Specified cast is not valid.
some data types cannot be cast
cast to a compatible data type
Can't find the data
Somewhere in the chain access is not given
Error-Message Import: Sorry, currently import from TIS File System is not possible!
The data-source is used by another process and cannot be accessed.
Wait until the other process releases the data-set or file or consider a redesign.
Connection String does not work
Some ODBC-Drive to not work with blanks in the filename.
Write the name in squared brackets
Some drivers prefer ' instead of " to delimit texts
Check whether the Web-Server has access rights to the file you want to read (might have been lost when copying).
Import does not work
The datatype is not correct. E.g. the database provides numeric data but TIS expects it to either floating point or integer, or database export Date but TIS expects DateTime.
Use a convert command in the SQL string – see Dealing with different SQL-Keywords from MS-SQL and Oracle.
Connection string does not work anymore
It could be caused by a change of the server from 32bit to 64bit and no update of ODBC-drivers.
Talk with your system administrator for updates of corresponding ODBC-drivers.
Cannot find file I uploaded to the TIS-data management
Import SQL cannot access these files
Put the files on the server.
Sum or Average does not work
Sometimes the database as difficulties with large values
Maximum Query Run Time was exceeded
Although a maximum runtime was set for the query, the query need more time.
Not all SQL provider support the maximum runtime feature.
Want to learn more?
This operator imports data from MSSQL, ORACLE or ODBC data sources
|Generate blank row if nothing has been found|
|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.||-|
|Text fields content for blank line|
|opt.||If the filter operator creates a blank line, then this text will be displayed in all text columns.||-|
- Get the information which database is actually in use via XIH Functions in the Formula operator