TOPIC: Import from one file to multiples MySql Tables ( Foreign key

Hi, I just downloaded this convertor program.
We always do inventory imports in here that stock is splited into 2 Mysql tables ( some import 3 or 4 ). The first one is the part definition tables ( tblparts ) and the second one is items definition for a part (tblitems). These tables are naturally linked with a partID as foreign key in tblItem to the primary key of tblPart.

So my questions are :
1 - Is it possible to make multiple adapters that are linked between them, or I must create one conversion file for each table ? As i can see no unfortunatly

2 - So if I create one file for each, I tried the 'Lookup' heater that seems to be the solution of what i'm trying to do, but it doesn't work at all. Here is the way I use it in the graphic interface, guess its not like this but there is no exemple

---

Read Side Write side ( tblitems)

Name

---

> Lookup <

---

PartID ( Foreign key )

---

The lookup properties goes like this
Site : Write
Table : tblPart
Field : PartID (I want the ID to create the foreign key)
Where : PartName = $1 ( Name is the first field of Read heater )


I am using it correctly ? Cause all other fields import well, but my foreign key doesn't link at all. I actually imported fields to tblpart before executing the definition of tblitem, so all part exist. I just d'ont understand. I'm guessing on a wrong use of the graphic component, is it the way to use it ?

Thx, and great soft tought !! Sorry for my bad english, second language



EDIT : I just found out the script heater, guess that what I want to do can be done with this ? Is there an example somewhere with syntax of how to call mysql adapter, how to execute request etc..The script would get the value of the first parameter ( Name ) objectInValues[1].GetString(). And so do a select statement on my other table like this 'Select PartID from tblPart where partname = objectInValues[1].GetString(). Do you have any example script C# or VB using mysql Adapter ? seems like we can do magic with this

?? is it possible

Thx again for helping me, if the tool can do what we want here ( it seems too ), we might buying a designer license !

Hi Francis,

For 1) I´m afraid you have to create 2 (or more) FlowHeater definition for each table import. You can run these definitions with a little batch command file and the Batch Modul fhbatch.exe step by step automatically or about the Designer manually.

For example:

@Echo off
FHBatch.exe Import-tblParts.fhd
FHBatch.exe Import tblItems.fhd
…

With version 1.x FlowHeater only supports one adapter per side. In Version 2 we planed that you can use more than one adapter on each side. NOTE: On the READ side you can use more complex SQL statements (e.g. joins) for combination more than one tables.


For 2) I think you using the Lookup Heater correctly. It can be possible that you have some errors in writing for table and/or field names? E.g. the table name one time you using tblParts and one time you using tblPart, without an ending ‘s’! I´ve made the same mistake when I´m creating the little example, see the attachment


Script Heater)
Yes of course, you can use the .NET Script Heater for creating SQL Statements and retrieving Fields like the Lookup Heater. Soon we planned to publish a script library for a lot of thing you can do with this powerful heater/function


Example)
The example (see the attachment) imports a CSV text file to an MS Access database . I´ve used the same table and field names as you. Access runs without MySQL Server , just extract the files and open the lookup-example.fhd definition. Here you can see one time the Lookup Heater in action and one time the .NET Script Heater doing the same like the Lookup Heater.

Hope this helps!

Thx ! it's working now ! The lookup was Ok, the problem was incompatible type between the two fields to match-up. Thx for the little script too, it will help me a lot.

Pretty nice this soft !. Got another problem, is it possible to ignore the insert of the complete record if a certain field from Read as a value = 0?? Didnt find out yet

Another question : Strangly, i'm doing excel-mysql conversion, the data don't go in DB... mysql adapter is set to 'Insert data only' and 'Clear table before importation'. Because by now, I must generate an SQL script for each and launch them all by one.. not very nice.

Thx

Hi Francis,

You can ignore (filter) records with the Filter heater and for the condition you have to use the IF-Then-Else heater . Move these heaters on the designer connect the READ / import field to the IF-Then-Else heater and connect the output from If-Then-Else to the Filter heater. Now open the configuration dialog for the If-Then-Else heater and use the following condition:

= 0

That’s all! Note: It can be possible that you have to change the data type. For more info please have a look to the IF-Then-Else heater online help.


Your new problem I don´t understand? Maybe you have forgotten to deactivate the "Test run" checkbox in the " Test and Execute popup "?

Thx ! all you said in last post worked fine ! And yah.. didnt see the option 'Test and execute'.. now working

Got another question for you.

I'm trying to do a mass update from a mysql table as reader, to the same table as a writer. All is working but how can you access to result value of an AdapterRead.execute


I tried

ojbect o = adapter.execute(sql,true)
If ( o.ToString() = ... ) doesn't work

I tried a 'IfThenElse' that takes the result from return Adapter.execute to make a choice, got the error 'data type of the return value in not supported'. I link the result of the Adapter.Exec('Select max(field) from tblparts',true) to the integer field to get this error

I also Tried an update directly on the execute like this which would be too beautiful to work

AdapterWrite.Execute("update tblparts SET field =" + InValues[0].GetInt() + "WHERE partID = " Invalues[1].GetInt()",false);

No update is done at all with this! I didn't link the script to anything, and all the fields are link straight to the same field on the other side. Is there a way to do update correctly ? and to get result values from a Adapater.execute ? Is it stock in an object, a dataset etc.. ??

Thx for your devotion in helping me


Oh ! and while i'm here, got a suggestion if I may suggest. Is it possible to add in a new version a 'Like' comparaison operator between string in the 'IFThenElse' heater ?

Thanks for the suggestion. This is really great idea. In further version we add this function. By now you can do this with the .NET Script Heater, see following C# script.

Your MySQL mass update problem)
First of all. Please open a new thread for each further question, thanks!

The main reason could be that the .NET script code are only execute if the .NET Script heater have a connection to the WRITE site! I´ve made a simple example for you; please have a look to the attachment "update-example.zip".

One other reason could be; if you build the SQL statement by yourself like

The field partid maybe formatted with decimal and/or group separator like 1,000. If you compare strings this doesn’t work.


For the problem with the data type conversion please try to use

return Convert.ToDouble(o);

in your script code. If the same error occur export only the data type in a string field like

return o.GetType().ToString();

and post the receiving data type.


Hope this help. If your problem still exits, please post your FlowHeater definition in a new thread for more analytics.