TOPIC: Lookup heater

I had the earlier version of flowheater (the first version) and now that I have upgraded to version 2, my Lookup functions stop working. They all look correct, as per version 1. Any suggestions?

thanks Tim

Hi Tim

There are some changes about SQL Server user/owner tables and schemas. Do you use the SQL-Server Adapter with the database Lookup Heater?

Best practices) The Lookup Heater is very strength. Please try to use the new SQL Heater instead of the Lookup Heater. With the SQL Heater it is very simple to implement a Database Lookup.

Here you can find an example how you can use the SQL Heater. Dynamic SQL Filter

If you want to use the Lookup Heater furthermore please post you’re FlowHeater Definition (zip) for more analytic.

Hi, thanks for the reply. I am using the sqlServerSdapter. As I said in the first post, this worked in version 1; the first time I opened the V1 .FHD file, a message came up and asked if I wanted to convert it for V2 which I answered yes to.
When I tried the SQL heater, I received a message that stated “There is already an open DataReader associated with this Command which must be closed first.”

Any suggestion would be greatly appreciated.
Thanks tim

Hi Tim

Ups! This is an error with the SQL-Server Adapter on READ in connection with the SQL Heater. Maybe this is also the reason why your Database Lookup doesn’t work.
I’ve fix it. You can download the Fix (Beta) here . In the ZIP archive there’s no setup, you have to start FlowHeater.exe by double-click!

With the next version this is official fixed!

Thanks for the notification.

hi, i loaded your beta version of flowheater 2 and the lookup works, but i am still having problems withe the sql heater.

i have added an sql statement that is "select [address1] from communication_old where commtype = $comm$ and
commlocation = $location$". i have attached a picture of the heater properties.
when i run this i get the error message "lookup error - select [] from []" the read side is using the communication_old table as per the image.


i would appreciate any help as i don't think i can use the lookup heater to perform the function i need.

hi tim,

are you sure that this error message occurs from the sql heater? this error occurs usually form the database lookup heater.

verify that no database lookup heater is on your definition and try it again please. if the error message occurs again please post your flowheater definition (zip).

in case of you want to select some information from the same table on read you have choose in the read adapter please try the following. go to the adapter properties on the read side and enter for the property "autocommitafter" the value -1.

Hi, I tried to change that parameter (AutoCommitAfter) and the run did occur, however the value of "phoneNumber' which is a look up value dependant on 2 critera in the read table (heater) does not change value. It repeats the first value. I have attached the heater definition file. This functionality, if I can get it to work, would solve many problems in migrations and transfers that I am performing now.

the error message throws from an empty database lookup heater on your definition. see the screenshot below.

i don't know what your purpose? could you describe it please? i guess you want update the new table with some old content? in this case i think you can do it a bit simpler.

hi, thank you for your last reply. what i am trying to do is to take information from a table called “communication” which holds address, phone, email data to a new table which will hold similar information except in different columns. the problem with the old table is that all the address, phone, email information is in one column. this column is than uses two other columns which are guids defining the "type" of information (address, phone, email) and a second column which defines the "location" of the information (work, home). in the new table i break out the information (address, email, phone) into separate columns to allow better reporting of the information.

so i have to take a column from the old table called “address1” which holds the address, phone, email information and use criteria to put that information into new columns in the new table. i tried to use a sql heater (i also tried a lookup heater). when i use the sql heater, i used the sql statement “select communication.address1 from communication_old left join communication on communication_old.contactguid = communication.contactguid where communication.commtype = $comm$ and communication.commlocation = $location$” as per the definition file i sent you. the problem now is it takes the first value it finds in the old table (like a phone number) and repeats the same information for all rows in the new table. i have attached part of the definition run. i hope this explanation helps.

thank you
tim

You have to specify what record you want to read. I guess the right field is the ContactGUID field? I've made an example based on your definition; please have a look to the attachment. Note: The definition isn’t tested!

First I set the parameter "ContactGUID" with the content of the ContactGUID field. This parameter we need to build the Select in the SQL Heater.

For example)
Email field: Select Address1 from communication_old where ContactGUID = '$ContactGUID$' and CommType = '$commEmail$' and CommLocation = '$location$'

PhoneNumber field: Select Address1 from communication_old where ContactGUID = '$ContactGUID$' and CommType = '$commPhoneNumber$' and CommLocation = '$location$'

The parameters "CommType" and "CommLocation" are defined as static parameters. It's also possible to use the GUID without parameters.

Note: In the example I've used the GroupBy Heater . With this only one record per "ContactGUID" are imported/inserted in the new SQL Table. For this you have to use the following SQL select statement on the READ Side

select * from communication_old order by ContactGUID

Hi, as per your last reply, I don't understand set the "contactguid" parameter. This is the id of the user and changes for each record. Each row represents a user record. The primary key is "Contactguid"

Correction on my last post. The Contactguid is not the primary key, but it represents a user that is stored in the "Contact" table. Contactguid is a foreign key.

Hi Tim,

I guess it’s only. I don’t know your database schema. In my opinion our database looks like this.

CommGuid -> UniqueID (PrimaryKey)
ContactGuid -> per each contact unique
CommType -> Type of communication e.g. Email, Phone, …
CommLocation -> Home, Office, ..
Address1 -> the value for Phone, Email, …

Here a example without guid’s I’m using ID’s and Names.

First Contact
CommGuid1, ContactGuid1, CommType_Email, CommLocation_Home, Address1_Email1
CommGuid2, ContactGuid1, Commtype_Phone, CommLocation_Home, Address1_Phone1

Second Contact
CommGuid3, ContactGuid2, CommType_Email, CommLocation_Home, Address1_Email1
CommGuid4, ContactGuid2, Commtype_Phone, CommLocation_Home, Address1_Phone1

To select the home phone number from the second contact we need a SQL select like this

Select Address1 from communication_old where ContactGUID = ' ContactGuid2' and CommType = 'CommType_Email ' and CommLocation = 'CommLocation_Home '

If I have misunderstood you please post a short description of your database schema and some example data.

Hi, sorry I didn't get back sooner, I have been away. Your schema documented in the last post is correct. Again, what I don't understand is how to set a (one) parameter for contactguid in regards to coping from the old table to the new table. Contactguid is the userID and can change for each record or a set of records (one user can have an email, phone number fax number etc - therefore 3 records in the old table.). The old table has 10000 records and therefore around 5000 users (5000 contactguids that refer to each individual user). How do I set "ONE" contactguid parameter to copy from the old table to the new table. I need every user and every record.

A p.s. to the last post. Just to be clear, I need to transpose the information from the old table which look like

commGUID
ContactGUID
Address1 (all information such as email, address, phone)
Commtype
CommLocation

To a table that looks like this:

CommGUID
ContactGUID
Address (for address)
Email (for email)
Phone (for phone number)
CommType
Commlocation

Basically I need to break up the address1 field in the old table (that holds all the user information for address, phone, email etc.) and add it to individual fields in the new table.

I hope this helps.


Tim

Hi Tim,

I’m wondering why you want to import the field’s commType and commLocation to the WRITE side?

I have made a simple example including a little access database. Please have a look to the attachment.

Here I’ve replaced the commType and also commLocation GUID with the static words (Email, Phone, Home, Work). Also I’ve used numbers instead of GUID for the fields commGUID and contactGUID!

How does it work?
The GroupBy Heater groups all same contactGuids to one row on the WRITE. Then per each row the SetParameter Heater stores the actual contactGUID in the Parameter "$ContactGUID$". This parameter are used in the SQL Heater to build dynamic SQL Statements like this.

Select Address1 from communication_old where ContactGUID = '$ContactGUID$' and CommType = 'Email' and CommLocation = 'Work'

The parameter placeholder $ContactGUID$ is replaced with the current contactGUID of each row.