SQLHelper

Object
- SQLHelper

All Implemented Interfaces:

RemoteAccess
```
public class SQLHelper
extends Object
implements RemoteAccess
```
purpose: provide static methods to deal with sql against oracle/sqlserver because sqlserver often runs case sensitive regarding table and column names, two mapping tables are kept in memory for lookup:
SS_TAB_NAME maps from lowercase table name to db specific name, and
SS_COL_NAMES holds a Map for similar column name lookup per table (looked up by lowercase table name). since on most installations it is faster to read in the setup from a file on disk, the default is to save the setup to WT_HOME/codebase/ext/sulis/tab_col_names.ser
to prevent this behaviour the property ext.sulis.SQLHelper.cache can be set to false. (if ext.sulis.SQLHelper logger is DEBUG the times required will be printed to the log) NOTE: in case AddColumns is used to add support for standard attributes, the cache file must be removed to force recreate!

Author:

mkraegeloh

Field Summary

Fields
Modifier and Type	Field and Description
`static String`	`BLOB`
`static String`	`CHAR`
`static Map<String,String>`	`COL_TYP`
`static String`	`CONCATSTR`
`static String`	`DATE`
`static String`	`DBTYPE`
`static String`	`DBTYPE_MSSQL`
`static String`	`DBTYPE_ORACLE`
`static boolean`	`DBTYPEMSSQL`
`static boolean`	`DBTYPEORACLE`
`static boolean`	`HAS_REGEX_SUPPORT` HAS_REGEX_SUPPORT: Verification of SQL Regular Expressions support within the MSSQL database.
`static int`	`MAJOR`
`static int`	`MINOR`
`static String`	`NUM`
`static String`	`REGEXEXCEPTION` REGEXEXCEPTION: Returns the message of the SQL Regular Expression verification exception as string.
`static String`	`REGEXSUPPORTTEXT`
`static Map<String,Map<String,String[]>>`	`SS_COL_NAMES`
`static Map<String,String>`	`SS_TAB_NAME`
`static String`	`TAB_COLS`
`static String`	`WTHOME`

Fields inherited from interface RemoteAccess
versionID

Constructor Summary

Constructors
Constructor and Description

SQLHelper()

Constructors
Constructor and Description
`SQLHelper()`

Method Summary

Methods
Modifier and Type	Method and Description
`static void`	`createSeq(String name)` run a sql statement in oracle
`static Connection`	`dbConnect()` return a Connection using windchill's configured credentials
`static Connection`	`dbConnect(String user, String pass)` return a Connection with given credentials
`static Map<String,String>`	`getCC()` get credentials for database connection from keystore
`static String`	`getColName(String table, String col)` return column name with casing as required by database - returns unaltered String for oracle, checks dictionary for SQLServer
`static String[]`	`getColNames(String table, String[] cols)` return column name with casing as required by database - returns unaltered Strings for oracle, checks dictionary for SQLServer
`static void`	`getCount()` find all records that match a given regex in one of their varchar cols
`static String`	`getRegexWhere(String table, String col, String regex, boolean invert, boolean ignoreCase)`
`static List<String>`	`grep(String[] a)` find all records that match a given regex in one of their varchar cols
`static List<String>`	`idax(String[] a)` (try to) identify all records that reference a given ida2a2
`static void`	`main(String[] a)` print program usage info or invoke testConn()
`static void`	`runSQL(String[] a)` run a sql statement with the connection info from db.properties. prints result to STDERR
`static void`	`testConn(String[] a)` allow testing of oracle connection running "select sysdate from dual", either with supplied args or the connection info from db.properties. prints result to STDERR

Methods inherited from class Object
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

- Field Detail
  - WTHOME
```
public static String WTHOME
```
  - MAJOR
```
public static final int MAJOR
```
    See Also:
    Constant Field Values
  - MINOR
```
public static final int MINOR
```
    See Also:
    Constant Field Values
  - DBTYPE
```
public static final String DBTYPE
```
  - DBTYPE_MSSQL
```
public static final String DBTYPE_MSSQL
```
    See Also:
    Constant Field Values
  - DBTYPE_ORACLE
```
public static final String DBTYPE_ORACLE
```
    See Also:
    Constant Field Values
  - NUM
```
public static final String NUM
```
    See Also:
    Constant Field Values
  - DATE
```
public static final String DATE
```
    See Also:
    Constant Field Values
  - CHAR
```
public static final String CHAR
```
    See Also:
    Constant Field Values
  - BLOB
```
public static final String BLOB
```
    See Also:
    Constant Field Values
  - CONCATSTR
```
public static final String CONCATSTR
```
  - DBTYPEORACLE
```
public static boolean DBTYPEORACLE
```
  - DBTYPEMSSQL
```
public static boolean DBTYPEMSSQL
```
  - REGEXSUPPORTTEXT
```
public static String REGEXSUPPORTTEXT
```
  - HAS_REGEX_SUPPORT
