Run-SQL-Components

11 Run SQL Components ¶

Beside the ‘Read’ components, which read tables from databases, there are also components which run SQL in database.

Runin tables and streams ¶

• Runmysql – run SQL or mysql command in MariaDB/MySQL database,
• Runora – run SQL in Oracle database,
• Runpg – run SQL or psql command in PostgreSQL database,
• Runsqlite – run SQL or sqlite command in SQLite database.

---

11.1 Runmysql ¶

(since EVL 2.4)

Run SQL or mysql commands from stdin or <f_in> and write result into stdout or <f_out>. It returns output from ‘mysql’ as is, so for quering the table to get formatted EVL output use ‘Readmysql’ or ‘evl readmysql’.

Password is taken from $HOME/.mysqlpass file, unless other file is specified by ‘--defaults-extra-file=<file>’.

Runmysql
is to be used in EVS job structure definition file. <f_out> is either output file or flow name.

evl runmysql
is intended for standalone usage, i.e. to be invoked from command line and writing records to standard output.

EVD and EVS are EVL definition files, for details see evl-evd(5) and evl-evs(5).

Synopsis ¶

Runmysql
  <f_in> <f_out> [--dbname=<database>] [--host=<hostname>]
  [--port=<port>] [--username=<mysqluser>] [--mysql=<mysql_options>]

evl runmysql
  [--dbname=<database>] [--host=<hostname>]
  [--port=<port>] [--username=<mysqluser>] [--mysql=<mysql_options>]
  [-v|--verbose]

evl runmysql
  ( --help | --usage | --version )

Options ¶

Standard options: ¶

--help
print this help and exit

--usage
print short usage information and exit

-v, --verbose
print to stderr info/debug messages of the component

--version
print version and exit

’mysql’ options: ¶

--dbname=<database>
this option is provided to ‘mysql’ command as ‘--database=<database>’

--host=<hostname>
this option is provided to ‘mysql’ command

--port=<port>
using other than standard port 3306. This option is provided to ‘mysql’ command.

--username=<mysqluser>
if not mentioned, then current system username is used as mysql user. This option is provided to ‘mysql’ command as ‘--user=<mysqluser>’.

--mysql=<mysql_options>
all <mysql_options> is provided to ‘mysql’ utility.

---

11.2 Runora ¶

(since EVL 2.0)

Run SQL from stdin or <f_in> and write result into stdout or <f_out>. It returns output from ’sqlplus’ as is, so for quering the table to get formatted EVL output use ‘Readora’ or ‘evl readora’.

Runora
is to be used in EVS job structure definition file. <f_out> is either output file or flow name.

evl runora
is intended for standalone usage, i.e. to be invoked from command line and writing records to standard output.

EVD and EVS are EVL definition files, for details see evl-evd(5) and evl-evs(5).

Synopsis ¶

Runora
  <f_in> <f_out>
  [-u|--username=<user>] [-p|--password=<password>]
  [--connect=<connect_identifier>]

evl runora
  [-u|--username=<user>] [-p|--password=<password>]
  [--connect=<connect_identifier>]
  [-v|--verbose]

evl runora
  ( --help | --usage | --version )

Options ¶

Standard options: ¶

--help
print this help and exit

--usage
print short usage information and exit

--version
print version and exit

’sqlplus’ options: ¶

--connect=<connect_identifier>
sqlplus will be called in the form:

<username>/<password>@<connect_identifier>

where <connect_identifier> can be in the form

