org.pentaho.di.core.database

Class MSSQLServerDatabaseMeta

java.lang.Object

org.pentaho.di.core.database.BaseDatabaseMeta

org.pentaho.di.core.database.MSSQLServerDatabaseMeta

All Implemented Interfaces:

Cloneable, DatabaseInterface

Direct Known Subclasses:

MSSQLServerNativeDatabaseMeta

public class MSSQLServerDatabaseMeta
extends BaseDatabaseMeta
implements DatabaseInterface

Contains MySQL specific information through static final members

Since:

11-mrt-2005

Author:

Matt

Field Summary

Fields inherited from class org.pentaho.di.core.database.BaseDatabaseMeta

ATTRIBUTE_CLUSTER_DBNAME_PREFIX, ATTRIBUTE_CLUSTER_HOSTNAME_PREFIX, ATTRIBUTE_CLUSTER_PASSWORD_PREFIX, ATTRIBUTE_CLUSTER_PORT_PREFIX, ATTRIBUTE_CLUSTER_USERNAME_PREFIX, ATTRIBUTE_FORCE_IDENTIFIERS_TO_LOWERCASE, ATTRIBUTE_FORCE_IDENTIFIERS_TO_UPPERCASE, ATTRIBUTE_INITIAL_POOL_SIZE, ATTRIBUTE_IS_CLUSTERED, ATTRIBUTE_MAXIMUM_POOL_SIZE, ATTRIBUTE_MSSQL_DOUBLE_DECIMAL_SEPARATOR, ATTRIBUTE_POOLING_PARAMETER_PREFIX, ATTRIBUTE_PORT_NUMBER, ATTRIBUTE_PREFERRED_SCHEMA_NAME, ATTRIBUTE_PREFIX_EXTRA_OPTION, ATTRIBUTE_PRESERVE_RESERVED_WORD_CASE, ATTRIBUTE_QUOTE_ALL_FIELDS, ATTRIBUTE_SQL_CONNECT, ATTRIBUTE_SUPPORTS_BOOLEAN_DATA_TYPE, ATTRIBUTE_SUPPORTS_TIMESTAMP_DATA_TYPE, ATTRIBUTE_USE_POOLING, ATTRIBUTE_USE_RESULT_STREAMING, AUTOINCREMENT_SQL_FOR_BATCH_ID, poolingParameters, SELECT_COUNT_STATEMENT, SEQUENCE_FOR_BATCH_ID

Constructor Summary

Constructors

Constructor and Description
MSSQLServerDatabaseMeta()

Method Summary

Methods

Modifier and Type	Method and Description
boolean	checkIndexExists(Database database, String schemaName, String tableName, String[] idx_fields) Verifies on the specified database connection if an index exists on the fields with the specified name.
int[]	getAccessTypeList() Get the list of possible access types for a database.
String	getAddColumnStatement(String tablename, ValueMetaInterface v, String tk, boolean use_autoinc, String pk, boolean semicolon) Generates the SQL statement to add a column to the specified table
int	getDefaultDatabasePort()
String	getDriverClass() Obtain the name of the JDBC driver class that we need to use!
String	getDropColumnStatement(String tablename, ValueMetaInterface v, String tk, boolean use_autoinc, String pk, boolean semicolon) Generates the SQL statement to drop a column from the specified table
String	getExtraOptionsHelpText()
String	getFieldDefinition(ValueMetaInterface v, String tk, String pk, boolean use_autoinc, boolean add_fieldname, boolean add_cr) Describe a Value as a field in the database.
int	getMaxVARCHARLength() Get the maximum length of a text field (VARCHAR) for this database connection.
String	getModifyColumnStatement(String tablename, ValueMetaInterface v, String tk, boolean use_autoinc, String pk, boolean semicolon) Generates the SQL statement to modify a column in the specified table
Long	getNextBatchIdUsingLockTables(DatabaseMeta dbm, Database ldb, String schemaName, String tableName, String fieldName)
String[]	getReservedWords()
String	getSchemaTableCombination(String schema_name, String table_part) Get the schema-table combination to query the right table.
String	getSQLColumnExists(String columnname, String tablename)
String	getSQLInsertAutoIncUnknownDimensionRow(String schemaTable, String keyField, String versionField) Get the SQL to insert a new empty unknown record in a dimension.
String	getSQLListOfProcedures(String schemaName)
String	getSQLListOfSchemas()
String	getSQLLockTables(String[] tableNames)
String	getSQLQueryColumnFields(String columnname, String tableName)
String	getSQLQueryFields(String tableName) Returns the minimal SQL to launch in order to determine the layout of the resultset for a given database table
String	getSQLTableExists(String tablename)
String	getURL(String hostname, String port, String databaseName)
String[]	getUsedLibraries()
String	quoteSQLString(String string) Adds quotes around the string according to the database dialect and also escapes special characters like CR, LF and the quote character itself.
boolean	supportsBitmapIndex()
boolean	supportsCatalogs()
boolean	supportsErrorHandlingOnBatchUpdates()
boolean	supportsSchemas()
boolean	supportsSynonyms()
boolean	useSafePoints()

