Tables extract from LN CE with ION

[Gaith1]

Hi ,

I'm using Infor LN CE and I want to see if it possible from ION to schedule an extract on a daily basis of any table (all fields or specific fields) in csv format. Can we use File Connection point ? We have to define File template format.
I want to improve the way we can extract data directly from database other than creating LN session/script to generate flat file.

Thanks in advance

[ARijke]

HI This is not supported. One option is the one you mentioned, by creating a LN session generating a file. The other option is to use Data Lake. LN publishes data to DL.and with data lake tools you can extract the data to a local database.

[Gaith1]

For the Data Lake option , I have all records published from LN for table tcibd001 into Data Lake and I can verify the total line count when I Run Query in Data Lake/Compass for JSON object LN_tcibd001.

Form there can I extract to a csv file with an execution set up on daily basis ? Or I need to extract the data to a local database and then built a query to generate csv file ?

thanks

[ARijke]

HI, There is an DataLake API which can be used to retrieve data out of DL. Secondly there is a tool, DataLake ETL Tool, or something like that. This tool needs configuration of course, but will synchronize data from DL to an on prem database.

[Gaith1]

Thanks for the reply , I will look for the best option

[OmeLuuk]

There is yet another option I suppose, A number of tables do have a BDE available where you can send a LIST or SHOW command with a specific filter to get data from LN on demand.

You will need a deployed C4WS tomcat instance and deployed BDE. It is also possible to develop your own BDE in LN Studio.

In an Interface project you can import any LN table and generate a default implementation and definition. Compile, check-in, deploy and you can request all fields in the table.

[Gaith1]

Can you give more details when you say
" In an Interface project you can import any LN table and generate a default implementation and definition. Compile, check-in, deploy and you can request all fields in the table."

Where can you define an Interface project ? directly in LN Studio?

[OmeLuuk]

Yes, Check the documentation https://docs.infor.com/ln/2022.x/en-...267817978.html

In the Studio select the Integration Perspective, then Import Table. https://docs.infor.com/ln/2022.x/en-...267819601.html
It will import the table and the used domains. Then on the table select "Create Default Definition and Implementation" It will create a fully functional BDE for you.

Before you can compile you will need to set the identifier, then your BDE can be compiled and deployed...

[Gaith1]

thanks a lot , I will try

[ARijke]

A BDE can indeed be used. Please note that it is not always possible to retrieve a delta, changed since the previous extraction.

[OmeLuuk]

Originally Posted by ARijke

A BDE can indeed be used. Please note that it is not always possible to retrieve a delta, changed since the previous extraction.

What we usually do in that case is also create a BOD for the same table and create an extension on the table in which all (or relevant) fields of the table are declared as keep.<fld> variables.
And we need

#pragma used dll "otcextbodapi"
	table	    ttdcom902
	domain	    tcmcs.s999m   ErrorSituation
	domain	    tcmcs.long	    ErrorID
	domain	    tcmcs.str12	    keep.keyf      | Key Field

In the functions section we use:

function keep.some()
{	keep.keyf = tdcom902.keyf      | Key Field
	keep.<...> = tdcom902.<...>
}
function boolean some.change()
{        return(not(     keep.keyf = tdcom902.keyf and
                        keep.<...> = tdcom902.<...>))
}

Then in the Before Save hook:

function extern long before.save.object(long mode)
{	with.old.object.values.do(keep.some)
	return (0)
}

In the After.Save we trigger the BOD:

function extern long after.save.object(long mode)
{
	| hook code
if some.change() then
	if mode = DAL_NEW then
	    return(bod.publish("OwnBOD", "tdcom902", "Add", 0,
					"", tdcom902.keyf, "OnlyPublish", 
					ErrorSituation, ErrorID, tdcom902.keyf))
	endif
	return(bod.publish("OwnBOD", "tdcom902", "Change", 0,
					"", tdcom902.keyf, "OnlyPublish", 
					ErrorSituation, ErrorID, tdcom902.keyf))
endif
}

So we only get a BOD when the relevant fields are changed and that BOD can be used to "know" the "delta".

[Gaith1]

Thanks , I will do a try

[Renegade]

very interesting. I want to create a BOD for a master table (for which no standard BOD exist). Please direct me to any existing thread if any. This way any change can trigger a sync out. My environment is LN CE

Thanks

[OmeLuuk]

What I did several times:
After creating the "default definition and implementation" do delete the implementation, go to the definition, change the type from BDE into BOD and save.
In Studio you will notice the options in the definition will change into BOD specifics, like methods etc.

Model what you want to achieve (or nothing except for the mandatory methods for output - and input - of data). List and Show events will be the most important I assume.

Then generate the implementation, choose an identifier for your BOD and Generate the interface DLLs and you are done.

Congratulations, you created your first BOD.

[OmeLuuk]

Originally Posted by Renegade

very interesting. I want to create a BOD for a master table (for which no standard BOD exist). Please direct me to any existing thread if any. This way any change can trigger a sync out. My environment is LN CE

Thanks

Did you manage?