```
public static boolean HAS_REGEX_SUPPORT
```
    HAS_REGEX_SUPPORT: Verification of SQL Regular Expressions support within the MSSQL database. Variable returns True or False as boolean
  - REGEXEXCEPTION
```
public static String REGEXEXCEPTION
```
    REGEXEXCEPTION: Returns the message of the SQL Regular Expression verification exception as string.
  - TAB_COLS
```
public static String TAB_COLS
```
  - COL_TYP
```
public static Map<String,String> COL_TYP
```
  - SS_TAB_NAME
```
public static Map<String,String> SS_TAB_NAME
```
  - SS_COL_NAMES
```
public static Map<String,Map<String,String[]>> SS_COL_NAMES
```
- Constructor Detail
  - SQLHelper
```
public SQLHelper()
```
- Method Detail
  - getColName
```
public static String getColName(String table,
                String col)
                         throws Exception
```
    return column name with casing as required by database - returns unaltered String for oracle, checks dictionary for SQLServer
    
    Parameters:
    table - table name for column lookup (same column may have different casing: name vs. NAME)
    col - col name to lookup
    
    Returns:
    properly spelled column name or null if col does not exist and db is sqlserver
    
    Throws:
    
    Exception - if table not found
  - getColNames
```
public static String[] getColNames(String table,
                   String[] cols)
                            throws Exception
```
    return column name with casing as required by database - returns unaltered Strings for oracle, checks dictionary for SQLServer
    
    Parameters:
    table - table name for column lookup (same column may have different casing: name vs. NAME)
    col - col name to lookup
    
    Returns:
    properly spelled column name
    
    Throws:
    
    Exception - if table not found or a column is not found
  - getCC
```
public static Map<String,String> getCC()
                                throws Exception
```
    get credentials for database connection from keystore
    
    Returns:
    map with keys user and pass
    
    Throws:
    
    Exception
  - dbConnect
```
public static Connection dbConnect()
                            throws Exception
```
    return a Connection using windchill's configured credentials
    
    Returns:
    database connection
    
    Throws:
    
    Exception
  - dbConnect
```
public static Connection dbConnect(String user,
                   String pass)
                            throws Exception
```
    return a Connection with given credentials
    
    Returns:
    database connection
    
    Throws:
    
    Exception
  - testConn
```
public static void testConn(String[] a)
                     throws Exception
```
    allow testing of oracle connection running "select sysdate from dual", either with supplied args or the connection info from db.properties. prints result to STDERR
    
    Parameters:
    a - arguments, -c [host port sid user [pass]]
    
    Throws:
    
    Exception
  - runSQL
```
public static void runSQL(String[] a)
                   throws Exception
```
    run a sql statement with the connection info from db.properties. prints result to STDERR
    
    Parameters:
    a - arguments, -r statement
    
    Throws:
    
    Exception
  - idax
```
public static List<String> idax(String[] a)
                         throws Exception
```
    (try to) identify all records that reference a given ida2a2
    
    Parameters:
    a - arguments, ida2a2 [[-n] tab-name-regex]
    
    Returns:
    a list with string obids
    
    Throws:
    
    Exception
  - grep
```
public static List<String> grep(String[] a)
                         throws Exception
```
    find all records that match a given regex in one of their varchar cols
    
    Parameters:
    a - arguments, col-regex [[-n] tab-name-regex]
    
    Returns:
    a list with string obids
    
    Throws:
    
    Exception
  - getCount
```
public static void getCount()
                     throws Exception
```
    find all records that match a given regex in one of their varchar cols
    
    Parameters:
    a - arguments, col-regex [[-n] tab-name-regex]
    
    Throws:
    
    Exception
  - createSeq
```
public static void createSeq(String name)
                      throws Exception
```
    run a sql statement in oracle
    
    Parameters:
    name - name of sequence to create
    
    Throws:
    
    Exception
  - getRegexWhere
```
public static String getRegexWhere(String table,
                   String col,
                   String regex,
                   boolean invert,
                   boolean ignoreCase)
                            throws Exception
```
    Throws:
    
    Exception
  - main
```
public static void main(String[] a)
                 throws Exception
```
    print program usage info or invoke testConn()
    
    Parameters:
    a - commandline args
    
    Throws:
    
    Exception

Class SQLHelper

Field Summary

Fields inherited from interface RemoteAccess

Constructor Summary

Method Summary

Methods inherited from class Object

Field Detail

WTHOME

MAJOR

MINOR

DBTYPE

DBTYPE_MSSQL

DBTYPE_ORACLE

NUM

DATE

CHAR

BLOB

CONCATSTR

DBTYPEORACLE

DBTYPEMSSQL

REGEXSUPPORTTEXT

HAS_REGEX_SUPPORT

REGEXEXCEPTION

TAB_COLS

COL_TYP

SS_TAB_NAME

SS_COL_NAMES

Constructor Detail

SQLHelper

Method Detail

getColName

getColNames

getCC

dbConnect

dbConnect

testConn

runSQL

idax

grep

getCount

createSeq

getRegexWhere

main