Methods inherited from class org.pentaho.di.core.database.BaseDatabaseMeta

addExtraOption, clone, generateColumnAlias, getAccessType, getAttributes, getBackwardsCompatibleSchemaTableCombination, getBackwardsCompatibleTable, getConnectionPoolingProperties, getConnectSQL, getDatabaseFactoryName, getDatabaseName, getDatabasePortNumberString, getDataTablespace, getDataTablespaceDDL, getDisplayName, getEndQuote, getExtraOptionIndicator, getExtraOptions, getExtraOptionSeparator, getExtraOptionValueSeparator, getFunctionAverage, getFunctionCount, getFunctionMaximum, getFunctionMinimum, getFunctionSum, getHostname, getIndexTablespace, getIndexTablespaceDDL, getInitialPoolSize, getLimitClause, getMaxColumnsInIndex, getMaximumPoolSize, getMaxTextFieldLength, getName, getNextBatchId, getNextBatchIdUsingAutoIncSQL, getNextBatchIdUsingSequence, getNotFoundTK, getObjectId, getPartitioningInformation, getPassword, getPluginId, getPluginName, getPreferredSchemaName, getSafeFieldname, getSelectCountStatement, getServername, getSQLCurrentSequenceValue, getSQLListOfProcedures, getSQLListOfSequences, getSQLNextSequenceValue, getSqlScriptStatements, getSQLSequenceExists, getSQLUnlockTables, getSQLValue, getStartQuote, getSynonymTypes, getTablespaceDDL, getTableTypes, getTruncateTableStatement, getUsername, getValueFromResultSet, getViewTypes, getXulOverlayFile, isChanged, isDefaultingToUppercase, isDisplaySizeTwiceThePrecision, isExplorable, isFetchSizeSupported, isForcingIdentifiersToLowerCase, isForcingIdentifiersToUpperCase, isMySQLVariant, isPartitioned, isQuoteAllFields, isRequiringTransactionsOnQueries, isStreamingResults, isSystemTable, isUsingConnectionPool, isUsingDoubleDecimalAsSchemaTableSeparator, needsPlaceHolder, needsToLockAllTables, parseStatements, preserveReservedCase, quoteReservedWords, releaseSavepoint, requiresCastToVariousForIsNull, requiresCreateTablePrimaryKeyAppend, setAccessType, setAttributes, setChanged, setConnectionPoolingProperties, setConnectSQL, setDatabaseName, setDatabasePortNumberString, setDataTablespace, setDisplayName, setForcingIdentifiersToLowerCase, setForcingIdentifiersToUpperCase, setHostname, setIndexTablespace, setInitialPoolSize, setMaximumPoolSize, setName, setObjectId, setPartitioned, setPartitioningInformation, setPassword, setPluginId, setPluginName, setPreferredSchemaName, setPreserveReservedCase, setQuoteAllFields, setServername, setStreamingResults, setSupportsBooleanDataType, setSupportsTimestampDataType, setUsername, setUsingConnectionPool, setUsingDoubleDecimalAsSchemaTableSeparator, supportsAutoInc, supportsBatchUpdates, supportsBooleanDataType, supportsEmptyTransactions, supportsErrorHandling, supportsFloatRoundingOnUpdate, supportsGetBlob, supportsNewLinesInSQL, supportsOptionsInURL, supportsPreparedStatementMetadataRetrieval, supportsRepository, supportsResultSetMetadataRetrievalOnly, supportsSequenceNoMaxValueOption, supportsSequences, supportsSetCharacterStream, supportsSetLong, supportsSetMaxRows, supportsTimestampDataType, supportsTimeStampToDateConversion, supportsTransactions, supportsViews, useSchemaNameForTableList

Methods inherited from class java.lang.Object

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

Methods inherited from interface org.pentaho.di.core.database.DatabaseInterface

addExtraOption, clone, generateColumnAlias, getAccessType, getAttributes, getConnectionPoolingProperties, getConnectSQL, getDatabaseFactoryName, getDatabaseName, getDatabasePortNumberString, getDataTablespace, getDataTablespaceDDL, getDisplayName, getEndQuote, getExtraOptionIndicator, getExtraOptions, getExtraOptionSeparator, getExtraOptionValueSeparator, getFunctionAverage, getFunctionCount, getFunctionMaximum, getFunctionMinimum, getFunctionSum, getHostname, getIndexTablespace, getIndexTablespaceDDL, getInitialPoolSize, getLimitClause, getMaxColumnsInIndex, getMaximumPoolSize, getMaxTextFieldLength, getName, getNextBatchId, getNotFoundTK, getObjectId, getPartitioningInformation, getPassword, getPluginId, getPluginName, getPreferredSchemaName, getSafeFieldname, getSelectCountStatement, getServername, getSQLCurrentSequenceValue, getSQLListOfProcedures, getSQLListOfSequences, getSQLNextSequenceValue, getSqlScriptStatements, getSQLSequenceExists, getSQLUnlockTables, getSQLValue, getStartQuote, getSynonymTypes, getTableTypes, getTruncateTableStatement, getUsername, getValueFromResultSet, getViewTypes, getXulOverlayFile, isChanged, isDefaultingToUppercase, isDisplaySizeTwiceThePrecision, isExplorable, isFetchSizeSupported, isForcingIdentifiersToLowerCase, isForcingIdentifiersToUpperCase, isMySQLVariant, isPartitioned, isQuoteAllFields, isRequiringTransactionsOnQueries, isStreamingResults, isSystemTable, isUsingConnectionPool, isUsingDoubleDecimalAsSchemaTableSeparator, needsPlaceHolder, needsToLockAllTables, parseStatements, preserveReservedCase, quoteReservedWords, releaseSavepoint, requiresCastToVariousForIsNull, requiresCreateTablePrimaryKeyAppend, setAccessType, setAttributes, setChanged, setConnectionPoolingProperties, setConnectSQL, setDatabaseName, setDatabasePortNumberString, setDataTablespace, setDisplayName, setForcingIdentifiersToLowerCase, setForcingIdentifiersToUpperCase, setHostname, setIndexTablespace, setInitialPoolSize, setMaximumPoolSize, setName, setObjectId, setPartitioned, setPartitioningInformation, setPassword, setPluginId, setPluginName, setPreferredSchemaName, setPreserveReservedCase, setQuoteAllFields, setServername, setStreamingResults, setSupportsBooleanDataType, setSupportsTimestampDataType, setUsername, setUsingConnectionPool, setUsingDoubleDecimalAsSchemaTableSeparator, supportsAutoInc, supportsBatchUpdates, supportsBooleanDataType, supportsEmptyTransactions, supportsErrorHandling, supportsFloatRoundingOnUpdate, supportsGetBlob, supportsNewLinesInSQL, supportsOptionsInURL, supportsPreparedStatementMetadataRetrieval, supportsRepository, supportsResultSetMetadataRetrievalOnly, supportsSequenceNoMaxValueOption, supportsSequences, supportsSetCharacterStream, supportsSetLong, supportsSetMaxRows, supportsTimestampDataType, supportsTimeStampToDateConversion, supportsTransactions, supportsViews, useSchemaNameForTableList

