the main sqlutil service class More...

Inherits QorusSystemService.

Static Public Member Methods
static	init ()
	initalizes the system sqlutil service

static *hash< auto >	select (string ds, string table, *hash< auto > sh)
	executes the select method on the given datasource and table and returns the result More...

static *list< auto >	select_rows (string ds, string table, *hash< auto > sh)
	executes the selectRows method on the given datasource and table and returns the result More...

static *hash< auto >	select_row (string ds, string table, *hash< auto > sh)
	executes the selectRow method on the given datasource and table and returns the result More...

static int	insert (string ds, string table, softlist< auto > rl)
	executes the insert method on the given datasource and table with the given data and returns the number of rows inserted in a single transaction More...

static int	insert_from_select (string ds, string table, list< auto > cols, string source_table, hash< auto > sh)
	executes the insertFromSelect method on the given datasource and table and returns the result More...

static int	update (string ds, string table, hash< auto > set, *hash< auto > cond)
	executes the update method on the given datasource and table with the given data and returns the number of rows updated in a single transaction More...

static int	upsert_row (string ds, string table, hash< auto > row, int upsert_strategy=AbstractTable::UpsertAuto, *hash< auto > opt)
	executes the upsert() method on the given datasource and table with the given input data and returns the result of the upsert operation More...

static hash< auto >	upsert (string ds, string table, softlist< auto > rows, int upsert_strategy=AbstractTable::UpsertAuto, *hash< auto > opt)
	executes the upsertFromIterator() method on the given datasource and table with the given input data and returns a hash giving the result of the upsert operation More...

static int	del (string ds, string table, *hash< auto > cond)
	executes the delete method on the given datasource and table with the given data and returns the number of rows deleted in a single transaction More...

static nothing	truncate_table (string ds, string table)
	executes the truncate method on the given datasource and table More...

static *hash< auto >	align_schema (string ds, hash< auto > schema_template, *hash< auto > opt)
	aligns a schema with the given template and returns a hash with two keys: `"info"`: a list of informational strings about the result of processing, and `"sql"`: a list of the SQL executed More...

static *hash< auto >	drop_schema (string ds, hash< auto > schema_template, *hash< auto > opt)
	drops the given schema and returns a hash with two keys: `"info"`: a list of informational strings about the result of processing, and `"sql"`: a list of the SQL executed More...

static *hash< auto >	align_table (string ds, string table_name, hash< auto > table_template, *hash< auto > opt)
	aligns a schema with the given template and returns a hash with two keys: `"info"`: a list of informational strings about the result of processing, and `"sql"`: a list of the SQL executed More...

static *hash< auto >	drop_table (string ds, string table, *hash< auto > opt)
	drops the listed table and returns information about the operation executed More...

static list< auto >	list_functions (string ds)
	returns a list of strings giving the names of all functions in the given datasource More...

static list< auto >	list_procedures (string ds)
	returns a list of strings giving the names of all procedures in the given datasource More...

static list< auto >	list_sequences (string ds)
	returns a list of strings giving the names of all sequences in the given datasource More...

static list< auto >	list_tables (string ds)
	returns a list of strings giving the names of all tables in the given datasource More...

static bool	exists_table (string ds, string table)
	returns True if the given table exists in the given datasource More...

static list< auto >	list_views (string ds)
	returns a list of strings giving the names of all views in the given datasource More...

static *string	get_table_ddl (string ds, string table, *hash< auto > opt)
	returns the DDL for a table or NOTHING if the object does not exist or is not accessible More...

static list< auto >	get_sequence_ddl (string ds, string name, *hash opt)
	returns the DDL for a sequence or NOTHING if the object does not exist or is not accessible More...

static list< auto >	get_function_ddl (string ds, string name, *hash< auto > opt)
	returns the DDL for a function or NOTHING if the object does not exist or is not accessible More...

static list< auto >	get_procedure_ddl (string ds, string name, *hash< auto > opt)
	returns the DDL for a stored procedure or NOTHING if the object does not exist or is not accessible More...

static list< auto >	get_view_ddl (string ds, string name, *hash< auto > opt)
	returns the DDL for a view or NOTHING if the object does not exist or is not accessible More...

static auto	exec_sql (string ds, string sql)
	executes SQL in the given datasource with option arguments and returns a hash of the results More...

static auto	exec_raw_sql (string ds, string sql)
	executes raw SQL in the given datasource and returns a hash of the results More...

static hash< auto >	describe_table (string ds, string table)
	returns a hash describing the given table More...

static hash< auto >	describe_query (string ds, string sql)
	returns a hash describing the results of an SQL query More...

Detailed Description

the main sqlutil service class

Member Function Documentation

◆ align_schema()

static *hash<auto> sqlutil::align_schema	(	string	ds,
		hash< auto >	schema_template,
		*hash< auto >	opt
	)

inlinestatic

aligns a schema with the given template and returns a hash with two keys: "info": a list of informational strings about the result of processing, and "sql": a list of the SQL executed

Parameters

ds	the datasource name of the schema to align
schema_template	a schema template hash; see SqlUtil::AbstractDatabase::getAlignSql() for more information
opt	a schema alignment option hash; see SqlUtil::AbstractDatabase::getAlignSql() for more information

Returns

a hash with two keys:

"info": a list of informational strings about the result of processing
"sql": a list of the SQL executed

An implicit commit is made on the datasource if no persistent remote transaction is in progress and no errors occur executing the method; see Transaction Management with the sqlutil Service for more information on persistent remote transactions.

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-WRITE

◆ align_table()

static *hash<auto> sqlutil::align_table	(	string	ds,
		string	table_name,
		hash< auto >	table_template,
		*hash< auto >	opt
	)

inlinestatic

aligns a schema with the given template and returns a hash with two keys: "info": a list of informational strings about the result of processing, and "sql": a list of the SQL executed

Parameters

ds	the datasource name of the schema where the table will be aligned
table_name	the name of the table to align
table_template	a table description hash; see SqlUtil::AbstractTable::getAlignSql() for more information
opt	a table alignment hash; see SqlUtil::AbstractTable::getAlignSql() for more information

Returns

a hash with two keys:

"info": a list of informational strings about the result of processing
"sql": a list of the SQL executed

An implicit commit is made on the datasource if no persistent remote transaction is in progress and no errors occur executing the method; see Transaction Management with the sqlutil Service for more information on persistent remote transactions.

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-WRITE

◆ del()

static int sqlutil::del	(	string	ds,
		string	table,
		*hash< auto >	cond
	)

inlinestatic

executes the delete method on the given datasource and table with the given data and returns the number of rows deleted in a single transaction

An implicit commit is made on the datasource if no persistent remote transaction is in progress and no errors occur executing the method; see Transaction Management with the sqlutil Service for more information on persistent remote transactions.

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-WRITE

◆ describe_query()

static hash<auto> sqlutil::describe_query	(	string	ds,
		string	sql
	)

inlinestatic

returns a hash describing the results of an SQL query

Example:: hash<auto> desc = omqservice.system.sqlutil.describe_query("omquser", sql);

Parameters

ds	the name of the datasource
sql	the select statement to describe
...	any bind arguments to the select statement

◆ describe_table()

static hash<auto> sqlutil::describe_table	(	string	ds,
		string	table
	)

inlinestatic

returns a hash describing the given table

Example:: hash<auto> desc = omqservice.system.sqlutil.describe_table("omquser", table_name);

Parameters

ds	the name of the datasource
table	the name of the table

Returns

a hash keyed by column name where each value is a hash with the following keys:

native_type (string): the native DB type
qore_type (*string): the equivalent Qore type, if known
size (int): the size of the column, if relevant
nullable (bool): if the column can hold NULL values
def_val (*string): the default value code for the column, if any
comment (*string): any comment on the column

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

Since: Qorus 4.1

◆ drop_schema()

