psdi.iface.util

Class SQLConverterUtil

java.lang.Object

psdi.configure.Util

psdi.iface.util.SQLConverterUtil

All Implemented Interfaces:

UpgConstants

public class SQLConverterUtil
extends Util

Utility methods for programs in the psdi.configure package. These methods output various kinds of Sql statements, values, etc. but generally do not actually perform any database updates.

Click here for an overview of the most commonly-used data structures.

Field Summary

Fields inherited from class psdi.configure.Util

attrAttrs, attrCfgNPAttrs, attrTransAttrs, dbIn, dbOut, MAX_NAME_LENGTH, objectAttrs, objectCfgNPAttrs, objTransAttrs, post602, ssDELETEAUTH, ssGRANTED, ssINSERTAUTH, ssSELECTAUTH, ssUPDATEAUTH, viewAttrs, viewColAttrs

Fields inherited from interface psdi.configure.UpgConstants

DB2, doNotUpdateACTION, doNotUpdateALNDOMAIN, doNotUpdateAPPLICATIONAUTH, doNotUpdateAUTOKEY, doNotUpdateCONDITION, doNotUpdateCONTRACTPROPERTY, doNotUpdateCONTRACTTYPE, doNotUpdateCROSSOVERDOMAIN, doNotUpdateDOCTYPES, doNotUpdateLANGUAGE, doNotUpdateMAXAPPS, doNotUpdateMAXCONDDETAIL, doNotUpdateMAXCONTROLVALUE, doNotUpdateMAXDOMAIN, doNotUpdateMAXLOGAPPENDER, doNotUpdateMAXLOGGER, doNotUpdateMAXMENU, doNotUpdateMAXMESSAGES, doNotUpdateMAXMODULES, doNotUpdateMAXPROP, doNotUpdateMAXPROPVALUE, doNotUpdateMAXROLE, doNotUpdateMAXSEQUENCE, doNotUpdateMAXSERSECURITY, doNotUpdateMAXSERVICE, doNotUpdateMAXTABLEDOMAIN, doNotUpdateMAXVARS, doNotUpdateMAXVARTYPE, doNotUpdateMXCOLLAB, doNotUpdateMXCOLLABREF, doNotUpdateNUMERICDOMAIN, doNotUpdateNUMRANGEDOMAIN, doNotUpdatePORTLET, doNotUpdatePRICALC, doNotUpdateSECURITYRESTRICT, doNotUpdateSIGOPTION, doNotUpdateSYNONYMDOMAIN, doNotUpdateTAXTYPE, doNotUpdateWORKPRIORITY, doNotUpdateWORKTYPE, doNotUpdateWPEDITSETTING, messageCache, NEWONLY, OLDANDNEW, OLDONLY, ORACLE, SQLINET, SQLMICROSOFT, SQLSERVER, SYSDATATABLES, SYSTEMTABLES, UNKNOWN

Constructor Summary

Constructors

Constructor and Description
SQLConverterUtil(java.sql.Connection conparam, java.io.PrintStream outparam, java.lang.String schemaOwnerparam) Constructor.

Method Summary