Constructor Detail

MSSQLServerDatabaseMeta

public MSSQLServerDatabaseMeta()

Method Detail

supportsCatalogs

public boolean supportsCatalogs()

Specified by:

supportsCatalogs in interface DatabaseInterface

Overrides:

supportsCatalogs in class BaseDatabaseMeta

Returns:

true if the database supports catalogs

getAccessTypeList

public int[] getAccessTypeList()

Description copied from interface: DatabaseInterface

Get the list of possible access types for a database.

Specified by:

getAccessTypeList in interface DatabaseInterface

Specified by:

getAccessTypeList in class BaseDatabaseMeta

Returns:

the list of possible access types for a database.

getDefaultDatabasePort

public int getDefaultDatabasePort()

Specified by:

getDefaultDatabasePort in interface DatabaseInterface

Overrides:

getDefaultDatabasePort in class BaseDatabaseMeta

Returns:

the default database port number

getDriverClass

public String getDriverClass()

Description copied from interface: DatabaseInterface

Obtain the name of the JDBC driver class that we need to use!

Specified by:

getDriverClass in interface DatabaseInterface

Returns:

the name of the JDBC driver class for the specific database

getURL

public String getURL(String hostname,
            String port,
            String databaseName)

Specified by:

getURL in interface DatabaseInterface

Parameters:

hostname - the hostname

port - the port as a string

databaseName - the database name

Returns:

the URL to use for connecting to the database.

getSchemaTableCombination

public String getSchemaTableCombination(String schema_name,
                               String table_part)

Description copied from class: BaseDatabaseMeta

Get the schema-table combination to query the right table. Usually that is SCHEMA.TABLENAME, however there are exceptions to this rule...

Specified by:

getSchemaTableCombination in interface DatabaseInterface

Overrides:

getSchemaTableCombination in class BaseDatabaseMeta

Parameters:

schema_name - The schema name

table_part - The tablename

Returns:

the schema-table combination to query the right table.

supportsBitmapIndex

public boolean supportsBitmapIndex()

Specified by:

supportsBitmapIndex in interface DatabaseInterface

Overrides:

supportsBitmapIndex in class BaseDatabaseMeta

Returns:

true if the database supports bitmap indexes

supportsSynonyms

public boolean supportsSynonyms()

Specified by:

supportsSynonyms in interface DatabaseInterface

Overrides:

supportsSynonyms in class BaseDatabaseMeta

Returns:

true if the database supports synonyms

getSQLQueryFields

public String getSQLQueryFields(String tableName)

Description copied from class: BaseDatabaseMeta

Returns the minimal SQL to launch in order to determine the layout of the resultset for a given database table

Specified by:

getSQLQueryFields in interface DatabaseInterface

Overrides:

getSQLQueryFields in class BaseDatabaseMeta

Parameters:

tableName - The name of the table to determine the layout for

Returns:

The SQL to launch.

getSQLTableExists

public String getSQLTableExists(String tablename)

Specified by:

getSQLTableExists in interface DatabaseInterface

Overrides:

getSQLTableExists in class BaseDatabaseMeta

Parameters:

tablename - The table to verify the existance for

Returns:

The SQL to execute to verify if the given table exists. If an Exception is thrown for this SQL, we don't have the table.

getSQLColumnExists

public String getSQLColumnExists(String columnname,
                        String tablename)

Specified by:

getSQLColumnExists in interface DatabaseInterface

