Summary
This operator imports data from MSSQL, ORACLE, PostgreSQL or ODBC data sources
Please note for Oracle that connection strings needs to be fully qualified instead of using TNSNAMES as data source. This is due to changed data access layer starting form v 6.8
Version 1 | Works with the keyword dal. |
Version 3 | Understand different keywords – e.g. for the use of parameters. |
Version 5 | Masked password in connection string SQL editor with syntax highlighting and Intellisense |
Configuration
Settings
Name | Value | Opt. | Description | Example |
---|---|---|---|---|
@SQLIMPORT | System.String | - | - | - |
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. | - |
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. | - |
Overview
Aim | 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 |
|
Critical aspects |
|
Further aspects |
|
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?
Background | 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 | Select |
Using parameters
Starting from v 6.8 nested usage is supported.
Syntax | Description |
---|---|
#XI.TISPar('<par>')# | 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. |
#XI.TISParSQL('<par>')# | Special placeholder for partial SQL queries. |
#XI.TISTable('<path>','<tab>')# | 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 | |
Find texts | asd % is used as wild-Card for strings of any length |
Compare dates |
|
Convert |
|
Comments within |
|
Filter for NULL |
|
Count |
|
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 |
|
Empty strings | 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 XIH.DatabaseInfo
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
Area | Oracle | MS-SQL | PostgresQL |
Get the current Date & Time | Please use instead the following command which is provided by the [TIS] | Please use instead the following command | |
Convert |
|
| (Col1)::integer (Col1)::text (Col1)::timestamp Cast (INCLOCK as timestamp) |
Select first records offset |
|
| Select * From Data limit 100 |
Get substring from string |
|
| SUBSTRING |
Compare Dates |
|
| https://www.postgresql.org/docs/9.4/static/functions-datetime.html |
Calculating Seconds | (c6-c5)*24*60*60 | 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 | ||
An example | Select C0 as "Betrieb" |
Import Data from ODBC sources
Getting Started |
|
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: SET SESSION sql_mode= 'ansi' ; |
Additional information
Task | Steps | Notes |
How to understand the structure of a database |
| |
Dealing with formats in Postgresql | DateStyle > set DateStyle= 'DMY, German' ; SET > show datestyle; ┌─────────────┐ │ DateStyle │ ├─────────────┤ │ German, DMY │ └─────────────┘ > select '01.12.2017' ::timestamp; ┌─────────────────────┐ │ timestamp │ ├─────────────────────┤ │ 01.12 . 2017 00 : 00 : 00 │ └─────────────────────┘ > select now()::timestamp without time zone::text; ┌────────────────────────────┐ │ now │ ├────────────────────────────┤ │ 19.05 . 2017 14 : 17 : 38.455419 │ └────────────────────────────┘ > reset datestyle; RESET > show datestyle; ┌───────────┐ │ DateStyle │ ├───────────┤ │ ISO, MDY │ └───────────┘ > select '01.12.2017' ::timestamp; ┌─────────────────────┐ │ timestamp │ ├─────────────────────┤ │ 2017 - 01 - 12 00 : 00 : 00 │ └─────────────────────┘ > select now()::timestamp without time zone::text; ┌────────────────────────────┐ │ now │ ├────────────────────────────┤ │ 2017 - 05 - 19 14 : 17 : 38.455419 │ └────────────────────────────┘ LC_NUMERIC (hier müsste man Postgres auf DB8 restarten, damit man auf de_DE.utf8 zugreifen kann) > set lc_numeric= 'de_DE.utf8' ; SET > show lc_numeric; ┌────────────┐ │ lc_numeric │ ├────────────┤ │ de_DE.utf8 │ └────────────┘ > select 1.234 ::text; ┌───────┐ │ text │ ├───────┤ │ 1.234 │ └───────┘ > select to_char( 1.234 , '9D999' ); ┌─────────┐ │ to_char │ ├─────────┤ │ 1 , 234 │ └─────────┘ > reset lc_numeric; RESET > show lc_numeric; ┌─────────────┐ │ lc_numeric │ ├─────────────┤ │ en_US.UTF- 8 │ └─────────────┘ > select 1.234 ::text; ┌───────┐ │ text │ ├───────┤ │ 1.234 │ └───────┘ > select to_char( 1.234 , '9D999' ); ┌─────────┐ │ to_char │ ├─────────┤ │ 1.234 │ └─────────┘ |
Avoid Empty Tables
Task | Steps | Notes |
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
Task | Steps | Notes |
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
|
Troubleshooting
Log message | Description | Resolution |
---|---|---|
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 | Steps:
|
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 |
File-names … | Some drivers prefer ' instead of " to delimit texts | |
Access problems | 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 | Transformation helps |
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. |
Related topics
- Operators (alphabetical overview)
- Get the information which database is actually in use via XIH Functions in the Formula operator