Skip to main content
simply more productive

The Data Specialist

Thanks to our intuitive transformation designer, you can convert data effortlessly to a variety of formats without knowing how to program. It’s not only efficient, it’s also easy to use.
Flowheater Download

How to get a default value, or simply last_insert_id

More
13 years 5 months ago #2056 by Francis
Ok, here's my 2 questions

First
Is there anyway to get a default value based on the read adapter filename by launching a conversion with the script batch executer ? Like an argument that you can access like adapterRead.getArgument[0].ToString().

Seems not, so I tried to insert this value in the first row, in a column random, but when I try to get this value by using mysql Last_Insert_ID(), i can't access this value because I guess, flow heater can only get last insert ID of the conversion before, and not the current conversion.

for exemple, i have those excel to import

C-CCJJ.xls
C-FDIO.xls

So I use the forfiles *.*xls command in the batch executer to get these ( around 20 excels like this ).

But I need to use the value C-CCJJ and C-FDIO to do some lookup that refers to a conversion I launch before it.

This value is not in my excel. So if I really have no choice, I'll add it in the excel by copying the same value to all row, but it's not the option that I want :S

I tried as I said to write the value one time in the excel, just as a X-Value. It works for the first row, but I try to access this value to copy it in all rows with 2 select to get my field

obj = AdapterWrite.Execute( select max(ID) from tbl ). This ID should refer to the last row I entered that should contains my value.

And AdapterWrite.Execute( select myField where ID = obj.ToString()).If I debug this, the ID returned is always the last ID that conversion before the one I built just inserted, and not the one just inserted by the current conversion. Same if I use 'Select Last_Id()'

So is there someway to have a kind of property getArgument[0] that can get this dynamic value that changes between excels to import.



Second question ----

Is there a way to get current ID row of a read Adapter ?? like a property AdapterRead.getCurrentID() that would return the row currently importing. With this, it would be SO EASY to deal with 'unique' record that may appears in my excel files.

A little suggestion too would be to add row number the 'show input' adapter from each side and in the 'Preview data' when converting.



Thx for helping me ! I'm about to contact you to make an arrangement for licenses, I made some hardcore templates that I need to test with a huge excel importation. My boss would like to me convince him about the great things that tool can do.

Please Log in to join the conversation.

More
13 years 5 months ago #2057 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:How to get a default value, or simply last_insert_id
You can obtain the current input/import file name by using the .NET Script Heater and the following C# script code. Note: You don’t need an input value for this. The Output value contains the file name without path and extension. This also works if you use the forfiles command in batch processing mode.
Code:
public object DoWork() { string filename = String.Empty; // for excel adapter ExcelAdapter adapter = (ExcelAdapter)AdapterRead; //filename = Path.GetFileName(adapter.Database); filename = Path.GetFileNameWithoutExtension(adapter.Database); // for textfile apater //TextFileAdapter adapter = (TextFileAdapter)AdapterRead; //filename = Path.GetFileName(adapter.Filename); //filename = Path.GetFileNameWithoutExtension(adapter.Filename); return filename; }

Parameters: We planned to pass parameters in a further version. By now you can read parameters from an external text file and a short .NET Script. Please have a look to the examples enclosed in the attachment.

About the problem with the mysql last_insert_id():
Your way is correct but I think you have to switch the execution mode from memory (default) to mass data. Background: In memory mode FlowHeater reads first all data. After this the conversion starts, the value stored also in memory and at the end each transformed row are imported or updated. In mass data mode FlowHeater reads one row, transform and run heater functions, import or update this row, read the next row …

Hope this help.

Thanks for the suggestion. I´ve added this to our Todo list.

Attachment Parameter.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 to join the conversation.

Time to create page: 0.259 seconds

FlowHeater - The Data Specialist

Efficient data integration and transformation with FlowHeater – the perfect solution for a seamless transfer of your data.

Rechtliches

Support & Contact

Kontaktinformation

Telefon:0951 / 99339792 E-Mail:This email address is being protected from spambots. You need JavaScript enabled to view it.

Copyright © 2009-2024 by FlowHeater GmbH. All rights reserved.