psdi.mbo
Class SqlFormat

java.lang.Object
  extended by psdi.mbo.SqlFormat
Direct Known Subclasses:
Mbo.SubQueryFormatter, 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.


Nested Class Summary
private static class SqlFormat.StringFinder
           
 
Field Summary
static java.lang.String APPNAME
          Reserved keyword that is recognized by SqlFormat as the name of the application that the current MBO belongs.
private  java.util.Vector<java.lang.Object> binds
          Vector to hold the binds
private static java.lang.String CANTBINDERROR
           
static java.lang.String DATE
          Current date without the hour, minutes portion.
static java.lang.String DATETIME
          Current datetime.
private static java.util.Properties dbProperties
          List of database properties from the MXServer.properties file.
static java.lang.String DOMAINFILTERINGPREFIX
          Reserved keyward that is recognized by SqlFormat as the start of domain filtering where clause.
private  MXException encounteredError
           
private  boolean hasOr
           
static java.lang.String HOSTNAME
          Reserved keyword that is recognized by SqlFormat as the url of the host.
private  boolean ignoreUnresolved
           
private  java.util.Locale locale
          Locale information used for formatting.
private static int MAXBINDS
          Maximum # of binds supported
static java.lang.String MBONAME
          Reserved keyword that is recognized by SqlFormat as the name of the current MBO.
private  MboRemote mboRemote
          MboRemote reference for formatting bind columns.
private  MboSetInfo mboSetInfo
          MboSetInfo reference for formatting bind column when Mbo is not available;
private  MboSetRemote mbosetRemote
           
private static java.lang.String NEW_PREFIX
           
private  boolean noSpaces
          Tells makeString whether to add spaces around simpleReplacement strings
private  boolean nullBindValueExist
           
private  boolean nullDateBindValueExist
           
private  boolean nullDateReturnsNull
          Whether a null value in a Date/Time field returns an empty string or " null ".
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.
private  java.lang.String personId
          Personid of the maximo user.
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).
private  boolean simpleReplacement
          Format for sql or simple replacement.
(package private)  java.lang.String statement
          String to hold the SQL statement prior to formatting
private  SqlFormat.StringFinder strF
           
private  java.util.TimeZone timeZone
          Time zone information used for formatting.
static java.lang.String UNIQUEID
          Reserved keyword that is recognized by SqlFormat as the unique id of the current MBO.
(package private) static java.lang.String[] URL_PREFIX
           
static java.lang.String USER
          Reserved keyword that is recognized by SqlFormat as the user name of the loggied in user.
private  java.lang.String userName
          User name of the MAXIMO user.
private  boolean validateOnly
          Format for validating sql statement only.
 
Constructor Summary
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
(package private)  java.lang.String convertToWhere(java.lang.StringBuffer fieldBuf, java.lang.String entityName)
          Convenient method of converting the bind variables to field names.
 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
private  java.lang.String format(MboRemote mbo)
          Format the Sql string using the current value from the specified Mbo
 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.
private  java.util.Vector<java.lang.Object> getBinds()
          Return the vector that holds the binds
private  java.lang.String getBindValue(int col)
          Return a Sql compliant string for the specified bind value
private  java.lang.String getCurrentValue(MboRemote mbo, java.lang.String field, int dataType, int fDataType, boolean useLocale)
          Returns the current value associated with the given field.
private  java.lang.String getDatabaseValue(MboRemote mbo, java.lang.String field, int dataType)
          Returns the database value associated with the field.
private  int getDataType(java.lang.String tableName, java.lang.String columnName)
          Returns the datatype of the column name present in table.
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()
           
private  java.lang.String getFieldName(java.lang.String field)
          Returns the field name without the OLD_PREFIX or NEW_PREFIX prefix, if any
 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)
           
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.
private  boolean isURLPrefix(char[] ca, int beginIndex)
          Returns true if the character string starting at this index is a url prefix.
private  java.lang.String makeString(java.lang.String s)
          Return a quoted string if for sql and just the string if simple replacement
private  boolean needDBValue(java.lang.String fieldName)
          Checks to see whether the field has a prefix OLD_PREFIX or not.
private  java.lang.String replaceFieldText(MboRemote mbo, java.lang.StringBuffer fieldName, boolean useLocale)
          Replace the fieldName with the proper value.
 java.lang.String resolveContent()
          Resolves the variables within the string using the specified Mbo.
 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.
private  void setElementAt(java.lang.Object o, int col)
          Set an element in the list of binds
 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 tbName, java.lang.String colName, java.lang.String val)
          Set the specified paramter to a value and have it translated based on the specified table and column name.
(package private)  void setString(int col, java.lang.String val)
          Set the specified parameter number to a string value.
 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

NEW_PREFIX

private static final java.lang.String NEW_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

ignoreUnresolved

private boolean ignoreUnresolved

simpleReplacement

private boolean simpleReplacement
Format for sql or simple replacement.


nullDateReturnsNull

private boolean nullDateReturnsNull
Whether a null value in a Date/Time field returns an empty string or " null ".


