org.qore.lang.sqlutil.AbstractTable Class Reference

Java wrapper for the AbstractTable class in Qore. More...

Inheritance diagram for org.qore.lang.sqlutil.AbstractTable:

Public Member Methods
	AbstractTable (QoreObject obj)
	creates the object as a wrapper for the Qore object
String	getName () throws Throwable
	returns the name of the table
String	getDesc () throws Throwable
	returns a descriptive string of the datasource (without the password) and the table name (with a possible qualifier for schema, etc)
String	getBaseType () throws Throwable
	returns the base type of the underlying object (normally "table", some DB-specific implementations may support others like "view")
String	getSqlName () throws Throwable
	returns the name of the table to be used in SQL (with a possible qualifier for schema, etc)
String	getColumnSqlName (String col) throws Throwable
	returns the column name for use in SQL strings; subclasses can return a special string in case the column name is a reserved word
void	commit () throws Throwable
	commits the current transaction on the underlying org.qore.lang.AbstractDatasource
void	rollback () throws Throwable
	rolls back the current transaction on the underlying org.qore.lang.AbstractDatasource
boolean	inDb () throws Throwable
	returns true if the table has been read from or created in the database, false if not
void	drop (HashMap< String, Object > opt) throws Throwable
	drops the table from the database without any transaction management
void	drop () throws Throwable
	drops the table from the database without any transaction management
Object	tryExec (String sql) throws Throwable
	executes some SQL with optional arguments so that if an error occurs the current transaction state is not lost
Object	tryExecArgs (String sql, Object[] args) throws Throwable
	executes some SQL with optional arguments so that if an error occurs the current transaction state is not lost
Object	tryExecRaw (String sql) throws Throwable
	executes some SQL so that if an error occurs the current transaction state is not lost
String[]	getDropSql (Map< String, Object > opt) throws Throwable
	returns the sql required to drop the table; reimplement in subclasses if necessary
String[]	getDropSql () throws Throwable
	returns the sql required to drop the table; reimplement in subclasses if necessary
void	truncate () throws Throwable
	truncates all the table data without any transaction management
String	getTruncateSql (Map< String, Object > opt) throws Throwable
	gets the SQL that can be used to truncate the table
String	getTruncateSql () throws Throwable
	gets the SQL that can be used to truncate the table
void	create (Map< String, Object > opt) throws Throwable
	creates the table with all associated properties (indexes, constraints, etc) without any transaction management
void	create () throws Throwable
	creates the table with all associated properties (indexes, constraints, etc) without any transaction management
boolean	emptyData () throws Throwable
	returns true if the table has no data rows, false if not
boolean	empty () throws Throwable
	returns true if the table has no definitions, false if not
void	setupTable (Map< String, Object > desc, Map< String, Object > opt) throws Throwable
	creates the object from a table description hash
void	setupTable (Map< String, Object > desc) throws Throwable
	creates the object from a table description hash
void	addColumn (String cname, Map< String, Object > opt, boolean nullable) throws Throwable
	adds a column to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example
void	addColumn (String cname, Map< String, Object > opt) throws Throwable
	adds a nullable column to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example
String[]	getAddColumnSql (String cname, Map< String, Object > copt, boolean nullable, Map< String, Object > opt) throws Throwable
	returns a list of SQL strings that can be use to add a column to the table
String[]	getAddColumnSql (String cname, Map< String, Object > copt, boolean nullable) throws Throwable
	returns a list of SQL strings that can be use to add a column to the table
String[]	getAddColumnSql (String cname, Map< String, Object > copt) throws Throwable
	returns a list of SQL strings that can be use to add a nullable column to the table
String[]	getModifyColumnSql (String cname, Map< String, Object > copt, boolean nullable, Map< String, Object > opt) throws Throwable
	gets a list of SQL strings that can be used to modify an existing column in the table
String[]	getModifyColumnSql (String cname, Map< String, Object > copt, boolean nullable) throws Throwable
	gets a list of SQL strings that can be used to modify an existing column in the table
String[]	getModifyColumnSql (String cname, Map< String, Object > copt) throws Throwable
	gets a list of SQL strings that can be used to modify an existing column in the table
String	getRenameColumnSql (String old_name, String new_name, Map< String, Object > opt) throws Throwable
	gets an SQL String that can be used to rename an existing column in the table
String	getRenameColumnSql (String old_name, String new_name) throws Throwable
	gets an SQL String that can be used to rename an existing column in the table
void	addPrimaryKey (String pkname, String[] columns, Map< String, Object > opt) throws Throwable
	adds a primary key to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example
void	addPrimaryKey (String pkname, String[] columns) throws Throwable
	adds a primary key to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example
String	getAddPrimaryKeySql (String pkname, String[] cols, Map< String, Object > pkopt, Map< String, Object > opt) throws Throwable
	returns the SQL that can be used to add a primary key to the table
String	getAddPrimaryKeySql (String pkname, String[] cols, Map< String, Object > pkopt) throws Throwable
	returns the SQL that can be used to add a primary key to the table
String	getAddPrimaryKeySql (String pkname, String[] cols) throws Throwable
	returns the SQL that can be used to add a primary key to the table
String[]	getDropAllConstraintsAndIndexesOnColumnSql (String cname, Map< String, Object > opt) throws Throwable
	gets a list of SQL strings to drop all constraints and indexes with the given column name; if the column does not exist then an empty list is returned
String[]	getDropAllConstraintsAndIndexesOnColumnSql (String cname) throws Throwable
	gets a list of SQL strings to drop all constraints and indexes with the given column name; if the column does not exist then an empty list is returned
String[]	getDropPrimaryKeySql (Map< String, Object > opt) throws Throwable
	gets a list of SQL strings that can be used to drop the primary key from the table
String[]	getDropPrimaryKeySql () throws Throwable
	gets a list of SQL strings that can be used to drop the primary key from the table
void	addUniqueConstraint (String cname, String[] cols, Map< String, Object > opt) throws Throwable
	adds a unique constraint to the table; if the table is known to be in the database already, then the constraint is added to the database also immediately; otherwise it is only added internally and can be created when create() is called for example
String	getAddUniqueConstraintSql (String cname, String[] cols, Map< String, Object > ukopt, Map< String, Object > opt) throws Throwable
	returns an SQL String that can be used to add a unique constraint to the table
String	getAddUniqueConstraintSql (String cname, String[] cols, Map< String, Object > ukopt) throws Throwable
	returns an SQL String that can be used to add a unique constraint to the table
String	getAddUniqueConstraintSql (String cname, String[] cols) throws Throwable
	returns an SQL String that can be used to add a unique constraint to the table
void	addIndex (String iname, boolean unique, String[] cols, Map< String, Object > opt) throws Throwable
	adds an index to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example
void	addIndex (String iname, boolean unique, String[] cols) throws Throwable
	adds an index to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example
String	getAddIndexSql (String iname, boolean unique, String[] cols, Map< String, Object > ixopt, Map< String, Object > opt) throws Throwable
	returns an SQL String that can be used to add an index to the table
String	getAddIndexSql (String iname, boolean unique, String[] cols, Map< String, Object > ixopt) throws Throwable
	returns an SQL String that can be used to add an index to the table
String	getAddIndexSql (String iname, boolean unique, String[] cols) throws Throwable
	returns an SQL String that can be used to add an index to the table
String	getDropIndexSql (String iname, Map< String, Object > opt) throws Throwable
	gets the SQL that can be used to drop an index from the table
String	getDropIndexSql (String iname) throws Throwable
	gets the SQL that can be used to drop an index from the table
String	getAddForeignConstraintSql (String cname, String[] cols, String table, String[] tcols, Map< String, Object > fkopt, Map< String, Object > opt) throws Throwable
	returns an SQL String that can be used to add a foreign constraint to the table
String	getAddForeignConstraintSql (String cname, String[] cols, String table, String[] tcols, Map< String, Object > fkopt) throws Throwable
	returns an SQL String that can be used to add a foreign constraint to the table
String	getAddForeignConstraintSql (String cname, String[] cols, String table, String[] tcols) throws Throwable
	returns an SQL String that can be used to add a foreign constraint to the table
String	getAddForeignConstraintSql (String cname, String[] cols, String table) throws Throwable
	returns an SQL String that can be used to add a foreign constraint to the table
String	getAddCheckConstraintSql (String cname, String src, Map< String, Object > copt, Map< String, Object > opt) throws Throwable
	returns an SQL String that can be used to add a check constraint to the table
String	getAddCheckConstraintSql (String cname, String src, Map< String, Object > copt) throws Throwable
	returns an SQL String that can be used to add a check constraint to the table
String	getAddCheckConstraintSql (String cname, String src) throws Throwable
	returns an SQL String that can be used to add a check constraint to the table
String	getDropConstraintSql (String cname, Map< String, Object > opt) throws Throwable
	gets the SQL that can be used to drop a constraint from the table; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint
String	getDropConstraintSql (String cname) throws Throwable
	gets the SQL that can be used to drop a constraint from the table; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint
String	getDropConstraintIfExistsSql (String cname, Map< String, Object > opt) throws Throwable
	gets the SQL that can be used to drop a constraint from the table if it exists, otherwise returns null; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint
String	getDropConstraintIfExistsSql (String cname) throws Throwable
	gets the SQL that can be used to drop a constraint from the table if it exists, otherwise returns null; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint
String[]	getAddTriggerSql (String tname, String src, Map< String, Object > topt, Map< String, Object > opt) throws Throwable
	returns a list of SQL strings that can be used to add a trigger to the table
String[]	getAddTriggerSql (String tname, String src, Map< String, Object > topt) throws Throwable
	returns a list of SQL strings that can be used to add a trigger to the table
String[]	getAddTriggerSql (String tname, String src) throws Throwable
	returns a list of SQL strings that can be used to add a trigger to the table
String[]	getDropTriggerSql (String tname, Map< String, Object > opt) throws Throwable
	returns SQL that can be used to drop the given trigger from the table
String[]	getDropTriggerSql (String tname) throws Throwable
	returns SQL that can be used to drop the given trigger from the table
String[]	getDropColumnSql (String cname, Map< String, Object > opt) throws Throwable
	returns the SQL that can be used to drop a column from the table
String[]	getDropColumnSql (String cname) throws Throwable
	returns the SQL that can be used to drop a column from the table
HashMap< String, Object >	insert (Map< String, Object > row) throws Throwable
	inserts a row into the table without any transaction management; a transaction will be in progress after this method is successfully executed
HashMap< String, Object >	insert (Map< String, Object > row, Map< String, Object > opt) throws Throwable
	insert() variant
boolean	hasReturning () throws Throwable
	returns true if the current database driver supports the "returning" clause in insert statements, false if not
int	insertFromSelect (String[] cols, AbstractTable source, Map< String, Object > sh, Map< String, Object > opt) throws Throwable
	inserts rows into a table based on a select statement from another table (which must be using the same datasource as the current table); a transaction will be in progress after this method is successfully executed
int	insertFromSelect (String[] cols, AbstractTable source, Map< String, Object > sh) throws Throwable
	insertFromSelect() variant
int	insertFromSelect (String[] cols, AbstractTable source) throws Throwable
	insertFromSelect() variant
int	upsert (Map< String, Object > row, int upsert_strategy, Map< String, Object > opt) throws Throwable
	update or insert the data in the table according to the hash argument; the table must have a unique key to do this
int	upsert (Map< String, Object > row, int upsert_strategy) throws Throwable
	update or insert the data in the table according to the hash argument; the table must have a unique key to do this
