Qore jni Module  1.0.1
org.qore.lang.sqlutil.AbstractTable Class Reference

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

Inheritance diagram for org.qore.lang.sqlutil.AbstractTable:
org.qore.lang.sqlutil.AbstractSqlUtilBase org.qore.jni.QoreObjectWrapper org.qore.lang.sqlutil.Table

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) More...
 
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 More...
 
void drop (HashMap< String, Object > opt) throws Throwable
 drops the table from the database without any transaction management More...
 
void drop () throws Throwable
 drops the table from the database without any transaction management More...
 
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 More...
 
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 More...
 
Object tryExecRaw (String sql) throws Throwable
 executes some SQL so that if an error occurs the current transaction state is not lost More...
 
String [] getDropSql (Map< String, Object > opt) throws Throwable
 returns the sql required to drop the table; reimplement in subclasses if necessary More...
 
String [] getDropSql () throws Throwable
 returns the sql required to drop the table; reimplement in subclasses if necessary More...
 
void truncate () throws Throwable
 truncates all the table data without any transaction management More...
 
String getTruncateSql (Map< String, Object > opt) throws Throwable
 gets the SQL that can be used to truncate the table More...
 
String getTruncateSql () throws Throwable
 gets the SQL that can be used to truncate the table More...
 
void create (Map< String, Object > opt) throws Throwable
 creates the table with all associated properties (indexes, constraints, etc) without any transaction management More...
 
void create () throws Throwable
 creates the table with all associated properties (indexes, constraints, etc) without any transaction management More...
 
boolean emptyData () throws Throwable
 returns true if the table has no data rows, false if not More...
 
boolean empty () throws Throwable
 returns true if the table has no definitions, false if not More...
 
void setupTable (Map< String, Object > desc, Map< String, Object > opt) throws Throwable
 creates the object from a table description hash More...
 
void setupTable (Map< String, Object > desc) throws Throwable
 creates the object from a table description hash More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
String getAddPrimaryKeySql (String pkname, String[] cols) throws Throwable
 returns the SQL that can be used to add a primary key to the table More...
 
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 More...
 
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 More...
 
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 More...
 
String [] getDropPrimaryKeySql () throws Throwable
 gets a list of SQL strings that can be used to drop the primary key from the table More...
 
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 More...
 
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 More...
 
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 More...
 
String getAddUniqueConstraintSql (String cname, String[] cols) throws Throwable
 returns an SQL String that can be used to add a unique constraint to the table More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
String getDropIndexSql (String iname, Map< String, Object > opt) throws Throwable
 gets the SQL that can be used to drop an index from the table More...
 
String getDropIndexSql (String iname) throws Throwable
 gets the SQL that can be used to drop an index from the table More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
String getAddCheckConstraintSql (String cname, String src) throws Throwable
 returns an SQL String that can be used to add a check constraint to the table More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
String [] getDropTriggerSql (String tname, Map< String, Object > opt) throws Throwable
 returns SQL that can be used to drop the given trigger from the table More...
 
String [] getDropTriggerSql (String tname) throws Throwable
 returns SQL that can be used to drop the given trigger from the table More...
 
String [] getDropColumnSql (String cname, Map< String, Object > opt) throws Throwable
 returns the SQL that can be used to drop a column from the table More...
 
String [] getDropColumnSql (String cname) throws Throwable
 returns the SQL that can be used to drop a column from the table More...
 
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 More...
 
HashMap< String, Object > insert (Map< String, Object > row, Map< String, Object > opt) throws Throwable
 insert() variant More...
 
boolean hasReturning () throws Throwable
 returns true if the current database driver supports the "returning" clause in insert statements, false if not More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
int rowCount () throws Throwable
 returns the number of rows in the table More...
 
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 More...
 
AbstractSQLStatement getStatement (Map< String, Object > sh) throws Throwable
 returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments More...
 
AbstractSQLStatement getStatement () throws Throwable
 returns an AbstractSQLStatement object that will iterate all the rows in the table More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
HashMap< String, Object > [] selectRows () throws Throwable
 returns a list of hashes representing the rows in the table that match the argument hash More...
 
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 More...
 
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 More...
 