static *hash<auto> sqlutil::drop_schema	(	string	ds,
		hash< auto >	schema_template,
		*hash< auto >	opt
	)

inlinestatic

drops the given schema and returns a hash with two keys: "info": a list of informational strings about the result of processing, and "sql": a list of the SQL executed

Parameters

ds	the datasource name of the schema to drop
schema_template	a schema template hash; see SqlUtil::AbstractDatabase::getDropSchemaSql() for more information
opt	a schema drop option hash; see SqlUtil::AbstractDatabase::getDropSchemaSql() for more information

Returns

a hash with two keys:

"info": a list of informational strings about the result of processing
"sql": a list of the SQL executed

An implicit commit is made on the datasource if no persistent remote transaction is in progress and no errors occur executing the method; see Transaction Management with the sqlutil Service for more information on persistent remote transactions.

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-WRITE

◆ drop_table()

static *hash<auto> sqlutil::drop_table	(	string	ds,
		string	table,
		*hash< auto >	opt
	)

inlinestatic

drops the listed table and returns information about the operation executed

Parameters

ds	the datasource the table resides in
table	the name of the table to drop
opt	options for the drop operation (see SqlUtil::AbstractTable::getDropSql())

Returns

a hash with the following keys:

"info": one or more informative strings about the SQL operations executed
"sql": one or more DDL strings giving the actual SQL DDL commands executed

An implicit commit is made on the datasource if no persistent remote transaction is in progress and no errors occur executing the method; see Transaction Management with the sqlutil Service for more information on persistent remote transactions.

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-WRITE

◆ exec_raw_sql()

static auto sqlutil::exec_raw_sql	(	string	ds,
		string	sql
	)

inlinestatic

executes raw SQL in the given datasource and returns a hash of the results

Example:: string sql = "begin my_proc(); end;"
hash<auto> h = omqservice.system.sqlutil.exec_raw_sql("omquser", sql);

Parameters

ds	the name of the datasource
sql	the SQL to execute

An implicit commit is made on the datasource if no persistent remote transaction is in progress and no errors occur executing the method; see Transaction Management with the sqlutil Service for more information on persistent remote transactions.

Returns: The return value depends on the DBI driver; normally, for commands with placeholders, a hash is returned holding the values acquired from executing the SQL statement. For all other commands, normally an integer row count is returned. However, some DBI drivers also allow select statements to be executed through this interface, which would also return a hash (column names) of lists (values for each column). See Qore::SQL::AbstractDatasource::execRaw() as a reference.

Note: requires permission DATASOURCE-CONTROL or both SQLUTIL-READ and SQLUTIL-WRITE

◆ exec_sql()

static auto sqlutil::exec_sql	(	string	ds,
		string	sql
	)

inlinestatic

executes SQL in the given datasource with option arguments and returns a hash of the results

Example:: string sql = "begin my_proc(%v, %v, :result); end;"
hash<auto> h = omqservice.system.sqlutil.exec_sql("omquser", sql, arg1, arg2, Type::Int);

Parameters

ds	the name of the datasource
sql	the SQL to execute

Returns: The return value depends on the DBI driver; normally, for commands with placeholders, a hash is returned holding the values acquired from executing the SQL statement. For all other commands, normally an integer row count is returned. However, some DBI drivers also allow select statements to be executed through this interface, which would also return a hash (column names) of lists (values for each column). See Qore::SQL::AbstractDatasource::exec() as a reference.

An implicit commit is made on the datasource if no persistent remote transaction is in progress and no errors occur executing the method; see Transaction Management with the sqlutil Service for more information on persistent remote transactions.

Note: requires permission DATASOURCE-CONTROL or both SQLUTIL-READ and SQLUTIL-WRITE

◆ exists_table()

static bool sqlutil::exists_table	(	string	ds,
		string	table
	)

inlinestatic

returns True if the given table exists in the given datasource

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

Since: Qorus 4.1

◆ get_function_ddl()