HashMap< String, Object >	upsertFromSelect (AbstractTable t, Map< String, Object > sh, int upsert_strategy, Map< String, Object > opt) throws Throwable
	this method upserts or merges data from the given foreign table and select option hash into the current table; no transaction management is performed with this method
HashMap< String, Object >	upsertFromSelect (AbstractTable t, Map< String, Object > sh, int upsert_strategy) throws Throwable
	this method upserts or merges data from the given foreign table and select option hash into the current table; no transaction management is performed with this method
int	del (HashMap< String, Object > cond, HashMap< String, Object > opt) throws Throwable
	deletes rows in the table matching the condition and returns the count of rows deleted; no transaction management is performed with this method
int	del (HashMap< String, Object > cond) throws Throwable
	del() variant
int	del () throws Throwable
	del() variant
int	update (HashMap< String, Object > set, HashMap< String, Object > cond, HashMap< String, Object > opt) throws Throwable
	updates rows in the table matching an optional condition and returns the count of rows updated; no transaction management is performed with this method
int	update (HashMap< String, Object > set, HashMap< String, Object > cond) throws Throwable
	A update() variant.
int	update (HashMap< String, Object > set) throws Throwable
	A update() variant.
HashMap< String, Object >	upsertFromSelect (AbstractTable t, Map< String, Object > sh) throws Throwable
	this method upserts or merges data from the given foreign table and select option hash into the current table; no transaction management is performed with this method
int	rowCount () throws Throwable
	returns the number of rows in the table
AbstractSQLStatement	getStatement (Map< String, Object > sh, Map< String, Object > opt) throws Throwable
	returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments
AbstractSQLStatement	getStatement (Map< String, Object > sh) throws Throwable
	returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments
AbstractSQLStatement	getStatement () throws Throwable
	returns an AbstractSQLStatement object that will iterate all the rows in the table
AbstractSQLStatement	getStatementNoExec (Map< String, Object > sh, Map< String, Object > opt) throws Throwable
	returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments; the statement is only prepared and not executed
AbstractSQLStatement	getStatementNoExec (Map< String, Object > sh) throws Throwable
	returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments; the statement is only prepared and not executed
AbstractSQLStatement	getStatementNoExec () throws Throwable
	returns an AbstractSQLStatement object that will iterate all the rows in the table; the statement is only prepared and not executed
HashMap< String, Object >	selectRow (Map< String, Object > sh, Map< String, Object > opt) throws Throwable
	returns a hash representing the row in the table that matches the argument hash; if more than one row would be returned an exception is raised
HashMap< String, Object >	selectRow (Map< String, Object > sh) throws Throwable
	returns a hash representing the row in the table that matches the argument hash; if more than one row would be returned an exception is raised
HashMap< String, Object >[]	selectRows (Map< String, Object > sh, Map< String, Object > opt) throws Throwable
	returns a list of hashes representing the rows in the table that match the argument hash
HashMap< String, Object >[]	selectRows (Map< String, Object > sh) throws Throwable
	returns a list of hashes representing the rows in the table that match the argument hash
HashMap< String, Object >[]	selectRows () throws Throwable
	returns a list of hashes representing the rows in the table that match the argument hash
HashMap< String, Object >	select (Map< String, Object > sh, Map< String, Object > opt) throws Throwable
	returns a hash of lists representing the columns and rows in the table that match the argument hahs
HashMap< String, Object >	select (Map< String, Object > sh) throws Throwable
	returns a hash of lists representing the columns and rows in the table that match the argument hahs
HashMap< String, Object >	select () throws Throwable
	returns a hash of lists representing the columns and rows in the table that match the argument hahs
Public Member Methods inherited from org.qore.lang.sqlutil.AbstractSqlUtilBase
	AbstractSqlUtilBase (QoreObject obj)
	creates the object from a weak reference to the Qore object
AbstractDatasource	getDatasource () throws Throwable
	gets the underlying AbstractDatasource
String	getDriverName () throws Throwable
	returns the database driver name
String	getDatasourceDesc () throws Throwable
	returns a descriptive string for the datasource

Static Public Member Methods
static HashMap< String, Object >	make_cop (String cop, Object column, Object arg) throws Throwable
	returns a ColumnOperatorInfo hash
static HashMap< String, Object >	make_cop (String cop, Object column) throws Throwable
	returns a ColumnOperatorInfo hash
static HashMap< String, Object >	cop_as (Object column, String arg) throws Throwable
	returns a ColumnOperatorInfo hash for the "as" operator with the given argument
static HashMap< String, Object >	cop_cast (Object column, String arg, Object arg1, Object arg2) throws Throwable
	returns a ColumnOperatorInfo hash for the "cast" operator with the given argument(s)
static HashMap< String, Object >	cop_cast (Object column, String arg, Object arg1) throws Throwable
	returns a ColumnOperatorInfo hash for the "cast" operator with the given argument(s)
static HashMap< String, Object >	cop_cast (Object column, String arg) throws Throwable
	returns a ColumnOperatorInfo hash for the "cast" operator with the given argument(s)
static HashMap< String, Object >	cop_prepend (Object column, String arg) throws Throwable
	returns a ColumnOperatorInfo hash for the "prepend" operator with the given argument
static HashMap< String, Object >	cop_append (Object column, String arg) throws Throwable
	returns a ColumnOperatorInfo hash for the "append" operator with the given argument
static HashMap< String, Object >	cop_value (Object arg) throws Throwable
	returns a ColumnOperatorInfo hash for the "value" (literal) operator with the given argument
static HashMap< String, Object >	cop_upper (Object column) throws Throwable
	returns a ColumnOperatorInfo hash for the "upper" operator with the given argument; returns a column value in upper case
static HashMap< String, Object >	cop_lower (Object column) throws Throwable
	returns a ColumnOperatorInfo hash for the "lower" operator with the given argument; returns a column value in lower case
static HashMap< String, Object >	cop_distinct (Object column) throws Throwable
	returns a ColumnOperatorInfo hash for the "distinct" operator with the given argument; returns distinct column values
static HashMap< String, Object >	cop_min (Object column) throws Throwable
	returns a ColumnOperatorInfo hash for the "min" operator; returns minimum column values
static HashMap< String, Object >	cop_max (Object column) throws Throwable
	returns a ColumnOperatorInfo hash for the "max" operator; returns maximum column values
static HashMap< String, Object >	cop_avg (Object column) throws Throwable
	returns a ColumnOperatorInfo hash for the "avg" operator; returns average column values
static HashMap< String, Object >	cop_sum (Object column) throws Throwable
	returns a ColumnOperatorInfo hash for the "sum" operator; returns the total sum of a numeric column.
static HashMap< String, Object >	cop_count (Object column) throws Throwable
	returns a ColumnOperatorInfo hash for the "count" operator; returns row counts
static HashMap< String, Object >	cop_count () throws Throwable
	returns a ColumnOperatorInfo hash for the "count" operator; returns row counts
static HashMap< String, Object >	cop_over (Object column, String partitionby, String orderby) throws Throwable
	returns a ColumnOperatorInfo hash for the "over" clause
static HashMap< String, Object >	cop_over (Object column, String partitionby) throws Throwable
	returns a ColumnOperatorInfo hash for the "over" clause
static HashMap< String, Object >	cop_over (Object column) throws Throwable
	returns a ColumnOperatorInfo hash for the "over" clause
static HashMap< String, Object >	cop_minus (Object column1, Object column2) throws Throwable
	returns a ColumnOperatorInfo hash for the "-" operator with the given arguments
static HashMap< String, Object >	cop_plus (Object column1, Object column2) throws Throwable
	returns a ColumnOperatorInfo hash for the "+" operator with the given arguments
static HashMap< String, Object >	cop_divide (Object column1, Object column2) throws Throwable
	returns a ColumnOperatorInfo hash for the "/" operator with the given arguments
static HashMap< String, Object >	cop_multiply (Object column1, Object column2) throws Throwable
	returns a ColumnOperatorInfo hash for the "*" operator with the given arguments
static HashMap< String, Object >	cop_year (Object column) throws Throwable
	returns a ColumnOperatorInfo hash for the "year" operator with the given argument
static HashMap< String, Object >	cop_year_month (Object column) throws Throwable
	returns a ColumnOperatorInfo hash for the "year_month" operator with the given argument
static HashMap< String, Object >	cop_year_day (Object column) throws Throwable
	returns a ColumnOperatorInfo hash for the "year_day" operator with the given argument
static HashMap< String, Object >	cop_year_hour (Object column) throws Throwable
	returns a ColumnOperatorInfo hash for the "year_hour" operator with the given argument
static HashMap< String, Object >	cop_seq (String seq, String as) throws Throwable
	returns a ColumnOperatorInfo hash for the "seq" operator with the given argument giving the sequence name whose value should be returned
static HashMap< String, Object >	cop_seq (String seq) throws Throwable
	returns a ColumnOperatorInfo hash for the "seq" operator with the given argument giving the sequence name whose value should be returned
static HashMap< String, Object >	cop_seq_currval (String seq, String as) throws Throwable
	returns a ColumnOperatorInfo hash for the "seq_currval" operator with the given argument giving the sequence name whose current value should be returned
static HashMap< String, Object >	cop_seq_currval (String seq) throws Throwable
	returns a ColumnOperatorInfo hash for the "seq_currval" operator with the given argument giving the sequence name whose current value should be returned
static HashMap< String, Object >	cop_coalesce (Object col1, Object col2) throws Throwable
	returns a ColumnOperatorInfo hash for the "coalesce" operator with the given column arguments; the first non-NULL column value will be returned
static HashMap< String, Object >	cop_coalesce (Object col1, Object col2, Object... args) throws Throwable
	returns a ColumnOperatorInfo hash for the "coalesce" operator with the given column arguments; the first non-NULL column value will be returned
static HashMap< String, Object >	cop_substr (Object column, int start, int count) throws Throwable
	returns a ColumnOperatorInfo hash for the "substr" operator with the given arguments; returns a substring of a column value
static HashMap< String, Object >	cop_substr (Object column, long start, long count) throws Throwable
	returns a ColumnOperatorInfo hash for the "substr" operator with the given arguments; returns a substring of a column value
static HashMap< String, Object >	cop_substr (Object column, int start) throws Throwable
	returns a ColumnOperatorInfo hash for the "substr" operator with the given arguments; returns a substring of a column value
static HashMap< String, Object >	cop_substr (Object column, long start) throws Throwable
	returns a ColumnOperatorInfo hash for the "substr" operator with the given arguments; returns a substring of a column value
static HashMap< String, Object >	cop_length (Object column) throws Throwable
	returns a ColumnOperatorInfo hash for the "len" operator with the given argument; returns the length of the given text field
static HashMap< String, Object >	cop_trunc_date (Object column, String mask) throws Throwable
	Truncates a date column or value regarding the given mask. The resulting value remains Qore::date (no conversion to eg. string)
static HashMap< String, Object >	cop_cume_dist () throws Throwable
	Analytic/window method: relative rank of the current row.
static HashMap< String, Object >	cop_dense_rank () throws Throwable
	Analytic/window method: rank of the current row without gaps.
static HashMap< String, Object >	cop_first_value (Object column) throws Throwable
	Analytic/window method: value evaluated at the row that is the first row of the window frame.
static HashMap< String, Object >	cop_last_value (Object column) throws Throwable
	Analytic/window method: value evaluated at the row that is the last row of the window frame.
static HashMap< String, Object >	cop_ntile (int value) throws Throwable
	Analytic/window method: integer ranging from 1 to the argument value, dividing the partition as equally as possible.