Overrides:

getSQLColumnExists in class BaseDatabaseMeta

Parameters:

columnname - The column to verify the existance for

tablename - The table to verify the existance for

Returns:

The SQL to execute to verify if the given table exists. If an Exception is thrown for this SQL, we don't have the column.

getSQLQueryColumnFields

public String getSQLQueryColumnFields(String columnname,
                             String tableName)

getSQLLockTables

public String getSQLLockTables(String[] tableNames)

Specified by:

getSQLLockTables in interface DatabaseInterface

Overrides:

getSQLLockTables in class BaseDatabaseMeta

Parameters:

tableNames - The names of the tables to lock

Returns:

The SQL command to lock database tables for write purposes. null is returned in case locking is not supported on the target database. null is the default value

getAddColumnStatement

public String getAddColumnStatement(String tablename,
                           ValueMetaInterface v,
                           String tk,
                           boolean use_autoinc,
                           String pk,
                           boolean semicolon)

Generates the SQL statement to add a column to the specified table

Specified by:

getAddColumnStatement in interface DatabaseInterface

Parameters:

tablename - The table to add

v - The column defined as a value

tk - the name of the technical key field

use_autoinc - whether or not this field uses auto increment

pk - the name of the primary key field

semicolon - whether or not to add a semi-colon behind the statement.

Returns:

the SQL statement to add a column to the specified table

getModifyColumnStatement

public String getModifyColumnStatement(String tablename,
                              ValueMetaInterface v,
                              String tk,
                              boolean use_autoinc,
                              String pk,
                              boolean semicolon)

Generates the SQL statement to modify a column in the specified table

Specified by:

getModifyColumnStatement in interface DatabaseInterface

Parameters:

tablename - The table to add

v - The column defined as a value

tk - the name of the technical key field

use_autoinc - whether or not this field uses auto increment

pk - the name of the primary key field

semicolon - whether or not to add a semi-colon behind the statement.

Returns:

the SQL statement to modify a column in the specified table

getDropColumnStatement

public String getDropColumnStatement(String tablename,
                            ValueMetaInterface v,
                            String tk,
                            boolean use_autoinc,
                            String pk,
                            boolean semicolon)

Generates the SQL statement to drop a column from the specified table

Specified by:

getDropColumnStatement in interface DatabaseInterface

Overrides:

getDropColumnStatement in class BaseDatabaseMeta

Parameters:

tablename - The table to add

v - The column defined as a value

tk - the name of the technical key field

use_autoinc - whether or not this field uses auto increment

pk - the name of the primary key field

semicolon - whether or not to add a semi-colon behind the statement.

Returns:

the SQL statement to drop a column from the specified table

getFieldDefinition

public String getFieldDefinition(ValueMetaInterface v,
                        String tk,
                        String pk,
                        boolean use_autoinc,
                        boolean add_fieldname,
                        boolean add_cr)

Description copied from interface: DatabaseInterface

Describe a Value as a field in the database.

Specified by:

getFieldDefinition in interface DatabaseInterface

Parameters:

v - The value to describe

tk - The field that's going to be the technical key

pk - The field that's going to be the primary key

use_autoinc - Use autoincrement or not

add_fieldname - Add the fieldname to the definition or not

add_cr - Add a cariage return at the end of the definition or not.

Returns:

a value described as a field in this database.

getSQLListOfProcedures

public String getSQLListOfProcedures(String schemaName)

Parameters:

the - schema name to search in or null if you want to search the whole DB

Returns:

The SQL on this database to get a list of stored procedures.

getReservedWords

public String[] getReservedWords()

Specified by:

getReservedWords in interface DatabaseInterface

Overrides:

getReservedWords in class BaseDatabaseMeta

Returns:

an array of reserved words for the database type...

getUsedLibraries

public String[] getUsedLibraries()

Specified by:

getUsedLibraries in interface DatabaseInterface

Returns:

the required libraries (in lib) for this database connection.

getExtraOptionsHelpText

public String getExtraOptionsHelpText()

Specified by:

getExtraOptionsHelpText in interface DatabaseInterface

Overrides:

getExtraOptionsHelpText in class BaseDatabaseMeta

Returns:

extra help text on the supported options on the selected database platform.

checkIndexExists

public boolean checkIndexExists(Database database,
                       String schemaName,
                       String tableName,
                       String[] idx_fields)
                         throws KettleDatabaseException

Verifies on the specified database connection if an index exists on the fields with the specified name.

Specified by:

checkIndexExists in interface DatabaseInterface

Overrides:

checkIndexExists in class BaseDatabaseMeta

Parameters:

database - a connected database

schemaName -

tableName -

idxFields -

Returns:

true if the index exists, false if it doesn't.

Throws:

KettleDatabaseException

getSQLListOfSchemas

public String getSQLListOfSchemas()

Specified by:

getSQLListOfSchemas in interface DatabaseInterface

Overrides:

getSQLListOfSchemas in class BaseDatabaseMeta

Returns:

the SQL to retrieve the list of schemas or null if the JDBC metadata needs to be used.

supportsSchemas

public boolean supportsSchemas()

Specified by:

supportsSchemas in interface DatabaseInterface

Overrides:

supportsSchemas in class BaseDatabaseMeta

Returns:

true if the database supports schemas

getSQLInsertAutoIncUnknownDimensionRow

public String getSQLInsertAutoIncUnknownDimensionRow(String schemaTable,
                                            String keyField,
                                            String versionField)

Get the SQL to insert a new empty unknown record in a dimension.

Specified by:

getSQLInsertAutoIncUnknownDimensionRow in interface DatabaseInterface

Overrides:

getSQLInsertAutoIncUnknownDimensionRow in class BaseDatabaseMeta

Parameters:

schemaTable - the schema-table name to insert into

keyField - The key field

versionField - the version field

Returns:

the SQL to insert the unknown record into the SCD.

quoteSQLString

public String quoteSQLString(String string)

Description copied from interface: DatabaseInterface

Adds quotes around the string according to the database dialect and also escapes special characters like CR, LF and the quote character itself.

Specified by:

quoteSQLString in interface DatabaseInterface

Overrides:

quoteSQLString in class BaseDatabaseMeta

Parameters:

string -

Returns:

A string that is properly quoted for use in an Oracle SQL statement (insert, update, delete, etc)

getNextBatchIdUsingLockTables

public Long getNextBatchIdUsingLockTables(DatabaseMeta dbm,
                                 Database ldb,
                                 String schemaName,
                                 String tableName,
                                 String fieldName)
                                   throws KettleDatabaseException

Overrides:

getNextBatchIdUsingLockTables in class BaseDatabaseMeta

Throws:

KettleDatabaseException

useSafePoints

public boolean useSafePoints()

Specified by:

useSafePoints in interface DatabaseInterface

Overrides:

useSafePoints in class BaseDatabaseMeta

Returns:

true if the database supports the use of safe-points and if it is appropriate to ever use it (default to false)

supportsErrorHandlingOnBatchUpdates

public boolean supportsErrorHandlingOnBatchUpdates()

Specified by:

supportsErrorHandlingOnBatchUpdates in interface DatabaseInterface

Overrides:

supportsErrorHandlingOnBatchUpdates in class BaseDatabaseMeta

Returns:

true if the database supports error handling (recovery of failure) while doing batch updates.

getMaxVARCHARLength

public int getMaxVARCHARLength()

Description copied from class: BaseDatabaseMeta

Get the maximum length of a text field (VARCHAR) for this database connection. If this size is exceeded use a CLOB.

Specified by:

getMaxVARCHARLength in interface DatabaseInterface

Overrides:

getMaxVARCHARLength in class BaseDatabaseMeta

Returns:

The maximum VARCHAR field length for this database type. (mostly identical to getMaxTextFieldLength() - CLOB_LENGTH)