BUG: Database Lookup Functoid Does Not Work with Oracle Database

If you configure a BizTalk Map to use the Database Lookup functoid to connect to an Oracle Database, the following error may occur when you attempt to test the map: Also, the Database Lookup functoid will fail to retrieve records from an Oracle database when called from a map that is used in a BizTalk Channel.

The BizTalk Server Database Lookup Map functoid hard codes brackets around the table name that is used in the functoid's SELECT statement. Oracle does not support the use of brackets surrounding table names in SELECT statements.

To work around this problem, substitute scriptor functoids for the BizTalk Database Lookup and Value Extractor functoids. To do this, perform the following steps:

Create a scriptor functoid that provides the functionality of the Database Lookup functoid

1. Copy the script code used in the Database Lookup functoid into a scriptor functoid.
2. Comment out the following line of code in the scriptor:

   Set ArrRecordSet(Index).ActiveConnection = Nothing
   					

3. Remove the square brackets contained in the following ADO SELECT statement that is contained in the scriptor code:

   strQuery = "SELECT * FROM [" + CStr(Table) + "] WHERE " + CStr(Column) + " = " + "'" + CStr(Value) + "'"
   					

4. The Database Lookup functoid requires four input parameters; however, the FctDBLookup function used by the Database Lookup script requires a fifth Index parameter that is supplied by the BizTalk Messaging engine when the Database Lookup functoid is called. To create a scriptor functoid that provides the same functionality that the Database Lookup functoid provides, you must manually provide the Index input parameter, because the BizTalk Messaging engine does not provide the index value to a generic scriptor functoid.
   
   After you copy the script code that is used in the Database Lookup functoid into the scriptor functoid, insert these five input parameters in the following order:
   
   
   1. The index value
   2. The lookup value
   3. The database connection string
   4. The table name
   5. The column name for the lookup value
   Although you can set the index value to any positive integer value, this index value should be set to 0 for optimal performance.

After you complete the previous steps, you will have a working copy of a scriptor that provides the same functionality as the Database Lookup scriptor. You must then create additional scriptors to mimic the functionality of the Value Extractor functoid, because the Value extractor functoid only accepts input from the Database Lookup functoid and will not accept input from a scriptor functoid.

Create scriptor functoids that provide the functionality of the Value Extractor functoid

1. Copy the script code used in the Value Extractor functoid into a scriptor functoid.
2. If more than one Value Extractor functoid is needed, copy the script code that is used in the Value Extractor functoid into additional scriptor functoids. If more than one scriptor functoid is used in place of the Value Extractor functoid, you must change the name of the FctDBValueExtract function in subsequent scriptor functoids, otherwise you will not be able to save the functoid into the map, and the following error message will be displayed when you attempt to save the scriptor:
   The function name "fctdbvalueextract" is already in use. Rename the function to another name.
3. Insert these two input parameters into the scriptor in the following order:
   
   
   1. A link to the Scriptor functoid that you created in step 1
   2. The column name for the field from which you want to extract data

After you complete these steps, you will have a working copy of a scriptor that provides the same functionality as the Value Extractor functoid.

Microsoft has confirmed that this is a problem in Microsoft BizTalk Server 2000.