static HashMap< String, Object >	cop_ntile (long value) throws Throwable
	Analytic/window method: integer ranging from 1 to the argument value, dividing the partition as equally as possible.
static HashMap< String, Object >	cop_percent_rank () throws Throwable
	Analytic/window method: relative rank of the current row.
static HashMap< String, Object >	cop_rank () throws Throwable
	Analytic/window method: rank of the current row with gaps.
static HashMap< String, Object >	cop_row_number () throws Throwable
	Analytic/window method: number of the current row within its partition, counting from 1.

Static Public Attributes
static int	UpsertInsertFirst = 1
	Upsert option: insert first, if the insert fails, then update.
static int	UpsertUpdateFirst = 2
	Upsert option: update first, if the update fails, then insert.
static int	UpsertSelectFirst = 3
	Upsert option: select first, if the row is unchanged, do nothing, if it doesn't exist, insert, otherwise update.
static int	UpsertAuto = 4
	Upsert option: if the target table is empty, use UpsertInsertFirst, otherwise use UpsertUpdateFirst.
static int	UpsertInsertOnly = 5
	Upsert option: insert if the row does not exist, otherwise ignore.
static int	UpsertUpdateOnly = 6
	Upsert option: update if the row exists, otherwise ignore.
static final Map< Integer, String >	UpsertStrategyMap
	hash mapping upsert strategy codes to a text description
static final Map< String, Integer >	UpsertStrategyDescriptionMap
	hash mapping upsert strategy descriptions to upsert strategy codes
static final String	DT_YEAR = "Y"
	Format unit: year.
static final String	DT_MONTH = "M"
	Format unit: month.
static final String	DT_DAY = "D"
	Format unit: day.
static final String	DT_HOUR = "H"
	Format unit: hour.
static final String	DT_MINUTE = "m"
	Format unit: minute.
static final String	DT_SECOND = "S"
	Format unit: hour.
static int	UR_Inserted = 1
	row was inserted
static int	UR_Verified = 2
	row was updated unconditionally (not returned with UpsertSelectFirst)
static int	UR_Updated = 3
	row was updated because it was different (only possible with UpsertSelectFirst)
static int	UR_Unchanged = 4
	row was unchanged (only possible with UpsertSelectFirst, UpsertInsertOnly, and UpsertUpdateOnly)
static int	UR_Deleted = 5
	row was deleted (only possible with batch upsert methods such as upsertFromIterator() where upsert option delete_others is true)
static final Map< Integer, String >	UpsertResultMap
	hash mapping upsert results to a description
static final Map< String, Integer >	UpsertResultDescriptionMap
	hash mapping upsert descriptions to codes
static final Map< Integer, String >	UpsertResultLetterMap
	maps upsert result codes to single letter symbols

Detailed Description

Java wrapper for the AbstractTable class in Qore.

Callback Options

The following keys can be set for this option:

• info_callback: see info_callback
• sql_callback: see sql_callback
• sql_callback_executed: see sql_callback_executed

Creation Options

This option is comprised of Callback Options plus the following keys:

• replace: (coolean) if true and supported by the underlying db driver, "create or replace" text is used when creating objects
• table_cache: (Tables) an optional table cache for maintaining cached tables and foreign key relationships between tables
• data_tablespace: (String) a string giving the data tablespace to use for tables
• index_tablespace: (String) a string giving the index tablespace to use for indexes

Index Options

• index_tablespace: (String) a string giving the index tablespace to use for indexes
• replace: (boolean) if true and supported by the underlying db driver "create or replace" text is used when creating objects

Table Creation Options

currently this option is a combination of Index Options and Creation Options plus the following:

• omit: a list pf attributes to omit; possible values are: indexes, foreign_constraints, triggers

Align Table Options

Currently this option is a combination of Table Creation Options and the following options:

• column_map: (Map) a hash for automatically renaming columns; if the source name (key) exists and the target name (value) does not exist, then the source column is automatically renamed
• index_map: (Map) a hash for automatically renaming indexes; if the source name (key) exists and the target name (value) does not exist, then the source index is automatically renamed
• constraint_map: (Map) a hash for automatically renaming constraints; if the source name (key) exists and the target name (value) does not exist, then the source constraint is automatically renamed
• trigger_map: (Map) a hash for automatically renaming triggers; if the source name (key) exists and the target name (value) does not exist, then the source trigger is automatically renamed
• db_table_cache: (Tables) an optional table cache for maintaining tables in the database and foreign key relationships between tables
• force: (boolean) if true and supported by the driver and object, any objects dropped will be dropped with FORCE or CASCADE options

Insert Options

In addition to any SqlDataCallbackOptions, the following keys can be set for this option:

• returning: a list having elements of one of the two following types:
  • String: column names to return the value inserted
  • Map: a hash having the following keys:
  • "key": (required) the column name to return
  • "type": (optional) the data type for the output placeholder buffer (ex: number)

Note

using "returning" with a database that does not support this clause will cause an exception to be thrown; see AbstractTable.hasReturning()

Upsert Options

The following keys can be set for upsert options:

• commit_block: the number of changes made before an automatic commit is made for upsert methods that perform commits
• delete_others: if this option is true, then a hash of primary key values in the input data is built as the input data is iterated. After iterating, if the row count of the table and the input data matches, then nothing more is done, otherwise, every row of the table is iterated and compared to the primary key hash; if a row does not match a primary key value, then it is deleted. This operation allows tables to be completely synchronized by removing rows in the target table not present in the source table. This operation is expensive for large data sets.
• info_callback: see upsert_info_callback

• omit_update: allows for an asymmetrical upsert where a set of column values is inserted, but a smaller set is updated in case the unique key values are present in the target table; the value of this key should be set to the columns to omit in the update clause

  Deprecated:

  Use dynamic imports instead: import qoremod.SqlUtil.AbstractTable;

Member Function Documentation

addColumn() [1/2]

void org.qore.lang.sqlutil.AbstractTable.addColumn ( String  cname, Map< String, Object >  opt  ) throws Throwable

inline

adds a nullable column to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example

Example:

table.addColumn("name", column_hash, false);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname

the name of the column

opt

a hash describing the column; the following keys are permitted (other column options may be supported depending on the underlying AbstractTable implementation): qore_type: a qore type string that will be converted to a native DB type with some default conversion; native_type: the native database column type; if both native_type and qore_type are given then native_type is used size: for data types requiring a size component, the size; for numeric columns this represents the precision for example scale: for numeric data types, this value gives the scale default_value: the default value for the column default_value_native: a boolean flag to say if a default_value should be validated against table column type (False) or used as it is (True) to allow to use DBMS native methods or features. Defaults to False. It is strongly recommended to use default_value_native for default_value in driver specific sub-hash to avoid non-portable schema hashes

Exceptions

COLUMN-ERROR

no native_type or qore_type keys in column option hash, column already exists, invalid column data

Note

make sure and add a default_value value when adding a column with a "not null" constraint with existing data

See also

inDb() for a method that tells if the table is already in the database or not

addColumn() [2/2]

void org.qore.lang.sqlutil.AbstractTable.addColumn ( String  cname, Map< String, Object >  opt, boolean  nullable  ) throws Throwable

inline

adds a column to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example

Example:

table.addColumn("name", column_hash, false);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the column
opt	a hash describing the column; the following keys are permitted (other column options may be supported depending on the underlying AbstractTable implementation): qore_type: a qore type string that will be converted to a native DB type with some default conversion; native_type: the native database column type; if both native_type and qore_type are given then native_type is used size: for data types requiring a size component, the size; for numeric columns this represents the precision for example scale: for numeric data types, this value gives the scale default_value: the default value for the column default_value_native: a boolean flag to say if a default_value should be validated against table column type (False) or used as it is (True) to allow to use DBMS native methods or features. Defaults to False. It is strongly recommended to use default_value_native for default_value in driver specific sub-hash to avoid non-portable schema hashes
nullable	if true then the column can hold NULL values; note that primary key columns cannot be nullable

Exceptions

COLUMN-ERROR

no native_type or qore_type keys in column option hash, column already exists, invalid column data

Note

make sure and add a default_value value when adding a column with a "not null" constraint with existing data

See also

inDb() for a method that tells if the table is already in the database or not

addIndex() [1/2]

void org.qore.lang.sqlutil.AbstractTable.addIndex ( String  iname, boolean  unique, String[]  cols  ) throws Throwable

inline

adds an index to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example

Example:

table.addIndex("uk_mytable_name", true, new String[]{"name"});

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

iname	the name of the new index
unique	a flag to tell if the new index should be unique or not
cols	a single column name or a list of columns that make up the index

Exceptions

INDEX-ERROR

the table already has an index with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

addIndex() [2/2]

void org.qore.lang.sqlutil.AbstractTable.addIndex ( String  iname, boolean  unique, String[]  cols, Map< String, Object >  opt  ) throws Throwable

inline

adds an index to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example

Example:

table.addIndex("uk_mytable_name", true, new String[]{"name"}, opt);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

iname	the name of the new index
unique	a flag to tell if the new index should be unique or not
cols	a single column name or a list of columns that make up the index
opt	a hash of options for the new index; each driver may implement its own options; for common options, see AbstractTable::IndexOptions

Exceptions

INDEX-ERROR

the table already has an index with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

addPrimaryKey() [1/2]

void org.qore.lang.sqlutil.AbstractTable.addPrimaryKey ( String  pkname, String[]  columns  ) throws Throwable

inline

adds a primary key to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example

Example:

table.addPrimaryKey("pk_mytable", "id");

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

pkname	the name of the new primary key constraint
columns	a single column name or a list of columns that make up the primary key

Exceptions

PRIMARY-KEY-ERROR

the table already has a primary key or invalid columns or options passed

See also

inDb() for a method that tells if the table is already in the database or not

addPrimaryKey() [2/2]

void org.qore.lang.sqlutil.AbstractTable.addPrimaryKey ( String  pkname, String[]  columns, Map< String, Object >  opt  ) throws Throwable

inline

adds a primary key to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example

Example:

table.addPrimaryKey("pk_mytable", "id");

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

pkname	the name of the new primary key constraint
columns	a single column name or a list of columns that make up the primary key
opt	a hash of options for the new primary key; each driver may implement its own options; for common options, see AbstractTable::ConstraintOptions

Exceptions

PRIMARY-KEY-ERROR

the table already has a primary key or invalid columns or options passed

See also

inDb() for a method that tells if the table is already in the database or not

addUniqueConstraint()

void org.qore.lang.sqlutil.AbstractTable.addUniqueConstraint ( String  cname, String[]  cols, Map< String, Object >  opt  ) throws Throwable

inline

adds a unique constraint to the table; if the table is known to be in the database already, then the constraint is added to the database also immediately; otherwise it is only added internally and can be created when create() is called for example

Example:

table.addUniqueConstraint("uk_mytable", "name", opt);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the new unique constraint
cols	a single column name or a list of columns that make up the unique constraint
opt	a hash of options for the new unique constraint; each driver may implement its own options; for common options, see AbstractTable::ConstraintOptions

Returns

an AbstractUniqueConstraint object corresponding to the unique constraint created

Exceptions

UNIQUE-CONSTRAINT-ERROR	the table already has a constraint with the given name or invalid columns passed
OPTION-ERROR	invalid or unsupported option passed

See also

inDb() for a method that tells if the table is already in the database or not

create() [1/2]

void org.qore.lang.sqlutil.AbstractTable.create ( ) throws Throwable

inline

creates the table with all associated properties (indexes, constraints, etc) without any transaction management

Example:

table.create();

Note

