Friday 20 June 2008

DB Lookup map functoids perform Select Query

DB Lookup map functoids perform Select Query

Create the Solution and Project name and schema etc as mentioned below

Object : How to Create Select * from Employee where EmpName=’A’ using
BizTalk DB Lookup Functoids ?


Solution Name : DBLookUpTest.sln
Project Name : DBLookUpProject
Map Name : mapEmp.btm
Schema Names : InputEmp.xsd , DBOutputs.xsd

Create a simple Table in SQL Server database as mentioned below.

CREATE TABLE [dbo].[Employee](
[EmployeeName] [varchar](50) ,
[JobGrade] [char](1) ,
[NetSalary] [money] NULL
) ON [PRIMARY]

Insert few Rows in Employee Table. I got less time to insert many rows so I inserted 3 rows as shown below.
EmployeeName JobGrade NetSalary
-------------- ------------ ------------
John A 5000.0000
MikeMiles A 5600.0000
Armados B 3000.0000

If any one asked you to write a query to find salary based on selected employee, I know you can write within seconds. However to perform similar query in BizTalk you could use sophisticated method. I will guide you how to write same query in BizTalk mapping.

Let say I want to find the Employee John Salary, looking at above table structure I can easily say that its 5000/-. Lets find John same salary via BizTalk mapper.

Create Schemas
1. Add one Field under “InputEmp” Schema as empName - This should be used as
Where clause value in SQL Query.

2. Add 3 Fields under DBOutputs Schema one should filed should be dedicated for database
error descriptions. Here I designed 2 Record Nodes and 2 fields (employeename
and Salary) for EmpData Record node and one field for Status records which holds
the database error descriptions.

You can find my Schema structure as mentioned below.

Root
EmpData
EmployeeName
Salary
Status
ErroDesc

Creating Map
Hope you remember that we need to accomplish the Select Query via biztalk mapper.

Before creating mapper just try to under stand the 3 database functoids Database Lookup, Value Extractor, Error Return Functoids.

Database Lookup: This functoid used to extract information from a database and store it as a Microsoft ActiveX Data Objects (ADO) recordset. This functoid requires four input parameters in the order of lookup value, database connection string, table name and column name for the lookup value.

Value Extractor: extract the appropriate column value from a recordset returned by the Database Lookup functoid. This functoid requires two input parameters like a link to the Database Lookup functoid and a column name.

Error Return : capture information about errors, such as database connection failures, that occur during run time. This functoid requires one input parameter like a link from the Database Lookup functoid to any Error description field

In below image you can identify each functoid.

Now lets start add inputs for Database Lookup functoid as we learned in above description.
You can find 4 inputs added for DatabaseLookup functod
shown in right side image.


First parameter mapped from “InputEmp” schema
field name called empName

Second parameter clearly says that its
Connection string for your database.

Third parameter employee is the Table
Name what we defined in beginning.

Fourth parameter EmployeeName is
Column name of the Table.

SELECT EmployeeName,
JobGrade,
NetSalary
FROM Employee ( 3rd parameter)
Where EmployeeName = { ? }
(4th parameter) (1st parameter)





Now you have successfully written SQL Query in BizTalk DatabaseLookup Functoid. What next??

Once query is executed you need to find salary for requested employee that is NETSALARY.


To find NetSalary column you need to use Value
Extractor functoid. Establish a link between

DBLookup functoid and Extractor functoid and
add additional parameter with name NetSalary
shown in image.




Once you through with these settings create a links between Dblookup functoid and Error Return functoid. This functoid helps to track the database errors.






Your final mapping should look as shown the image below.











Before you test the map you need to generate Input XML file for InputEmp schema.
Here is my generated input xml file. And I saved as Testmap.xml at C: directory.





Select the mapEmp.btm form your Solution and Right click and select the properties, enter the below settings.

TestMap Input Instance = c:\Testmap.xml
TestMap Input = XML
TestMap Output = XML

now lets try to test the map. Select the mapEmp.btm form your Solution and Right click and select “Test Map” you can find the generated file in your output window. When you open the file you will see the result as shown below.












Looks perfect… what about the error Description? Is it working??
let try this.. I am trying to make my database to be offline ( Right click your database à Tasksà Take Offline )

When I do Test Map I could see the below result..











However this method can apply for your map only when you know that database is not going to change for your various environments like UAT and production.


If you database name is different for each environment I suggest you to go for Business Rules rather than Db Look up fuctoids.
















































1 comment: