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

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?

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.

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)

Check Table names and Column Name

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 using 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')#

#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.

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 2^nd 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.

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

offset

SELECT * FROM data WHERE ROWNUM <= 100

SELECT * FROM data TOP 100

Select * From Data limit 100
Offset

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*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"
from #XI.TISTable('Pfad','Cube')# 
order by c0
LIMIT 700 offset 0

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.

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

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

Task

Steps

Notes

Avoiding empty Tables

Task

Steps

Notes

System's database

Dynamically support different DBMS

Provide scripts in folder C:\inetpub\wwwroot\TIS\TISBoard\TISSQLScripts

• myscript.ora.txt
• myscript.pg.txt
• myscript.sql.txt

in der Query field enter the script name

• myscript

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

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.