Transaction management is normally not performed when creating tables, however this method uses the org.qore.lang.AbstractDatasource.exec() method, which normally participates in acquiring a transaction lock for the underlying datasource object; therefore after this method executes normally the transaction lock will be dedicated to the calling thread.

Exceptions

CREATE-TABLE-ERROR

table has already been read from or created in the database

create() [2/2]

void org.qore.lang.sqlutil.AbstractTable.create ( Map< String, Object >  opt ) throws Throwable

inline

creates the table with all associated properties (indexes, constraints, etc) without any transaction management

Example:

table.create();

Parameters

opt	a hash of options for the SQL creation strings

Note

Transaction management is normally not performed when creating tables, however this method uses the org.qore.lang.AbstractDatasource.exec() method, which normally participates in acquiring a transaction lock for the underlying datasource object; therefore after this method executes normally the transaction lock will be dedicated to the calling thread.

Exceptions

CREATE-TABLE-ERROR

table has already been read from or created in the database

del()

int org.qore.lang.sqlutil.AbstractTable.del ( HashMap< String, Object >  cond, HashMap< String, Object >  opt  ) throws Throwable

inline

deletes rows in the table matching the condition and returns the count of rows deleted; no transaction management is performed with this method

Example:

int dcnt = table.del(cond_hash);

Parameters

cond	a hash of conditions for the where clause; see where_clauses for more information
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

the count of rows deleted

Exceptions

WHERE-ERROR

unknown operator or invalid arguments given in the cond hash for the where clause

drop() [1/2]

void org.qore.lang.sqlutil.AbstractTable.drop ( ) throws Throwable

inline

drops the table from the database without any transaction management

Example:

table.drop();

Exceptions

OPTION-ERROR

invalid or unknown callback option

Note

Transaction management is normally not performed when dropping tables, however this method uses the org.qore.lang.AbstractDatasource.exec() method, which normally participates in acquiring a transaction lock for the underlying datasource object; therefore after this method executes normally the transaction lock will be dedicated to the calling thread.

drop() [2/2]

void org.qore.lang.sqlutil.AbstractTable.drop ( HashMap< String, Object >  opt ) throws Throwable

inline

drops the table from the database without any transaction management

Example:

table.drop();

Parameters

opt	optional callback options; see Callback Options for more info

Exceptions

OPTION-ERROR

invalid or unknown callback option

Note

Transaction management is normally not performed when dropping tables, however this method uses the org.qore.lang.AbstractDatasource.exec() method, which normally participates in acquiring a transaction lock for the underlying datasource object; therefore after this method executes normally the transaction lock will be dedicated to the calling thread.

empty()

boolean org.qore.lang.sqlutil.AbstractTable.empty ( ) throws Throwable

inline

returns true if the table has no definitions, false if not

Example:

boolean b = table.empty();

Returns

true if the table has no definitions, false if not

See also

emptyData()

emptyData()

boolean org.qore.lang.sqlutil.AbstractTable.emptyData ( ) throws Throwable

inline

returns true if the table has no data rows, false if not

Example:

boolean b = table.emptyData();

Returns

true if the table has no data rows, false if not

See also

• empty()
• rowCount()

getAddCheckConstraintSql() [1/3]

String org.qore.lang.sqlutil.AbstractTable.getAddCheckConstraintSql ( String  cname, String  src  ) throws Throwable

inline

returns an SQL String that can be used to add a check constraint to the table

Example:

String sql = table.getAddCheckConstraintSql("check_mytable_id", "id > 10");

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the new constraint
src	the source of the constraint clause
copt	a hash of options for the new constraint; each driver may implement its own options; for common options, see ConstraintOptions
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

an SQL String that can be used to add a check constraint to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
CHECK-CONSTRAINT-ERROR	the table already has a constraint with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddCheckConstraintSql() [2/3]

String org.qore.lang.sqlutil.AbstractTable.getAddCheckConstraintSql ( String  cname, String  src, Map< String, Object >  copt  ) throws Throwable

inline

returns an SQL String that can be used to add a check constraint to the table

Example:

String sql = table.getAddCheckConstraintSql("check_mytable_id", "id > 10");

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the new constraint
src	the source of the constraint clause
copt	a hash of options for the new constraint; each driver may implement its own options; for common options, see ConstraintOptions
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

an SQL String that can be used to add a check constraint to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
CHECK-CONSTRAINT-ERROR	the table already has a constraint with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddCheckConstraintSql() [3/3]

String org.qore.lang.sqlutil.AbstractTable.getAddCheckConstraintSql ( String  cname, String  src, Map< String, Object >  copt, Map< String, Object >  opt  ) throws Throwable

inline

returns an SQL String that can be used to add a check constraint to the table

Example:

String sql = table.getAddCheckConstraintSql("check_mytable_id", "id > 10");

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the new constraint
src	the source of the constraint clause
copt	a hash of options for the new constraint; each driver may implement its own options; for common options, see ConstraintOptions
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

an SQL String that can be used to add a check constraint to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
CHECK-CONSTRAINT-ERROR	the table already has a constraint with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddColumnSql() [1/3]

String[] org.qore.lang.sqlutil.AbstractTable.getAddColumnSql ( String  cname, Map< String, Object >  copt  ) throws Throwable

inline

returns a list of SQL strings that can be use to add a nullable column to the table

Example:

String[] l = table.getAddColumnSql("name", copt);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname

the name of the column

copt

a hash describing the column; the following keys are permitted (other column options may be supported depending on the underlying AbstractTable implementation): qore_type: a qore type String that will be converted to a native DB type with some default conversion; native_type: the native database column type; if both native_type and qore_type are given then native_type is used size: for data types requiring a size component, the size; for numeric columns this represents the precision for example scale: for numeric data types, this value gives the scale default_value: the default value for the column default_value_native: a boolean flag to say if a default_value should be validated against table column type (false) or used as it is (true) to allow to use DBMS native methods or features. Defaults to false. It is strongly recommended to use default_value_native for default_value in driver specific sub-hash to avoid non-portable schema hashes

Returns

a list of SQL strings that can be use to add a column to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
COLUMN-ERROR	no native_type or qore_type keys in column option hash, column already exists, invalid column data

Note

• make sure and add a default_value value when adding a column with a "not null" constraint with existing data
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getAddColumnSql() [2/3]

String[] org.qore.lang.sqlutil.AbstractTable.getAddColumnSql ( String  cname, Map< String, Object >  copt, boolean  nullable  ) throws Throwable

inline

returns a list of SQL strings that can be use to add a column to the table

Example:

String[] l = table.getAddColumnSql("name", copt, false);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the column
copt	a hash describing the column; the following keys are permitted (other column options may be supported depending on the underlying AbstractTable implementation): qore_type: a qore type String that will be converted to a native DB type with some default conversion; native_type: the native database column type; if both native_type and qore_type are given then native_type is used size: for data types requiring a size component, the size; for numeric columns this represents the precision for example scale: for numeric data types, this value gives the scale default_value: the default value for the column default_value_native: a boolean flag to say if a default_value should be validated against table column type (false) or used as it is (true) to allow to use DBMS native methods or features. Defaults to false. It is strongly recommended to use default_value_native for default_value in driver specific sub-hash to avoid non-portable schema hashes
nullable	if true then the column can hold NULL values; note that primary key columns cannot be nullable

Returns

a list of SQL strings that can be use to add a column to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
COLUMN-ERROR	no native_type or qore_type keys in column option hash, column already exists, invalid column data

Note

• make sure and add a default_value value when adding a column with a "not null" constraint with existing data
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getAddColumnSql() [3/3]

String[] org.qore.lang.sqlutil.AbstractTable.getAddColumnSql ( String  cname, Map< String, Object >  copt, boolean  nullable, Map< String, Object >  opt  ) throws Throwable

inline

returns a list of SQL strings that can be use to add a column to the table

Example:

String[] l = table.getAddColumnSql("name", copt, false);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the column
copt	a hash describing the column; the following keys are permitted (other column options may be supported depending on the underlying AbstractTable implementation): qore_type: a qore type String that will be converted to a native DB type with some default conversion; native_type: the native database column type; if both native_type and qore_type are given then native_type is used size: for data types requiring a size component, the size; for numeric columns this represents the precision for example scale: for numeric data types, this value gives the scale default_value: the default value for the column default_value_native: a boolean flag to say if a default_value should be validated against table column type (false) or used as it is (true) to allow to use DBMS native methods or features. Defaults to false. It is strongly recommended to use default_value_native for default_value in driver specific sub-hash to avoid non-portable schema hashes
nullable	if true then the column can hold NULL values; note that primary key columns cannot be nullable
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

a list of SQL strings that can be use to add a column to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
COLUMN-ERROR	no native_type or qore_type keys in column option hash, column already exists, invalid column data

Note

• make sure and add a default_value value when adding a column with a "not null" constraint with existing data
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getAddForeignConstraintSql() [1/4]

String org.qore.lang.sqlutil.AbstractTable.getAddForeignConstraintSql ( String  cname, String[]  cols, String  table  ) throws Throwable

inline

returns an SQL String that can be used to add a foreign constraint to the table

Example:

String sql = table.getAddForeignConstraintSql("fk_mytable_other_table", cols, "other_table");

Parameters

cname	the name of the new foreign constraint
cols	a single column name or a list of columns in the local table that make up the foreign constraint
table	the name of the other table that the constraint targets

Returns

an SQL String that can be used to add a foreign constraint to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
FOREIGN-CONSTRAINT-ERROR	the table already has a constraint with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddForeignConstraintSql() [2/4]

String org.qore.lang.sqlutil.AbstractTable.getAddForeignConstraintSql ( String  cname, String[]  cols, String  table, String[]  tcols  ) throws Throwable

inline

returns an SQL String that can be used to add a foreign constraint to the table

Example:

String sql = table.getAddForeignConstraintSql("fk_mytable_other_table", cols, "other_table");

Parameters

cname	the name of the new foreign constraint
cols	a single column name or a list of columns in the local table that make up the foreign constraint
table	the name of the other table that the constraint targets
tcols	a single column name or a list of columns in the foreign table or null meaning that the column names are the same as in the local table; if column names are given the same number of columns must be given in the local and foreign tables

Returns

an SQL String that can be used to add a foreign constraint to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
FOREIGN-CONSTRAINT-ERROR	the table already has a constraint with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddForeignConstraintSql() [3/4]

String org.qore.lang.sqlutil.AbstractTable.getAddForeignConstraintSql ( String  cname, String[]  cols, String  table, String[]  tcols, Map< String, Object >  fkopt  ) throws Throwable

inline

returns an SQL String that can be used to add a foreign constraint to the table

Example:

String sql = table.getAddForeignConstraintSql("fk_mytable_other_table", cols, "other_table");

Parameters

cname	the name of the new foreign constraint
cols	a single column name or a list of columns in the local table that make up the foreign constraint
table	the name of the other table that the constraint targets
tcols	a single column name or a list of columns in the foreign table or null meaning that the column names are the same as in the local table; if column names are given the same number of columns must be given in the local and foreign tables
fkopt	a hash of options for the new foreign constraint; each driver may implement its own options; for common options, see ForeignConstraintOptions

Returns

an SQL String that can be used to add a foreign constraint to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
FOREIGN-CONSTRAINT-ERROR	the table already has a constraint with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddForeignConstraintSql() [4/4]

String org.qore.lang.sqlutil.AbstractTable.getAddForeignConstraintSql ( String  cname, String[]  cols, String  table, String[]  tcols, Map< String, Object >  fkopt, Map< String, Object >  opt  ) throws Throwable

inline

returns an SQL String that can be used to add a foreign constraint to the table

