Package com.inductiveautomation.ignition.common.script.builtin

Class AbstractDBUtilities

java.lang.Object

com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities

Direct Known Subclasses:

ClientDBUtilities

public abstract class AbstractDBUtilities
extends Object

Base implementation for database functions (system.db.*).

These functions provide access to configured SQL databases. Shared across Gateway and Client scopes.

Field Summary

Fields

Modifier and Type	Field	Description
static final int	ARRAY
static final int	BIGINT
static final int	BINARY
static final int	BIT
static final int	BLOB
static final int	BOOLEAN
static final int	CHAR
static final int	CLOB
static final int	DATALINK
static final int	DATE
static final int	DECIMAL
static final int	DISTINCT
static final int	DOUBLE
static final int	FLOAT
static final int	INTEGER
static final int	JAVA_OBJECT
static final int	LONGNVARCHAR
static final int	LONGVARBINARY
static final int	LONGVARCHAR
static final int	NCHAR
static final int	NCLOB
static final int	NULL
static final int	NUMERIC
static final int	NVARCHAR
static final int	ORACLE_CURSOR
static final int	OTHER
static final int	READ_COMMITTED
static final int	READ_UNCOMMITTED
static final int	REAL
static final int	REF
static final int	REPEATABLE_READ
static final int	ROWID
protected static ThreadLocal<SimpleDateFormat>	SDF_THREAD_LOCAL
static final int	SERIALIZABLE
static final int	SMALLINT
static final int	SQLXML
static final int	STRUCT
static final int	TIME
static final int	TIMESTAMP
static final int	TINYINT
static final int	VARBINARY
static final int	VARCHAR

Constructor Summary

Constructors

Constructor	Description
AbstractDBUtilities()

Method Summary

Modifier and Type	Method	Description
protected abstract String	_beginTransaction(String datasource, int isolationLevel, long timeout)
protected abstract void	_call(SProcCall callContext)
protected abstract void	_clearAllQueryCaches(String projectName)
protected abstract void	_clearQueryCache(String projectName, Set<String> path)
protected abstract void	_closeTransaction(String txId)
protected abstract void	_commitTransaction(String txId)
protected abstract Object	_execNamedQuery(String projectName, String path, Map<String,Object> parameters, String tx, boolean getKey)
protected abstract boolean	_execNamedQueryAsync(String projectName, String path, Map<String,Object> parameters)
protected abstract String	_getDatasourceName(String txId)
protected abstract void	_rollbackTransaction(String txId)
protected abstract Dataset	_runPrepQuery(String query, String datasource, String txId, Object[] args)
protected abstract Integer	_runPrepStmt(String query, String datasource, String txId, boolean getIds, boolean skipAudit, Object[] args)
protected abstract Dataset	_runQuery(String query, String datasource, String txId)
protected abstract Integer	_runUpdateQuery(String s, String datasource, String txId, boolean getIds, boolean skipAudit)
String	beginTransaction(org.python.core.PyObject[] pyArgs, String[] keywords)	Starts a new database transaction.
void	clearQueryCache(org.python.core.PyObject[] values, String[] keywords)
void	closeTransaction(String txId)
void	commitTransaction(String txId)
SProcCall	createSProcCall(org.python.core.PyObject[] pyArgs, String[] keywords)
static String	dataSetToCSV(boolean headerRow, Dataset ds)	Deprecated. Use DatasetUtilities.toCSVJava()
static String	dateFormat(Date date, String pattern)	Deprecated. Use system.date.format instead.
protected org.python.core.PyException	error(String function, Exception cause, String... args)
Dataset	execQuery(org.python.core.PyObject[] pyArgs, String[] keywords)
Object	execScalar(org.python.core.PyObject[] pyArgs, String[] keywords)
void	execSProcCall(SProcCall callContext)
Long	execUpdate(org.python.core.PyObject[] pyArgs, String[] keywords)
boolean	execUpdateAsync(org.python.core.PyObject[] pyArgs, String[] keywords)
protected static Map<String,Object>	extractParameters(PyArgParser args)
protected static String	extractPath(PyArgParser args)
protected String	extractProject(PyArgParser args)
protected abstract List<RpcDatasourceMeta>	findDatasources()
Dataset	getConnectionInfo(String name)
Dataset	getConnections()
protected static String	getDatasource(PyArgumentMap args)	Returns the datasource defined by the "database" parameter, or the default set on the ScriptContext, if available.
protected abstract Optional<String>	getDefaultProject()
protected abstract @NonNull Set<String>	getQueryParameters(String project, String queryPath)
protected String	getTransactionDatasource(String txId)
void	rollbackTransaction(String txId)
DatasetUtilities.PyDataSet	runPrepQuery(org.python.core.PyObject[] pyArgs, String[] keywords)
Integer	runPrepStmt(String query, Object[] args)	Deprecated.
Integer	runPrepStmt(String query, String datasource, Object[] args)	Deprecated.
Integer	runPrepStmtGetKey(String query, Object[] args)	Deprecated.
Integer	runPrepStmtGetKey(String query, String datasource, Object[] args)	Deprecated.
Integer	runPrepUpdate(org.python.core.PyObject[] pyArgs, String[] keywords)	Executes a prepared update query (INSERT, UPDATE, DELETE).
DatasetUtilities.PyDataSet	runQuery(org.python.core.PyObject[] pyArgs, String[] keywords)	Deprecated. Use execQuery or runPrepQuery(org.python.core.PyObject[], java.lang.String[]) for improved performance and security.
Object	runScalarPrepQuery(org.python.core.PyObject[] pyArgs, String[] keywords)
Object	runScalarQuery(org.python.core.PyObject[] pyArgs, String[] keywords)	Deprecated. Use execQuery or runScalarPrepQuery(org.python.core.PyObject[], java.lang.String[]) for improved performance and security.
Integer	runUpdateQuery(org.python.core.PyObject[] pyArgs, String[] keywords)	Deprecated. Use execUpdate or runPrepUpdate(org.python.core.PyObject[], java.lang.String[]) for improved performance and security.
Integer	runUpdateQueryGetKey(String q)	Deprecated.
Integer	runUpdateQueryGetKey(String q, String datasource)	Deprecated.
protected Dataset	toDataset(List<RpcDatasourceMeta> list)	S1845: We have multiple methods named toDataSet that differs in case (note the uppercase S).
static Dataset	toDataSet(Dataset pds)	Deprecated. use DatasetUtilities.toDataSet()
static Dataset	toDataSet(org.python.core.PySequence headers, org.python.core.PySequence pyData)	Deprecated. Use DatasetUtilities.toDataSet()
static DatasetUtilities.PyDataSet	toPyDataSet(Dataset dataset)	Deprecated. Use DatasetUtilities.toPyDataSet()

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Details

ORACLE_CURSOR

public static final int ORACLE_CURSOR

See Also:

• Constant Field Values

BIT

public static final int BIT

See Also:

• Constant Field Values

TINYINT

public static final int TINYINT

See Also:

• Constant Field Values

SMALLINT

public static final int SMALLINT

See Also:

• Constant Field Values

INTEGER

public static final int INTEGER

See Also:

• Constant Field Values

BIGINT

public static final int BIGINT

See Also:

• Constant Field Values

FLOAT

public static final int FLOAT

See Also:

• Constant Field Values

REAL

public static final int REAL

See Also:

• Constant Field Values

DOUBLE

public static final int DOUBLE

See Also:

• Constant Field Values

NUMERIC

public static final int NUMERIC

See Also:

• Constant Field Values

DECIMAL

public static final int DECIMAL

See Also:

• Constant Field Values

CHAR

public static final int CHAR

See Also:

• Constant Field Values

VARCHAR

public static final int VARCHAR

See Also:

• Constant Field Values

LONGVARCHAR

public static final int LONGVARCHAR

See Also:

• Constant Field Values

DATE

public static final int DATE

See Also:

• Constant Field Values

TIME

public static final int TIME

See Also:

• Constant Field Values

TIMESTAMP

public static final int TIMESTAMP

See Also:

• Constant Field Values

BINARY

public static final int BINARY

See Also:

• Constant Field Values

VARBINARY

public static final int VARBINARY

See Also:

• Constant Field Values

LONGVARBINARY

public static final int LONGVARBINARY

See Also:

• Constant Field Values

NULL

public static final int NULL

See Also:

• Constant Field Values

OTHER

public static final int OTHER

See Also:

• Constant Field Values

JAVA_OBJECT

public static final int JAVA_OBJECT

See Also:

• Constant Field Values

DISTINCT

public static final int DISTINCT

See Also:

• Constant Field Values

STRUCT

public static final int STRUCT

See Also:

• Constant Field Values

ARRAY

public static final int ARRAY

See Also:

• Constant Field Values

BLOB

public static final int BLOB

See Also:

• Constant Field Values

CLOB

public static final int CLOB

See Also:

• Constant Field Values

REF

public static final int REF

See Also:

• Constant Field Values

DATALINK

public static final int DATALINK

See Also:

• Constant Field Values

BOOLEAN

public static final int BOOLEAN

See Also:

• Constant Field Values

ROWID

public static final int ROWID

See Also:

• Constant Field Values

NCHAR

public static final int NCHAR

See Also:

• Constant Field Values

NVARCHAR

public static final int NVARCHAR

See Also:

• Constant Field Values

LONGNVARCHAR

public static final int LONGNVARCHAR

See Also:

• Constant Field Values

NCLOB

public static final int NCLOB

See Also:

• Constant Field Values

SQLXML

public static final int SQLXML

See Also:

• Constant Field Values

READ_COMMITTED

public static final int READ_COMMITTED

See Also:

• Constant Field Values

READ_UNCOMMITTED

public static final int READ_UNCOMMITTED

See Also:

• Constant Field Values

REPEATABLE_READ

public static final int REPEATABLE_READ

See Also:

• Constant Field Values

SERIALIZABLE

public static final int SERIALIZABLE

See Also:

• Constant Field Values

SDF_THREAD_LOCAL

protected static ThreadLocal<SimpleDateFormat> SDF_THREAD_LOCAL

Constructor Details

AbstractDBUtilities

public AbstractDBUtilities()

Method Details

dateFormat

@Deprecated(since="8.3.0")
public static String dateFormat(Date date,
 String pattern)

Deprecated.

Use system.date.format instead.

getDatasource

protected static String getDatasource(PyArgumentMap args)

Returns the datasource defined by the "database" parameter, or the default set on the ScriptContext, if available.

toPyDataSet

@Deprecated
public static DatasetUtilities.PyDataSet toPyDataSet(Dataset dataset)

Deprecated.

Use DatasetUtilities.toPyDataSet()

For backwards compatibility. @see DatasetUtilities

toDataSet

@Deprecated
public static Dataset toDataSet(Dataset pds)

Deprecated.

use DatasetUtilities.toDataSet()

For backwards compatibility. @see DatasetUtilities

toDataSet

@Deprecated
public static Dataset toDataSet(org.python.core.PySequence headers,
 org.python.core.PySequence pyData)

Deprecated.

Use DatasetUtilities.toDataSet()

For backwards compatibility.

See Also:

• DatasetUtilities.toDataset(PySequence, PySequence)

dataSetToCSV

@Deprecated
public static String dataSetToCSV(boolean headerRow,
 Dataset ds)

Deprecated.

Use DatasetUtilities.toCSVJava()

For backwards compatibility. @see DatasetUtilities

runUpdateQuery

@Deprecated(since="8.3.0")
public Integer runUpdateQuery(org.python.core.PyObject[] pyArgs,
 String[] keywords)

Deprecated.

Use execUpdate or runPrepUpdate(org.python.core.PyObject[], java.lang.String[]) for improved performance and security.

runUpdateQueryGetKey

@Deprecated
public Integer runUpdateQueryGetKey(String q)

Deprecated.

runUpdateQueryGetKey

@Deprecated
public Integer runUpdateQueryGetKey(String q,
 String datasource)

Deprecated.

_runUpdateQuery

protected abstract Integer _runUpdateQuery(String s,
 String datasource,
 String txId,
 boolean getIds,
 boolean skipAudit)
                                    throws Exception

Throws:

Exception

runPrepStmt

@Deprecated
public Integer runPrepStmt(String query,
 Object[] args)

Deprecated.

runPrepStmt

@Deprecated
public Integer runPrepStmt(String query,
 String datasource,
 Object[] args)

Deprecated.

runPrepStmtGetKey

@Deprecated
public Integer runPrepStmtGetKey(String query,
 Object[] args)

Deprecated.

runPrepStmtGetKey

@Deprecated
public Integer runPrepStmtGetKey(String query,
 String datasource,
 Object[] args)

Deprecated.

runPrepUpdate

public Integer runPrepUpdate(org.python.core.PyObject[] pyArgs,
 String[] keywords)

Executes a prepared update query (INSERT, UPDATE, DELETE).

This should be used for all parameter-driven queries to prevent SQL injection.

Parameters:

pyArgs - The keyword arguments: 'query', 'args', 'database', 'tx', 'getKey', 'skipAudit'.

keywords - The keyword names.

Returns:

The number of rows affected, or the newly generated key if 'getKey' is true.

_runPrepStmt

protected abstract Integer _runPrepStmt(String query,
 String datasource,
 String txId,
 boolean getIds,
 boolean skipAudit,
 Object[] args)
                                 throws Exception

Throws:

Exception

runPrepQuery

public DatasetUtilities.PyDataSet runPrepQuery(org.python.core.PyObject[] pyArgs,
 String[] keywords)

_runPrepQuery

protected abstract Dataset _runPrepQuery(String query,
 String datasource,
 String txId,
 Object[] args)
                                  throws Exception

Throws:

Exception

runScalarPrepQuery

public Object runScalarPrepQuery(org.python.core.PyObject[] pyArgs,
 String[] keywords)

runQuery

@Deprecated(since="8.3.0")
public DatasetUtilities.PyDataSet runQuery(org.python.core.PyObject[] pyArgs,
 String[] keywords)

Deprecated.

Use execQuery or runPrepQuery(org.python.core.PyObject[], java.lang.String[]) for improved performance and security.

runScalarQuery

@Deprecated(since="8.3.0")
public Object runScalarQuery(org.python.core.PyObject[] pyArgs,
 String[] keywords)

Deprecated.

Use execQuery or runScalarPrepQuery(org.python.core.PyObject[], java.lang.String[]) for improved performance and security.

clearQueryCache

public void clearQueryCache(org.python.core.PyObject[] values,
 String[] keywords)
                     throws Exception

Throws:

Exception

error

protected org.python.core.PyException error(String function,
 Exception cause,
 String... args)

getConnections

public Dataset getConnections()

getConnectionInfo

public Dataset getConnectionInfo(String name)

_runQuery

protected abstract Dataset _runQuery(String query,
 String datasource,
 String txId)
                              throws Exception

Throws:

Exception

findDatasources

protected abstract List<RpcDatasourceMeta> findDatasources()

toDataset

protected Dataset toDataset(List<RpcDatasourceMeta> list)

S1845: We have multiple methods named toDataSet that differs in case (note the uppercase S). Those methods are marked as deprecated. Newer methods should use Dataset with a lower case s. This is the form (dataset vs data set) that is used for our online documentation. The suppression and this comment should be removed when those deprecated methods are removed.

beginTransaction

public String beginTransaction(org.python.core.PyObject[] pyArgs,
 String[] keywords)
                        throws SQLException

Starts a new database transaction.

The returned transaction ID must be passed to subsequent database calls to execute them within this transaction. You must eventually call commitTransaction(String) or rollbackTransaction(String) to release the connection.

Parameters:

pyArgs - The keyword arguments: 'database', 'isolationLevel', 'timeout'.

keywords - The keyword names.

Returns:

A transaction ID string.

Throws:

SQLException - If a database error occurs.

commitTransaction

public void commitTransaction(String txId)
                       throws SQLException

Throws:

SQLException

rollbackTransaction

public void rollbackTransaction(String txId)
                         throws SQLException

Throws:

SQLException

closeTransaction

public void closeTransaction(String txId)
                      throws SQLException

Throws:

SQLException

_beginTransaction

protected abstract String _beginTransaction(String datasource,
 int isolationLevel,
 long timeout)
                                     throws Exception

Throws:

Exception

_commitTransaction

protected abstract void _commitTransaction(String txId)
                                    throws Exception

Throws:

Exception

_rollbackTransaction

protected abstract void _rollbackTransaction(String txId)
                                      throws Exception

Throws:

Exception

_closeTransaction

protected abstract void _closeTransaction(String txId)
                                   throws Exception

Throws:

Exception

_getDatasourceName

protected abstract String _getDatasourceName(String txId)
                                      throws Exception

Throws:

Exception

_clearQueryCache

protected abstract void _clearQueryCache(String projectName,
 Set<String> path)
                                  throws Exception

Throws:

Exception

_clearAllQueryCaches

protected abstract void _clearAllQueryCaches(String projectName)
                                      throws Exception

Throws:

Exception

_execNamedQuery

protected abstract Object _execNamedQuery(String projectName,
 String path,
 Map<String,Object> parameters,
 String tx,
 boolean getKey)
                                   throws Exception

Throws:

Exception

_execNamedQueryAsync

protected abstract boolean _execNamedQueryAsync(String projectName,
 String path,
 Map<String,Object> parameters)
                                         throws Exception

Throws:

Exception

createSProcCall

public SProcCall createSProcCall(org.python.core.PyObject[] pyArgs,
 String[] keywords)

execSProcCall

public void execSProcCall(SProcCall callContext)

_call

protected abstract void _call(SProcCall callContext)
                       throws Exception

Throws:

Exception

getTransactionDatasource

protected String getTransactionDatasource(String txId)

execQuery

public Dataset execQuery(org.python.core.PyObject[] pyArgs,
 String[] keywords)
                  throws Exception

Throws:

Exception

getDefaultProject

protected abstract Optional<String> getDefaultProject()

getQueryParameters

protected abstract @NonNull Set<String> getQueryParameters(String project,
 String queryPath)

execUpdate

public Long execUpdate(org.python.core.PyObject[] pyArgs,
 String[] keywords)
                throws Exception

Throws:

Exception

execUpdateAsync

public boolean execUpdateAsync(org.python.core.PyObject[] pyArgs,
 String[] keywords)
                        throws Exception

Throws:

Exception

execScalar

public Object execScalar(org.python.core.PyObject[] pyArgs,
 String[] keywords)
                  throws Exception

Throws:

Exception

extractProject

protected String extractProject(PyArgParser args)

extractPath

protected static String extractPath(PyArgParser args)

extractParameters

protected static Map<String,Object> extractParameters(PyArgParser args)