DJImport and DJExport Object Types
DJExport and DJImport are types of objects designed to provide generic interfaces to the data connectors. These objects can be used to read or write data using the data connectors or they can be used to manipulate some of the connector metadata.
Note: The DJImport object returns a result record set. The DJImport object cannot be used to execute a general SQL statement.
Properties
AtEOF Property
Determine whether or not the adaptor for a DJImport object is positioned at the end of file. The AtEOF property always returns False when used with the DJExport object.
Syntax
objectName.AtEOF
Parameters
Example
'The following example demonstrates the use of AtEOF as a condition for an If statement.
If myImport.AtEOF Then
Set myImport = Nothing
End If
ConnectString Property
Get or set the connection string for a DJExport or DJImport object. The connection string is a set of semicolon separated attribute-value pairs that specify all the information the DJExport or DJImport object needs to establish a connection with the data source. The following attributes are supported for the DJImport and DJExport connection strings.
Syntax
objectName.ConnectString
'OR
objectName.ConnectString = connection_string
Parameters
Exceptions
ConnectString returns exceptions if there is an error.
Example
'The following example shows a simple connection string for connecting to a file oriented data source and for an Oracle data source
myImport.ConnectString = "FILE=tutor1.asc"
myImport.ConnectString = "server=test;database=ORCL; _ username=scott;password=tiger;table=emp"
CurrentRecordType Property
Get the record type index of the last record read or written. The CurrentRecordType property returns an integer index that can be used with the RecordAt property to get a reference to the Record object for the current record type.
Syntax
objectName.CurrentRecordType
Parameters
Example
'The following example writes the name of the current record type for a DJImport object called myImport to the log.
LogMessage("Info",myImport.Records(myImport.CurrentRecordType).Name)
Database Property
Use the Database Property to get the database name component of the connection string. Use when a DJImport or DJExport object when these objects are used with data adaptors that use database names for connecting to a data source.
Syntax
objectName.Database
Parameters
Example
'The following example logs the name of the database for the map source.
LogMessage("Info",myImport.Database)
Encoding Property
Get the character set encoding number used by the data adapter for the DJExport or DJImport object. The Encoding property returns the numeric code used to represent a character set.
For data adapters that support Unicode, the number will correspond to the setting of Properties("Encoding"). The Properties("Encoding") property can be used to get or set the encoding by name (Unicode-supported adaptors only). For non-Unicode data adapters, the return value is ENC_OEM.
Syntax
objectName.Encoding
Parameters
Example
'The following example tests the DJImport object, imp, to see if it is using UTF-8 encoding.
If imp.Encoding == ENC_UTF8 Then
LogMessage("Info","Encoding is UTF8")
End If
FieldCount Property
The FieldCount property returns an integer value with the number of fields in the default record layout for the DJExport or DJImport object. The default record layout is the first record layout in the RecordAt container.
Syntax
objectName.FieldCount
Parameters
Example
'The following example logs the names of each field in the default record layout for the DJImport object myImport.
For i=1 To myImport.FieldCount
LogMessage("Info","Field name " & i & ": " & myImport.Fields(i).Name)
Next i
Fields Property
Get a reference to a field in the default record layout by name. The Fields property is a container for the fields in the default record layout. The property is indexed by field name in the record layout. The default record layout is the first record in the Records container. Fields for record layouts other than the default layout can be accessed indirectly through the Records property.
Syntax
objectName.Field(n)
Parameters
Example
'Get a reference to the third field in the default record layout of the myImport DJImport object.
Dim f As DJField
Set f = myImport.Fields(3)
FileName Property
Use the FileName property to get the name of the file name used by a DJImport or DJExport object when these objects are used with data adapters that use file oriented or URI based data access.
Syntax
objectName.FileName
Parameters
Example
'The following example logs the file name for the primary target of a transformation.
LogMessage("Info",myTarget.FileName)
LogFile Property
Use the LogFile property to retrieve and set logging for individual DJImport / DJExport objects.
Syntax
objectName.LogFile
Parameters
LogMask Property
Use the LogMask property to retrieve and set masking for individual DJImport / DJExport objects.
Syntax
objectName.LogMask
Parameters
Name Property
Use the Name property to get either the file name or table name of the connection string for a DJExport or DJImport object. The name that is returned is the one that is most appropriate for the adapter type.
Syntax
objectName.Name
Parameters
Example
'The following example logs the file name or table name for the DJExport object, myExport.
LogMessage("Info",myExport.Name)
Properties Property
Get or set an adapter property. The Properties property is a collection of property values for the DJExport or DJImport adapter. Property values are strings and the array of properties is indexed using the adapter property name. The set of properties is adapter dependent.
Syntax
objectName.Properties(name)
'OR
objectName.Properties(name) = value
Parameters
Exceptions
Properties returns exceptions if there is an error.
Example
'The following example shows how to enable the header record feature of the ASCII (Delimited) adapter.
Dim myExport As DJExport
Set myExport = New DJExport "ASCII (Delimited)"
myExport.Properties("Header") = "true"
RecordNumber Property
The RecordNumber property is used to set or get the current record number for a DJImport object.
Use the RecordNumber property to control the position of the adapter in the data source. When RecordNumber is changed, the adapter uses the current record number to read the data from the data source. The data from the source record is parsed and the contents moved to the field objects in the record layout.
For adapters that support multiple record layouts, the rules from the schema for the DJImport object is used to determine the record type. The data is moved to the field objects for the appropriate record layout and the contents of the CurrentRecordType property is changed accordingly.
RecordNumber can be used to provide either sequential or direct access to records. For adapters that do not support direct access, direct access is emulated if necessary.
Syntax
objectName.RecordNumber
Parameters
Exceptions
RecordNumber returns exceptions if there is an error or if the end of file condition is detected. The following exceptions can be returned.
Example
'The following code uses the RecordNumber property to get an estimate of the number of records in the data source associated with the DJImport object myImport.
Dim i
On Error Resume Next
For i = 1 to 1000 Step 10
myImport.RecordNumber = i
If Err = ERR_EOF Exit For
Next i
If i > 1000 Then
LogMessage("Info",myImport.Name & " has more than 1000 records")
Else
LogMessage("Info",myImport.Name & " has approximately " & i & " records")
End If
Record Property
The Record property provides access to the list of record layouts from the schema for the DJExport or DJImport object. The collection can be indexed either by number or by the record layout name.
Syntax
objectName.Record(n)
Parameters
Example
'The following code snippet prints the field count for the second record layout of the DJImport object myImport.
LogMessage("Info","The second record layout has " _ & myImport.Record2.FieldCount & " fields")
Self Property
Use the Self property for getting a reference to an object that can then be passed as an argument to a function or method call. The reason for having a special property for getting the object reference is to resolve an ambiguity with the default property, Value. This is similar to the distinction between normal variable assignment (Let) the use of Set for assigning object variables.
When a DJExport or DJImport object variable is used without an associated property or method, the integration language processor assumes an implied access of the Value property.
Syntax
objectName.Self
Parameters
Example
'The following shows how to use the Self property for passing a reference to a DJImport object to the ILBuild function.
' Create a new DJImport object using the SQL Server adapter
Dim myImport As DJImport
Set myImport = New DJImport("SQL Server 2008")
' Specify the SQL Server connection information
myImport.ConnectString="database=pubs;username=sa"
' Build the incore table from the result of a SQL query
ILBuild("publishers", myImport.self,"select prod_id, name from publishers", "pub_id", "pub_name")
Server Property
Use the Server property to get the name of the server used by a DJImport or DJExport object when these objects are used with data adapters that are clients to server-based storage management systems.
Note: The Server property returns a blank value if you are using the ConnectString property to supply the server name.
Syntax
objectName.Server
Parameters
Example
'The following example logs the name of the server used for the map source.
LogMessage("Info",myImport.Server)
SQLStatement Property
Set the SQL statement to be used by a DJImport object for querying a SQL data source.
Syntax
objectName.SQLStatement
Parameters
Remarks
When you use a DJImport object with one of the SQL adapters, you have the option of specifying a table name or a SQL statement for accessing data. When you use a table name, the SQL adapter generates a simple SQL SELECT statement to return all columns and all rows in the table. The SQLStatement property provides greater flexibility by allowing you to execute any SQL statement that returns a row set. This includes the execution of queries that use filtering, ordering, grouping, or joins and stored procedures.
The SQL statement should be executable with no side effects. It is possible that the SQL statement may be executed multiple times if the SQL adapter needs to emulate direct access. Frequent execution of SQL statements can cause performance problems. Execution of the SQL statement occurs when the SQLStatement property is assigned. The execution is dynamic, so the SQL statement must be compiled and executed by the back end SQL engine each time.
In cases where a row set can be reused, it is best to run the query outside of loops. This includes implicit loops, such as the transformation loop in a map. The best way to ensure that the SQL statement is executed once is use a Public variable for the DJImport object and to perform the query in the BeforeTransformation event of map or in an early step of a process design.
The DJImport object can be shared across multiple steps in a process design if it is declared as a public variable. The use of the SQLStatement property for table lookups should be limited to cases where performance is not a concern or the lookups are infrequent. If the lookups are performed using large tables or complex queries, it is recommended that the queries be optimized through the use of indexes and precompiled SQL statements.
When performance is important and lookups are frequent, the use of lookup files within maps is recommended.
SQLStatement is a write-only property. The purpose of the property is to affect the DJImport object by executing a SQL statement and not to provide a place to store the statement.
Exceptions
SQLStatement returns exceptions if there is an error.
Example
'This example uses the SQLStatement property to select a set of records.
' Create a new DJImport object using the SQL Server adapter
Dim myImport As DJImport
Set myImport = New DJImport("SQL Server 2008")
' Specify the SQL Server connection information
myImport.ConnectString="database=pubs;username=sa"
' Build the incore table from the result of a SQL query
myImport.SQLStatement = "select * from publishers"
Table Property
Get the table name component of the connection string. Use the Table property to get the name of a table used by a DJImport or DJExport object when these objects are used with data adapters that work with database management systems or for adapters that work with partitioned file formats.
Syntax
objectName.Table
Parameters
Example
'The following example logs the name of the server used for the map source.
LogMessage("Info",myImport.Table)
TypeName Property
Get the adapter type name. The TypeName property returns the adapter name used to create the DJExport or DJImport object.
Syntax
objectName.TypeName
Parameters
UserName Property
The UserName property is used to get the user name (USERNAME or USERID) specified in the connection string for authentication.
Note: The Server property returns a blank value if you are using the ConnectString property to supply the server name.
Syntax
objectName.UserName
Parameters
Example
'The following example logs the name of the user name used for the map source.
LogMessage("Info",myImport.UserName)
Value Property
The Value property returns the adapter name used to create the DJExport or DJImport object. It returns the same result as the TypeName property. The Value property is the default property for the DJExport and DJImport objects.
Syntax
objectName.Value
Parameters
Example
'Log the adapter name for the DJExport object myExport. It is not necessary to reference the Value property explicitly, since it is the default property.
Dim myExport As DJExport
myExport = New DJExport "XML-DMS"
LogMessage("Info",myExport)
Methods
Copy Method
The Copy method copies the contents of a DJRowSet record or another DJRecord object to the default record layout of a DJExport or DJImport object. The data is copied field by field. If the source of the copy has more fields than the target, the copy will stop after data is copied to the last field in the target. Similarly, if the source has fewer fields than the target, the copy will stop after copying the last source field.
The clear parameter can be used to clear the target fields of data before the copy operation. The fields are cleared by initializing them with null values. This is useful for cases where the source records may have fewer fields than the target and it is necessary to make sure that none of the old field values remain.
Syntax
objectName.Copy(record, [, clear])
'OR
objectName.Copy(rowset, num [, clear])
Parameters
Parameters used with the first variant of the method:
Parameters used with the second variant of the method:
Exceptions
The Copy method may return one of the following exceptions if there is an error at run time:
Example
'The following code snippet copies field values from the first source record type to the first target record type.
Records(1).Copy(Records(1).Self)
NewField Method
Use the NewField method to append a new field to the end of the default record layout for the DJExport or DJImport object. When the NewField method is called without the optional field argument, a new Text field with a length of 16 characters are appended to the end of the record layout. The length of the Text field can be changed using the Length property of the DJField object interface.
When the optional field argument is provided, it is used as a template for creating the new field. The new field is created using all of the data type information from the template. Record and field metadata, such as the field name, will not be cloned. The NewField method returns a reference to the new DJField object.
Syntax
objectName.NewField([field])
Parameters
Exceptions
The NewField method may return one of the following exceptions.
Example
'The following example shows how to create new DJField objects for a record layout using NewField.
'Append a field to the default record object
Dim myExport As DJExport
Set myExport = New DJExport "ASCII (Delimited)"
Dim fld As DJField
Set fld = myexport.NewField()
'Change the length of the field to 64 characters
fld.Length = 64
fld.Name = "MyNewField"
FileWrite("The default record has " & myExport.FieldCount & " fields")
PutRecord Method
Use the PutRecord method to put a record of the specified type to the target of a DJExport object. The PutRecord method outputs a record to the target for a DJExport object. The record is assembled from the field values stored in the DJField objects for either the default record layout or for a record layout specified with the type parameter.
Syntax
objectName.PutRecord([type])
Parameters
Exceptions
The PutRecord method may return one of the following exceptions.
Example
'The following example shows write a record with a single field to a delimited ASCII file.
Dim myExport As DJExport
Set myExport = New DJExport "ASCII (Delimited)"
Dim fld As DJField
Set fld = myExport.NewField()
'Change the length of the field to 64 characters
fld.Length = 64
fld.Value = "This is a test"
myExport.ConnectString = "FILE=test.asc"
myExport.PutRecord()
Operators
New Operator
The New operator creates a new instance of a DJExport or DJImport object. It takes one parameter, the name of the data access adapter. Once an object is created, the choice of adapter can not be changed without recreating the object.
The names used to create the objects are the same names used to select source and target adapters in the Map window. The operator returns a reference to the new object. Typically, this reference is assigned to an object variable that has been declared with a compatible type. The assignment is done using the Set statement.
Syntax
New DJExport adapter_type
'OR
New DJImport adapter_type
Parameters
Exceptions
The New operator returns exceptions if there is an error creating the object.
Example
'The following example shows how to create a DJExport object with the "XML" adapter and to assign a reference to the new object to a public variable called myExport:
Public myExport As DJExport
Set myExport = New DJExport "XML-DMS"
Examples of DJImport Object Usage
Checking for Empty Recordsets
If you want to check whether or not the DJImport object returned an empty recordset, you can inspect the AtEOF object property:
'Ignore all errors, including "End of File" (119).
On Error Resume Next
'Set the SQL statement property to query the DJImport source.
myImport.SQLStatement = "SELECT * FROM [Customers] WHERE [State] = 'TX'"
'Test if we are at the end of the record set.
If myImport.AtEOF = 0 Then
'The dynamic SQL lookup was successful; assign the result to this target field.
myImport.FieldAt("Customer")
Else
'No results were returned. You can reject the record, insert a default value, etc.
"No Match"
End If
DJImport Resultset Looping
'Ignore all errors, including "End of File" (119).
On Error Resume Next
'Set the SQL statement property to query the DJIMPORT source.
myImport.SQLStatement = "SELECT * FROM [Customers] WHERE [State] = 'TX'"
Assume the lookup returns multiple records, so build a loop. Use the AtEOF property as the condition of evaluating/ repeating the loop.
While myImport.AtEOF = 0
'Concatenate the value of this record into a temporary string variable. You can also sum numeric values, use an expression to get the min/max, etc.
strResults = strResults & ", " & myImport.Field("Customer")
'Move to the next record in the DJImport object by incrementing the RecordNumber property.
myImport.RecordNumber = myImport.RecordNumber + 1
Wend
'Return the value of the temporary string variable to target field.
strResults
Note: This example does not handle expression errors.
Trapping Expression Errors
In an error event, type:
On Error GoTo ErrHandler
'...
'Check the specific error code returned (119 = End of File).
If Err.Number = 119 Then
'Ignore End of File errors; continue where we left off.
Resume Next
Else
'Handle any other error here.
'...
End If
DJExport Object Type Notes
The DJExport objects are internal objects designed to provide generic interfaces to connectors. These objects can be used to read or write data using the data connectors. They can also be used to manipulate some of the connector metadata.
DJExport is the target type or target connector object variable, and must be set to one of the entries on the target connector list, exactly (case sensitive) as shown in the Map window.
The default property for DJExport is NAME.
Limitations: The default size is 16 and cannot be changed.
Example of DJExport Object Type Usage
Enter your DJExport variable in an event:
Private myExport as DJExport
Set myExport = New DJExport "ConnectorName"
myExport.NewFieldMyExport.Field(N).Name = "FieldNameN"
myExport.Properties("Property Name") = "Prop Value"
myExport.ConnectString = "FileName = path\filename"
myExport.Field(N) = "Value(N)"
myExport.PutRecord
Be sure to destroy the object variable after you are finished. To do this, go to the AfterTransformation event and enter:
Set myExport = Nothing