HashMap< String, Object > select () throws Throwable
 returns a hash of lists representing the columns and rows in the table that match the argument hahs More...
 
- 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
 
- Public Member Methods inherited from org.qore.jni.QoreObjectWrapper
 QoreObjectWrapper (QoreObject obj)
 creates the wrapper object with the Qore object
 
void release ()
 releases the Qore object; do not call any further methods on the object after this call
 
QoreObject getQoreObject ()
 returns the Qore object
 
String className ()
 returns the class name for the Qore object
 
boolean instanceOf (String class_name)
 returns true if the object is an instance of the given class
 

Static Public Member Methods

static HashMap< String, Object > make_cop (String cop, Object column, Object arg) throws Throwable
 returns a ColumnOperatorInfo hash More...
 
static HashMap< String, Object > make_cop (String cop, Object column) throws Throwable
 returns a ColumnOperatorInfo hash More...
 
static HashMap< String, Object > cop_as (Object column, String arg) throws Throwable
 returns a ColumnOperatorInfo hash for the "as" operator with the given argument More...
 
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) More...
 
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) More...
 
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) More...
 
static HashMap< String, Object > cop_prepend (Object column, String arg) throws Throwable
 returns a ColumnOperatorInfo hash for the "prepend" operator with the given argument More...
 
static HashMap< String, Object > cop_append (Object column, String arg) throws Throwable
 returns a ColumnOperatorInfo hash for the "append" operator with the given argument More...
 
static HashMap< String, Object > cop_value (Object arg) throws Throwable
 returns a ColumnOperatorInfo hash for the "value" (literal) operator with the given argument More...
 
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 More...
 
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 More...
 
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 More...
 
static HashMap< String, Object > cop_min (Object column) throws Throwable
 returns a ColumnOperatorInfo hash for the "min" operator; returns minimum column values More...
 
static HashMap< String, Object > cop_max (Object column) throws Throwable
 returns a ColumnOperatorInfo hash for the "max" operator; returns maximum column values More...
 
static HashMap< String, Object > cop_avg (Object column) throws Throwable
 returns a ColumnOperatorInfo hash for the "avg" operator; returns average column values More...
 
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. More...
 
static HashMap< String, Object > cop_count (Object column) throws Throwable
 returns a ColumnOperatorInfo hash for the "count" operator; returns row counts More...
 
static HashMap< String, Object > cop_count () throws Throwable
 returns a ColumnOperatorInfo hash for the "count" operator; returns row counts More...
 
static HashMap< String, Object > cop_over (Object column, String partitionby, String orderby) throws Throwable
 returns a ColumnOperatorInfo hash for the "over" clause More...
 
static HashMap< String, Object > cop_over (Object column, String partitionby) throws Throwable
 returns a ColumnOperatorInfo hash for the "over" clause More...
 
static HashMap< String, Object > cop_over (Object column) throws Throwable
 returns a ColumnOperatorInfo hash for the "over" clause More...
 
static HashMap< String, Object > cop_minus (Object column1, Object column2) throws Throwable
 returns a ColumnOperatorInfo hash for the "-" operator with the given arguments More...
 
static HashMap< String, Object > cop_plus (Object column1, Object column2) throws Throwable
 returns a ColumnOperatorInfo hash for the "+" operator with the given arguments More...
 
static HashMap< String, Object > cop_divide (Object column1, Object column2) throws Throwable
 returns a ColumnOperatorInfo hash for the "/" operator with the given arguments More...
 
static HashMap< String, Object > cop_multiply (Object column1, Object column2) throws Throwable
 returns a ColumnOperatorInfo hash for the "*" operator with the given arguments More...
 
static HashMap< String, Object > cop_year (Object column) throws Throwable
 returns a ColumnOperatorInfo hash for the "year" operator with the given argument More...
 
static HashMap< String, Object > cop_year_month (Object column) throws Throwable
 returns a ColumnOperatorInfo hash for the "year_month" operator with the given argument More...
 
static HashMap< String, Object > cop_year_day (Object column) throws Throwable
 returns a ColumnOperatorInfo hash for the "year_day" operator with the given argument More...
 