Example:

String sql = table.getAddForeignConstraintSql("fk_mytable_other_table", ("name", "version"), "other_table");

Parameters

cname	the name of the new foreign constraint
cols	a single column name or a list of columns in the local table that make up the foreign constraint
table	the name of the other table that the constraint targets
tcols	a single column name or a list of columns in the foreign table or null meaning that the column names are the same as in the local table; if column names are given the same number of columns must be given in the local and foreign tables
fkopt	a hash of options for the new foreign constraint; each driver may implement its own options; for common options, see ForeignConstraintOptions
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

an SQL String that can be used to add a foreign constraint to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
FOREIGN-CONSTRAINT-ERROR	the table already has a constraint with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddIndexSql() [1/3]

String org.qore.lang.sqlutil.AbstractTable.getAddIndexSql ( String  iname, boolean  unique, String[]  cols  ) throws Throwable

inline

returns an SQL String that can be used to add an index to the table

Example:

String sql = table.getAddIndexSql("uk_mytable_name", true, "name", ixopt);

Parameters

iname	the name of the new index
unique	a flag to tell if the new index should be unique or not
cols	a single column name or a list of columns that make up the index

Returns

an SQL String that can be used to add an index to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
INDEX-ERROR	the table already has an index with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddIndexSql() [2/3]

String org.qore.lang.sqlutil.AbstractTable.getAddIndexSql ( String  iname, boolean  unique, String[]  cols, Map< String, Object >  ixopt  ) throws Throwable

inline

returns an SQL String that can be used to add an index to the table

Example:

String sql = table.getAddIndexSql("uk_mytable_name", true, "name", ixopt);

Parameters

iname	the name of the new index
unique	a flag to tell if the new index should be unique or not
cols	a single column name or a list of columns that make up the index
ixopt	a hash of options for the new index; each driver may implement its own options; for common options, see Index Options

Returns

an SQL String that can be used to add an index to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
INDEX-ERROR	the table already has an index with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddIndexSql() [3/3]

String org.qore.lang.sqlutil.AbstractTable.getAddIndexSql ( String  iname, boolean  unique, String[]  cols, Map< String, Object >  ixopt, Map< String, Object >  opt  ) throws Throwable

inline

returns an SQL String that can be used to add an index to the table

Example:

String sql = table.getAddIndexSql("uk_mytable_name", true, "name", ixopt);

Parameters

iname	the name of the new index
unique	a flag to tell if the new index should be unique or not
cols	a single column name or a list of columns that make up the index
ixopt	a hash of options for the new index; each driver may implement its own options; for common options, see Index Options
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

an SQL String that can be used to add an index to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
INDEX-ERROR	the table already has an index with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddPrimaryKeySql() [1/3]

String org.qore.lang.sqlutil.AbstractTable.getAddPrimaryKeySql ( String  pkname, String[]  cols  ) throws Throwable

inline

returns the SQL that can be used to add a primary key to the table

Example:

String sql = table.getAddPrimaryKeySql("pk_mytable", "id");

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

pkname	the name of the new primary key constraint
cols	a single column name or a list of columns that make up the primary key

Returns

the SQL that can be used to add a primary key to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
PRIMARY-KEY-ERROR	the table already has a primary key or invalid columns or options passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddPrimaryKeySql() [2/3]

String org.qore.lang.sqlutil.AbstractTable.getAddPrimaryKeySql ( String  pkname, String[]  cols, Map< String, Object >  pkopt  ) throws Throwable

inline

returns the SQL that can be used to add a primary key to the table

Example:

String sql = table.getAddPrimaryKeySql("pk_mytable", "id", pkopt);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

pkname	the name of the new primary key constraint
cols	a single column name or a list of columns that make up the primary key
pkopt	a hash of options for the new primary key; each driver may implement its own options; for common options, see ConstraintOptions

Returns

the SQL that can be used to add a primary key to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
PRIMARY-KEY-ERROR	the table already has a primary key or invalid columns or options passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddPrimaryKeySql() [3/3]

String org.qore.lang.sqlutil.AbstractTable.getAddPrimaryKeySql ( String  pkname, String[]  cols, Map< String, Object >  pkopt, Map< String, Object >  opt  ) throws Throwable

inline

returns the SQL that can be used to add a primary key to the table

Example:

String sql = table.getAddPrimaryKeySql("pk_mytable", "id", pkopt, opt);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

pkname	the name of the new primary key constraint
cols	a single column name or a list of columns that make up the primary key
pkopt	a hash of options for the new primary key; each driver may implement its own options; for common options, see org.qore.sqlutil.AbstractTableConstraintOptions
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

the SQL that can be used to add a primary key to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
PRIMARY-KEY-ERROR	the table already has a primary key or invalid columns or options passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddTriggerSql() [1/3]

String[] org.qore.lang.sqlutil.AbstractTable.getAddTriggerSql ( String  tname, String  src  ) throws Throwable

inline

returns a list of SQL strings that can be used to add a trigger to the table

Example:

String sql = table.getAddTriggerSql("trig_mytable", trigger_src);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

tname	the name of the new trigger
src	the source of the trigger

Returns

a list of SQL strings that can be used to add a trigger to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
TRIGGER-ERROR	the table already has a trigger with the given name or invalid options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddTriggerSql() [2/3]

String[] org.qore.lang.sqlutil.AbstractTable.getAddTriggerSql ( String  tname, String  src, Map< String, Object >  topt  ) throws Throwable

inline

returns a list of SQL strings that can be used to add a trigger to the table

Example:

String sql = table.getAddTriggerSql("trig_mytable", trigger_src);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

tname	the name of the new trigger
src	the source of the trigger
topt	a hash of options for the new trigger; each driver may implement its own options; for common options, see TriggerOptions

Returns

a list of SQL strings that can be used to add a trigger to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
TRIGGER-ERROR	the table already has a trigger with the given name or invalid options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddTriggerSql() [3/3]

String[] org.qore.lang.sqlutil.AbstractTable.getAddTriggerSql ( String  tname, String  src, Map< String, Object >  topt, Map< String, Object >  opt  ) throws Throwable

inline

returns a list of SQL strings that can be used to add a trigger to the table

Example:

String sql = table.getAddTriggerSql("trig_mytable", trigger_src);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

tname	the name of the new trigger
src	the source of the trigger
topt	a hash of options for the new trigger; each driver may implement its own options; for common options, see TriggerOptions
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

a list of SQL strings that can be used to add a trigger to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
TRIGGER-ERROR	the table already has a trigger with the given name or invalid options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddUniqueConstraintSql() [1/3]

String org.qore.lang.sqlutil.AbstractTable.getAddUniqueConstraintSql ( String  cname, String[]  cols  ) throws Throwable

inline

returns an SQL String that can be used to add a unique constraint to the table

Example:

String sql = table.getAddUniqueConstraintSql("name", cols);

Parameters

cname	the name of the new unique constraint
cols	a single column name or a list of columns that make up the unique constraint

Returns

an SQL String that can be used to add a unique constraint to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
UNIQUE-CONSTRAINT-ERROR	the table already has a constraint with the given name or invalid columns passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddUniqueConstraintSql() [2/3]

String org.qore.lang.sqlutil.AbstractTable.getAddUniqueConstraintSql ( String  cname, String[]  cols, Map< String, Object >  ukopt  ) throws Throwable

inline

returns an SQL String that can be used to add a unique constraint to the table

Example:

String sql = table.getAddUniqueConstraintSql("name", ukopt);

Parameters

cname	the name of the new unique constraint
cols	a single column name or a list of columns that make up the unique constraint
ukopt	a hash of options for the new unique constraint; each driver may implement its own options; for common options, see ConstraintOptions

Returns

an SQL String that can be used to add a unique constraint to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
UNIQUE-CONSTRAINT-ERROR	the table already has a constraint with the given name or invalid columns passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getAddUniqueConstraintSql() [3/3]

String org.qore.lang.sqlutil.AbstractTable.getAddUniqueConstraintSql ( String  cname, String[]  cols, Map< String, Object >  ukopt, Map< String, Object >  opt  ) throws Throwable

inline

returns an SQL String that can be used to add a unique constraint to the table

Example:

String sql = table.getAddUniqueConstraintSql("uk_mytable", "name", ukopt);

Parameters

cname	the name of the new unique constraint
cols	a single column name or a list of columns that make up the unique constraint
ukopt	a hash of options for the new unique constraint; each driver may implement its own options; for common options, see ConstraintOptions
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

an SQL String that can be used to add a unique constraint to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
UNIQUE-CONSTRAINT-ERROR	the table already has a constraint with the given name or invalid columns passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getDesc()

String org.qore.lang.sqlutil.AbstractTable.getDesc ( ) throws Throwable

inline

returns a descriptive string of the datasource (without the password) and the table name (with a possible qualifier for schema, etc)

Used in exception descriptions

Since

SqlUtil 1.3

getDropAllConstraintsAndIndexesOnColumnSql() [1/2]

String[] org.qore.lang.sqlutil.AbstractTable.getDropAllConstraintsAndIndexesOnColumnSql ( String  cname ) throws Throwable

inline

gets a list of SQL strings to drop all constraints and indexes with the given column name; if the column does not exist then an empty list is returned

Example:

String[] strlist = table.getDropAllConstraintsAndIndexesOnColumnSql("status");

Parameters

cname

the name of the column

Exceptions

OPTION-ERROR

invalid or unsupported option passed

Note

• this method retrieves current primary key definition from the database if not already loaded or defined
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropAllConstraintsAndIndexesOnColumnSql() [2/2]

String[] org.qore.lang.sqlutil.AbstractTable.getDropAllConstraintsAndIndexesOnColumnSql ( String  cname, Map< String, Object >  opt  ) throws Throwable

inline

gets a list of SQL strings to drop all constraints and indexes with the given column name; if the column does not exist then an empty list is returned

Example:

String[] strlist = table.getDropAllConstraintsAndIndexesOnColumnSql("status");

Parameters

cname	the name of the column
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Exceptions

OPTION-ERROR

invalid or unsupported option passed

Note

• this method retrieves current primary key definition from the database if not already loaded or defined
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropColumnSql() [1/2]

String[] org.qore.lang.sqlutil.AbstractTable.getDropColumnSql ( String  cname ) throws Throwable

inline

returns the SQL that can be used to drop a column from the table

Example:

String sql = table.getDropColumnSql("notes_2");

Parameters

cname

the name of the column to drop

Returns

the SQL that can be used to drop a column from the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
COLUMN-ERROR	the named column is not present in the table

Note

• this method retrieves the column definitions from the database if none have already been defined
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropColumnSql() [2/2]

String[] org.qore.lang.sqlutil.AbstractTable.getDropColumnSql ( String  cname, Map< String, Object >  opt  ) throws Throwable

inline

returns the SQL that can be used to drop a column from the table

Example:

String sql = table.getDropColumnSql("notes_2");

Parameters

cname	the name of the column to drop
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

the SQL that can be used to drop a column from the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
COLUMN-ERROR	the named column is not present in the table

Note

• this method retrieves the column definitions from the database if none have already been defined
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropConstraintIfExistsSql() [1/2]

String org.qore.lang.sqlutil.AbstractTable.getDropConstraintIfExistsSql ( String  cname ) throws Throwable

inline

gets the SQL that can be used to drop a constraint from the table if it exists, otherwise returns null; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint

Example:

String sql = table.getDropConstraintIfExistsSql("uk_mytable_name");

Parameters

cname

the name of the constraint to drop

Returns

the SQL that can be used to drop a constraint from the table if it exists, otherwise returns null; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint

Exceptions

OPTION-ERROR

invalid or unsupported option passed

Note

• this method retrieves current constraint definitions from the database if not already loaded or defined
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropConstraintIfExistsSql() [2/2]

String org.qore.lang.sqlutil.AbstractTable.getDropConstraintIfExistsSql ( String  cname, Map< String, Object >  opt  ) throws Throwable

inline

gets the SQL that can be used to drop a constraint from the table if it exists, otherwise returns null; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint

Example:

String sql = table.getDropConstraintIfExistsSql("uk_mytable_name");

Parameters

cname	the name of the constraint to drop
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

the SQL that can be used to drop a constraint from the table if it exists, otherwise returns null; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint

Exceptions

OPTION-ERROR

invalid or unsupported option passed

Note

• this method retrieves current constraint definitions from the database if not already loaded or defined
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropConstraintSql() [1/2]

String org.qore.lang.sqlutil.AbstractTable.getDropConstraintSql ( String  cname ) throws Throwable

inline

gets the SQL that can be used to drop a constraint from the table; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint

Example:

String sql = table.getDropConstraintSql("uk_mytable_name");

Parameters

cname	the name of the constraint to drop
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

the SQL that can be used to drop a constraint from the table; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint

Exceptions

OPTION-ERROR	invalid or unsupported option passed
CONSTRAINT-ERROR	the given constraint does not exist in the table

Note

• this method retrieves current constraint definitions from the database if not already loaded or defined
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropConstraintSql() [2/2]

String org.qore.lang.sqlutil.AbstractTable.getDropConstraintSql ( String  cname, Map< String, Object >  opt  ) throws Throwable

inline

gets the SQL that can be used to drop a constraint from the table; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint

Example:

String sql = table.getDropConstraintSql("uk_mytable_name");

Parameters

cname	the name of the constraint to drop
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

the SQL that can be used to drop a constraint from the table; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint

Exceptions

OPTION-ERROR	invalid or unsupported option passed
CONSTRAINT-ERROR	the given constraint does not exist in the table

Note

• this method retrieves current constraint definitions from the database if not already loaded or defined
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropIndexSql() [1/2]

String org.qore.lang.sqlutil.AbstractTable.getDropIndexSql ( String  iname ) throws Throwable

inline

gets the SQL that can be used to drop an index from the table

Example:

String sql = table.getDropIndexSql("uk_mytable_name");

Parameters

iname

the name of the index to drop

Returns

the SQL that can be used to drop an index from the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
INDEX-ERROR	the given index does not exist in the table

Note

• this method retrieves current index definitions from the database if not already loaded or defined
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropIndexSql() [2/2]

String org.qore.lang.sqlutil.AbstractTable.getDropIndexSql ( String  iname, Map< String, Object >  opt  ) throws Throwable

inline

gets the SQL that can be used to drop an index from the table

Example:

String sql = table.getDropIndexSql("uk_mytable_name");

Parameters

iname	the name of the index to drop
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

the SQL that can be used to drop an index from the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
INDEX-ERROR	the given index does not exist in the table

Note

• this method retrieves current index definitions from the database if not already loaded or defined
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropPrimaryKeySql() [1/2]

String[] org.qore.lang.sqlutil.AbstractTable.getDropPrimaryKeySql ( ) throws Throwable

inline

gets a list of SQL strings that can be used to drop the primary key from the table

Example:

String[] l = table.getDropPrimaryKeySql();

Returns

a list of SQL strings that can be used to drop the primary key from the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
PRIMARY-KEY-ERROR	the table has no primary key

Note

• this method retrieves current primary key definition from the database if not already loaded or defined
• if there are known foreign contraints on the primary key, SQL for dropping those constraints is also returned
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropPrimaryKeySql() [2/2]

String[] org.qore.lang.sqlutil.AbstractTable.getDropPrimaryKeySql ( Map< String, Object >  opt ) throws Throwable

inline

gets a list of SQL strings that can be used to drop the primary key from the table

Example:

String[] l = table.getDropPrimaryKeySql();

Parameters

opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

a list of SQL strings that can be used to drop the primary key from the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
PRIMARY-KEY-ERROR	the table has no primary key

Note

• this method retrieves current primary key definition from the database if not already loaded or defined
• if there are known foreign contraints on the primary key, SQL for dropping those constraints is also returned
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropSql() [1/2]

String[] org.qore.lang.sqlutil.AbstractTable.getDropSql ( ) throws Throwable

inline

returns the sql required to drop the table; reimplement in subclasses if necessary

Example:

String[] l = table.getDropSql();

Returns

a list of strings that can be used to drop the table and any other objects assocatied with the table (for example: PostgreSQL table trigger method(s))

Exceptions

OPTION-ERROR

invalid or unknown callback option

getDropSql() [2/2]

String[] org.qore.lang.sqlutil.AbstractTable.getDropSql ( Map< String, Object >  opt ) throws Throwable

inline

returns the sql required to drop the table; reimplement in subclasses if necessary

Example:

list l = table.getDropSql();

Parameters

opt	optional callback options; see Callback Options for more info

Returns

a list of strings that can be used to drop the table and any other objects assocatied with the table (for example: PostgreSQL table trigger method(s))

Exceptions

OPTION-ERROR

invalid or unknown callback option

getDropTriggerSql() [1/2]

String[] org.qore.lang.sqlutil.AbstractTable.getDropTriggerSql ( String  tname ) throws Throwable

inline

returns SQL that can be used to drop the given trigger from the table

Example:

String sql = table.getDropTriggerSql("trig_mytable");

Parameters

tname

the name of the trigger to drop

Returns

the SQL that can be used to drop the given trigger from the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
TRIGGER-ERROR	the given trigger does not exist

Note

• this method retrieves all current trigger definitions from the database if none have already been defined
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropTriggerSql() [2/2]

String[] org.qore.lang.sqlutil.AbstractTable.getDropTriggerSql ( String  tname, Map< String, Object >  opt  ) throws Throwable

inline

returns SQL that can be used to drop the given trigger from the table

Example:

String sql = table.getDropTriggerSql("trig_mytable");

Parameters

tname	the name of the trigger to drop
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

the SQL that can be used to drop the given trigger from the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
TRIGGER-ERROR	the given trigger does not exist

Note

• this method retrieves all current trigger definitions from the database if none have already been defined
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getModifyColumnSql() [1/3]

String[] org.qore.lang.sqlutil.AbstractTable.getModifyColumnSql ( String  cname, Map< String, Object >  copt  ) throws Throwable

inline

gets a list of SQL strings that can be used to modify an existing column in the table

Example:

String[] l = table.getModifyColumnSql("name", copt, false);

Parameters

cname

the name of the column

copt

a hash describing the column; the following keys are permitted (other column options may be supported depending on the underlying AbstractTable implementation): qore_type: a qore type String that will be converted to a native DB type with some default conversion; native_type: the native database column type; if both native_type and qore_type are given then native_type is used size: for data types requiring a size component, the size; for numeric columns this represents the precision for example scale: for numeric data types, this value gives the scale default_value: the default value for the column default_value_native: a boolean flag to say if a default_value should be validated against table column type (false) or used as it is (true) to allow to use DBMS native methods or features. Defaults to false. It is strongly recommended to use default_value_native for default_value in driver specific sub-hash to avoid non-portable schema hashes

Returns

a list of SQL strings that can be used to modify an existing column in the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
COLUMN-ERROR	no native_type or qore_type keys in column option hash, column does not exist, invalid column data

Note

• make sure and add a default_value value when modifying a column to have a "not null" constraint with existing data
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getModifyColumnSql() [2/3]

String[] org.qore.lang.sqlutil.AbstractTable.getModifyColumnSql ( String  cname, Map< String, Object >  copt, boolean  nullable  ) throws Throwable

inline

gets a list of SQL strings that can be used to modify an existing column in the table

Example:

String[] l = table.getModifyColumnSql("name", copt, false);

Parameters

cname	the name of the column
copt	a hash describing the column; the following keys are permitted (other column options may be supported depending on the underlying AbstractTable implementation): qore_type: a qore type String that will be converted to a native DB type with some default conversion; native_type: the native database column type; if both native_type and qore_type are given then native_type is used size: for data types requiring a size component, the size; for numeric columns this represents the precision for example scale: for numeric data types, this value gives the scale default_value: the default value for the column default_value_native: a boolean flag to say if a default_value should be validated against table column type (false) or used as it is (true) to allow to use DBMS native methods or features. Defaults to false. It is strongly recommended to use default_value_native for default_value in driver specific sub-hash to avoid non-portable schema hashes
nullable	if true then the column can hold NULL values; note that primary key columns cannot be nullable

Returns

a list of SQL strings that can be used to modify an existing column in the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
COLUMN-ERROR	no native_type or qore_type keys in column option hash, column does not exist, invalid column data

Note

• make sure and add a default_value value when modifying a column to have a "not null" constraint with existing data
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getModifyColumnSql() [3/3]

String[] org.qore.lang.sqlutil.AbstractTable.getModifyColumnSql ( String  cname, Map< String, Object >  copt, boolean  nullable, Map< String, Object >  opt  ) throws Throwable

inline

gets a list of SQL strings that can be used to modify an existing column in the table

Example:

String[] l = table.getModifyColumnSql("name", copt, false);

Parameters

cname	the name of the column
copt	a hash describing the column; the following keys are permitted (other column options may be supported depending on the underlying AbstractTable implementation): qore_type: a qore type String that will be converted to a native DB type with some default conversion; native_type: the native database column type; if both native_type and qore_type are given then native_type is used size: for data types requiring a size component, the size; for numeric columns this represents the precision for example scale: for numeric data types, this value gives the scale default_value: the default value for the column default_value_native: a boolean flag to say if a default_value should be validated against table column type (false) or used as it is (true) to allow to use DBMS native methods or features. Defaults to false. It is strongly recommended to use default_value_native for default_value in driver specific sub-hash to avoid non-portable schema hashes
nullable	if true then the column can hold NULL values; note that primary key columns cannot be nullable
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

a list of SQL strings that can be used to modify an existing column in the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
COLUMN-ERROR	no native_type or qore_type keys in column option hash, column does not exist, invalid column data

Note

• make sure and add a default_value value when modifying a column to have a "not null" constraint with existing data
• if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getRenameColumnSql() [1/2]

String org.qore.lang.sqlutil.AbstractTable.getRenameColumnSql ( String  old_name, String  new_name  ) throws Throwable

inline

gets an SQL String that can be used to rename an existing column in the table

Example:

String sql = table.getRenameColumnSql("name", "family_name");

Parameters

old_name	the current name of the column
new_name	the new name of the column
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

an SQL String that can be used to rename an existing column in the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
COLUMN-ERROR	if the old column does not exist in the table or the new column already does

See also

getModifyColumnSql() for a method that allows the column definition to be updated

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getRenameColumnSql() [2/2]

String org.qore.lang.sqlutil.AbstractTable.getRenameColumnSql ( String  old_name, String  new_name, Map< String, Object >  opt  ) throws Throwable

inline

gets an SQL String that can be used to rename an existing column in the table

Example:

String sql = table.getRenameColumnSql("name", "family_name");

Parameters

old_name	the current name of the column
new_name	the new name of the column
opt	a hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options

Returns

an SQL String that can be used to rename an existing column in the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
COLUMN-ERROR	if the old column does not exist in the table or the new column already does

See also

getModifyColumnSql() for a method that allows the column definition to be updated

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getStatement() [1/3]

