May 062009
 

Here is the functiod description for the Database Lookup functiod:

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

Let’s review the arguments again

1. Lookup Value
2. Database Connection String
3. Table Name
4. Column Name

I did not want to create a view, as I don’t have permissions to add objects to that database, but I could not simply get the value from one table, I needed to join two tables together.

The answer was SIMPLE!

Underneath the covers, the statement that is being called is:

using (OleDbCommand command = new OleDbCommand("SELECT * FROM " + table + " WHERE " + column + "= ?", helper.Connection))
{
...
}

WAIT A MINUTE! I am not limited to only a table name in the table variable of the OleDbCommand method, I can put an entire join statement in argument 3 of the Database Lookup functiod.

Here is what I had for the arguments:

dblookup_arguments

The arguments without the single ticks

1. Source Data

2. Connection Information (in this case I just pointed to a udl file): ‘File Name=D:\Eric\Warehouse\Schemas\connection.udl’

3. Table name, which has the join: ‘EdiPartnerContacts INNER JOIN bts_party ON EdiPartnerContacts.PartyId = bts_party.nID’

4. Column that has the input value: ‘nvcName’

So in essence I had created the following query using the DBLookup functiod

SELECT     *
FROM         EdiPartnerContacts INNER JOIN
                      bts_party ON EdiPartnerContacts.PartyId = bts_party.nID

WHERE       nvcName=?

I then pulled the Email column from the data in a Value Extractor that was connected to the Database Lookup functiod

dblookup_map

  • Quite inspiring,

    looks easy to implement, this should work fine

    Keep up the good work

  • panerai panerai ladies watches ladies watches replica patek philippe watches replica patek philippe watches longines watches longines watches replica breitling watches replica breitling watches audemars piguet audemars piguet replica panerai watches replica panerai watches cartier cartier breitling watches breitling watches tag heuer tag heuer womens watches womens watches audemars piguet watches audemars piguet watches replica watches replica watches replica rolex watches replica rolex watches mens watches mens watches replica watches replica watches rolex watches rolex watches patek philippe watches patek philippe watches patek philippe patek philippe replica tag heuer watches replica tag heuer watches omega omega longines longines omega watches omega watches replica cartier watches replica cartier watches cartier watches cartier watches breitling breitling tag heuer watches tag heuer watches rolex rolex replica longines watches replica longines watches panerai watches panerai watches