Sep 242007
 

I was given the requirement to pull information from a Sybase Database to cross reference and ID.  I’ve had a similar requirement in pulling data from a SQL stored procedure, so this was pretty similar.

 

First I used the Sybase.Data.AseClient.dll and the sybdrvado11.dll provided by Sybase as part of their client install.  Both of these .dll’s need to be installed into your ..\Program Files\Microsoft BizTalk Server 2006\  folder.

 

I created a VB.NET class that contains the function that brings in the ID I needed to cross reference and the connection string to the Sybase database.

 

The connection string is in the format Data Source=<IP Address>;Port=<Port Number>;Database=<Database Name>;UID=<UserName>;PWD=<Password>;Max Pool Size=50;Min Pool Size=5″

 

Here is the VB.NET class with accompanying comments.

Imports System.Data Imports Sybase.Data.AseClient Imports System.Xml Public Class DatabaseConnectionClass Public Function ReturnNEWID(ByVal OLD_ID As String, ByVal ConnectionString As String) As String 'Create Connection Dim conn As New AseConnection(ConnectionString) 'Open a Connection to the Database conn.Open() 'Create the SQL Statement Dim sql As String = "SELECT Field from TABLE WHERE ID= '" + OLD_ID+ "'" 'Retrieve the Data Dim cmd As New AseCommand(sql, conn) 'Return the Data Reader Object Dim reader As AseDataReader = cmd.ExecuteReader() 'Populate the Return Value While reader.Read() Return reader.GetString(0) End While 'Close the DataReader and Connection objects reader.Close() conn.Close() End Function End Class

I signed the class with a strongly named key, built and GAC the class, and referenced it as an external assembly.

 

image

When I test the map it correctly pulls from the Sybase database.