psdi.mbo

Class SqlFormat

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

Methods

Modifier and Type	Method and Description
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.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	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

equals, 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.

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: