SqlFormat (Maximo API)

java.lang.Object
- psdi.mbo.SqlFormat

Direct Known Subclasses:

SqlFormatRelationship
```
public class SqlFormat
extends java.lang.Object
```
Performs a number of formatting operations on Sql Strings. Currently, this includes bind values from an Mbo to a SQL statement. eg. Convert a statement like 'WHERE X=:assetnum to 'WHERE X='11430''. Converts strings to db-specific SQL Strings. The user may also do manual replacements by placing numeric bind variables in the string and using the setXXXX(col, val) methods to substitute values. The advantage of this is that it takes care of DB specific calls.

Field Summary

Fields
Modifier and Type	Field and Description
`static java.lang.String`	`APPNAME` Reserved keyword that is recognized by SqlFormat as the name of the application that the current MBO belongs.
`static java.lang.String`	`DATE` Current date without the hour, minutes portion.
`static java.lang.String`	`DATETIME` Current datetime.
`static java.lang.String`	`DOMAINFILTERINGPREFIX` Reserved keyward that is recognized by SqlFormat as the start of domain filtering where clause.
`static java.lang.String`	`HOSTNAME` Reserved keyword that is recognized by SqlFormat as the url of the host.
`static java.lang.String`	`LOCALEPREFIX` Keyword to specify a locale override.
`static java.lang.String`	`MBONAME` Reserved keyword that is recognized by SqlFormat as the name of the current MBO.
`static java.lang.String`	`OLD_PREFIX`
`static java.lang.String`	`OWNER` Reserved keyword that is recognized by SqlFormat as owner object of the current MBO.
`static java.lang.String`	`OWNERNAME` Reserved keyword that is recognized by SqlFormat as the object name of the owner MBO of the current MBO.
`static java.lang.String`	`PERSONID` Reserved keyword that is recognized by SqlFormat as the person id of the logged in user
`static java.lang.String`	`SEQUENCE` Next value from sequence (maxsequence, native sequence depending on platform).
`static java.lang.String`	`SYNONYMLISTPREFIX`
`static java.lang.String`	`TIMEZONEPREFIX` Keyword to specify a timezone override.
`static java.lang.String`	`UNIQUEID` Reserved keyword that is recognized by SqlFormat as the unique id of the current MBO.
`static java.lang.String`	`USER` Reserved keyword that is recognized by SqlFormat as the user name of the loggied in user.

Constructor Summary

Constructors
Constructor and Description
`SqlFormat(java.util.Locale locale, java.util.TimeZone timeZone, java.lang.String stmt)` Construct with the specified SQL statement or SQL snippet using the specified locale and time zone information.
`SqlFormat(MboRemote mr, java.lang.String stmt)` Construct with the specified SQL statement or SQL snippet using the specified mbo.
`SqlFormat(java.lang.String stmt)` Construct with the specified SQL statement or SQL snippet.
`SqlFormat(UserInfo uInfo, java.lang.String stmt)` Construct with the specified SQL statement or SQL snippet using the specified user info's locale and time zone information.

Method Summary