static HashMap< String, Object > cop_year_hour (Object column) throws Throwable
 returns a ColumnOperatorInfo hash for the "year_hour" operator with the given argument More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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 More...
 
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) More...
 
static HashMap< String, Object > cop_cume_dist () throws Throwable
 Analytic/window method: relative rank of the current row. More...
 
static HashMap< String, Object > cop_dense_rank () throws Throwable
 Analytic/window method: rank of the current row without gaps. More...
 
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. More...
 
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. More...
 
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. More...
 
static HashMap< String, Object > cop_percent_rank () throws Throwable
 Analytic/window method: relative rank of the current row. More...
 
static HashMap< String, Object > cop_rank () throws Throwable
 Analytic/window method: rank of the current row with gaps. More...
 
static HashMap< String, Object > cop_row_number () throws Throwable
 Analytic/window method: number of the current row within its partition, counting from 1. More...
 

Static Public Attributes

static int UpsertInsertFirst = 1
 Upsert option: insert first, if the insert fails, then update. More...
 
static int UpsertUpdateFirst = 2
 Upsert option: update first, if the update fails, then insert. More...
 
static int UpsertSelectFirst = 3
 Upsert option: select first, if the row is unchanged, do nothing, if it doesn't exist, insert, otherwise update. More...
 
static int UpsertAuto = 4
 Upsert option: if the target table is empty, use UpsertInsertFirst, otherwise use UpsertUpdateFirst. More...
 
static int UpsertInsertOnly = 5
 Upsert option: insert if the row does not exist, otherwise ignore. More...
 
static int UpsertUpdateOnly = 6
 Upsert option: update if the row exists, otherwise ignore. More...
 
static final Map< Integer, String > UpsertStrategyMap
 hash mapping upsert strategy codes to a text description More...
 
static final Map< String, Integer > UpsertStrategyDescriptionMap
 hash mapping upsert strategy descriptions to upsert strategy codes More...
 
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 More...
 
static final Map< String, Integer > UpsertResultDescriptionMap
 hash mapping upsert descriptions to codes More...
 
static final Map< Integer, String > UpsertResultLetterMap
 maps upsert result codes to single letter symbols
 

Additional Inherited Members

- Private Attributes inherited from org.qore.jni.QoreObjectWrapper
QoreObject obj
 the wrapper Qore object
 

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

Member Function Documentation

