Advanced Database Lookup Functoid Usage

http://allcomputers.us/windows_server/biztalk-2010-recipes---document-mapping---using-the-database-lookup-functoid.aspx

Advanced Database Lookup Functoid Usage

The BizTalk map translates the Database Lookup functoid information into a dynamic SQL SELECTstatement. If you run a SQL Profiler trace during testing of the BizTalk map, you will see the SELECTcall with the dynamic SQL. Knowing that dynamic SQL is created by the Database Lookup functoid allows you to use it to perform some relatively powerful database lookups. The Database Lookup functoid allows only a single value and single column name to be referenced in the query. However, with a bit of extra mapping, you can use this functoid to query against multiple columns. The map inFigure 4 generates the following SQL query code:

exec sp_executesql N'SELECT * FROM people WHERE ID= @P1', N'@P1 nvarchar(9)',
N'172321176'

       

This query performs a SELECT to retrieve all rows from the People table where the author ID is equal to the value in the inbound XML document (for example, 172321176).

Keep in mind that the Database Lookup functoid returns only the first row that it encounters in the recordset. If multiple authors had the same ID, you would potentially retrieve the incorrect author. For example, if the author ID is the last name of the author, you may retrieve multiple authors that share the same last name. One way to ensure uniqueness, aside from querying on a unique column, is to specify additional columns in the query. The Database Lookup functoid accepts only four parameters, so additional concatenation must occur before submitting the parameters to the Database Lookup functoid.

After configuring the inbound concatenated value, the next step is to specify multiple column names as the input parameter in the Database Lookup functoid. Figure 6 demonstrates a sample Database Lookup functoid configuration with multiple columns specified. The output from the Database Lookup functoid to the Value Extractor functoid does not change.

Figure 6. Database Lookup functoid with multiple columns

NOTE

A plus symbol (+) is used between the column names in the Database Lookup functoid, whereas in the Concatenation functoid, no + is required. If a + is specified in the Concatenation functoid, you will receive incorrect results, as the dynamic SQL statement created will be incorrect.

In this example, the inbound message specifies an author's first name and last name instead of a unique author ID. The map must still retrieve the author's information and map the information to the outbound message. The inbound XML message may have a format to the following message:

<ns0:PersonSearch xmlns:ns0="http://DatabaseLookupFunctoid.PersonSearch">
  <FirstName>Juan</FirstName>
  <LastName>Dos</LastName>
</ns0:PersonSearch>

The following is the dynamic SQL created in the map that accepts multiple columns:

exec sp_executesql N'SELECT * FROM authors WHERE FirstName+LastName= @P1', N'@P1
nvarchar(12)', N'JuanDos'

       

The dynamic SQL created shows the inbound author's first name and last name parameters as a concatenated parameter. The SQL statement also shows a combination WHERE clause with FirstName + LastName.

There are some limitations to specifying multiple columns through the concatenation approach. Specifically, string data types are the only data types that work reliably due to the concatenation operation that occurs in SQL. Integer data types may also be used, but in the case of integer (or other numeric data types), SQL will perform an additive operation versus a concatenation operation. Adding two numbers together, as what would happen when specifying numeric data types, and comparing the result to another set of numbers being added together may yield multiple matches and may not achieve the desired results. The mix of varchar and numeric fields will not work with this approach, as you will receive a data casting exception from your data provider.