system sqlutil service More...

Inherits QorusSystemService.

Public Member Methods
	select (string ds, string table, *hash< auto > sh)
	select service method More...

	select_rows (string ds, string table, *hash< auto > sh)
	select_rows service method More...

	select_row (string ds, string table, *hash< auto > sh)
	select_row service method More...

int	insert (string ds, string table, softlist< auto > rl)
	insert service method More...

int	insert_from_select (string ds, string table, list< auto > cols, string source_table, hash< auto > sh)
	insert_from_select service method More...

int	update (string ds, string table, hash< auto > set, *hash< auto > cond)
	update service method More...

int	upsert_row (string ds, string table, hash< auto > row, int upsert_strategy=AbstractTable::UpsertAuto, *hash< auto > opt)
	upsert_row service method More...

	upsert (string ds, string table, softlist< auto > rows, int upsert_strategy=AbstractTable::UpsertAuto, *hash< auto > opt)
	upsert service method More...

int	del (string ds, string table, *hash< auto > cond)
	del service method More...

nothing	truncate_table (string ds, string table)
	truncate_table service method More...

	align_schema (string ds, hash< auto > schema_template, *hash< auto > opt)
	align_schema service method More...

	drop_schema (string ds, hash< auto > schema_template, *hash< auto > opt)
	drop_schema service method More...

	align_table (string ds, string table_name, hash< auto > table_template, *hash< auto > opt)
	align_table service method More...

	drop_table (string ds, string table, *hash< auto > opt)
	drop_table service method More...

	list_functions (string ds)
	list_functions service method More...

	list_procedures (string ds)
	list_procedures service method More...

	list_sequences (string ds)
	list_sequences service method More...

	list_tables (string ds)
	list_tables service method More...

bool	exists_table (string ds, string table)
	exists_table service method More...

	list_views (string ds)
	list_views service method More...

string	get_table_ddl (string ds, string table, *hash< auto > opt)
	get_table_ddl service method More...

	get_sequence_ddl (string ds, string name, *hash opt)
	get_sequence_ddl service method More...

	get_function_ddl (string ds, string name, *hash< auto > opt)
	get_function_ddl service method More...

	get_procedure_ddl (string ds, string name, *hash< auto > opt)
	get_procedure_ddl service method More...

	get_view_ddl (string ds, string name, *hash< auto > opt)
	get_view_ddl service method More...

auto	exec_sql (string ds, string sql)
	exec_sql service method More...

auto	exec_raw_sql (string ds, string sql)
	exec_raw_sql service method More...

	describe_table (string ds, string table)
	describe_table service method More...

	describe_query (string ds, string sql)
	describe_query service method More...

Static Public Member Methods
static	init ()
	init service method

Detailed Description

system sqlutil service

Member Function Documentation

◆ align_schema()

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

inline

align_schema service method

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()

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

inline

align_table service method

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()

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

inline

del service method

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()

QorusSystemSqlutilService::describe_query	(	string	ds,
		string	sql
	)

inline

describe_query service method

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()

QorusSystemSqlutilService::describe_table	(	string	ds,
		string	table
	)

inline

describe_table service method

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()

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

inline

drop_schema service method

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()

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

inline

drop_table service method

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()

auto QorusSystemSqlutilService::exec_raw_sql	(	string	ds,
		string	sql
	)

inline

exec_raw_sql service method

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()

auto QorusSystemSqlutilService::exec_sql	(	string	ds,
		string	sql
	)

inline

exec_sql service method

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()

bool QorusSystemSqlutilService::exists_table	(	string	ds,
		string	table
	)

inline

exists_table service method

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

Since: Qorus 4.1

◆ get_function_ddl()

QorusSystemSqlutilService::get_function_ddl	(	string	ds,
		string	name,
		*hash< auto >	opt
	)

inline

get_function_ddl service method

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ get_procedure_ddl()

QorusSystemSqlutilService::get_procedure_ddl	(	string	ds,
		string	name,
		*hash< auto >	opt
	)

inline

get_procedure_ddl service method

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ get_sequence_ddl()

QorusSystemSqlutilService::get_sequence_ddl	(	string	ds,
		string	name,
		*hash	opt
	)

inline

get_sequence_ddl service method

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ get_table_ddl()

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

inline

get_table_ddl service method

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ get_view_ddl()

QorusSystemSqlutilService::get_view_ddl	(	string	ds,
		string	name,
		*hash< auto >	opt
	)

inline

get_view_ddl service method

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ insert()

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

inline

insert service method

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()

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

inline

insert_from_select service method

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()

QorusSystemSqlutilService::list_functions ( string ds )

inline

list_functions service method

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ list_procedures()

QorusSystemSqlutilService::list_procedures ( string ds )

inline

list_procedures service method

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ list_sequences()

QorusSystemSqlutilService::list_sequences ( string ds )

inline

list_sequences service method

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ list_tables()

QorusSystemSqlutilService::list_tables ( string ds )

inline

list_tables service method

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ list_views()

QorusSystemSqlutilService::list_views ( string ds )

inline

list_views service method

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ select()

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

inline

select service method

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ select_row()

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

inline

select_row service method

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ select_rows()

QorusSystemSqlutilService::select_rows	(	string	ds,
		string	table,
		*hash< auto >	sh
	)

inline

select_rows service method

Note: requires permission DATASOURCE-CONTROL or SQLUTIL-READ

◆ truncate_table()

nothing QorusSystemSqlutilService::truncate_table	(	string	ds,
		string	table
	)

inline

truncate_table service method

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()

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

inline

update service method

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()

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

inline

upsert service method

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()

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

inline

upsert_row service method

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-v6.0.qsd

Public Member Methods

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()