JDBC to file

[Bhargavakrishna]

Hi Experts,

we have a requirement, where i want to fetch the data from Database and place the file in sap directories. the file format will be either text or excel.

im planing to implement the scenario like JDBC to FILE. can we achieve with this or i have to use some other adapters..

can anyone explain me how to place the file in sap directories?

can we achieve, this with the file adapter or we have to define any UDF's or functional modules? if so which function module should we use? is it pre-defined or user defined?

please guide me with step by step guides.

Message was edited by: Agasthuri Doss

[naveen_chichili · Accepted solution]

Hi Bhargava,

For your scenario you can use JDBC -->PI-->File Adapters.

you need to use JDBC adapter in the sender sind and you need to fetch the data using sender query from the Database.

once the data is fetched from DB you can map the records according to your business requirements in mapping .

---->can we achieve, this with the file adapter or we have to define any UDF's or functional modules? if so which function module should we use? is it pre-defined or user defined?

if there is any complex maaping to be done you might go to udfs else you can do the mapping using standard or Node functions.

after mapping is done you need to use file adapter at your receiver side and configure the file adapter as per your requirement ex:filename.txt

--------->can anyone explain me how to place the file in sap directories?

You need to select transport protocal as File Transport Protocal(FTP)

1.You need to give your SAP Server host name

2.provide the directory where you have to place the file

3.Provide the file name to be written in target server with the desired extension

        you need to do content conversion if the file is text in your target side

4.Provide your user credentials for the server

Thanks and Regards,

naveen

[smavachee · Accepted solution]

Very well choreographed Naveen.!

Regards,

Sunil

[naveen_chichili · Accepted solution]

Thanks Sunil..

[anand_shankar10 · Accepted solution]

Hi Bhargava,

In addition to what Naveen suggested you can go for File Adapter at Receiver end with NFS protocol (since you want the files in sap directories) and place the file in MOUNTED directories between PI and SAP directories. These directories are generally accessed by using AL11 t-code..(You can also store these files on PI server and can access those files using t-code AL11 on PI GUI login).

Regards

Anand

[Bhargavakrishna · Accepted solution]

thank you very much for your reply naveen. it gives me some idea to work on.

should we use FTP for this scenario? cant we use NFS?

in this scenario, i have to fetch the emp attendance details from database, using JDBC adapter.

The data to fetch on daily basis and transfer the data around 11:50 Mid night.

After receiving the list if there is no Out time of the employee in his last record for the day, filter the employee numbers and store in a custom table.  The next day fetch those employee previous day data along with current day data.Repeat the same above check every day.

In Case the employees doesn't have out time in the exception even of the second day as stored in custom table send the employee details as excel file with data with missing entry to the time administrator of the employee. 

i will take care the mapping part. can you explain me how to trigger a mail to the administrator, as we are using JDBC (sender) and File (receiver) in this scenario.should we implement separate interface to trigger an email to the administrator or how to achieve this.

[gagandeep_batra · Accepted solution]

Hi Bhargava,

how to trigger a mail to the administrator, as we are using JDBC (sender) and File (receiver) in this scenario.

For that if you take care of mapping then you can configure one more receiver with mail adapter which is refer to administrator and define this condition in the Receiver Determination.

Hope this work,,,,

Regards

Gagan

[naveen_chichili · Accepted solution]

Hi Bhargava,

--->should we use FTP for this scenario? cant we use NFS?

Yes you can use NFS if you need to place the file in the same server where your PI is installed.If you need to place the file in the other server you need to use FTP protocal.

As you said you can fetch the emp attendance details from database, using JDBC adapter.

Also you can configure one Receiver comm channel to send email to the administrator.Please check the below link for configuring receiver mail channel.

http://help.sap.com/saphelp_nw04/helpdata/en/6b/4493404f673028e10000000a1550b0/content.htm

Thanks,

Naveen.

[Former Member]

Hi Bhargava,

                     If you are trying to create Excel file then you need to write some Java code in Adapter Module as shown in the following links.You can refer them to have an idea.

http://scn.sap.com/community/pi-and-soa-middleware/blog/2009/04/05/excel-files--how-to-handle-them-i...

http://wiki.sdn.sap.com/wiki/display/xi/code%20-%20create%20an%20excel%20file%20via%20the%20jexcel%2...

Regards,

Keerthi

[Bhargavakrishna]

thank you.