com.informatica.powercenter.sdk.mapfwk.core
Class SQLTransformation

java.lang.Object
  com.informatica.powercenter.sdk.mapfwk.core.Table
      com.informatica.powercenter.sdk.mapfwk.core.Transformation
          com.informatica.powercenter.sdk.mapfwk.core.SQLTransformation

All Implemented Interfaces:

Transform

public class SQLTransformation
extends Transformation

The class StoredProcTransformation extents Transformation.

This class implements the logic for SQL Transformation. The following information needs to be provided to the constructor. 1. Is the transformation in QueryMode 2. The Database type (Use the static variables used in SQLTransformation class. 3. Is the connection Static 4. Is the connection Object provided - This is considered only if the Connection is NOT static. Though the constructor takes a vector of InputSets as argument, currently SQLTransformations support only a single InputSet and thus only the first element of the Vector is used for the transformation. SQLTransformation consists of 2 GroupSets, SQLInputs and SQLOutputs. Depending on the combination the options given in the constructor, the following ports are created automatically when the object is created. If isQueryMode is true, then SQLError is created in SQLOutputs GroupSet. Else ScriptName is created in SQLInputs and ScriptResult and ScriptError are created in SQLOutputs. If isStaticConnection is true, then no ports are created. If isConnectionObjectProvided is true, then LogicalConnectionObject is created in SQLInputs. Else ConnectString, DBUser, DBPassword,CodePage, AdvancedOptions are created in SQLInputs If the InputSet or the tranform fields in the vector passed has any INPUT_OUTPUT fields, then they are split into an INPUT field with the same name and an OUTPUT field with an "_output" appended to the field name.

For Further details see Transformation Guide.

The code snippet for the SQLTransformation is

            
           //create a Stored Procedure Transformation using the constructor given below:
           Mapping mapping = ...; // mapping to which the transform should be added
           Vector inputSetObj = ...; // collection of InputSet objects
           Vector vTransformFields = ...; // collection of TransformField objects
           TransformationProperties props = ...; // properties
           
           SQLTransformation sqlProcTrans = new SQLTransformation( "SQLTrans", "SQLTrans",
                                                   "This is SQL Transformation", "SQLTrans",
                                                   mapping, inputSetObj,vTransformFields,true,
                                                   SQLTransformation.DBTYPE_ORACLE,
                                                   true,false,props);
 
           //apply the transformation
           OutputSet outputSet = sqlProcTrans.apply();
 
           //get the first rowset as Stored Procedure always returns a RowSet
           RowSet outRowSet = (RowSet)outputSet.getRowSets().get(0);
 

DBTYPE_ORACLE

public static final int DBTYPE_ORACLE

This defines the oracle database type.

See Also:

Constant Field Values

DBTYPE_DB2

public static final int DBTYPE_DB2

This defines the DB2 database type.

See Also:

Constant Field Values

DBTYPE_SYBASE

public static final int DBTYPE_SYBASE

This defines the Sybase database type.

See Also:

Constant Field Values

DBTYPE_INFORMIX

public static final int DBTYPE_INFORMIX

This defines the Informix database type.

See Also:

Constant Field Values

DBTYPE_MICROSOFT_SQL_SERVER

public static final int DBTYPE_MICROSOFT_SQL_SERVER

This defines the Microsoft SQL server database type.

See Also:

Constant Field Values

DBTYPE_TERADATA

public static final int DBTYPE_TERADATA

This defines the Teradata database type.

See Also:

Constant Field Values

DBTYPE_ODBC

public static final int DBTYPE_ODBC

This defines the ODBC database type.

See Also:

Constant Field Values

ATTR_SQLT_PORT_ATTRIBUTE

public static final java.lang.String ATTR_SQLT_PORT_ATTRIBUTE

TransformField atrribute type SQLT_PORT_ATTRIBUTE

See Also:

Constant Field Values

ATTR_SQLT_PORT_NATIVE_TYPE

public static final java.lang.String ATTR_SQLT_PORT_NATIVE_TYPE

TransformField atrribute type SQLT_PORT_NATIVE_TYPE

See Also:

Constant Field Values

SQLTransformation

public SQLTransformation()

Default constructor. Sets SQL transformation by default to Query mode with static connection and DBType as SQLTransformation.DBTYPE_ORACLE.

SQLTransformation

public SQLTransformation(java.lang.String name,
                         java.lang.String busName,
                         java.lang.String desc,
                         java.lang.String instName)

This sets SQL transformation by default to Query mode with static connection and DBType as SQLTransformation.DBTYPE_ORACLE.

Parameters:

name - name of the table

busName - business name

desc - description

instName - instance name

SQLTransformation

public SQLTransformation(java.lang.String name,
                         java.lang.String busName,
                         java.lang.String desc,
                         java.lang.String instName,
                         Mapping mapping,
                         java.util.Vector vInputSets,
                         java.util.Vector vTransformFields,
                         boolean isQueryMode,
                         int DBType,
                         boolean isStaticConnection,
                         boolean isConnectionObjectProvided,
                         TransformationProperties properties)
                  throws InvalidTransformationException

Initialise the Transformation with all the required fields.

Parameters:

name - name of the table

busName - business name

desc - description

instName - instance name

mapping - Mapping for this Transformation

vInputSets - Vector of InputSet objects. Currently since SQL transformation supports only single InputSet, only the first element is considered.

vTransformFields - Vector of transform field objects. These are added to the SQL transformation group sets depending on the type of port.

isQueryMode - Is the SQL transformation in query mode. If false, then it is in Script mode

DBType - Use the static variables defined in SQLTransformation class to initialise this. E.g SQLTransformation.DBTYPE_ORACLE.

isStaticConnection - Is the connection static. If false, then it is dynamic connection.

isConnectionObjectProvided - For dynamic connection, is the connection Object provided? If false, then it creates ports to receive Full Connection Information

properties - Properties object to set the properties of the transformation

Throws:

InvalidTransformationException

getDBType

public int getDBType()

returns the DBType of currently set for this transformation

Returns:

returns the DBType of currently set for this transformation

setDBType

public void setDBType(int type)

Use the static variables defined in this class for setting the value. E.g. SQLTransformation.DBTYPE_ORACLE.

Parameters:

type - Sets teh DB type for this transformation. This has to be set before the apply function is called.

getSQLQuery

public java.lang.String getSQLQuery()

Returns the SQLQuery that is currently set in the transformation.

Returns:

returns the SQLQuery that is currently set in the transformation.

setSQLQuery

public void setSQLQuery(java.lang.String query)

This field is set only if the transformation was initialised in query mode, i.e, only if isQueryMode is true. Else, the function returns without setting anything.

Parameters:

query - The query for the SQL transformation

apply

public OutputSet apply()
                throws InvalidTransformationException

Apply the SQL transformation and get a OutputSet as output.

Returns:

OutputSet object

Throws:

InvalidTransformationException - exception