Modifier and Type	Method and Description
void	buildCreateRelationshipDBC(java.lang.String parent, java.lang.String child, java.lang.String name, java.lang.String whereClause, java.lang.String remarks, java.util.List allData)
void	buildCreateSigOptionDBC(java.lang.String app, java.lang.String optionName, int esigenabled, int visible, java.lang.String description, java.lang.String alsoGrants, java.lang.String alsoRevokes, java.lang.String prereq, java.lang.String langCode, java.lang.String grantApp, boolean addDelete, java.util.List allData)
java.util.List	buildCSVfile(java.lang.String tbname, java.sql.ResultSet rs, java.util.TreeMap tableCols, boolean addColumns)
byte[]	buildDBCFile(java.lang.String fileName, java.lang.String dir, java.util.List allData, java.lang.String scriptType, boolean demoOnly) Get parent tables name
void	buildDefaineTableDBC(java.lang.String tbname, boolean mainObject, java.util.List allData, boolean addDelete)
java.util.List	buildFreeForm(java.util.List allData, java.lang.String data, java.lang.String name) Get parent tables name
org.jdom2.Element	buildInsertElementDBC(java.lang.String tbname, java.sql.ResultSet rs, java.util.TreeMap tableCols, java.util.List allData, org.jdom2.Element header, boolean defaultDate, java.util.HashMap allSeq, boolean allowReplace)
java.util.TreeMap	buildInsertValuesStatement(java.lang.String prefix, java.sql.ResultSet rs, java.util.TreeMap tableCols, boolean includeDroppedCols, java.lang.String rowstampValue, boolean psFormat, boolean defaultDate, java.util.HashMap allSeq, java.lang.String whereClause, boolean dbc, boolean addTenantID) Build one complete insert statement in the format "insert into tbname (col1, col2, col3, ...) values (val1, val2, val3,...)".
java.lang.String	buildRecordList(java.lang.String tableName, long maxRowStamp) Cache the sequences for later use in createSequences.
java.lang.String	buildRecordWhere(java.lang.String tableName, long maxRowStamp) Cache the sequences for later use in createSequences.
java.lang.String	buildSelectStatement(java.lang.String tbname, java.util.TreeMap tableCols, boolean includeDroppedCols, boolean addRowstamp, boolean addtenantID, java.lang.String colNameList) Builds a select statement in the format "select col1, col2, col3, ...
java.util.HashMap	cacheSequences(java.lang.String tableName) Cache the sequences for later use in createSequences.
java.lang.String	getFileName(java.lang.String dirName, java.lang.String name, java.lang.String ext) Get the full path and file name based on given message data.
java.lang.Object	getMaxRowstamp(java.lang.String tableName) Cache the sequences for later use in createSequences.
protected static java.lang.String	handleSpecialChars(java.lang.String str) Calls replaceSpecialCharsForXML for &, <, > and " in str.
int	writeDumpTable(java.lang.String tbname, java.util.TreeMap tableCols, java.io.PrintStream out, int maxLines, int numLines, Unlcvt unlcvt, boolean psFormat, java.lang.String whereClause, boolean addRowStamp, boolean useSequence, boolean defaultDate, boolean addDelete, boolean skipLangCode, int fileType, java.lang.String grantApp, java.util.List allData) Output to the print stream a series of "insert into..." statements so that the table can be rebuilt.

Methods inherited from class psdi.configure.Util

