Please Help!,Count size of a dataSet from SQL DB.

[Former Member]

i'm going to use JDBC adapter to selet a table and get a dataSet from SQL server,after that i need to know the size of the dataSet.(how many records it have) to insert it into another table.

does the XI has that kind of function?

how to do it in XI?

should i use UDF?

is it possible can provide a sample for me to use UDF.

i'm a very beginner of SAP XI.

thank you very much for your help...

Regards

Jack Lee

[Former Member]

thanks for help.

i know how to get row count now.

[Former Member]

Hi,

You can count the number of occurences of field which is coming with every record. I will say primary key. You just count the number of occurences using count function in Message mapping and then map it to target field where you need that information.

you can explain bit more for more help, like what type of logic you want to implement using count.

Kulwinder

Rewad if helpful

[Former Member]

Hi Jack ,

There is a standard function - count in XI which will give you the number of values in a field. You can give any key field as the input.

Alternatively, you can restrict the selection to a required number of records by using condition ROWNUM < 100 in the sender adapter.

Are you trying to insert into SAP table or SQL server table?

-regards,

Shamly.

[Former Member]

thank you so much for your help , i didn't expect could have a response that soon.

here are what i'm going to do.

1.call RFC function to get a dataSet from SAP/R3.

2.connect to SQL server and make sure the connection is alive.

3.delete a table data from a SQL server(not SAP tables)

4.insert the dataSet i got into the SQL server table.

5.if insert fail then need to recover the table data(rollback) and send a error email.

6.if insert sucess then commit.

7.end.

do you have any suggestion how to implement the process above?

or any step by step scenario can be referance?

i'm still try to figure out how to do it in XI use BPM.

thank you again for your help.

very beginner

Jack Lee

[Former Member]

Hi Jack,

My sugestions is you can avoid BPM.

1.call RFC function to get a dataSet from SAP/R3.

2.connect to SQL server and make sure the connection is alive.

3.delete a table data from a SQL server(not SAP tables)

4.insert the dataSet i got into the SQL server table.

The above 4 steps can be maintained in the same asynchronous interface using 2 statement tags - 1 for action delete and the other for action insert.

check for JDBC receiver adapter document formats in the below link.

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Specify retry numbers in the receiver JDBC adapter communication channel. Commit is done automatically once the data is inserted.

If still the message fails, the message can be resent from the runtime work bench manually. Data is not lost.

Configure a mail alert for this interface using alert configuration , which will send a mail, when the communication channel error occurs and hence no insert happens.

Hope it helps.

-regards,

Shamly.