Database Features
Tips and Tricks on using Exasol - from SQL and Lua Scripts to transactions and performance
Showing results for 
Search instead for 
Did you mean: 
Background By default, an Exasol virtual machine (VM) is configured to use a host-only network. This configuration allows to access the database and the EXAoperation management interface locally from the host machine. Nevertheless, this configuration prevents the use of publically available hosts and services on the internet from the virtual machine. This How-To provides information about configuring Exasol to be able to access the internet and enables users to: use DNS to access publicly reachable servers for making backups or in database IMPORT/EXPORT statements use LDAP servers of your choice for database or EXAoperation accounts use standard repositories for the installation of UDF packages use the Exasol Version Check (only if this feature has not been disabled) Prerequisites If not already done, import the Exasol OVA file into the VM tool of your choice (in Virtualbox: File -> Import Appliance). Accept the "End User License Agreement" and the "Privacy Statement" and wait until the image has been successfully loaded. How to enable internet access for Exasol Community Edition Configuration of VM network Step 1 Now, a new VM has been created. Change its network settings to use NAT (in Virtualbox: right click on VM -> Settings -> Network -> Adapter 1 -> Attached to NAT ) Step 2 Define port forwardings to guest ports 8563 (database) and 443 (EXAoperation management interface) (in Virtualbox: Adapter 1 -> Port Forwarding -> Add rule -> Host Port 8563 and Guest Port 8563 -> Add rule -> Host Port 4443 and Guest Port 443). The port forwarding rules will enable you to use the VM from the physical host machine. Step 3 Start the VM and wait until Exasol is up and running.  Configuration of DNS server Step 1 Browse to EXAoperation and login. Step 2 Go to Network. In the System tab, click on "Edit". Step 3 Add one or two DNS servers reachable from the VM (e.g. "" for an environment that can reach internet DNS servers) and click "Apply". Step 4 Log out. Additional Notes Troubleshooting Firewalls and/or company proxy servers may block or restrict traffic to internet hosts.
View full article
Problem When switching from EXAplus, you would like to change the shortcut for executing the current statement to do Auto completion create LUA Scripts / UDFs  use of Favorites Solution Change keyboard shortcuts When switching from EXAplus, the first thing that you would like to change is the shortcut for executing the current statement. In DBVisualizer this is set to   Ctrl + .  per default.  In EXAplus on the other hand this shortcut is defined as Ctrl + Enter. To chance it go to Tools -> Tool Properties and open the Key Bindings Dialog in the tree, create a copy of the current keymap called exasol and change the Binding for Main menu -> SQL Commander -> Execute Current:   Auto completion  It's important to note that in DbVisualizer the auto completion of table/column names works a little bit different than in EXAplus. In EXAplus you had to type first the schema name to access the tables. In DBVisualizer on the other hand you can directly access the table or column name without the need to fully qualify the object. It's also important to note that in DBVisualizer if you are in the context of a schema only the tables of that schema are shown in the auto completion. You can also customize the behavior of the auto-completion in Tools -> Tool Properties in the Dialog SQL Commander -> Auto Completion:   Creation of LUA Scripts / UDFs  In DBVisualizer LUA scripts / UDFs should be created as a SQL block. The start of the SQL Block is   --/   and the end of the block is defined with   /: --/ CREATE LUA SCRIPT MY_CAT RETURNS TABLE AS return query([[select * from cat]]) /   Per default   keywords in DBVisualizer are displayed in uppercase, so if you open an existing LUA script keywords like are automatically transformed to uppercase, so the case sensitive LUA script is not working anymore, please change this setting in Tool Properties -> Tools - SQL Formatting to "Keep Case Asis"   Please also have a look at the official documentation Favorites versus Bookmarks In EXAplus frequently used SQL Statements have been stored in the favorite tab. In DBVisualizer you have a similiar feature called Bookmarks. Learn more in the official documentation: . EXAplus commands EXAplus commands like   set autocommit off  are not working in DBVisualizer. DBVisualizer has it's syntax for it's client commands starting with @, e.g.   @set autocommit off;   See also official documentation: **  Additional Notes Known Issues of DBVisualizer  The open schema call is currently not interpreted by DBVisualizer Newly created schemas are not shown in the SQL Editor drop down menu -> Workaround reconnect the connection R Scripts sent from a Windows OS using DBVisualizer can't be executed because of windows CRLF, Following Workaround  In the current version, DbVisualizer uses the platforms linefeed convention, i.e. CRLF for Windows and LF for all other platforms. There is a way to force DbVisualizer to use LF for the text it sends to the database regardless of platform:   1) Open the file named   DBVIS-HOME/resources/dbvis-custom.prefs   (where DBVIS-HOME is the DbVisualizer installation folder) in a text editor, 2) Add the following on a new row:   dbvis.exasol.ReplaceCRLFwithLF=true 3) Restart DbVisualizer. 
View full article
This article addresses ODBC and special characters.
View full article
Background This article describes a way how to connect Spotfire to Exasol. Prerequisites Download the EXASOL JDBC driver and extract the exajdbc.jar How to connect Spotfire to Exasol Step 1 Copy the exajdbc.jar to to the Spotfire library directory, see the following link where this directory is located: Step 2 Restart the Spotfire Server so the EXASOL jdbc driver is loaded. Step 3 Start the Spotfire Server configuration tool Step 4 Go to the Configuration tab and create a new Data Source Template Step 5 In the Add data source template enter EXASOL as name and copy paste the following template: <jdbc-type-settings> <type-name>EXASOL</type-name> <driver>com.exasol.jdbc.EXADriver</driver> <connection-url-pattern>jdbc:exa:&lt;host&gt;:&lt;port&gt;;clientname=Spotfire;</connection-url-pattern> <ping-command>SELECT 1</ping-command> <supports-catalogs> false </supports-catalogs> <supports-schemas> true </supports-schemas> <java-to-sql-type-conversions> <type-mapping> <from max-length= "2000000" > String </from> <to>VARCHAR($$value$$)</to> </type-mapping> <type-mapping> <from> Integer </from> <to>DECIMAL(18,0)</to> </type-mapping> <type-mapping> <from> Long </from> <to>DECIMAL(36,0)</to> </type-mapping> <type-mapping> <from> Float </from> <to>REAL</to> </type-mapping> <type-mapping> <from> Double </from> <to>DOUBLE PRECISION</to> </type-mapping> <type-mapping> <from>Date</from> <to>DATE</to> </type-mapping> <type-mapping> <from>DateTime</from> <to>TIMESTAMP</to> </type-mapping> </java-to-sql-type-conversions> </jdbc-type-settings> Step 6 Save the config, close the configuration tool and restart the server Step 7 Check the logs if the XML config for EXASOL was validated correctly Step 8 Use the Information Designer tool in the Spotfire Analyst to create a new Data Source
View full article
Problem IBM Unica Campaign with MySQL template [EXASOL] [EXASolution driver] syntax error, unexpected '<'   [line x, column y]  of  [EXASOL] [EXASolution driver] function or script ADDDATE not found   [line x, column y]  Unica generates SQL statements that are not identically supported by EXASolution. Solution Create a pre-processor script that adjusts the SQL statements to appropriate ones. CREATE OR REPLACE LUA SCRIPT DATAMART.PPSCRIPT_MYSQL RETURNS ROWCOUNT AS function unicaproc(sqltext) local tokens = sqlparsing.tokenize(sqltext) for i=1,#tokens do if string.upper(tokens[i]) == 'ADDDATE' then tokens[i] = 'ADD_DAYS' end if tokens[i] == '<' and string.upper(tokens[i+1]) == 'CURRENT_DATE' then tokens[i] = '' end if string.upper(tokens[i]) == 'UNSUPPORTED' and tokens[i+1] == '>' then tokens[i] = '' tokens[i+1] = '' end end return table.concat(tokens) end / Activate this preprocessor script for all Unica session or system wide: ALTER [SESSION|SYSTEM] SET sql_preprocessor_script=DATAMART.PPSCRIPT_MYSQL;
View full article
Top Contributors