validateOnly

private boolean validateOnly
Format for validating sql statement only.


MAXBINDS

private static final int MAXBINDS
Maximum # of binds supported

See Also:
Constant Field Values

statement

java.lang.String statement
String to hold the SQL statement prior to formatting


binds

private java.util.Vector<java.lang.Object> binds
Vector to hold the binds


locale

private java.util.Locale locale
Locale information used for formatting.


timeZone

private java.util.TimeZone timeZone
Time zone information used for formatting.


userName

private java.lang.String userName
User name of the MAXIMO user.


personId

private java.lang.String personId
Personid of the maximo user.


mboRemote

private MboRemote mboRemote
MboRemote reference for formatting bind columns.


mboSetInfo

private MboSetInfo mboSetInfo
MboSetInfo reference for formatting bind column when Mbo is not available;


mbosetRemote

private MboSetRemote mbosetRemote

dbProperties

private static java.util.Properties dbProperties
List of database properties from the MXServer.properties file. This is set by the DBManager at startup.


nullBindValueExist

private boolean nullBindValueExist

nullDateBindValueExist

private boolean nullDateBindValueExist

hasOr

private boolean hasOr

strF

private SqlFormat.StringFinder strF

CANTBINDERROR

private static final java.lang.String CANTBINDERROR
See Also:
Constant Field Values

encounteredError

private MXException encounteredError

noSpaces

private boolean noSpaces
Tells makeString whether to add spaces around simpleReplacement strings


URL_PREFIX

static final java.lang.String[] URL_PREFIX
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.


getBinds

private java.util.Vector<java.lang.Object> getBinds()
Return the vector that holds the binds


setString

void setString(int col,
               java.lang.String val)
Set the specified parameter number to a string value.


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 paramter to a value and have it translated based on the specified table and column name.

Throws:
MXException
java.rmi.RemoteException

setElementAt

private void setElementAt(java.lang.Object o,
                          int col)
Set an element in the list of binds


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()

format

private java.lang.String format(MboRemote mbo)
                         throws MXException
Format the Sql string using the current value from the specified Mbo

Throws:
MXException

replaceFieldText

private java.lang.String replaceFieldText(MboRemote mbo,
                                          java.lang.StringBuffer fieldName,
                                          boolean useLocale)
                                   throws MXException
Replace the fieldName with the proper value. i.e. ":hostname" becomes "localhost:7001".

Parameters:
mbo - Where named fields get their values from.
fieldName - The name of the field from the string to format
Returns:
The string to replace the field name with.
Throws:
MXException - Throws unbound field exception.
See Also:
This flag may be set., This flag determines whether an unresolved field name causes an error.

getDataType

private int getDataType(java.lang.String tableName,
                        java.lang.String columnName)
                 throws java.rmi.RemoteException
Returns the datatype of the column name present in table.

Throws:
java.rmi.RemoteException

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

needDBValue

private boolean needDBValue(java.lang.String fieldName)
Checks to see whether the field has a prefix OLD_PREFIX or not. If the prefix OLD_PREFIX is present, then it means that the database value is requested.


getFieldName

private java.lang.String getFieldName(java.lang.String field)
Returns the field name without the OLD_PREFIX or NEW_PREFIX prefix, if any


getCurrentValue

private java.lang.String getCurrentValue(MboRemote mbo,
                                         java.lang.String field,
                                         int dataType,
                                         int fDataType,
                                         boolean useLocale)
                                  throws java.rmi.RemoteException,
                                         MXException
Returns the current value associated with the given field.

Throws:
java.rmi.RemoteException
MXException

getDatabaseValue

private java.lang.String getDatabaseValue(MboRemote mbo,
                                          java.lang.String field,
                                          int dataType)
                                   throws java.rmi.RemoteException,
                                          MXException
Returns the database value associated with the field. The database value is a value that has been fetched from the database and with which the field is initialized.

Throws:
java.rmi.RemoteException
MXException

getBindValue

private java.lang.String getBindValue(int col)
Return a Sql compliant string for the specified bind value


setNoSpaces

public void setNoSpaces(boolean b)
Tells makeString whether to add spaces around simpleReplacement strings


makeString

private java.lang.String makeString(java.lang.String s)
Return a quoted string if for sql and just the string if simple replacement


getSQLString

public static java.lang.String getSQLString(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 convered, such as when &OWNER& keyword exists. Exceptions will be thrown if it cannot be converted.

Returns:
convereted string of where clause.
Throws:
MXException
java.rmi.RemoteException

convertToWhere

java.lang.String convertToWhere(java.lang.StringBuffer fieldBuf,
                                java.lang.String entityName)
                          throws MXException
Convenient method of converting the bind variables to field names.

Parameters:
fieldBuf -
Returns:
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:

isURLPrefix

private boolean isURLPrefix(char[] ca,
                            int beginIndex)
Returns true if the character string starting at this index is a url prefix.

Parameters:
ca - The character string being parsed.
beginIndex - The beginning index to look at.
Returns: