Scripting

This section explains the scripting programming that you can use to execute multiple SQL commands sequentially and to handle errors during the executions. You can run control jobs within the database (for example, complex loading processes) and simplify the repetitive jobs by parameterized scripts, such as, creating a user with its password and privileges.

Additionally, you can process result tables of SQL queries, however, a scripting program is a sequential program and only runs on a single cluster node (except the contained SQL statement). Therefore, it is not reasonable to do iterative operations on big data sets. For this purpose, you can use user-defined functions, see UDF Functions for more details.

For control jobs including several SQL commands, you can use Lua scripts. If you want to run multiple SQL commands, you have to use Lua scripts.

For scripting programs, only the programming language Lua is available (in version 5.1), extended by a couple of specific features. The information in the following chapters will enable you to work with scripting language of Exasol. If you want to learn about Lua, read the official Lua documentation.

A script program is created, executed, and dropped by the commands CREATE SCRIPT, EXECUTE SCRIPT, and DROP SCRIPT. The return value of the EXECUTE SCRIPT command is either a number (as rowcount) or a result table.

Example

In the following example a script is created that can copy all tables from one schema into a new schema.

--/
CREATE SCRIPT copy_schema (src_schema, dst_schema) AS
	-- define function if anything goes wrong
	function cleanup()
		query([[DROP SCHEMA ::s CASCADE]], {s=dst_schema})
		exit()
	end
	
	-- first create new schema
	query([[CREATE SCHEMA ::s]], {s=dst_schema})
	
	-- then get all tables of source schema
	local success, res = pquery([[SELECT table_name FROM EXA_ALL_TABLES
				    WHERE table_schema=:s]], {s=src_schema})
	if not success then
		cleanup()
	end
	-- now copy all tables of source schema into destination
	for i=1, #res do
		local table_name = res[i][1]
		-- create table identifiers
		local dst_table = join(".", quote(dst_schema), quote(table_name))
		local src_table = join(".", quote(src_schema), quote(table_name))
	-- use protected call of SQL execution including parameters
	local success = pquery([[CREATE TABLE ::d AS SELECT * FROM ::s]],
				  {d=dst_table, s=src_table})
	if not success then
		cleanup()
	end
end

/
EXECUTE SCRIPT copy_schema ('MY_SCHEMA', 'NEW_SCHEMA');