All Methods Static Methods Instance Methods Concrete Methods
Modifier and Type	Method and Description
`SqlFormat`	`appendStatement(java.lang.String statement)` Allows for dynamically building a query with binds (especially without a mbo).
`java.lang.String`	`expressionToQueryWhere()` Convert the expression to a query where clause.
`java.lang.String`	`format()` Format the SQL string to be a db specific string
`java.lang.String`	`format(MboSetInfo msi, MboSetRemote set)`
`java.lang.String`	`formatRaw()` This is similar to @see format() except this method will pass any exceptions forward rather than printing a stack trace to the console.
`static java.lang.String`	`getDateFunction(java.util.Date param)` Return a timestamp function for the databse.
`static java.lang.String`	`getDateHistogramFunction(java.lang.String funcName, java.lang.String attr)`
`static java.util.Properties`	`getDBProperties()` Get a list of the database properties
`MXException`	`getEncounteredError()`
`java.lang.String`	`getFieldValue(java.lang.String field, MboRemote mbo)` Return the value of a field in the correct SQL format.
`java.lang.String`	`getFieldValue(java.lang.String field, MboRemote mbo, boolean useLocale)`
`java.lang.String`	`getFieldValue(java.lang.String field, MboRemote mbo, boolean useLocale, int indexAfter)`
`static java.lang.String`	`getNullValueFunction(java.lang.String param, java.lang.String nullVal)` Return a NVL function for the database.
`static java.lang.String`	`getSQLString(java.lang.String s)`
`static java.lang.String`	`getTimeFunction(java.util.Date param)` Return a timestamp function for the databse.
`static java.lang.String`	`getTimestampFunction(java.util.Date param)` Return a timestamp function for the databse.
`static java.lang.String`	`getUpperFunction(java.lang.String param)` Returns the upper case fucntion for the database, with the specified paramter placed appropriately in the function.
`boolean`	`hasNullBoundValue()`
`static boolean`	`isStrReservedKey(java.lang.String str)` Check if the given string is the reserved keywords.
`java.lang.String`	`limitInClause(java.lang.String where)` IV95042 Checks if the db is Oracle, and if the string is larger than 2000 characters.
`java.lang.StringBuffer`	`limitInClause(java.lang.StringBuffer where)` IV95042 Checks if the db is Oracle, and if the string is larger than 2000 characters.
`java.lang.String`	`limitInClause(java.lang.String where, java.lang.String lastWord, int numOfBraces, boolean isInClause)` IV95042 Parses string for in-clauses.
`java.lang.String`	`resolveContent()` Resolves the variables within the string using the specified Mbo.
`static java.lang.String`	`reverseSQLString(java.lang.String s)`
`void`	`setBoolean(int col, boolean val)` Set the specified parameter number to a boolean value
`void`	`setBytes(int col, byte[] val)` Set the specified parameter number to a byte array value.
`void`	`setDate(int col, java.util.Date val)` Set the specified parameter number to a date value.
`static void`	`setDBProperties(java.util.Properties p)` Set a list of database properties.
`void`	`setDouble(int col, double val)` Set the specified parameter number to a double value.
`void`	`setFloat(int col, float val)` Set the specified parameter number to an float value.
`void`	`setIgnoreUnresolved(boolean b)` Inform sqlformat that if there is unresolved bind variable, it will not throw exception during format and the original string followed by colon will remain in the formated message.
`void`	`setInt(int col, int val)` Set the specified parameter number to an int value.
`void`	`setLong(int col, long val)` Set the specified parameter number to a long value.
`void`	`setNoSpaces(boolean b)` Tells makeString whether to add spaces around simpleReplacement strings
`void`	`setObject(int col, java.lang.String type, java.lang.String val)` Set the specified parameter to a value and have it translated based on the specified type.
`void`	`setObject(int col, java.lang.String tbName, java.lang.String colName, java.lang.String val)` Set the specified parameter to a value and have it translated based on the specified table and column name.
`void`	`setRealNumericNulls(boolean b)` See `useRealNumericNulls`.
`void`	`setTime(int col, java.util.Date val)` Set the specified parameter number to a Time value.
`void`	`setTimestamp(int col, java.util.Date val)` Set the specified parameter number to a Timestamp value.
`java.lang.String`	`simpleFormat()` Return the string after a simple replacement of the bind variables.
`java.lang.String`	`validateFormat()` Use this method to validate a sql statement.

Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

- Field Detail
  - OLD_PREFIX
```
public static final java.lang.String OLD_PREFIX
```
    See Also:
    
    Constant Field Values
  - OWNER
```
public static final java.lang.String OWNER
```
    Reserved keyword that is recognized by SqlFormat as owner object of the current MBO.
    
    See Also:
    
    Constant Field Values
  - APPNAME
```
public static final java.lang.String APPNAME
```
    Reserved keyword that is recognized by SqlFormat as the name of the application that the current MBO belongs.
    
    See Also:
    
    Constant Field Values
  - MBONAME
```
public static final java.lang.String MBONAME
```
    Reserved keyword that is recognized by SqlFormat as the name of the current MBO.
    
    See Also:
    
    Constant Field Values
  - OWNERNAME
```
public static final java.lang.String OWNERNAME
```
    Reserved keyword that is recognized by SqlFormat as the object name of the owner MBO of the current MBO.
    
    See Also:
    
    Constant Field Values
  - PERSONID
