Summary

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


Version 01

Works with the keyword dal.

Version 03

Understand different keywords – e.g. for the use of parameters.

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
STEP 1 - How to access data-base tables
STEP 2 - How to select data from such a table?

  • STEP 1: Make sure you can access the data which often makes it necessary to provide a password …
  • STEP 2: Define the SQL-Query that specifies what data to provide in the [TIS]Editor result data-node

Critical aspects

  • This feature gives access to all the data within the schema. The corresponding passwords should be handled properly.
  • Please check whether external access to the website is possible. This operator could be an door for hackers, trying to access the data in the database
  • Queries can cause substantial load for the database. Please check whether it is acceptable, that potentially inefficient queries are sent to the database or involve an expert.

Further aspects

  • Since this is an import operation, it can only be applied to empty data nodes.
  • The TIS-Server must be connected to the source via the operating system (e.g. via shared directories). If the data source is a file (e.g. Excel, Access,...) the user connected to the webserver must be able to access the file or have the necessary rights.
  • MS-SQL and Oracle differ in their SQL please consider the different SQL-keywords (especially when moving from one Server to another.
  • You can use Parameters and access TIS-Tables directly – see the [TIS]Board-Manual for a detailed description.

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).
If you use Windows authentication then user connected to the WEB-Server is used for authentication (not the account on the client PC where session runs).

Select the Data Source

Choose from the different connection methods<br>

  • MS-SQL-Server 2000
  • MS-SQL-Server 2005
  • Oracle (as of version 9i or higher)
  • PostgresQL (version 9.4 or higher)
  • ODBC-data source... ''e.g. Access, Excel, txt, or CSV files

OPTIONAL fetch additional information

  • DSN: Displays data sources available for the method chosen (data bases, ODBC-sources, etc.)
  • OBJECTS: Displays objects available for the method chosen (data bases, etc.)

Define the Connection String (if entering dal is not sufficient)

Parameter may be used in the form of #XI.TISPar('...')# as part of the connection string. V 5.15


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…
Excellent information on <b>Composition of Connection information can be found on the following website http://www.connectionstrings.com/
An example to access an MS-access table in a file called ODBC.mdb
driver={Microsoft Access Drive(*.mdb)};Dbq= C:\Inetpub\wwwroot\TIS2005\IMPORTEXPORTPROJECT\ODBC.mdb


Many different databases can be accessed.E.g.

  • MySQL
  • Filemaker
  • MSProject
  • MSEXchange
  • Active Directory
  • Lotus Notes

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:

In the next steps one can also see the column-names and types

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.
E.g.
select * from your_tablename

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.

  • E.g. accesing TIS-Tables directly - select * from #XI.TISTable('your folder name','your table name')#
  • Or via Parameter select * from #XI.TISTable(#XI.TISPar('parCPath')#,#XI.TISPar('parCName')#)# where C1 >= trunc(current_date) order by C1 Select * from #XI.TISTable('Demand\All Cargo', 'my Tablename')#
  • E.g. using Parameters select projectid, projectname from project where projectid=#XI.TISPar('Parameter_ID')#

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 usinf TISParSQL

Select 
 #XI.TISParSQL('STAT_Descr_MasterData_VAR1')# as VAR1, Count(*) as Nr 
 from #XI.TISTable(#XI.TISPar('TAB_Pfad')#,#XI.TISPar('TAB_Stamm')#)# 
 GROUP BY #XI.TISParSQL('STAT_Descr_MasterData_VAR1')# 
 ORDER BY #XI.TISParSQL('STAT_Descr_MasterData_VAR1')#

Examples for query elements (see also Dealing with different SQL-Keywords from MS-SQL and Oracle)

Good examples can be found at: http://www.w3schools.com/Sql/default.asp


Find texts

asd where column_A like '%1C%'

% is used as wild-Card for strings of any length

_ is used as wild_card for letters
if one wants to find _ then Txt LIKE '_1C%' ESCAPE '\'


Compare dates

From < to_date('20091201000000', 'YYYYMMDDHH24MISS')
From >= to_timestamp('200904010000', 'yyyymmddhh24mi')


Convert

TO_NUMBER(C3,'99999', 'NLS_NUMERIC_CHARACTERS = '',.-''')
UPPER('aabb')


Comments within

/* this is treated as comment */


Filter for NULL

Time_Col is not null


Count

Count(*)


Not in 2nd table

select * from Table_A where Key_col not in (select Key-col from Table_B)


Group by & order by

select Divisionname, Avg(Prio), Count(*) from XY
group by Divisionname, IncType
order by Divisionname


Select only different names

SELECT DISTINCT C0, C1 FROM Table


Calculate Sums and Mittelwert (avg)

SELECT Sum(C0) FROM Table
SELECT Avg(C0) FROM table
If AVG doesn't work, type conversion often helps:
Select cast(avg(C0) as float(53)) FROM table


Select and count different values

SELECT C0 as Bereich, C2 as Monat, count(*) as Anzahl From Table GROUP BY C0, C2 ORDER BY C0,C2