[<net_service_name> | [//]Host[:Port]/<service_name>]

without this option environment variable ORACONN (if defined) is used as connection identifier for sqlplus

-p, --password=<password>
without this option environment variable ORAPASS is used as password for sqlplus

-u, --username=<user>
without this option environment variable ORAUSER is used as user for sqlplus

---

11.3 Runpg ¶

(since EVL 2.0)

Run SQL or psql commands from stdin or <f_in> and write result into stdout or <f_out>. It returns output from ‘psql’ as is, so for quering the table to get formatted EVL output use ‘Readpg’ or ‘evl readpg’.

Password is taken:

1. from file ‘$EVL_PASSFILE’, which is by default ‘$HOME/.evlpass’,
2. from file ‘$PGPASSFILE’, which is by default ‘$HOME/.pgpass’.

When such file has not permissions 600, it is ignored! For details see ‘evl-password’.

Runpg
is to be used in EVS job structure definition file. <f_out> is either output file or flow name.

evl runpg
is intended for standalone usage, i.e. to be invoked from command line and writing records to standard output.

EVD and EVS are EVL definition files, for details see evl-evd(5) and evl-evs(5).

Synopsis ¶

Runpg
  <f_in> <f_out>
  [-b|--dbname=<database>] [-h|--host=<hostname>] [-p|--port=<port>]
  [-u|--username=<pguser>] [--psql=<psql_options>]

evl runpg
  [-b|--dbname=<database>] [-h|--host=<hostname>] [-p|--port=<port>]
  [-u|--username=<pguser>] [--psql=<psql_options>]
  [-v|--verbose]

evl runpg
  ( --help | --usage | --version )

Options ¶

Standard options: ¶

--help
print this help and exit

--usage
print short usage information and exit

--version
print version and exit

’psql’ options: ¶

-b, --dbname=<database>
either this or environment variable ‘PGDATABASE’ should be provided, if not, then current system username is used as psql database. If also ‘PGDATABASE’ environment variable is set, this option has preference. (This option is provided to ’psql’ command.)

-h, --host=<hostname>
either this or environment variable ‘PGHOST’ should be provided when connecting to other host than localhost. If also ‘PGHOST’ variable is set, this option has preference. (This option is provided to ‘psql’ command.)

-p, --port=<port>
either this or environment variable ‘PGPORT’ should be provided when using other then standard port ‘5432’. (This option is provided to ‘psql’ command.)

--psql=<psql_options>
by default it runs ‘psql’ command behind with these options:

``--no-align --quiet --tuples-only --set=\"ON_ERROR_STOP=1\"``

If any other option is needed, specify <psql_options>. See ‘man psql’ for details.

-u, --username=<pguser>
either this or environment variable ‘PGUSER’ should be provided, if not, then current system username is used as psql user. If variable ‘PGUSER’ is set, this option has preference. (This option is provided to ‘psql’ command.)

---

11.4 Runsqlite ¶

(since EVL 2.7)

Run SQL or sqlite3 commands from stdin or <f_in> and write result into stdout or <f_out>. It returns output from ‘sqlite3’ as is, so for quering the table to get formatted EVL output use ‘Readsqlite’ or ‘evl readsqlite’.

Path to the database file is taken from environment variable ‘$EVL_SQLITE_DATABASE’, unless <db_file> is specified.

Runsqlite
is to be used in EVS job structure definition file. <f_out> is either output file or flow name.

evl runsqlite
is intended for standalone usage, i.e. to be invoked from command line and writing records to standard output.

EVD and EVS are EVL definition files, for details see evl-evd(5) and evl-evs(5).

Synopsis ¶

Runsqlite
  <f_in> <f_out> [--dbname=<db_file>] [--sqlite=<sqlite_options>]

evl runsqlite
  [--dbname=<db_file>] [--sqlite=<sqlite_options>]
  [-v|--verbose]

evl runsqlite
  ( --help | --usage | --version )

Options ¶

--dbname=<db_file>
path to the SQLite database file; if this option is not used, database file is taken from environment variable ‘$EVL_SQLITE_DATABASE’.

--sqlite=<sqlite_options>
all <sqlite_options> is provided to ‘sqlite’ utility.

Standard options: ¶

--help
print this help and exit

--usage
print short usage information and exit

-v, --verbose
print to stderr info/debug messages of the component

--version
print version and exit

---