- Posts: 13
Import from one file to multiples MySql Tables ( Foreign key
- Francis
- Topic Author
- Offline
- User
Less
More
14 years 2 months ago #2026
by Francis
Import from one file to multiples MySql Tables ( Foreign key was created by Francis
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 unfortunately
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 example
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 !
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 unfortunately
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 example
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 !
Please Log in or Create an account to join the conversation.
- FlowHeater-Team
- Offline
- Admin
14 years 2 months ago #2027
by FlowHeater-Team
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Replied by FlowHeater-Team on topic Re:Import from one file to multiples MySql Tables ( Foreign key
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 planned 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!
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 planned 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!
Attachment lookup_example.zip not found
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Attachments:
Please Log in or Create an account to join the conversation.
- Francis
- Topic Author
- Offline
- User
Less
More
- Posts: 13
14 years 2 months ago #2028
by Francis
Replied by Francis on topic Re:Import from one file to multiples MySql Tables ( Foreign key
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?? Didn't find out yet
Another question : Strangly, I'm doing Excel to 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
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?? Didn't find out yet
Another question : Strangly, I'm doing Excel to 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
Please Log in or Create an account to join the conversation.
- FlowHeater-Team
- Offline
- Admin
14 years 2 months ago #2029
by FlowHeater-Team
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Replied by FlowHeater-Team on topic Re:Import from one file to multiples MySql Tables ( Foreign key
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 "?
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 "?
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Please Log in or Create an account to join the conversation.
- Francis
- Topic Author
- Offline
- User
Less
More
- Posts: 13
14 years 2 months ago #2030
by Francis
Replied by Francis on topic Re:Import from one file to multiples MySql Tables ( Foreign key
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 ?
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 ?
Please Log in or Create an account to join the conversation.
- FlowHeater-Team
- Offline
- Admin
14 years 2 months ago #2031
by FlowHeater-Team
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Replied by FlowHeater-Team on topic Re:Import from one file to multiples MySql Tables ( Foreign key
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.
Code:
public object DoWork()
{
string searchfor = "xyz"; // or by InValues[1] ...
string s = (string)InValues[0].GetString();
if (s.IndexOf(searchfor, StringComparison.CurrentCultureIgnoreCase) < 0)
return false;
return true;
}
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
Code:
String sql = "update …. Where partid = " + InValues[0].GetInt();
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.
Attachment update_example.zip not found
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Attachments:
Please Log in or Create an account to join the conversation.
Time to create page: 0.293 seconds