◆ addColumn() [1/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
cnamethe name of the column
opta 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
nullableif true then the column can hold NULL values; note that primary key columns cannot be nullable
Exceptions
COLUMN-ERRORno 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 
) 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
cnamethe name of the column
opta 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-ERRORno 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,
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
inamethe name of the new index
uniquea flag to tell if the new index should be unique or not
colsa single column name or a list of columns that make up the index
opta hash of options for the new index; each driver may implement its own options; for common options, see AbstractTable::IndexOptions
Exceptions
INDEX-ERRORthe 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 
) 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
inamethe name of the new index
uniquea flag to tell if the new index should be unique or not
colsa single column name or a list of columns that make up the index
Exceptions
INDEX-ERRORthe 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,
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
pknamethe name of the new primary key constraint
columnsa single column name or a list of columns that make up the primary key
opta hash of options for the new primary key; each driver may implement its own options; for common options, see AbstractTable::ConstraintOptions
Exceptions
PRIMARY-KEY-ERRORthe 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 
) 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
pknamethe name of the new primary key constraint
columnsa single column name or a list of columns that make up the primary key
Exceptions
PRIMARY-KEY-ERRORthe 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
cnamethe name of the new unique constraint
colsa single column name or a list of columns that make up the unique constraint
opta 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-ERRORthe table already has a constraint with the given name or invalid columns passed
OPTION-ERRORinvalid 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 ( 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
opta 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-ERRORtable has already been read from or created in the database

◆ create() [2/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-ERRORtable 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
conda hash of conditions for the where clause; see where_clauses for more information
sqlan optional reference to a string to return the SQL generated for the select statement
optoptional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info
Returns
the count of rows deleted
Exceptions
WHERE-ERRORunknown operator or invalid arguments given in the cond hash for the where clause

◆ drop() [1/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
optoptional callback options; see Callback Options for more info
Exceptions
OPTION-ERRORinvalid 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 ( ) throws Throwable
inline

drops the table from the database without any transaction management

Example:
table.drop();
Exceptions
OPTION-ERRORinvalid 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

◆ getAddCheckConstraintSql() [1/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
cnamethe name of the new constraint
srcthe source of the constraint clause
copta hash of options for the new constraint; each driver may implement its own options; for common options, see ConstraintOptions
opta 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-ERRORinvalid or unsupported option passed
CHECK-CONSTRAINT-ERRORthe 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
cnamethe name of the new constraint
srcthe source of the constraint clause
copta hash of options for the new constraint; each driver may implement its own options; for common options, see ConstraintOptions
opta 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-ERRORinvalid or unsupported option passed
CHECK-CONSTRAINT-ERRORthe 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 
) 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
cnamethe name of the new constraint
srcthe source of the constraint clause
copta hash of options for the new constraint; each driver may implement its own options; for common options, see ConstraintOptions
opta 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-ERRORinvalid or unsupported option passed
CHECK-CONSTRAINT-ERRORthe 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,
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
cnamethe name of the column
copta 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
nullableif true then the column can hold NULL values; note that primary key columns cannot be nullable
opta 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-ERRORinvalid or unsupported option passed
COLUMN-ERRORno 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
cnamethe name of the column
copta 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
nullableif 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-ERRORinvalid or unsupported option passed
COLUMN-ERRORno 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 
) 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
cnamethe name of the column
copta 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-ERRORinvalid or unsupported option passed
COLUMN-ERRORno 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,
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
cnamethe name of the new foreign constraint
colsa single column name or a list of columns in the local table that make up the foreign constraint
tablethe name of the other table that the constraint targets
tcolsa 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
fkopta hash of options for the new foreign constraint; each driver may implement its own options; for common options, see ForeignConstraintOptions
opta 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-ERRORinvalid or unsupported option passed
FOREIGN-CONSTRAINT-ERRORthe 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,
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
cnamethe name of the new foreign constraint
colsa single column name or a list of columns in the local table that make up the foreign constraint
tablethe name of the other table that the constraint targets
tcolsa 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
fkopta 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-ERRORinvalid or unsupported option passed
FOREIGN-CONSTRAINT-ERRORthe 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 
) 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
cnamethe name of the new foreign constraint
colsa single column name or a list of columns in the local table that make up the foreign constraint
tablethe name of the other table that the constraint targets
tcolsa 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-ERRORinvalid or unsupported option passed
FOREIGN-CONSTRAINT-ERRORthe 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 
) 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
cnamethe name of the new foreign constraint
colsa single column name or a list of columns in the local table that make up the foreign constraint
tablethe 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-ERRORinvalid or unsupported option passed
FOREIGN-CONSTRAINT-ERRORthe 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,
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
inamethe name of the new index
uniquea flag to tell if the new index should be unique or not
colsa single column name or a list of columns that make up the index
ixopta hash of options for the new index; each driver may implement its own options; for common options, see Index Options
opta 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-ERRORinvalid or unsupported option passed
INDEX-ERRORthe 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
inamethe name of the new index
uniquea flag to tell if the new index should be unique or not
colsa single column name or a list of columns that make up the index
ixopta 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-ERRORinvalid or unsupported option passed
INDEX-ERRORthe 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 
) 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
inamethe name of the new index
uniquea flag to tell if the new index should be unique or not
colsa 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-ERRORinvalid or unsupported option passed
INDEX-ERRORthe 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,
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
pknamethe name of the new primary key constraint
colsa single column name or a list of columns that make up the primary key
pkopta hash of options for the new primary key; each driver may implement its own options; for common options, see org.qore.sqlutil.AbstractTableConstraintOptions
opta 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-ERRORinvalid or unsupported option passed
PRIMARY-KEY-ERRORthe 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
pknamethe name of the new primary key constraint
colsa single column name or a list of columns that make up the primary key
pkopta 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-ERRORinvalid or unsupported option passed
PRIMARY-KEY-ERRORthe 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 
) 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
pknamethe name of the new primary key constraint
colsa 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-ERRORinvalid or unsupported option passed
PRIMARY-KEY-ERRORthe 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,
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
tnamethe name of the new trigger
srcthe source of the trigger
topta hash of options for the new trigger; each driver may implement its own options; for common options, see TriggerOptions
opta 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-ERRORinvalid or unsupported option passed
TRIGGER-ERRORthe 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
tnamethe name of the new trigger
srcthe source of the trigger
topta 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-ERRORinvalid or unsupported option passed
TRIGGER-ERRORthe 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 
) 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
tnamethe name of the new trigger
srcthe source of the trigger
Returns
a list of SQL strings that can be used to add a trigger to the table
Exceptions
OPTION-ERRORinvalid or unsupported option passed
TRIGGER-ERRORthe 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,
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
cnamethe name of the new unique constraint
colsa single column name or a list of columns that make up the unique constraint
ukopta hash of options for the new unique constraint; each driver may implement its own options; for common options, see ConstraintOptions
opta 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-ERRORinvalid or unsupported option passed
UNIQUE-CONSTRAINT-ERRORthe 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
cnamethe name of the new unique constraint
colsa single column name or a list of columns that make up the unique constraint
ukopta 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-ERRORinvalid or unsupported option passed
UNIQUE-CONSTRAINT-ERRORthe 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 
) 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
cnamethe name of the new unique constraint
colsa 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-ERRORinvalid or unsupported option passed
UNIQUE-CONSTRAINT-ERRORthe 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,
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
cnamethe name of the column
opta hash of options for the SQL string; see Align Table Options for common options; each driver can support additional driver-specific options
Exceptions
OPTION-ERRORinvalid 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) 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
cnamethe name of the column
Exceptions
OPTION-ERRORinvalid 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,
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
cnamethe name of the column to drop
opta 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-ERRORinvalid or unsupported option passed
COLUMN-ERRORthe 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) 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
cnamethe name of the column to drop
Returns
the SQL that can be used to drop a column from the table
Exceptions
OPTION-ERRORinvalid or unsupported option passed
COLUMN-ERRORthe 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,
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
cnamethe name of the constraint to drop
opta 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-ERRORinvalid 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) 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
cnamethe 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-ERRORinvalid 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,
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
cnamethe name of the constraint to drop
opta 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-ERRORinvalid or unsupported option passed
CONSTRAINT-ERRORthe 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) 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
cnamethe name of the constraint to drop
opta 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-ERRORinvalid or unsupported option passed
CONSTRAINT-ERRORthe 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,
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
inamethe name of the index to drop
opta 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-ERRORinvalid or unsupported option passed
INDEX-ERRORthe 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) 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
inamethe name of the index to drop
Returns
the SQL that can be used to drop an index from the table
Exceptions
OPTION-ERRORinvalid or unsupported option passed
INDEX-ERRORthe 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 ( 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
opta 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-ERRORinvalid or unsupported option passed
PRIMARY-KEY-ERRORthe 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 ( ) 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-ERRORinvalid or unsupported option passed
PRIMARY-KEY-ERRORthe 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 ( 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
optoptional 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-ERRORinvalid or unknown callback option

◆ getDropSql() [2/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-ERRORinvalid or unknown callback option

◆ getDropTriggerSql() [1/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
tnamethe name of the trigger to drop
opta 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-ERRORinvalid or unsupported option passed
TRIGGER-ERRORthe 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) 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
tnamethe name of the trigger to drop
Returns
the SQL that can be used to drop the given trigger from the table
Exceptions
OPTION-ERRORinvalid or unsupported option passed
TRIGGER-ERRORthe 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,
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
cnamethe name of the column
copta 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
nullableif true then the column can hold NULL values; note that primary key columns cannot be nullable
opta 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-ERRORinvalid or unsupported option passed
COLUMN-ERRORno 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
cnamethe name of the column
copta 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
nullableif 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-ERRORinvalid or unsupported option passed
COLUMN-ERRORno 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 
) 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
cnamethe name of the column
copta 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-ERRORinvalid or unsupported option passed
COLUMN-ERRORno 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,
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_namethe current name of the column
new_namethe new name of the column
opta 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-ERRORinvalid or unsupported option passed
COLUMN-ERRORif 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 
) 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_namethe current name of the column
new_namethe new name of the column
opta 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-ERRORinvalid or unsupported option passed
COLUMN-ERRORif 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 ( 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
sha hash of conditions for the select statement; see select option hash for information about this argument
optoptional 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-ERRORinvalid 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

◆ 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
sha 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 ( ) 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
See also
getStatementNoExec()
Since
SqlUtil 1.5

◆ getStatementNoExec() [1/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
sha hash of conditions for the select statement; see select option hash for information about this argument
optoptional 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-ERRORinvalid 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

◆ 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
sha 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 ( ) 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
See also
getStatement()
Since
SqlUtil 1.5

◆ getTruncateSql() [1/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
opta 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-ERRORinvalid 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 ( ) 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-ERRORinvalid 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
rowa 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-ERRORan 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
rowa 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
optoptional 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
colsthe list of column names to use to insert in the current table
sourcethe source table for the select statement
sha hash of conditions for the select statement; see select option hash for information about this argument
optoptional SQL data operation callback options; see SqlDataCallbackOptions for more inf
Returns
the number of rows inserted
Exceptions
OPTION-ERRORinvalid or unsupported option
COLUMN-ERRORunknown 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 ( 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
sha hash of conditions for the select statement; see select option hash for information about this argument
optoptional 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-ERRORinvalid 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
sha 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-ERRORinvalid 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 ( ) 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-ERRORinvalid 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,
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
sha hash of conditions for the select statement; see select option hash for information about this argument
optoptional 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-ERRORinvalid 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-ERRORmore 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) 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
sha 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-ERRORinvalid 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-ERRORmore 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 ( 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
sha hash of conditions for the select statement; see select option hash for information about this argument
optoptional 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-ERRORinvalid 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
sha 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-ERRORinvalid 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 ( ) 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-ERRORinvalid 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,
Map< String, Object >  opt 
) throws Throwable
inline

creates the object from a table description hash

Parameters
desca table description hash describing the table
optan 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-ERRORinvalid or unsupported option passed
DESCRIPTION-ERRORinvalid or unsupported description hash value passed

◆ setupTable() [2/2]

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

creates the object from a table description hash

Parameters
desca table description hash describing the table
Exceptions
OPTION-ERRORinvalid or unsupported option passed
DESCRIPTION-ERRORinvalid 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
sqlthe 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
sqlthe SQL to execute
argsthe 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
sqlthe 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
setthe 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)
conda hash of conditions for the where clause; see where_clauses for more information
sqlan optional reference to a string to return the SQL generated for the select statement
optoptional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info
Returns
the count of rows updated
Exceptions
UPDATE-ERRORthe set hash is empty
WHERE-ERRORunknown 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,
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
rowa hash representing the row to insert or update
upsert_strategysee Upsert Strategy Codes for possible values for the upsert strategy
opta 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-ERRORan unknown column was referenced in the hash to be inserted
UPSERT-ERRORno 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 
) 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
rowa hash representing the row to insert or update
upsert_strategysee 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-ERRORan unknown column was referenced in the hash to be inserted
UPSERT-ERRORno 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,
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
tthe 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
sha hash of conditions for the select statement; see select option hash for information about this argument
upsert_strategysee Upsert Strategy Codes for possible values for the upsert strategy
opta 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-ERRORinvalid or unsupported option
COLUMN-ERRORan unknown column was referenced in the hash to be inserted
UPSERT-ERRORno 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

    See also

◆ 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
tthe 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
sha hash of conditions for the select statement; see select option hash for information about this argument
upsert_strategysee 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-ERRORinvalid or unsupported option
COLUMN-ERRORan unknown column was referenced in the hash to be inserted
UPSERT-ERRORno 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

    See also

◆ upsertFromSelect() [3/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
tthe 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
sha 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-ERRORinvalid or unsupported option
COLUMN-ERRORan unknown column was referenced in the hash to be inserted
UPSERT-ERRORno 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

    See also

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: