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

Methods

Modifier and Type	Method and Description
void	buildDBCFile(java.lang.String fileName, java.lang.String dir, java.util.List<org.jdom.Element> allData) Get parent tables name
java.util.List<org.jdom.Element>	buildFreeForm(java.util.List<org.jdom.Element> allData, java.lang.String data, java.lang.String name) Get parent tables name
java.util.TreeMap<java.lang.Integer,java.lang.String>	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<java.lang.String,java.lang.String> allSeq, java.lang.String whereClause) Build one complete insert statement in the format "insert into tbname (col1, col2, col3, ...) values (val1, val2, val3,...)".
java.util.HashMap<java.lang.String,java.lang.String>	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.
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) 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, 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, createOneSequenceFromMaxSequence, createPermission, createPermission, createStatement, createTenantidTriggeres, db2textAlterIndexes, db2textAlterIndexes, deleteObjectDelta, descendingIndexesSupported, disableMTPermission, disableRichTextSearch, dropDeltaTriggers, dupIndexNamesExist, enableForMultiTenancy, enableRichTextSearch, enableRichTextSearch, enableRichTextSearch, enableRichTextSearch, explicitConversionSupported, fixMetadataForDescendingKeys, 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, 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, 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, getTextSearchIndexForColumn, getTriggerNames, getTrigRoot, getUniqueColumnName, getUniqueColumnNameNative, getUniqueIndexSpace, getUnrestoredTables, getUpdateStatistics, getUpdateTenantDelta, getUserFromProperties, getVarcharMultiple, getViewCfgRefresh, getViewCfgRefresh, getViewColumnCfgRefresh, getViewColumnCfgRefresh, getViewColumnRefresh, getViewColumnRefresh, getViewName, getViewRefresh, getViewRefresh, getYes, implicitConversionSupported, indexMetaExistsForPrikeycolseq, isHandleColumn, isLocAllowed, isMTEnabled, isNickname, isObjectLocked, 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, rebuildProcessUserVariable, reEvaluateAttributeChanged, reEvaluateAttributeChangedQuickGL, reEvaluateObjectChanged, reEvaluateObjectChangedQuickGL, reEvaluateTableAttributeChanged, reEvaluateViewAttributeChanged, registerTableFromTemplate, reloadAttributeList, renumberAttributeNumber, renumberExtAttributeNumber, renumberExtendedAttributes, reorgTable, revalidateTextSearchIndexes, rowFound, selectString, setConfigInProcess, setConnection, setDB2tsPreferSysproc, setDBOut, setLangcode, 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

equals, 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<java.lang.String,java.lang.String> 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)
                   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), Util.buildSelectStatement(java.lang.String, java.util.TreeMap, boolean, boolean), Util.buildInsertStatementPrefix(java.lang.String, java.util.TreeMap, boolean, boolean), buildInsertValuesStatement(java.lang.String, java.sql.ResultSet, java.util.TreeMap, boolean, java.lang.String, boolean, boolean, java.util.HashMap<java.lang.String, java.lang.String>, java.lang.String)

buildInsertValuesStatement

public java.util.TreeMap<java.lang.Integer,java.lang.String> 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<java.lang.String,java.lang.String> allSeq,
                                                                               java.lang.String whereClause)
                                                                                 throws java.sql.SQLException

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.sql.SQLException

See Also:

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

buildDBCFile

public void buildDBCFile(java.lang.String fileName,
                java.lang.String dir,
                java.util.List<org.jdom.Element> allData)
                  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<org.jdom.Element> buildFreeForm(java.util.List<org.jdom.Element> 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 MXException

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