QorusSystemSqlutilService Class Reference

system sqlutil service More...

Inherits QorusSystemService.

Public Member Methods
	select (string ds, string table, *hash< auto > sh)
	select service method
	select_rows (string ds, string table, *hash< auto > sh)
	select_rows service method
	select_row (string ds, string table, *hash< auto > sh)
	select_row service method
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
	list_procedures (string ds)
	list_procedures service method
	list_sequences (string ds)
	list_sequences service method
	list_tables (string ds)
	list_tables service method
bool	exists_table (string ds, string table)
	exists_table service method More...
	list_views (string ds)
	list_views service method
string	get_table_ddl (string ds, string table, *hash< auto > opt)
	get_table_ddl service method
	get_sequence_ddl (string ds, string name, *hash opt)
	get_sequence_ddl service method
	get_function_ddl (string ds, string name, *hash< auto > opt)
	get_function_ddl service method
	get_procedure_ddl (string ds, string name, *hash< auto > opt)
	get_procedure_ddl service method
	get_view_ddl (string ds, string name, *hash< auto > opt)
	get_view_ddl service method
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.

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.

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.

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

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.

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.

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.

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.

exists_table()

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

inline

exists_table service method

Since

Qorus 4.1

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.

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.

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.

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.

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

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

Since

Qorus 4.1

---

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

• sqlutil-v6.0.qsd