activateRichTextSearch, addIndex, addIndex, addTenantid, addTenantIDIndex, addTenantidToTable, adjustNativeSql, adjustPrimaryKeyColSeq, adjustViewMeta, adjustViewMeta, attrRefresh, buildChangeCaseStatement, buildChangeCaseStatement, buildColumnLine, buildColumnLine, buildCreateTableStatement, buildCreateTableStatement, buildCreateViewStatement, buildDefaultDataStatement, buildDefaultDataStatement, buildGrants, buildIndexStatement, buildIndexStatement, buildInsertStatementPrefix, buildInsertStatementPrefix, buildInsertStatementPrefix, buildInsertValuesStatement, buildInsertValuesStatement, buildInsertValuesStatement, buildInsertValuesStatement, buildSelectStatement, buildSelectStatement, buildSelectStatementForRestore, buildSequences, buildSequences, buildShortenStringStatement, bytesSpaceNeeded, canAlterForLengthChange, canAlterTable, canAnalyzeTable, changeAmountFormat, changeStorageType, changeTreeKey, cleanStatisticNames, clearAlreadyDidUsingMultiSchema, columnIsEmpty, configInProcess, copyData, createExtensionView, createMaxsequenceTrigger, createOneSequenceFromMaxSequence, createOneSqlServerSequences, createPermission, createPermission, createSqlServerSequences, createStatement, createTenantidTriggeres, db2textAlterIndexes, db2textAlterIndexes, deleteObjectDelta, descendingIndexesSupported, disableMTPermission, disableRichTextSearch, dropDeltaTriggers, dupIndexNamesExist, enableForMultiTenancy, enableRichTextSearch, enableRichTextSearch, enableRichTextSearch, enableRichTextSearch, explicitConversionSupported, fixMetadataForDescendingKeys, fixSqlServerPrimaryIndex, fixSqlServerSequence, fixSqlServerSequence, getAddRowstampSql, getAnalyzeTableStatement, getApplyTenantChanges, getAttributeCfgRefresh, getAttributeCfgRefresh, getAttributeForMaxViewColumn, getAttributeMeta, getAttributeName, getAttributeRefresh, getAttributeRefresh, getBackupTableName, getColumnInfo, getColumnName, getColumnNames, getColumnNames, getColumnNamesForExtendedView, getColumnNamesNative, getCurrentNativeSchema, getData, getDatabaseName, getDB2ConvertString, getDB2TextIndexSpace, getDB2Version, getDB2Version, getDBHostName, getDBStoragePartitions, getDefaultStoragePartition, getDefaultString, getDefaultString, getDefaultString, getDeltaTriggerSql, getDisableTriggerStatement, getDisallowPageLocks, getDropStatisticsStatements, getDropStatisticsStatementsForConfig, getEnableDisableTriggersAll, getEnableTriggerStatement, getExtendedUniqueColumnForView, getFileText, getGrants, getGrantSql, getIndexMeta, getIndexMeta, getIndexMeta, getIndexNamesForColumn, getInternalSearchType, getInternalSiteOrgType, getJdbcType, getKeyAttribute, getKeyColumns, getLangColumnName, getLocDefault, getMaxColno, getMaxColumnLength, getMaxEAuditTransID, getMaximumColumnNameLength, getMaxLengthColumnInIndex, getMaxLengthIndex, getMaxTableAttrs, getMaxType, getMaxUpgVersion, getMaxvar, getMaxVarcharLength, getMetadataSequenceNames, getNativeDateDefault, getNativePartition, getNativeType, getNativeTypeForColumn, getNewIndexName, getNextAttrNo, getNextSequenceNo, getNextSequenceValueForSqlServer, getNo, getObjectCfgRefresh, getObjectCfgRefresh, getObjectMeta, getObjectName, getObjectRefresh, getObjectRefresh, getOldBackupTables, getOracleConvertString, getOracleVersion, getOracleVersion, getOrgs, getPartitionRefresh, getPrimaryKeys, getPrimaryKeys, getRemoveChanges, getRemoveTenantChanges, getRenameTableStatement, getRowstampTriggerSql, getRowstampTriggerSql, getSchemaName, getSequences, getSiteOrgType, getSites, getSqlServerCompatLevel, getSqlServerConvertString, getSqlServerVersion, getSqlServerVersion, getStorageClause, getStoragePartition, getStorageType, getSysIndexRefresh, getTableCfgRefresh, getTableCfgRefresh, getTableName, getTableRefresh, getTableRefresh, getTenantAttributeMeta, getTenantCodeMetadata, getTenantExtAttributes, gettenantSameAsExtendedAtributes, getTextSearchIndexes, getTextSearchIndexForColumn, getTriggerNames, getTrigRoot, getUniqueColumnName, getUniqueColumnNameNative, getUniqueIndexSpace, getUnrestoredTables, getUpdateStatistics, getUpdateTenantDelta, getUserFromProperties, getVarcharMultiple, getViewCfgRefresh, getViewCfgRefresh, getViewColumnCfgRefresh, getViewColumnCfgRefresh, getViewColumnRefresh, getViewColumnRefresh, getViewName, getViewRefresh, getViewRefresh, getYes, hasGrants, implicitConversionSupported, indexMetaExistsForPrikeycolseq, isHandleColumn, isLocAllowed, isMTEnabled, isNickname, isObjectLocked, isPrimaryIndex, isReorgPending, isReservedWord, isRichTextSupported, isRTSupportedForDB, isSpecialDefaultValue, isSysNumeric, isSysYORN, isTable, lockMaximoTables, longIndexNamesExist, makeSpace, makeSpace, mxServerIsUp, nameInList, nativeColumnExists, nativeColumnIsNullable, nativeDefaultConstraintExists, nativeIndexExists, nativeIndexExistsForColumn, nativeIndexExistsForColumns, nativeIndexExistsForColumns, nativeIndexIsPrimaryKey, nativeIndexIsUnique, nativeSequenceExists, nativeSpaceExists, nativeTableExists, nativeTriggerExists, nativeTypesAreCompatible, nativeViewExists, needLength, needScale, nullValueExists, objAttrDelRefresh, prepareStatement, processTemplateData, rebuildIndexes, rebuildMetadataSequences, rebuildPermissions, rebuildPrimaryIndex, rebuildProcessUserVariable, reEvaluateAttributeChanged, reEvaluateAttributeChangedQuickGL, reEvaluateObjectChanged, reEvaluateObjectChanged, reEvaluateObjectChangedQuickGL, reEvaluateTableAttributeChanged, reEvaluateViewAttributeChanged, registerTableFromTemplate, reloadAttributeList, renumberAttributeNumber, renumberExtAttributeNumber, renumberExtendedAttributes, reorgBeforeDB2TSUpdate, reorgTable, revalidateTextSearchIndexes, rowFound, selectString, setConfigInProcess, setConnection, setDB2tsPreferSysproc, setDBOut, setPrintStream, setVarcharMultiple, setVargraphic, singleToDoubleQuotes, spaceCheckForDBConfig, spaceIsActive, spaceIsSysManaged, spaceNeeded, storeChangedAttributes, tableIsEmpty, toggleStringConcat, updateDMSource, updateGroupName, updateIndexRequired, updateMaxSequence, updateMaxSequence, updateViewMetaDataForExtendedAttr, upgradeDefaultExists, useQuotes, useVargraphic, usingMultiSchema, writeDumpTable, writeDumpTable

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

SQLConverterUtil

public SQLConverterUtil(java.sql.Connection conparam,
                        java.io.PrintStream outparam,
                        java.lang.String schemaOwnerparam)
                 throws java.lang.Exception

Constructor. This is the normal way to instanciate this class.

Throws:

java.lang.Exception

Method Detail

cacheSequences

public java.util.HashMap cacheSequences(java.lang.String tableName)
                                 throws java.lang.Exception

Cache the sequences for later use in createSequences.

Throws:

java.lang.Exception

writeDumpTable

public int writeDumpTable(java.lang.String tbname,
                          java.util.TreeMap tableCols,
                          java.io.PrintStream out,
                          int maxLines,
                          int numLines,
                          Unlcvt unlcvt,
                          boolean psFormat,
                          java.lang.String whereClause,
                          boolean addRowStamp,
                          boolean useSequence,
                          boolean defaultDate,
                          boolean addDelete,
                          boolean skipLangCode,
                          int fileType,
                          java.lang.String grantApp,
                          java.util.List allData)
                   throws java.lang.Exception

Output to the print stream a series of "insert into..." statements so that the table can be rebuilt. There will be one insert statement for each row, specifying the values to be inserted.

If this is Oracle, the rowstamp value is set to "maxseq.nextval".

Depending on the platform, there will be a semicolon (";") or "go" after the statement, so that it can be run via a script.

Any encrypted datatypes (such as password) will be set to a foobar value, as encrypted data cannot be output to a print file.

Parameters:

tbname - The table name (must be a persistent table).

tableCols - The column attributes for persistent columns. Can be null. If null, this method calls getAttributeMeta.

out - The output print stream.

numLines - The number of lines that have been written to the current output file.

maxLines - The maximum number of lines that can be written to an output file. This parameter enables Unlcvt to output multiple files. If there is no maximum, this will equal 0.

unlcvt - Instance of Unlcvt that invoked this method. Used only when maxLines > 0.

psFormat - If this is true then output will support use of PreparedStatement when script is run by Maxinst. This is set to true by using the -b param when running Unlcvt.

Returns:

New value of numLines. This can have recycled if new output files were generated.

Throws:

java.lang.Exception

See Also:

Util.getAttributeMeta(java.lang.String, boolean, boolean, boolean, boolean, boolean, java.lang.String), SQLConverterUtil.buildSelectStatement(java.lang.String, java.util.TreeMap, boolean, boolean, boolean, java.lang.String), Util.buildInsertStatementPrefix(java.lang.String, java.util.TreeMap, boolean, boolean), SQLConverterUtil.buildInsertValuesStatement(java.lang.String, java.sql.ResultSet, java.util.TreeMap, boolean, java.lang.String, boolean, boolean, java.util.HashMap, java.lang.String, boolean, boolean)

buildSelectStatement

public java.lang.String buildSelectStatement(java.lang.String tbname,
                                             java.util.TreeMap tableCols,
                                             boolean includeDroppedCols,
                                             boolean addRowstamp,
                                             boolean addtenantID,
                                             java.lang.String colNameList)

Builds a select statement in the format "select col1, col2, col3, ... from tbname".

If you need a "where" clause, you can append it separately.

Besides being used for Select statements, this can be used with buildInsertStatementPrefix to create an Insert statement.

Overrides:

buildSelectStatement in class Util

Parameters:

tbname - Table name (must be persistent).

tableCols - Column info, see getAttributeMeta.

includeDroppedCols - True to include columns whose "changed" attribute equals "R", else False to exclude them.

addRowstamp - True if the rowstamp column should be included in the statement, else False.

colNameList - Can be null. If this parameter is not null, it should be a comma-delimited list of column names, and a column name will be included in the output string only if it exists in both the tableCols TreeMap and in the colNameList. If this parameter is null then this comparison is not performed. For building this list, see getColumnNames and getColumnNamesNative.

Returns:

Sql statement for select

buildInsertValuesStatement

public java.util.TreeMap buildInsertValuesStatement(java.lang.String prefix,
                                                    java.sql.ResultSet rs,
                                                    java.util.TreeMap tableCols,
                                                    boolean includeDroppedCols,
                                                    java.lang.String rowstampValue,
                                                    boolean psFormat,
                                                    boolean defaultDate,
                                                    java.util.HashMap allSeq,
                                                    java.lang.String whereClause,
                                                    boolean dbc,
                                                    boolean addTenantID)
                                             throws java.sql.SQLException,
                                                    java.io.UnsupportedEncodingException

Build one complete insert statement in the format "insert into tbname (col1, col2, col3, ...) values (val1, val2, val3,...)". Uses the current row from the result set. To dump the entire table, you need to code a loop through the result set, calling this method for each row.

For an example of how this can be used, see writeDumpTable.

Any encrypted datatypes (such as password) will be set to a foobar value, as encrypted data cannot be output to a print file.

Parameters:

prefix - "insert into" part of the statement, see buildInsertStatementPrefix.

rs - ResultSet, where you have already called "next" to get a row. It is presumed that the Select statement specified the column names in the same sequence as tableCols. See buildSelectStatement.

tableCols - Column info, see getAttributeMeta. These must be in the same sequence as the columns listed in the prefix, presumed to be in sequence by attributeno.

includeDroppedCols - True to include columns whose "changed" attribute equals "R", else False to exclude them.

rowstampValue - String to specify in the statement for rowstamp value (for example, maxseq.nextval or '12345'). If no rowstamp should be included, this should be null.

psFormat - If true, output will support use of PreparedStatement in Maxinst. If false, output will be normal string format for use in a Sql statement.

Returns:

Sql insert statement(s). Normally, the TreeMap will contain only one statement. However, if the data contained a Clob or string datatype longer than 2000 and this is Oracle or DB2, there will be one insert statement followed by one or more update statements until the entire Clob has been filled.

Throws:

java.io.UnsupportedEncodingException

java.sql.SQLException

See Also:

Util.useQuotes(java.lang.String), Util.singleToDoubleQuotes(java.lang.String), SQLConverterUtil.getChunkUpdates(java.lang.String, int, java.lang.String, java.lang.String, java.sql.ResultSet, java.lang.Object, java.lang.String)

buildInsertElementDBC

public org.jdom2.Element buildInsertElementDBC(java.lang.String tbname,
                                               java.sql.ResultSet rs,
                                               java.util.TreeMap tableCols,
                                               java.util.List allData,
                                               org.jdom2.Element header,
                                               boolean defaultDate,
                                               java.util.HashMap allSeq,
                                               boolean allowReplace)
                                        throws java.sql.SQLException,
                                               java.io.UnsupportedEncodingException

Throws:

java.sql.SQLException

java.io.UnsupportedEncodingException

buildCSVfile

public java.util.List buildCSVfile(java.lang.String tbname,
                                   java.sql.ResultSet rs,
                                   java.util.TreeMap tableCols,
                                   boolean addColumns)
                            throws java.sql.SQLException,
                                   java.io.UnsupportedEncodingException

Throws:

java.sql.SQLException

java.io.UnsupportedEncodingException

buildDBCFile

public byte[] buildDBCFile(java.lang.String fileName,
                           java.lang.String dir,
                           java.util.List allData,
                           java.lang.String scriptType,
                           boolean demoOnly)
                    throws java.lang.Exception

Get parent tables name

Throws:

MXException - Maximo Exception

java.rmi.RemoteException - Remote Exception

java.lang.Exception

buildFreeForm

public java.util.List buildFreeForm(java.util.List allData,
                                    java.lang.String data,
                                    java.lang.String name)
                             throws java.lang.Exception

Get parent tables name

Returns:

name of the parent table

Throws:

MXException - Maximo Exception

java.rmi.RemoteException - Remote Exception

java.lang.Exception

getFileName

public java.lang.String getFileName(java.lang.String dirName,
                                    java.lang.String name,
                                    java.lang.String ext)
                             throws java.lang.Exception

Get the full path and file name based on given message data.

Parameters:

dirName - directory name

name - Service Name

ext - extension

Returns:

Full path and file name of message file.

Throws:

MXException

java.rmi.RemoteException

java.lang.Exception

buildDefaineTableDBC

public void buildDefaineTableDBC(java.lang.String tbname,
                                 boolean mainObject,
                                 java.util.List allData,
                                 boolean addDelete)
                          throws java.lang.Exception

Throws:

java.lang.Exception

buildCreateRelationshipDBC

public void buildCreateRelationshipDBC(java.lang.String parent,
                                       java.lang.String child,
                                       java.lang.String name,
                                       java.lang.String whereClause,
                                       java.lang.String remarks,
                                       java.util.List allData)
                                throws java.lang.Exception

Throws:

java.lang.Exception

buildCreateSigOptionDBC

public void buildCreateSigOptionDBC(java.lang.String app,
                                    java.lang.String optionName,
                                    int esigenabled,
                                    int visible,
                                    java.lang.String description,
                                    java.lang.String alsoGrants,
                                    java.lang.String alsoRevokes,
                                    java.lang.String prereq,
                                    java.lang.String langCode,
                                    java.lang.String grantApp,
                                    boolean addDelete,
                                    java.util.List allData)
                             throws java.lang.Exception

Throws:

java.lang.Exception

handleSpecialChars

protected static java.lang.String handleSpecialChars(java.lang.String str)

Calls replaceSpecialCharsForXML for &, <, > and " in str.

Parameters:

str -

Returns:

getMaxRowstamp

public java.lang.Object getMaxRowstamp(java.lang.String tableName)
                                throws java.lang.Exception

Cache the sequences for later use in createSequences.

Throws:

java.lang.Exception

buildRecordList

public java.lang.String buildRecordList(java.lang.String tableName,
                                        long maxRowStamp)
                                 throws java.lang.Exception

Cache the sequences for later use in createSequences.

Throws:

java.lang.Exception

buildRecordWhere

public java.lang.String buildRecordWhere(java.lang.String tableName,
                                         long maxRowStamp)
                                  throws java.lang.Exception

Cache the sequences for later use in createSequences.

Throws:

java.lang.Exception