AppDb

Description

This object represents the current connection to the database. There is only one such object defined at a time. Scripts can use AppDb objects provided in the Shell but cannot create new AppDb objects.

Inheritance

AppDb does not inherit from any object type.

Methods

• ColName (colname): Converts the parameter to the form of a valid database column name, using the same transformation applied when you enter logical field names in SBM Composer. There is no database lookup of actual field or column names; it is a simple text transformation. For example, given "submit date?", this function returns "TS_SUBMIT_DATE". "TS_" is prepended, alphanumeric characters are converted to uppercase, spaces are converted to underscores, and other characters are omitted.

  This function provides a measure of safety when working with column names, because the output is guaranteed to be of a legal form. However, because logical names can be changed at any time after a column's creation, passing a field's logical name to this function will not necessarily yield its actual database name. This function can assist an SBM ModScript programmer who recalls a column's "basic" database name but not the exact formatting rules for punctuation and spaces. An alternative to calling this function is to be sure of the exact column name by looking at the field's database name in SBM Composer or opening the database itself to see the desired column.

  Input: colname (string) – Base name of field or column (without "TS_"). Can contain spaces, punctuation, and mixed case.

  Output: N/A

  Return: string – A column name in legal database form, containing only upper case letters and underscores.

• GetConnectionInfo(dsn, dbname, srvname, remote) (On-premise only): This method provides information on the data source name, the name of the database, the name or IP address of the server that contains the database currently connected to SBM, and whether the connection is remote. If you are connecting to a Microsoft Access database, the server name is not returned.

  Input: N/A

  Output: dsn (string) – The data source name from ODBC the connection is using to access the database.

  Output: dbname (string) – The full database name that the connection is accessing.

  Output: srvname (string) – The server name the connection is using to access SBM. This could be a machine name or an IP address.

  Output: remote (bool) – True if the connection is a remote administrator; false if not. Since scripts cannot be executed from a remote connection, this is always false.

  Return: bool. True if the method was successful; false if not.

• ReadFolderItems(list, folderId) (On-premise only): This method provides a list of items in a folder. Create a FolderItemList and pass in the TS_ID from the TS_Folders table of the folder you would like. Keep in mind that a folder ID is needed to retrieve this list and the folder table contains an inbox for every user so it may be a bit of processing to find the correct TS_ID from the TS_Folders table before this method can be used effectively.

  Input: folderId (int) – The parent folder of the items you wish to access.

  Output: list (FolderItemList) – The list of items in the folder.

  Return: bool. True if the method is successful. False if not.

• GetDbType(): Returns the database connection type.

  Return: int

  • Oracle: 1
  • SQL Server: 2
  • Import Source DB possible values:
    • Access: 3
    • Sybase: 4
    • DB2: 5

  Example:

  var myDbType = Shell.Db.GetDbType();

• IsMSSQL(): Returns true if database connection is SQL Server.

  Return: bool

  Example:

  if( Shell.Db().IsMSSQL() ){
    Ext.WriteStream( "The Db is MSSQL" );}

• IsOracle(): Returns true if database connection is Oracle.

  Return: bool

  Example:

  if(Shell.Db().IsOracle()){
    Ext.WriteStream( "The Db is Oracle" );}

• ReadIntWithSQL( sqlText [, Vector<Pair> params] ) (On-premise only): Reads any single integer out of the database.

  Params is an optional Vector storing SQL bind parameters, where each entry is a Pair, where the first value is the parameter type and the second value is the value to bind to the SQL parameter, corresponds to DBTypeConstants.

  Return: int

  • BigInt (64 bit): -5
  • Integer (32 bit): 4
  • SmallInt (16 bit): 5
  • TinyInt (8 bit): -6
  • Double (64 bit floating point): 8
  • Float (32 bit floating point): 6
  • Date: 9
  • Char: 1
  • Varchar: 12
  • LongVarchar: -1

  Example:

  Shell.Db.ReadIntWithSQL
  ( "select TS_SOLUTIONID from TS_TABLES where TS_ID=1000 and TS_TYPE=1" );

  Example:

  Shell.Db.ReadIntWithSQL
  ( "select TS_ID from TS_TABLES where TS_DBNAME=? and TS_TYPE=?", 
  [Pair(12,"UBG_ISSUES"), Pair(4,1)] );

• ReadTextWithSQL( sqlText [, Vector<Pair> params] ) (On-premise only): Reads any single varchar value, up to 256 characters, out of the database.

  Input: Params – See AppDb.ReadIntWithSQL.

  Return: string

  Example:

  Shell.Db.ReadTextWithSQL
  ( "select TS_DBNAME from TS_TABLES where TS_ID=1000 and TS_TYPE=1" );

  Example:

  Shell.Db.ReadTextWithSQL
  ( "select TS_DBNAME from TS_TABLES where TS_ID=? and TS_TYPE=?", [Pair(4,1000), Pair(4,1)] );

• ReadIntegersWithSQL( sqlText, Vector& out, [, Vector<Pair> params] ) (On-premise only): Reads multiple rows of a single integer column from the database, filling the Vector "out".

  Input: Params – See AppDb.ReadIntWithSQL.

  Return: bool

  Example:

  var v = [];
  Shell.Db.ReadIntegersWithSQL 
  ( "select TS_SOLUTIONID from TS_TABLES where TS_TYPE=1", v ); 
  Shell.Db.ReadIntegersWithSQL 
  ( "select TS_SOLUTIONID from TS_TABLES where TS_TYPE=?", v, [Pair(4,1)] );

• ReadTextValsWithSQL( sqlText, Vector& out, [, Vector<Pair> params] ) (On-premise only): Reads multiple rows of a single text column from the database, filling the Vector "out".

  Input: Params – See AppDb.ReadIntWithSQL.

  Return: bool

  Example:

  var v = [];
  Shell.Db.ReadTextValsWithSQL 
  ( "select TS_DBNAME from TS_TABLES where TS_TYPE=1", v );
  Shell.Db.ReadTextValsWithSQL 
  ( "select TS_DBNAME from TS_TABLES where TS_TYPE=?", v, [Pair(4,1)] );

• ReadIntegerPairsWithSQL( sqlText, Vector& out, [, Vector<Pair> params] ) (On-premise only): Reads multiple rows of two integer columns from the database, filling the Vector "out" with Pair<int,int> values.

  Input: Params – See AppDb.ReadIntWithSQL.

  Return: bool

  Example:

  var v = [];
  Shell.Db.ReadIntegerPairsWithSQL 
  ( "select TS_ID, TS_SOLUTIONID from TS_TABLES where TS_TYPE=1", v );
  Shell.Db.ReadIntegerPairsWithSQL 
  ( "select TS_ID, TS_SOLUTIONID from TS_TABLES where TS_TYPE=?", v, [Pair(4,1)] );

• Blob interaction:

  • WriteBlobToFile( blobID, filepath/filename ): Get the contents of a blob written to the file system. Use Ext.ReadTextFile, Ext.WriteTextFile, and Ext.AppendTextFile to interact with the file.

    Return: bool

    Example:

    var blobWrite = Shell.Db.WriteBlobToFile( 36976, "C:\\AppScript\\BlobFile.txt" );

  • UpdateBlobFromFile( blobID, filepath/filename ): Put a file's contents into a blob.

    Return: bool

    Example:

    var blobRead = Shell.Db.UpdateBlobFromFile( 36977, "C:\\AppScript\\BlobFile.txt" );

Properties

UserId: (long integer) – The TS_ID of the current user.