AbstractSQLStatement org.qore.lang.sqlutil.AbstractTable.getStatement ( ) throws Throwable

inline

returns an AbstractSQLStatement object that will iterate all the rows in the table

Example:

AbstractSQLStatement i = table.getStatement();

Returns

an AbstractSQLStatement object that will iterate all the rows in the table

Note

• the AbstractSQLStatement object created by a successful call to this method acquires a thread resource for the underlying AbstractDatasource object that must be released by calling commit() or rollback(), even if the statement does not acquire any database locks

See also

getStatementNoExec()

Since

SqlUtil 1.5

getStatement() [2/3]

AbstractSQLStatement org.qore.lang.sqlutil.AbstractTable.getStatement ( Map< String, Object >  sh ) throws Throwable

inline

returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments

Example:

AbstractSQLStatement i = table.getStatement(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument

Returns

an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments

Exceptions

SELECT-ERROR

'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• the AbstractSQLStatement object created by a successful call to this method acquires a thread resource for the underlying AbstractDatasource object that must be released by calling commit() or rollback(), even if the statement does not acquire any database locks

See also

getStatementNoExec()

Since

SqlUtil 1.5

getStatement() [3/3]

AbstractSQLStatement org.qore.lang.sqlutil.AbstractTable.getStatement ( Map< String, Object >  sh, Map< String, Object >  opt  ) throws Throwable

inline

returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments

Example:

AbstractSQLStatement i = table.getStatement(sh, opts);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see SqlDataCallbackOptions for more info

Returns

an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• the AbstractSQLStatement object created by a successful call to this method acquires a thread resource for the underlying AbstractDatasource object that must be released by calling commit() or rollback(), even if the statement does not acquire any database locks

See also

getStatementNoExec()

Since

SqlUtil 1.5

getStatementNoExec() [1/3]

AbstractSQLStatement org.qore.lang.sqlutil.AbstractTable.getStatementNoExec ( ) throws Throwable

inline

returns an AbstractSQLStatement object that will iterate all the rows in the table; the statement is only prepared and not executed

Example:

AbstractSQLStatement i = table.getStatementNoExec();

Returns

an AbstractSQLStatement object that will iterate all the rows in the table

Note

• the AbstractSQLStatement object created by a successful call to this method acquires a thread resource for the underlying AbstractDatasource object that must be released by calling commit() or rollback(), even if the statement does not acquire any database locks

See also

getStatement()

Since

SqlUtil 1.5

getStatementNoExec() [2/3]

AbstractSQLStatement org.qore.lang.sqlutil.AbstractTable.getStatementNoExec ( Map< String, Object >  sh ) throws Throwable

inline

returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments; the statement is only prepared and not executed

Example:

AbstractSQLStatement i = table.getStatementNoExec(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument

Returns

an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments

Exceptions

SELECT-ERROR

'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• the AbstractSQLStatement object created by a successful call to this method acquires a thread resource for the underlying AbstractDatasource object that must be released by calling commit() or rollback(), even if the statement does not acquire any database locks

See also

getStatement()

Since

SqlUtil 1.5

getStatementNoExec() [3/3]

AbstractSQLStatement org.qore.lang.sqlutil.AbstractTable.getStatementNoExec ( Map< String, Object >  sh, Map< String, Object >  opt  ) throws Throwable

inline

returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments; the statement is only prepared and not executed

Example:

AbstractSQLStatement i = table.getStatementNoExec(sh, opts);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see SqlDataCallbackOptions for more info

Returns

an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• the AbstractSQLStatement object created by a successful call to this method acquires a thread resource for the underlying AbstractDatasource object that must be released by calling commit() or rollback(), even if the statement does not acquire any database locks

See also

getStatement()

Since

SqlUtil 1.5

getTruncateSql() [1/2]

String org.qore.lang.sqlutil.AbstractTable.getTruncateSql ( ) throws Throwable

inline

gets the SQL that can be used to truncate the table

Example:

String sql = table.getTruncateSql();

Returns

the SQL that can be used to truncate the table

Exceptions

OPTION-ERROR

invalid or unsupported option passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

getTruncateSql() [2/2]

String org.qore.lang.sqlutil.AbstractTable.getTruncateSql ( Map< String, Object >  opt ) throws Throwable

inline

gets the SQL that can be used to truncate the table

Example:

String sql = table.getTruncateSql();

Parameters

opt	a hash of options for the SQL string; see AbstractTable.AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

the SQL that can be used to truncate the table

Exceptions

OPTION-ERROR

invalid or unsupported option passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is true in opt, then the changes are also effected in the current object, if not, then they are not (see sql_callback_executed for more information)

hasReturning()

boolean org.qore.lang.sqlutil.AbstractTable.hasReturning ( ) throws Throwable

inline

returns true if the current database driver supports the "returning" clause in insert statements, false if not

Returns

true if the current database driver supports the "returning" clause in insert statements, false if not

Since

SqlUtil 1.3

inDb()

boolean org.qore.lang.sqlutil.AbstractTable.inDb ( ) throws Throwable

inline

returns true if the table has been read from or created in the database, false if not

Example:

boolean b = table.inDb();

Returns

true if the table has been read from or created in the database, false if not

Note

this method only returns a flag if the object contains configuration already retrieved from the database,

See also

checkExistence() for a method that will check the database if the table exists in case the table is not already known to exist in the database

insert() [1/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.insert ( Map< String, Object >  row ) throws Throwable

inline

inserts a row into the table without any transaction management; a transaction will be in progress after this method is successfully executed

Example:

table.insert(row);

Parameters

row	a hash representing the row to insert; hash values can also be set with sql_iop_funcs to insert values based on SQL operations to be used directly in the insert statement

Returns

in case the "returning" insert option is used, a hash of return values is returned, otherwise null is returned

Exceptions

COLUMN-ERROR

an unknown column was referenced in the hash to be inserted

insert() [2/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.insert ( Map< String, Object >  row, Map< String, Object >  opt  ) throws Throwable

inline

insert() variant

Parameters

row	a hash representing the row to insert; hash values can also be set with sql_iop_funcs to insert values based on SQL operations to be used directly in the insert statement
opt	optional insert options; see Insert Options for more info

insertFromSelect()

int org.qore.lang.sqlutil.AbstractTable.insertFromSelect ( String[]  cols, AbstractTable  source, Map< String, Object >  sh, Map< String, Object >  opt  ) throws Throwable

inline

inserts rows into a table based on a select statement from another table (which must be using the same datasource as the current table); a transaction will be in progress after this method is successfully executed

Example:

int rows = table.insertFromSelect(new String[]{"id", "name", "created"}, source_table, sh);

Parameters

cols	the list of column names to use to insert in the current table
source	the source table for the select statement
sh	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see SqlDataCallbackOptions for more inf

Returns

the number of rows inserted

Exceptions

OPTION-ERROR	invalid or unsupported option
COLUMN-ERROR	unknown or invalid column in insert list
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

this method does not take insert options because it is executed entirely in the database server; use insertFromIterator() or insertFromIteratorCommit() to insert arbitrary data with insert options

rowCount()

int org.qore.lang.sqlutil.AbstractTable.rowCount ( ) throws Throwable

inline

returns the number of rows in the table

Example:

int cnt = table.rowCount();

Returns

the number of rows in the table

Note

to see if the table is empty or not, use emptyData() as this is much faster than rowCount()

See also

emptyData()

select() [1/3]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.select ( ) throws Throwable

inline

returns a hash of lists representing the columns and rows in the table that match the argument hahs

Example:

Map<String, Object> h = table.select();

Returns

a hash of lists representing the columns and rows in the table that match the argument hash

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

select() [2/3]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.select ( Map< String, Object >  sh ) throws Throwable

inline

returns a hash of lists representing the columns and rows in the table that match the argument hahs

Example:

Map<String, Object> h = table.select(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument

Returns

a hash of lists representing the columns and rows in the table that match the argument hash

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

select() [3/3]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.select ( Map< String, Object >  sh, Map< String, Object >  opt  ) throws Throwable

inline

returns a hash of lists representing the columns and rows in the table that match the argument hahs

Example:

Map<String, Object> h = table.select(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see SqlDataCallbackOptions for more info

Returns

a hash of lists representing the columns and rows in the table that match the argument hash

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

selectRow() [1/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.selectRow ( Map< String, Object >  sh ) throws Throwable

inline

returns a hash representing the row in the table that matches the argument hash; if more than one row would be returned an exception is raised

Example:

Map<String, Object> h = table.selectRow(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument

Returns

a hash representing the row in the table that matches the argument hash; if more than one row would be returned an exception is raised

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint
DBI-SELECT-ROW-ERROR	more than 1 row retrieved from the server

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

selectRow() [2/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.selectRow ( Map< String, Object >  sh, Map< String, Object >  opt  ) throws Throwable

inline

returns a hash representing the row in the table that matches the argument hash; if more than one row would be returned an exception is raised

Example:

Map<String, Object> h = table.selectRow(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see SqlDataCallbackOptions for more info

Returns

a hash representing the row in the table that matches the argument hash; if more than one row would be returned an exception is raised

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint
DBI-SELECT-ROW-ERROR	more than 1 row retrieved from the server

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

selectRows() [1/3]

HashMap< String, Object >[] org.qore.lang.sqlutil.AbstractTable.selectRows ( ) throws Throwable

inline

returns a list of hashes representing the rows in the table that match the argument hash

Example:

Map<String, Object>[] l = table.selectRows();

Returns

a list of hashes representing the rows in the table that match the argument hash

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

selectRows() [2/3]

HashMap< String, Object >[] org.qore.lang.sqlutil.AbstractTable.selectRows ( Map< String, Object >  sh ) throws Throwable

inline

returns a list of hashes representing the rows in the table that match the argument hash

Example:

Map<String, Object>[] l = table.selectRows(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument

Returns

a list of hashes representing the rows in the table that match the argument hash

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

selectRows() [3/3]

HashMap< String, Object >[] org.qore.lang.sqlutil.AbstractTable.selectRows ( Map< String, Object >  sh, Map< String, Object >  opt  ) throws Throwable

inline

returns a list of hashes representing the rows in the table that match the argument hash

Example:

Map<String, Object>[] l = table.selectRows(sh, opt);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see SqlDataCallbackOptions for more info

Returns

a list of hashes representing the rows in the table that match the argument hash

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

setupTable() [1/2]

void org.qore.lang.sqlutil.AbstractTable.setupTable ( Map< String, Object >  desc ) throws Throwable

inline

creates the object from a table description hash

Parameters

desc	a table description hash describing the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
DESCRIPTION-ERROR	invalid or unsupported description hash value passed

setupTable() [2/2]

void org.qore.lang.sqlutil.AbstractTable.setupTable ( Map< String, Object >  desc, Map< String, Object >  opt  ) throws Throwable

inline

creates the object from a table description hash

Parameters

desc	a table description hash describing the table
opt	an optional hash of options for the table creation string; see sqlutil.AbstractTableTableOptions for common options; each driver can support additional driver-specific options

Exceptions

OPTION-ERROR	invalid or unsupported option passed
DESCRIPTION-ERROR	invalid or unsupported description hash value passed

truncate()

void org.qore.lang.sqlutil.AbstractTable.truncate ( ) throws Throwable

inline

truncates all the table data without any transaction management

Example:

table.truncate();

Note

Transaction management may not be applied when truncating tables depending on the database driver (for example truncating tables in Oracle does not participate in transaction management), however this method uses the org.qore.lang.AbstractDatasource.exec() method, which normally participates in acquiring a transaction lock for the underlying datasource object; therefore after this method executes normally the transaction lock will be dedicated to the calling thread.

tryExec()

Object org.qore.lang.sqlutil.AbstractTable.tryExec ( String  sql ) throws Throwable

inline

executes some SQL with optional arguments so that if an error occurs the current transaction state is not lost

Example:

t.tryExec("drop table tmp_table");

Include any arguments in the parameter list after the sql argument

Parameters

sql	the SQL to execute

Returns

any return value from the SQL command executed

tryExecArgs()

Object org.qore.lang.sqlutil.AbstractTable.tryExecArgs ( String  sql, Object[]  args  ) throws Throwable

inline

executes some SQL with optional arguments so that if an error occurs the current transaction state is not lost

Example:

t.tryExec("delete from tmp_table where id = %v and name = %v", arglist);

Parameters

sql	the SQL to execute
args	the bind / placeholder or other arguments corresponding to the SQL string

Returns

any return value from the SQL command executed

tryExecRaw()

Object org.qore.lang.sqlutil.AbstractTable.tryExecRaw ( String  sql ) throws Throwable

inline

executes some SQL so that if an error occurs the current transaction state is not lost

Example:

t.tryExecRaw("drop table tmp_table");

Include any arguments in the parameter list after the sql argument

Parameters

sql	the SQL to execute

Returns

any return value from the SQL command executed

update()

int org.qore.lang.sqlutil.AbstractTable.update ( HashMap< String, Object >  set, HashMap< String, Object >  cond, HashMap< String, Object >  opt  ) throws Throwable

inline

updates rows in the table matching an optional condition and returns the count of rows updated; no transaction management is performed with this method

Example:

int ucnt = table.update(set_hash, cond_hash);

Parameters

set	the hash of values to set, key values are column names, hash values are the values to assign to those columns or update operators (see sql_uop_funcs)
cond	a hash of conditions for the where clause; see where_clauses for more information
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

the count of rows updated

Exceptions

UPDATE-ERROR	the set hash is empty
WHERE-ERROR	unknown operator or invalid arguments given in the cond hash for the where clause

upsert() [1/2]

int org.qore.lang.sqlutil.AbstractTable.upsert ( Map< String, Object >  row, int  upsert_strategy  ) throws Throwable

inline

update or insert the data in the table according to the hash argument; the table must have a unique key to do this

Example:

table.upsert(row);

Parameters

row	a hash representing the row to insert or update
upsert_strategy	see Upsert Strategy Codes for possible values for the upsert strategy

Returns

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

Exceptions

COLUMN-ERROR	an unknown column was referenced in the hash to be inserted
UPSERT-ERROR	no primary key, unique constraint, or unique index for upsert; not all columns of the unique constraint/index are used in the upsert statement

Note

if upserting multiple rows; it's better to use getBulkUpsertClosure(), getUpsertClosure(), or getUpsertClosureWithValidation() and execute the closure on each row; when using this method, the overhead for setting up the upsert is made for each row which is very inefficient

upsert() [2/2]

int org.qore.lang.sqlutil.AbstractTable.upsert ( Map< String, Object >  row, int  upsert_strategy, Map< String, Object >  opt  ) throws Throwable

inline

update or insert the data in the table according to the hash argument; the table must have a unique key to do this

Example:

table.upsert(row);

Parameters

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 Upsert Options 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

Exceptions

COLUMN-ERROR	an unknown column was referenced in the hash to be inserted
UPSERT-ERROR	no primary key, unique constraint, or unique index for upsert; not all columns of the unique constraint/index are used in the upsert statement

Note

if upserting multiple rows; it's better to use getBulkUpsertClosure(), getUpsertClosure(), or getUpsertClosureWithValidation() and execute the closure on each row; when using this method, the overhead for setting up the upsert is made for each row which is very inefficient

upsertFromSelect() [1/3]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.upsertFromSelect ( AbstractTable  t, Map< String, Object >  sh  ) throws Throwable

inline

this method upserts or merges data from the given foreign table and select option hash into the current table; no transaction management is performed with this method

Example:

Map<String, Object> h;
try {
    h = table.upsertFromSelect(table2, sh, AbstractTable.UpsertUpdateFirst);
 
    ds.commit();
} catch (Throwable e) {
    ds.collback();
    throw e;
}

The table argument does not need to be in the same database as the current table; it can also be in a different database server or a database server of a different type (you can use this method to upsert or merge data to or from any database supported by SqlUtil).

Parameters

t	the table for the source data; this does not need to be in the same database as the target (the current table), nor does it need to be the same database type
sh	a hash of conditions for the select statement; see select option hash for information about this argument

Returns

null 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 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 UpsertSelectFirst instead of "updated"
• "updated": the number of rows updated; note that this key is only returned if upsert_strategy is 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 UpsertSelectFirst, UpsertInsertOnly, or UpsertUpdateOnly
• "deleted": the number of rows deleted; this can only be returned if upsert option delete_others is true

Exceptions

OPTION-ERROR	invalid or unsupported option
COLUMN-ERROR	an unknown column was referenced in the hash to be inserted
UPSERT-ERROR	no primary key, unique constraint, or unique index for upsert; not all columns of the unique constraint/index are used in the upsert statement

Note

• if upsert option delete_others is true, then a hash of primary key values in the input data is built as the input data is iterated. After iterating, if the row count of the table and the input data matches, then nothing more is done, otherwise, every row of the table is iterated and compared to the primary key hash; if a row does not match a primary key value, then it is deleted. This operation is only executed if delete_others is true and is expensive for large data sets.

• this method uses an AbstractSQLStatement object to pipeline the select data to the upsert code; to release the transaction lock acquired by the AbstractSQLStatement object, a commit() or rollback() action must be executed on the underlying datasource object as in the example above

  • unlike insertFromSelect(), this method processes arbitrary input data and accepts upsert options

  See also

  • upsertFromSelect()
  • upsert()

upsertFromSelect() [2/3]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.upsertFromSelect ( AbstractTable  t, Map< String, Object >  sh, int  upsert_strategy  ) throws Throwable

inline

this method upserts or merges data from the given foreign table and select option hash into the current table; no transaction management is performed with this method

Example:

Map<String, Object> h;
try {
    h = table.upsertFromSelect(table2, sh, AbstractTable.UpsertUpdateFirst);
 
    ds.commit();
} catch (Throwable e) {
    ds.collback();
    throw e;
}

The table argument does not need to be in the same database as the current table; it can also be in a different database server or a database server of a different type (you can use this method to upsert or merge data to or from any database supported by SqlUtil).

Parameters

t	the table for the source data; this does not need to be in the same database as the target (the current table), nor does it need to be the same database type
sh	a hash of conditions for the select statement; see select option hash for information about this argument
upsert_strategy	see Upsert Strategy Codes for possible values for the upsert strategy

Returns

null 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 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 UpsertSelectFirst instead of "updated"
• "updated": the number of rows updated; note that this key is only returned if upsert_strategy is 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 UpsertSelectFirst, UpsertInsertOnly, or UpsertUpdateOnly
• "deleted": the number of rows deleted; this can only be returned if upsert option delete_others is true

Exceptions

OPTION-ERROR	invalid or unsupported option
COLUMN-ERROR	an unknown column was referenced in the hash to be inserted
UPSERT-ERROR	no primary key, unique constraint, or unique index for upsert; not all columns of the unique constraint/index are used in the upsert statement

Note

• if upsert option delete_others is true, then a hash of primary key values in the input data is built as the input data is iterated. After iterating, if the row count of the table and the input data matches, then nothing more is done, otherwise, every row of the table is iterated and compared to the primary key hash; if a row does not match a primary key value, then it is deleted. This operation is only executed if delete_others is true and is expensive for large data sets.

• this method uses an AbstractSQLStatement object to pipeline the select data to the upsert code; to release the transaction lock acquired by the AbstractSQLStatement object, a commit() or rollback() action must be executed on the underlying datasource object as in the example above

  • unlike insertFromSelect(), this method processes arbitrary input data and accepts upsert options

  See also

  • upsertFromSelect()
  • upsert()

upsertFromSelect() [3/3]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.upsertFromSelect ( AbstractTable  t, Map< String, Object >  sh, int  upsert_strategy, Map< String, Object >  opt  ) throws Throwable

inline

this method upserts or merges data from the given foreign table and select option hash into the current table; no transaction management is performed with this method

Example:

Map<String, Object> h;
try {
    h = table.upsertFromSelect(table2, sh, AbstractTable.UpsertUpdateFirst);
 
    ds.commit();
} catch (Throwable e) {
    ds.collback();
    throw e;
}

The table argument does not need to be in the same database as the current table; it can also be in a different database server or a database server of a different type (you can use this method to upsert or merge data to or from any database supported by SqlUtil).

Parameters

t	the table for the source data; this does not need to be in the same database as the target (the current table), nor does it need to be the same database type
sh	a hash of conditions for the select statement; see select option hash for information about this argument
upsert_strategy	see Upsert Strategy Codes for possible values for the upsert strategy
opt	a hash of options for the upsert operation; see Upsert Options for common options; each driver can support additional driver-specific options; note that this method ignores any "commit_block" option

Returns

null 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 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 UpsertSelectFirst instead of "updated"
• "updated": the number of rows updated; note that this key is only returned if upsert_strategy is 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 UpsertSelectFirst, UpsertInsertOnly, or UpsertUpdateOnly
• "deleted": the number of rows deleted; this can only be returned if upsert option delete_others is true

Exceptions

OPTION-ERROR	invalid or unsupported option
COLUMN-ERROR	an unknown column was referenced in the hash to be inserted
UPSERT-ERROR	no primary key, unique constraint, or unique index for upsert; not all columns of the unique constraint/index are used in the upsert statement

Note

• if upsert option delete_others is true, then a hash of primary key values in the input data is built as the input data is iterated. After iterating, if the row count of the table and the input data matches, then nothing more is done, otherwise, every row of the table is iterated and compared to the primary key hash; if a row does not match a primary key value, then it is deleted. This operation is only executed if delete_others is true and is expensive for large data sets.

• this method uses an AbstractSQLStatement object to pipeline the select data to the upsert code; to release the transaction lock acquired by the AbstractSQLStatement object, a commit() or rollback() action must be executed on the underlying datasource object as in the example above

  • unlike insertFromSelect(), this method processes arbitrary input data and accepts upsert options

  See also

  • upsertFromSelect()
  • upsert()

Member Data Documentation

UpsertResultDescriptionMap

final Map<String, Integer> org.qore.lang.sqlutil.AbstractTable.UpsertResultDescriptionMap

static

Initial value:

= Collections.unmodifiableMap(new HashMap<String, Integer>() {
        {
            put("inserted", UR_Inserted);
            put("verified", UR_Verified);
            put("updated", UR_Updated);
            put("unchanged", UR_Unchanged);
            put("deleted", UR_Deleted);
        }
    })

hash mapping upsert descriptions to codes

See also

UpsertResultMap for a reverse mapping

UpsertResultMap

final Map<Integer, String> org.qore.lang.sqlutil.AbstractTable.UpsertResultMap

static

Initial value:

= Collections.unmodifiableMap(new HashMap<Integer, String>() {
        {
            put(UR_Inserted, "inserted");
            put(UR_Verified, "verified");
            put(UR_Updated, "updated");
            put(UR_Unchanged, "unchanged");
            put(UR_Deleted, "deleted");
        }
    })

hash mapping upsert results to a description

See also

UpsertResultDescriptionMap for a reverse mapping

---

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

• /tmp/module-jni/src/java/org/qore/lang/sqlutil/AbstractTable.java