Looking for many values

C1 in ('Einzeldaten', 'Summendaten') AND C2 in ('2010', '2011'AND C5 in ('Überzahlung')


Empty strings

If you need to search for empty strings in a TIS-Table use the following statement:
c0 = ''


Select data from different tables

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2
If duplicated records should be allowed use the keyword ALL:
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2


Join Data from two Sources

SELECT t.title_id, t.title, s.qty FROM titles AS t, sales AS s WHERE t.title_id *= s.title_id
From the table "titles AS" the columns title_id and title are selected. From table "sales AS" the column "qty" is selected, where the columns "title" in both tables match.


Different SQL-Keywords from MS-SQL and Oracle

If you buils SQL Strings in the formulaoperator, 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]
#XI.SysDate()#

Please use instead the following command
which is provided by the [TIS]
#XI.SysDate()#

 #XI.SysDate()#

Convert

TO_NUMBER(C3,'99999', 'NLS_NUMERIC_CHARACTERS = '',.-''')
TO_CHAR(DATE_VAL, 'YYYY/MM/DD')

Col1 = convert(float, Col1)
cast (INCCLOCK as date)

(Col1)::integer

(Col1)::text

(Col1)::timestamp

Cast (INCLOCK as timestamp)
https://www.postgresql.org/docs/9.4/static/datatype.html

Select first records

SELECT * FROM data WHERE ROWNUM <= 100

SELECT * FROM data TOP 100

 Select * From Data limit 100

Get substring from string

SUBSTR

SUBSTRING

 SUBSTRING

Compare Dates

From < to_date('20091201000000', 'YYYYMMDDHH24MISS')
From >= to_timestamp('200904010000', 'yyyymmddhh24mi')
TO_CHAR(DATE_VAL, 'YYYY/MM/DD')
c0 >= #XI.TISPar('parFrom')#

C0 >= convert(datetime,
'#XI.TISParSQL('DATA_SPY Von')#',104) *)
*) 104 is the code for data format

 https://www.postgresql.org/docs/9.4/static/functions-datetime.html
Calculating Seconds
(c6-c5)*24*60*60EXTRACT(EPOCH from (C6 - C5)) as 
Datentyp des Ergebnisses einer Rechnung sehen 

 pg_typeof( EXTRACT(EPOCH from (C6 - C5)) )::text
Zahlem drekt aus Textdarstellung lesen 

cast(replace(replace('1.234,00','.',''),',','.') as float8

Import Data from ODBC sources

Getting Started

  • The necessary drivers have to be installed (e.g. Text Driver)
  • A dataset name (DSN) has to be defined that is then used in the import
    • The name should without blanks.
    • File must be reached from TIS server.
    • If several files have the same structure then it is enough to define one
    • It is much faster to import characters than floating numbers or integers (convert later!)


Example for Import Definition

Example for Creating a Statement

Select top 100 Firma, Beruf from PEP_Dateiname.txt

Note: blanks in filenames may cause difficulties

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.
If there are real UTF 8 symbols, the file needs to be saved as ANSI format.

NoteCheck if the right ODBC driver is installed. Corresponding your operating system the driver must be either 32 or 64 bit.

Additional information

Task

Steps

Notes

How to understand the structure of a database

Integrated Security=False;User ID=NAME;Password=xxxxxxxxxxxx
ORACLE:
select table_name, column_name, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, column_id from user_tab_cols
order by table_name,column_id
List all tables and Columns 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 "dal" for the system's database connection. Data source setting is irrelevant.



Troubleshooting

Log messageDescriptionResolution

XimesDAL.SQLImportConnectionInternal - Specified cast is not valid.

some data types cannot be cast

  • date
  • bigint

cast to a compatible data type

  • datetime
  • int, double, string


Example: ...

Situation

...

Settings...
Result

...

Project-File...

Want to learn more?

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

Parameter


ParameterValueOpt.DescriptionExample
@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.-

Example 1: ...

...

Calculation methodSettingsResult
...


ProblemFrequent CauseSolutions

Can't find the data

Somewhere in the chain access is not given

Steps:

  1. Test the TCP/IP Connection to the server
  2. Check whether the database is visible to the database server(e.g. for oracle check the OracleNet settings)
  3. Check User-Dsn & System DSN
  4. Check whether the server process (e.g. IIS_WPG) has the right to read,RXL (Read, Execute und List)</… in the Orcale-Client folder: C:\Oracle\ … her are the drivers.
  5. Reset the IIS-Server if updates do not get through (but make sure not to interrupt other persons work)

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
[ODBC with blanks.mdb].


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
cast(avg(C40) as float(53)) as Hour

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.

IndexingHow to build an index for a table

create index <ix> on <table> (c0,c1,c2,.....);

The column names in the tables are always Cxxxxx. Speaking names are defined only in the views.

If possible, you should take care that you do not insert sorted, otherwise the tree degenerates. If this is not possible, you can make a reindex at the end of large inserts to balance the tree again.

Related topics