static list<auto> sqlutil::get_function_ddl	(	string	ds,
		string	name,
		*hash< auto >	opt
	)

inlinestatic

returns the DDL for a function or NOTHING if the object does not exist or is not accessible

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ get_procedure_ddl()

static list<auto> sqlutil::get_procedure_ddl	(	string	ds,
		string	name,
		*hash< auto >	opt
	)

inlinestatic

returns the DDL for a stored procedure or NOTHING if the object does not exist or is not accessible

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ get_sequence_ddl()

static list<auto> sqlutil::get_sequence_ddl	(	string	ds,
		string	name,
		*hash	opt
	)

inlinestatic

returns the DDL for a sequence or NOTHING if the object does not exist or is not accessible

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ get_table_ddl()

static *string sqlutil::get_table_ddl	(	string	ds,
		string	table,
		*hash< auto >	opt
	)

inlinestatic

returns the DDL for a table or NOTHING if the object does not exist or is not accessible

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ get_view_ddl()

static list<auto> sqlutil::get_view_ddl	(	string	ds,
		string	name,
		*hash< auto >	opt
	)

inlinestatic

returns the DDL for a view or NOTHING if the object does not exist or is not accessible

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ insert()

static int sqlutil::insert	(	string	ds,
		string	table,
		softlist< auto >	rl
	)

inlinestatic

executes the insert method on the given datasource and table with the given data and returns the number of rows inserted in a single transaction

An implicit commit is made on the datasource if no persistent remote transaction is in progress and no errors occur executing the method; see Transaction Management with the sqlutil Service for more information on persistent remote transactions.

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-WRITE

◆ insert_from_select()

static int sqlutil::insert_from_select	(	string	ds,
		string	table,
		list< auto >	cols,
		string	source_table,
		hash< auto >	sh
	)

inlinestatic

executes the insertFromSelect method on the given datasource and table and returns the result

An implicit commit is made on the datasource if no persistent remote transaction is in progress and no errors occur executing the method; see Transaction Management with the sqlutil Service for more information on persistent remote transactions.

Note: requires permission DATASOURCE-CONTROL or (SQLUTIL-READ and SQLUTIL-WRITE)

◆ list_functions()

static list<auto> sqlutil::list_functions ( string ds )

inlinestatic

returns a list of strings giving the names of all functions in the given datasource

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ list_procedures()

static list<auto> sqlutil::list_procedures ( string ds )

inlinestatic

returns a list of strings giving the names of all procedures in the given datasource

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ list_sequences()

static list<auto> sqlutil::list_sequences ( string ds )

inlinestatic

returns a list of strings giving the names of all sequences in the given datasource

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ list_tables()

static list<auto> sqlutil::list_tables ( string ds )

inlinestatic

returns a list of strings giving the names of all tables in the given datasource

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ list_views()

static list<auto> sqlutil::list_views ( string ds )

inlinestatic

returns a list of strings giving the names of all views in the given datasource

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ select()

static *hash<auto> sqlutil::select	(	string	ds,
		string	table,
		*hash< auto >	sh
	)

inlinestatic

executes the select method on the given datasource and table and returns the result

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ select_row()

static *hash<auto> sqlutil::select_row	(	string	ds,
		string	table,
		*hash< auto >	sh
	)

inlinestatic

executes the selectRow method on the given datasource and table and returns the result

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ select_rows()

static *list<auto> sqlutil::select_rows	(	string	ds,
		string	table,
		*hash< auto >	sh
	)

inlinestatic

executes the selectRows method on the given datasource and table and returns the result

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ truncate_table()

static nothing sqlutil::truncate_table	(	string	ds,
		string	table
	)

inlinestatic

executes the truncate method on the given datasource and table

An implicit commit is made on the datasource if no persistent remote transaction is in progress and no errors occur executing the method; see Transaction Management with the sqlutil Service for more information on persistent remote transactions.

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-WRITE

◆ update()

static int sqlutil::update	(	string	ds,
		string	table,
		hash< auto >	set,
		*hash< auto >	cond
	)