```
public static final java.lang.String PERSONID
```
    Reserved keyword that is recognized by SqlFormat as the person id of the logged in user
    
    See Also:
    
    Constant Field Values
  - USER
```
public static final java.lang.String USER
```
    Reserved keyword that is recognized by SqlFormat as the user name of the loggied in user.
    
    See Also:
    
    Constant Field Values
  - DATE
```
public static final java.lang.String DATE
```
    Current date without the hour, minutes portion.
    
    See Also:
    
    Constant Field Values
  - DATETIME
```
public static final java.lang.String DATETIME
```
    Current datetime.
    
    See Also:
    
    Constant Field Values
  - SEQUENCE
```
public static final java.lang.String SEQUENCE
```
    Next value from sequence (maxsequence, native sequence depending on platform).
    
    See Also:
    
    Constant Field Values
  - HOSTNAME
```
public static final java.lang.String HOSTNAME
```
    Reserved keyword that is recognized by SqlFormat as the url of the host.
    
    See Also:
    
    Constant Field Values
  - UNIQUEID
```
public static final java.lang.String UNIQUEID
```
    Reserved keyword that is recognized by SqlFormat as the unique id of the current MBO.
    
    See Also:
    
    Constant Field Values
  - DOMAINFILTERINGPREFIX
```
public static final java.lang.String DOMAINFILTERINGPREFIX
```
    Reserved keyward that is recognized by SqlFormat as the start of domain filtering where clause. The attribute name needs to be specified after the "_". The site and org where clause based on the domain associated to this attribute will be returned. For example :&DOMAINFILTER_STATUS yields to a where clause of site org where for the current workorder MBO based on whether the workorder has site or org specified value for domain "WOSTATUS"
    
    See Also:
    
    Constant Field Values
  - SYNONYMLISTPREFIX
```
public static final java.lang.String SYNONYMLISTPREFIX
```
    See Also:
    
    Constant Field Values
  - TIMEZONEPREFIX
```
public static final java.lang.String TIMEZONEPREFIX
```
    Keyword to specify a timezone override. The override only applies to the field substitution. :&timezone&_America/New_York[:duedate]
    
    See Also:
    
    Constant Field Values
  - LOCALEPREFIX
```
public static final java.lang.String LOCALEPREFIX
```
    Keyword to specify a locale override. The override only applies to the field substitution. :&local&_uze[:&timezone&_xyz[:duedate]]
    
    See Also:
    
    Constant Field Values
- Constructor Detail
  - SqlFormat
```
public SqlFormat(java.lang.String stmt)
```
    Construct with the specified SQL statement or SQL snippet.
  - SqlFormat
```
public SqlFormat(java.util.Locale locale,
                 java.util.TimeZone timeZone,
                 java.lang.String stmt)
```
    Construct with the specified SQL statement or SQL snippet using the specified locale and time zone information.
  - SqlFormat
```
public SqlFormat(MboRemote mr,
                 java.lang.String stmt)
```
    Construct with the specified SQL statement or SQL snippet using the specified mbo. The mbo is used for obtaining the locale and timezone information and also for formatting the column name bind variables, if needed.
  - SqlFormat
```
public SqlFormat(UserInfo uInfo,
                 java.lang.String stmt)
```
    Construct with the specified SQL statement or SQL snippet using the specified user info's locale and time zone information.
- Method Detail
  - hasNullBoundValue
```
public boolean hasNullBoundValue()
```
  - getDBProperties
```
public static java.util.Properties getDBProperties()
```
    Get a list of the database properties
  - setDBProperties
```
public static void setDBProperties(java.util.Properties p)
```
    Set a list of database properties.
  - getUpperFunction
```
public static java.lang.String getUpperFunction(java.lang.String param)
```
    Returns the upper case fucntion for the database, with the specified paramter placed appropriately in the function.
  - getTimestampFunction
```
public static java.lang.String getTimestampFunction(java.util.Date param)
```
    Return a timestamp function for the databse.
  - getTimeFunction
```
public static java.lang.String getTimeFunction(java.util.Date param)
```
    Return a timestamp function for the databse.
  - getNullValueFunction
```
public static java.lang.String getNullValueFunction(java.lang.String param,
                                                    java.lang.String nullVal)
```
    Return a NVL function for the database.
  - getDateFunction
