Automation of interface by modifying the query

[harish_babu5]

Hi all,

I have an interface MS SQL to ECC configured in my landscape and it's working fine. The JDBC adapter polls every 15 minutes and pick the last 24 hours data. Few days back the SQL system was down for two days and they missed a days data.

The client is not ready to allow to use flag field. They have provided an InsertionDateTime field.

How can I modify the interface so that even if the system is down for two days, the data will not be lost?

Can it be made ECC - MS SQL synchronous with the use of stored procedures at receiver system to use timestamp as a condition for the data to be picked?

Any suggestions would be appreciated

Regards

Harish

[iaki_vila · Accepted solution]

Hi Harish,

I can think anyway to control with sender jdbc. I think the best way is to control from ECC like you said, you can do a job every day with two characteristics, the first to know if the connection is broken or not and the second to do the select only if in the ECC system there are not registers in the insertionDateTime period. Then you can do a proxy - mail to inform you and other responsibles about a possible case of an issue.

Regards.

[harish_babu5 · Accepted solution]

Hi Inaki

Can you elaborate on this?

What do you mean by connection is broken or not?

I create a time stamp in ECC. Then I have to use that as a criteria in receiver JDBC adapter to poll from the database. I guess I need stored procedures also

[iaki_vila · Accepted solution]

Hi Harish,

What do you mean by connection is broken or not?

You have to talk with the DB to activate a standard stored procedure for this purpose http://stackoverflow.com/questions/1248423/how-to-see-active-sql-server-connections

You can do a ECC - JDBC scenario to call this stored procedure a check its availability.

Regards.

[harish_babu5 · Accepted solution]

Hi Inaki

The design that I am doing is this!

There is a field InsertionDateTime in MS SQL which is auto populated every time a record is inserted to the database. I pull the value to the ECC system

ECC will search the tables and find the highest value for InsertionDateTime in records. Then it will send that value of InsertionDateTime to MS SQL. MS SQL will use that field and poll all data that have a date timestamp greater than that of InsertionDataTime.

This will be updated back to ECC. While picking the fields I will update the insertionDatetime in ECC

If I am doing this, can I do this without stored procedure?

I am using a synchronous receiver JDBC databse.

Can I use this here?

Harish

[former_member182412 · Accepted solution]

Hi Harish,

>>>MS SQL will use that field and poll all data that have a date timestamp greater than that of InsertionDateTime

How are you going to do this without writing the stored procedure? You must use stored procedure for this because date value is dynamic in the select query.

Regards,

Praveen.

[harish_babu5 · Accepted solution]

Hi Praveen,

Can't the requirement of mine be met using this blog

http://scn.sap.com/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select...

Here, I would use the InsertionDateTime as a value. The ECC always sent a fixed value for that.

Say if it sends, 31:03:2016 12:00:00 then I use this value and pick records with timestamp greater than value

Harish

[harish_babu5]

The interface was done without using Stored procedure

Created sender synchronous proxy and JDBC receiver synchrnous proxy

JDBC, I created two structures. MT_XYZ_Request and MT_XYZ_Request_response

MT_XYZ_Request structure

MT_XYZ_Request

     Statement

          dbTable

          action

          access

I gave action as SQL_QUERY

For access I did a mapping

Constant

                                        concat               access

InsertionDateTime

In the InsertionDateTime, the value will be sent from ECC

In Constant I wrote the SQL query select A, B, C, D, E, InsertionDateTime from <dbTable>  where InsertionDateTime >=

This was concatenated with the dynamic value that I got from ECC and passed to the access field

This is now working fine, thanks for all the inputs

[Former Member]

Hello Harish,

You may query your ECC system to check when was the interface executed last and accordingly send a sync request to the MSSQL db to fetch the new records when the insertionDateTime in db is more than the last execution time in ECC. Both these can be scheduled in a job in ECC, just like Inaki said.

Regards,

Diptee