inlinestatic

executes the update method on the given datasource and table with the given data and returns the number of rows updated in a single transaction

An implicit commit is made on the datasource if no persistent remote transaction is in progress and no errors occur executing the method; see Transaction Management with the sqlutil Service for more information on persistent remote transactions.

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-WRITE

◆ upsert()

static hash<auto> sqlutil::upsert	(	string	ds,
		string	table,
		softlist< auto >	rows,
		int	upsert_strategy = `AbstractTable::UpsertAuto`,
		*hash< auto >	opt
	)

inlinestatic

executes the upsertFromIterator() method on the given datasource and table with the given input data and returns a hash giving the result of the upsert operation

An implicit commit is made on the datasource if no persistent remote transaction is in progress and no errors occur executing the method; see Transaction Management with the sqlutil Service for more information on persistent remote transactions.

Parameters

ds	the datasource name
table	the table name
rows	the row data to insert
upsert_strategy	see Upsert Strategy Codes for possible values for the upsert strategy
opt	a hash of options for the upsert operation; see SqlUtil::AbstractTable::UpsertOptions for common options; each driver can support additional driver-specific options

Returns

NOTHING if no actions were taken or a hash with the following keys assigned to numeric values indicating the number of rows processed (keys correspond to SqlUtil::AbstractTable::UpsertResultDescriptionMap keys):

"inserted": the number of rows inserted
"verified": the number of rows updated unconditionally; note that this key is returned with all upsert strategy codes other than SqlUtil::AbstractTable::UpsertSelectFirst instead of "updated"
"updated": the number of rows updated; note that this key is only returned if upsert_strategy is SqlUtil::AbstractTable::UpsertSelectFirst, otherwise updated rows are reported as "verified" since rows are updated unconditionally with other the upsert strategy codes
"unchanged": the number of rows unchanged; this key can only be returned if upsert_strategy is SqlUtil::AbstractTable::UpsertSelectFirst, SqlUtil::AbstractTable::UpsertInsertOnly, or SqlUtil::AbstractTable::UpsertUpdateOnly
"deleted": the number of rows deleted; this can only be returned if upsert option delete_others is True

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-WRITE

◆ upsert_row()

static int sqlutil::upsert_row	(	string	ds,
		string	table,
		hash< auto >	row,
		int	upsert_strategy = `AbstractTable::UpsertAuto`,
		*hash< auto >	opt
	)

inlinestatic

executes the upsert() method on the given datasource and table with the given input data and returns the result of the upsert operation

An implicit commit is made on the datasource if no persistent remote transaction is in progress and no errors occur executing the method; see Transaction Management with the sqlutil Service for more information on persistent remote transactions.

Parameters

ds	the datasource name
table	the table name
row	a hash representing the row to insert or update
upsert_strategy	see Upsert Strategy Codes for possible values for the upsert strategy
opt	a hash of options for the upsert operation; see SqlUtil::AbstractTable::UpsertOptions for common options; each driver can support additional driver-specific options

Returns: an integer code giving the result of the update; see Upsert Result Codes for more information

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-WRITE

Since: Qorus 4.1

The documentation for this class was generated from the following file:

sqlutil-v4.1.qsd

Static Public Member Methods

Detailed Description

Member Function Documentation

◆ align_schema()

◆ align_table()

◆ del()

◆ describe_query()

◆ describe_table()

◆ drop_schema()

◆ drop_table()

◆ exec_raw_sql()

◆ exec_sql()

◆ exists_table()

◆ get_function_ddl()

◆ get_procedure_ddl()

◆ get_sequence_ddl()

◆ get_table_ddl()

◆ get_view_ddl()

◆ insert()

◆ insert_from_select()

◆ list_functions()

◆ list_procedures()

◆ list_sequences()

◆ list_tables()

◆ list_views()

◆ select()

◆ select_row()

◆ select_rows()

◆ truncate_table()

◆ update()

◆ upsert()

◆ upsert_row()