Database Interaction
Executing SQL statements through query() and pquery()
To execute SQL commands, you can use the functions query()
and pquery()
that accept a string value as input parameter. For the string values, the alternate string notation [[SELECT ...]]
is useful to avoid problems with single or double quotes within the SQL commands. The ending semicolon after the SQL commands is optional in the database scripts.
In case of an error during the call of query()
function, the script terminates and returns the corresponding exception message. To protect such calls, you can use the special functions pquery()
or pcall()
. For more information, see Error Handling through pcall() and error().
Return Values of Function query()
The query()
function returns the value depending on the query type:
1. SELECT statements
The query()
function returns a two-dimensional array with read-only values. To process these values, you need to create another two-dimensional array and copy the values. However, the scripts are not applicable for operations on big data volume.
The first dimension of the result represents the rows, the second one represents the columns. Apart from addressing by numbers, you can also use column names. However, you have to consider the case (for example, result[i]["MY_COLUMN"]
).
The number of rows can be determined through the #operator (#result
) and the number of columns through the #operator on the first row (#result[1]
). Additionally, you can access the SQL text through the key statement_text
.
Example for SELECT
:
--/
create or replace script example() as
res = query([[SELECT user_name FROM EXA_ALL_SESSIONS]])
output("Number of rows returned by the SELECT query: ".. #res) -- e.g., 12
output("Value of the USER_NAME column in the first row: ".. res[1].USER_NAME) -- e.g., SYS
output("Value of the first column in the first row: ".. res[1][1]) -- e.g., SYS
/
execute script example() with output;
Output |
---|
Number of rows returned by the SELECT query: 4 |
Value of the USER_NAME column in the first row: SYS |
Value of the first column in the first row: SYS |
2. Other statements
The query()
function returns a dictionary table containing the following keys:
rows_inserted
: Number of inserted rows.rows_updated
: Number of updated rows.rows_deleted
: Number of deleted rows.rows_affected
: Sum of the other three values.statement_text
: SQL text of the executed statement.statement_id
: Statement ID within the session as string value.
When executing IMPORT and EXPORT commands, the following keys are defined:
etl_rows_written
: Number of inserted rows.etl_rows_with_error
: Number of invalid rows (only reasonable if theREJECT LIMIT
option was used).etl_rows_read
: Number of read rows (sum of two numbers above).
Example for INSERT
:
--/
create or replace script example() as
res = query([[INSERT INTO people(pid, name) VALUES (1, 'Julia')]])
output("Number of inserted rows: ".. res.rows_inserted) -- shows: 1
output("Number of updated rows: ".. res.rows_updated) -- shows: 0
output("Number of deleted rows: ".. res.rows_deleted) -- shows: 0
output("Number of affected rows: ".. res.rows_affected) -- shows: 1
/
execute script example() with output;
Output |
---|
Number of inserted rows: 1 |
Number of updated rows: 0 |
Number of deleted rows: 0 |
Number of affected rows: 1 |
Return Values of Function pquery()
Similar to the pcall()
, the pquery()
function returns two values:
- Whether the execution was successful or not. It returns
false
in case of any error. - The actual result of the query, if no error occurred. Otherwise a dictionary table containing the keys
error_message
anderror_code
. In both cases, you can access the SQL text through the keystatement_text
.
The result of the functions pcall()
and pquery()
is read-only.
Parametrized SQL Commands
You can use the parameterized SQL commands to execute dynamic SQL statements without creating a new SQL text. For example, you can execute a command within a script to create a table multiple times by passing different table names. In this case, you can use the table name as a parameter of the SQL statement.
However, such parameters are evaluated either as identifier or as a value before they are placed into the text. The reason for that approach is to avoid any security risk through SQL Injection.
You can use the following parameters within the SQL statements:
:variable
: Evaluates the content of the variable as constant value. For example, in a column filterquery([[SELECT * FROM t WHERE i=:v]],{v=1})
.::variable
: Evaluates the content of the variable as identifier. Delimited identifier have to be delimited by double quotes within the string. For example,table_name=[["t"]]
.
If you use a parametrized query()
or pquery()
function, you have to specify a dictionary table as second function parameter to define the variables. In this definition you can either specify constant values (for example, query([[SELECT * FROM t WHERE ::c=:v]],{c=column_name,v=1})
) or assign script variables. Using script variables directly in the functions query()
and pquery()
is not possible.
If the second parameter is not specified, the SQL text is executed without interpretation. This is important if you want to create a script through query()
or pquery()
which contain such variables in the text.
Example
/*
* just executing simple SQL statements
*/
query([[CREATE TABLE t(c CHAR(1))]]) -- creates table T
query([[CREATE TABLE "t"(c CHAR(1))]]) -- creates table t
local my_sql_text = [[INSERT INTO "T" VALUES 'a','b','c','d']]
query(my_sql_text)
/*
* using result tables of a query by concatenating
* all table entries into one result string
*/
local res_table = query([[SELECT c FROM t]])
local first_element = res_table[1][1] -- access first row of first column
local row_size = #res_table -- row_size=4
if row_size==0 then
col_size = 0
else
col_size=#res_table[1] -- col_size=1
end
local concat_str = ''
for col=1, col_size do
for row=1,row_size do
concat_str = concat_str..res_table[row][col] --> 'abcd'
end
end
/*
* using result information about manipulated rows
*/
local res = query([[
MERGE INTO staff s USING update_table u ON (s.id=u.id)
WHEN MATCHED THEN UPDATE SET s.salary=u.salary DELETE WHERE u.delete_flag
WHEN NOT MATCHED THEN INSERT VALUES (u.id,u.salary)
]])
local i, u, d = res.rows_inserted, res.rows_updated, res.rows_deleted
/*
* using pquery to avoid abort of script
*/
local success, result = pquery([[DROP USER my_user]])
if not success then
-- accessing error message
local error_txt = 'Could not drop user. Error: '..result.error_message
query([[INSERT INTO my_errors VALUES (CURRENT_TIMESTAMP, :err)]],
{err=error_txt})
end
/*
* now using variables inside the SQL statements
* and create 5 tables at once
*/
for i=1,5 do
local tmp_table_name = 'TABLE_'..i
query([[CREATE TABLE ::t (c CHAR(1))]], {t=tmp_table_name})
end
-- distribute 3 values to the first 3 tables created before
local values = {'x', 'y', 'z'}
for i=1,#values do
local table_name='TABLE_'..i
query([[INSERT INTO ::t VALUES :v]], {t=table_name,v=values[i]})
end
-- using both types of variables at once
query([[SELECT * FROM t WHERE ::x=:y]],{x='c',y='a'})
Scripting Parameters
To pass the parameters to a script, you can specify them as an input list within the script definition (CREATE SCRIPT). The parameters don't need any type definition. The simple variables are specified as identifiers which are case-sensitive within the script.
If you want to pass an array as a parameter, you need to declare that by using the keyword ARRAY
before the identifier. When calling a script, you have to use the construct ARRAY(value1, value2, ...)
. By using this, you can pass the dynamic lists to a script. For example, a script can be called for a list of user names and can grant a certain system privilege, independent of the number of users.
Example
-- using script parameter for table name
--/
create script create_single_table (table_name) as
query([[CREATE TABLE ::t (i INT)]],{t=table_name})
/
-- using ARRAY construct for multiple input
--/
create script create_multiple_tables (ARRAY table_names) as
for i=1,#table_names do
query([[CREATE TABLE ::t (i INT)]],{t=table_names[i]})
end
/
EXECUTE SCRIPT create_single_table ('t1');
EXECUTE SCRIPT create_multiple_tables (ARRAY('t2','t3','t4'));
SELECT * FROM CAT;
Results
TABLE_NAME | TABLE_TYPE |
---|---|
T1 | TABLE |
T2 | TABLE |
T3 | TABLE |
T4 | TABLE |
IMPORT of External Scripts
To access the functions that are defined in external scripts, you can use the import()
function. The function executes the script for initialization reasons and provides all functions and globally-defined variables in a namespace that can be accessed through the script name. Alternatively, you can pass an alias as second parameter.
When including a script through the import()
function, the imported script is executed and all functions and global variables are provided in the corresponding namespace.
In the following example, a script other_script
is created that defines the function min_max()
. You can access this function in the second script my_script
after inclusion through import("schema1.other_script", "my_alias")
by using the syntax my_alias.min_max()
.
If you want to import a parameterized script, you need to specify the parameters in the call of the import()
function at the end after the optional alias. Such parameters can be constant values or script variables.
Example
--/
CREATE SCRIPT schema1.other_script AS
function min_max(a,b,c)
local min,max=a,b
if a>b then min,max=b,a
end
if c>max then max=c
elseif c<min then min=c
end
return min,max
end
/
--/
CREATE SCRIPT schema2.my_script (param1, param2, param3) AS
import('schema1.other_script', 'my_alias')
-- accessing external function through alias
local lowest, highest = my_alias.min_max(param1, param2, param3)
output(lowest..','..highest)
/
Return Value of a Script
You can use the function exit()
to return the values in a script. You can place this function at any place in the script, it terminates the script and returns the specified value.
The possible return type is specified in the CREATE SCRIPT command:
1. Option RETURNS ROWCOUNT or absence of any option
The value which is returned by the drivers as rowcount
. If you omit the exit()
parameter or if the script is
not exited explicitly, then this value is 0.
If you directly return the result of a query execution through query()
or pquery()
, for example using exit(query([[INSERT INTO...]])
, then the rowcount
of that query is passed
(not possible when executing a SELECT
query).
Alternatively, you can specify the rowcount
explicitly by
passing a dictionary table which contains the key rows_affected
(for example, exit({rows_affected=30})
).
2. Option RETURNS TABLE
If you specify this option, the result of the script execution will be a table.
If no parameter is passed to the exit()
function or if the script is not terminated explicitly, the result is an empty table. If you directly return the result of a query execution through query()
or pquery()
(for example, exit(query([[SELECT...]])
), then the result table of this query is passed (only possible for SELECT
queries).
Alternatively, you can specify a two-dimensional array. In that case you, have to specify the column types of the result table as second parameter (for more information see example of the CREATE TABLE command).
The result table of a script can be processed in other scripts, however, a persistent storage
of that table in the database through SQL is not possible (similar to CREATE TABLE
<table> AS SELECT...
).
If you specify the WITH OUTPUT
option within the EXECUTE SCRIPT statement, the
RETURNS
option of the script creation is overwritten (for more information, see Debug Output).
Example
-- script which returns rowcount
--/
CREATE SCRIPT script_1 AS
function my_function(a, b)
if a == true then
exit() -- exit whole script
else
return b, b+1 -- returning result of function
end
end
local x, y = my_function(false, 5) --> y=6
exit({rows_affected=y}) -- return rows_affected
/
-- script which returns result table of query
--/
CREATE SCRIPT script_2 RETURNS TABLE AS
exit(query([[SELECT * FROM DUAL]]))
/
-- return explicitly created table
--/
CREATE SCRIPT script_3 RETURNS TABLE AS
local result_table = {{decimal(1),"abc",true},
{decimal(2),"xyz",false},
{decimal(3),nil,nil}}
exit(result_table, "i int, c char(3), b bool")
/
EXECUTE SCRIPT script_3;
Results
I | C | B |
---|---|---|
1 | abc | TRUE |
2 | xyz | FALSE |
3 |
Metadata
You can access various metadata through global variables in each script to enhance the control of the script. The following table describes the metadata.
Metadata | Description |
---|---|
|
Database name |
exa.meta.database_version
|
Database version |
exa.meta.script_language
|
Name and version of the script language |
exa.meta.script_name
|
Name of the script |
exa.meta.script_schema
|
Schema of the script |
exa.meta.script_code
|
Code of the script |
exa.meta.current_use r |
Similar to function |
exa.meta.session_id
|
Session ID |
exa.meta.statement_id
|
Statement ID of the The imported scripts or scripts executed using |
exa.meta.node_count
|
Number of cluster nodes |
exa.meta.node_id
|
Local node ID starting with 0 |
exa.meta.vm_id
|
Always 0 since there is only one single virtual machine. |
Debug Output
You can analyze the sequence of actions during the development of the scripts by using the debug output. The WITH OUTPUT
option of the EXECUTE SCRIPT
statement is provided for that. If you specify this option, every output created through output()
is returned as a result table with one column and multiple rows (independent of the actual return value of the script).
The input parameter of the output()
function is a single string. If you pass a parameter of a different type, an implicit conversion is tried, but if the conversion is not possible, the value NULL
is inserted in the result table.
The column name of the result table is called output, and the column type is VARCHAR
with the length of the longest string which was inserted through output()
.
If you omit the WITH OUTPUT
option while executing the script, then debug option through output()
is ignored.
Example
--/
CREATE SCRIPT my_script (param1, param2) AS
output('SCRIPT started')
if param1==false then
output('PARAM1 is false, exit SCRIPT')
exit()
else
output('PARAM2 is '..param2)
end
output('End of SCRIPT reached')
/
EXECUTE SCRIPT my_script (TRUE, 5) WITH OUTPUT;
Output |
---|
SCRIPT started |
PARAM2 is 5 |
End of SCRIPT reached |
Auxiliary Functions for Identifiers
You can use the following auxiliary functions to simplify working with the database:
quote(param)
: Adds quotes around the parameter and doubles embedded quotes. This function is useful for delimited identifiers within SQL commands.join(p1, p2, p3, ...)
: Generates a string that includes all parametersp2,p3,...,
separated byp1
. By using the function calljoin(".",...)
, you can create schema qualified identifiers (for more information, see SQL identifier.
Example
output("my_table") -- regular identifier
output(quote("my_table")) -- delimited identifier
output(join(".","my_schema","my_table","my_column"))
output(join(".",
quote("my_schema"),
quote("my_table"),
quote("my_column")))
Output |
---|
my_table |
"my_table" |
my_schema.my_table.my_column |
"my_schema"."my_table"."my_column" |
Considerations
Line Numbers |
The script text is stored beginning from the first line after the |
Scope-Schema |
During the execution of a script the current schema is used as scope schema (except when you change the schema explicitly within the script). Therefore, you should consider that schema objects are preferably accessed schema qualified. |
Transactions |
The queries sent to the database with query or pquery function are executed within the current session of the current user. All statements within the script belong to the current transaction unless |