```
public static java.lang.String getDateFunction(java.util.Date param)
```
    Return a timestamp function for the databse.
  - getDateHistogramFunction
```
public static java.lang.String getDateHistogramFunction(java.lang.String funcName,
                                                        java.lang.String attr)
                                                 throws java.rmi.RemoteException,
                                                        MXException
```
    Throws:
    
    java.rmi.RemoteException
    
    MXException
  - appendStatement
```
public SqlFormat appendStatement(java.lang.String statement)
```
    Allows for dynamically building a query with binds (especially without a mbo). Ie, when you append a query like
```
sql.appendStatement(" and field = :1 ").setString(1,"test");
```
    Parameters:
    
    statement -
  - setBoolean
```
public void setBoolean(int col,
                       boolean val)
```
    Set the specified parameter number to a boolean value
  - setLong
```
public void setLong(int col,
                    long val)
```
    Set the specified parameter number to a long value.
  - setInt
```
public void setInt(int col,
                   int val)
```
    Set the specified parameter number to an int value.
  - setFloat
```
public void setFloat(int col,
                     float val)
```
    Set the specified parameter number to an float value.
  - setDouble
```
public void setDouble(int col,
                      double val)
```
    Set the specified parameter number to a double value.
  - setDate
```
public void setDate(int col,
                    java.util.Date val)
```
    Set the specified parameter number to a date value.
  - setTime
```
public void setTime(int col,
                    java.util.Date val)
```
    Set the specified parameter number to a Time value.
  - setTimestamp
```
public void setTimestamp(int col,
                         java.util.Date val)
```
    Set the specified parameter number to a Timestamp value.
  - setBytes
```
public void setBytes(int col,
                     byte[] val)
```
    Set the specified parameter number to a byte array value.
  - setObject
```
public void setObject(int col,
                      java.lang.String tbName,
                      java.lang.String colName,
                      java.lang.String val)
               throws MXException,
                      java.rmi.RemoteException
```
    Set the specified parameter to a value and have it translated based on the specified table and column name.
    
    Throws:
    
    MXException
    
    java.rmi.RemoteException
  - setObject
```
public void setObject(int col,
                      java.lang.String type,
                      java.lang.String val)
               throws MXException,
                      java.rmi.RemoteException
```
    Set the specified parameter to a value and have it translated based on the specified type.
    
    Parameters:
    
    type - type is Maximo Type String constant, such as ALN, UPPER, DATE, etc
    
    Throws:
    
    MXException
    
    java.rmi.RemoteException
  - simpleFormat
```
public java.lang.String simpleFormat()
```
    Return the string after a simple replacement of the bind variables.
  - validateFormat
```
public java.lang.String validateFormat()
                                throws MXException
```
    Use this method to validate a sql statement.
    
    Throws:
    
    MXException
  - format
```
public java.lang.String format()
```
    Format the SQL string to be a db specific string
  - format
```
public java.lang.String format(MboSetInfo msi,
                               MboSetRemote set)
```
  - formatRaw
```
public java.lang.String formatRaw()
                           throws MXException
```
    This is similar to @see format() except this method will pass any exceptions forward rather than printing a stack trace to the console.
    
    Returns:
    
    The string from @see format(MboRemote)
    
    Throws:
    
    MXException
  - getEncounteredError
```
public MXException getEncounteredError()
```
  - getFieldValue
```
public java.lang.String getFieldValue(java.lang.String field,
                                      MboRemote mbo)
                               throws MXException
```
    Return the value of a field in the correct SQL format. eg. for string will enclose value in ''. For datetime will place the JDBC escape sequence around it.
    
    Throws:
    
    MXException
  - getFieldValue
```
public java.lang.String getFieldValue(java.lang.String field,
                                      MboRemote mbo,
                                      boolean useLocale)
                               throws MXException
```
    Throws:
    
    MXException
  - getFieldValue
```
public java.lang.String getFieldValue(java.lang.String field,
                                      MboRemote mbo,
                                      boolean useLocale,
                                      int indexAfter)
                               throws MXException
```
    Throws:
    
    MXException
  - setNoSpaces
```
public void setNoSpaces(boolean b)
```
    Tells makeString whether to add spaces around simpleReplacement strings
  - setRealNumericNulls
```
public void setRealNumericNulls(boolean b)
```
    See useRealNumericNulls.
    
    Parameters:
    
    b -
  - getSQLString
```
public static java.lang.String getSQLString(java.lang.String s)
```
  - reverseSQLString
```
public static java.lang.String reverseSQLString(java.lang.String s)
```
  - resolveContent
```
public java.lang.String resolveContent()
```
    Resolves the variables within the string using the specified Mbo.
    
    Parameters:
    
    content -
    
    Returns:
    
    Throws:
    
    MXException
    
    java.rmi.RemoteException
  - setIgnoreUnresolved
```
public void setIgnoreUnresolved(boolean b)
```
    Inform sqlformat that if there is unresolved bind variable, it will not throw exception during format and the original string followed by colon will remain in the formated message.
  - expressionToQueryWhere
```
public java.lang.String expressionToQueryWhere()
                                        throws MXException,
                                               java.rmi.RemoteException
```
    Convert the expression to a query where clause. This is used when a where clause is needed to be constructed from a expression when the qualified condition is applied during the query. Not all the expressions can be converted, such as when &OWNER& keyword exists. Exceptions will be thrown if it cannot be converted.
    
    Returns:
    
    converted string of where clause.
    
    Throws:
    
    MXException
    
    java.rmi.RemoteException
  - isStrReservedKey
```
public static boolean isStrReservedKey(java.lang.String str)
```
    Check if the given string is the reserved keywords.
    
    Parameters:
    
    str -
    
    Returns:
  - limitInClause
```
public java.lang.String limitInClause(java.lang.String where)
                               throws MXException,
                                      java.rmi.RemoteException
```
    IV95042 Checks if the db is Oracle, and if the string is larger than 2000 characters. If so, submits the string to be parsed for any in-clause value lists with a size larger than 1000.
    
    Parameters:
    
    where -
    
    Returns:
    
    Throws:
    
    MXException
    
    java.rmi.RemoteException
  - limitInClause
```
public java.lang.StringBuffer limitInClause(java.lang.StringBuffer where)
                                     throws MXException,
                                            java.rmi.RemoteException
```
    IV95042 Checks if the db is Oracle, and if the string is larger than 2000 characters. If so, submits the string to be parsed for any in-clause value lists with a size larger than 1000.
    
    Parameters:
    
    where -
    
    Returns:
    
    Throws:
    
    MXException
    
    java.rmi.RemoteException
  - limitInClause
```
public java.lang.String limitInClause(java.lang.String where,
                                      java.lang.String lastWord,
                                      int numOfBraces,
                                      boolean isInClause)
                               throws MXException,
                                      java.rmi.RemoteException
```
    IV95042 Parses string for in-clauses. If any are found, the size of the value list is evaluated, and if necessary, the value list is split.
    
    Parameters:
    
    where -
    
    lastWord - - {Or,And} + Attribute which the in-clause refers to + {"",Not}
    
    numOfBraces - - number of parenthesis surrounding value list
    
    isInClause - - flag for parsing value list
    
    Returns:
    
    Throws:
    
    MXException
    
    java.rmi.RemoteException

Methods inherited from class java.lang.Object

OLD_PREFIX

OWNER

APPNAME

MBONAME

OWNERNAME

PERSONID

USER

DATE

DATETIME

SEQUENCE

HOSTNAME

UNIQUEID

DOMAINFILTERINGPREFIX

SYNONYMLISTPREFIX

TIMEZONEPREFIX

LOCALEPREFIX

SqlFormat

SqlFormat

SqlFormat

SqlFormat

hasNullBoundValue

getDBProperties

setDBProperties

getUpperFunction

getTimestampFunction

getTimeFunction

getNullValueFunction

getDateFunction

getDateHistogramFunction

appendStatement

setBoolean

setLong

setInt

setFloat

setDouble

setDate

setTime

setTimestamp

setBytes

setObject

setObject

simpleFormat

validateFormat

format

format

formatRaw

getEncounteredError

getFieldValue

getFieldValue

getFieldValue

setNoSpaces

setRealNumericNulls

getSQLString

reverseSQLString

resolveContent

setIgnoreUnresolved

expressionToQueryWhere

isStrReservedKey

limitInClause

limitInClause

limitInClause