on 11-12-2013 12:19 PM
Dear Experts,
Scenario: JDBC to IDOC.
In my production database system the records are getting inserted into the table when ever employee swipes his card,
the data will come as a new entry into table which is present in Production database TABLE_ATTENDANCE.
I configured JDBC adapter at sender side with following QUERY Statement:
Query SQL Statement: SELECT TOP 2 * from TABLE_ATTENDANCE WHERE FLAG = 0
Update SQL Statement: UPDATE TOP (2) TABLE_ATTENDANCE SET FLAG = 1 WHERE FLAG = 0
I am facing issues here as below:
1 FirstRecord
2 SecondRecord
3 ThirdRecord
4 FourthRecord
5 FifthRecord
6 SixthRecord
7 SeventhRecord
8 EightRecord
Intitally My jdbc adapter is polling the table in the database and picks first 2 records 1 &2 then meanwhile few records are getting inserted and while fetching for the next poll interval it fetches 6&7 records, but where as it should fetch 3 & 4.
I want to read the records as if the same sequence in my database.
Expecting your inputs on this.
Thanks,
--Sai
When TOP is used with INSERT, UPDATE, MERGE, or DELETE, the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in these statements. If you need to use TOP to insert, delete, or modify rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause that is specified in a subselect statement. See the Examples section that follows in this topic.
TOP cannot be used in an UPDATE and DELETE statements on partitioned views.
TOP cannot be combined with OFFSET and FETCH in the same query expression (in the same query scope). For more information, see ORDER BY Clause (Transact-SQL).
see also TOP (Transact-SQL)
And I'm not sure if jdbc driver for mssql support T-SQL.
I had the same problem once and I didn't solve it. I changed my sql query to another logic, with subqueries.
If interesting my queries were:
select DATE, TOTALTIME, TICKET_NUMBER, SERVICE_GUID,PERSON_GUID,COMPANY_GUID from SAP_GUID where processed=0 and date=(select min(date) from SAP_GUID where processed=0)
and
update SAP_GUID set processed=1 where processed=0 and date=(select min(date) from SAP_GUID where processed=0)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Ambrish,
Still I am facing the same issue.
Eg: I am reading one set of data and updating another set of data
Query SQL Statement: SELECT TOP 10 * from TABLE_ATTENDANCE WHERE FLAG = 0
Update SQL Statement: UPDATE TOP (10) TABLE_ATTENDANCE SET FLAG = 1 WHERE FLAG = 0
In database while reading records using select I have 3 records but during update another 3 records were inserted at realtime for update it fetches 6 and updating its flag.
So, I want to know is there any way to lock the number of records which is selected in SELECT query for UPDATE.
Your inputs are well required here
Thanks,
--Sai
Dear Ambrish,
Yes I am open to Stored Procedures.
My ultimate goal is to fix this issue.
I just want to know is there any dependencies in using Stored Procedures like
1. Do I need to install any patch or update with in PI server?
2. Where do I need to put my stored procedure code, by default I will be developing in my source database and will check with its running.
3. I see CALL <StoredProcedure> as syntax while executing in SQL, but is the same statement do I need to define in QUREY SQL of jdbc adapter.
Need your valuable inputs here.
Thanks,
--Sai
Hi Sai,
Did you refer to the thread I forwarded to you.
Does it work for you.
My response to your questions:
1. Do I need to install any patch or update with in PI server?
No
2. Where do I need to put my stored procedure code, by default I will be developing in my source database and will check with its running.
The stored procedure will reside in the database and will be executed in DB when invoked from PI.
3. I see CALL <StoredProcedure> as syntax while executing in SQL, but is the same statement do I need to define in QUREY SQL of jdbc adapter.
You will just call stored procedure from PI adapter and that's it. It will return the number of rows the same way like a select. I will check the stored procedure if it is suitable to your requirement.
Ambrish
Hi Ambrish,
I have my stored procedure in my hand right now,
I tested this in my SQL server its working fine as expected.
But while calling from PI its throwing error.
QUERY SQL Statement: CALL SP_UPDATEFLAG;
Here SP_UPDATEFLAG is my stored procedure name.
Can you tell me how to call stored procedure in my JDBC Adapter.
Thanks,
-Sai
Hi Sai
Please change your query as below and test.
Query SQL Statement: SELECT * from TABLE_ATTENDANCE WHERE FLAG = 0 and ROWNUM < 3
Update SQL Statement: UPDATE TABLE_ATTENDANCE SET FLAG = 1 WHERE FLAG = 0
This should solve your problem. Let me know if there are any issues.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I believe the above suggested update statment needs to be corrected as it'll update all the records (not just the first two. )
UPDATE TABLE_ATTENDANCE SET FLAG = 1 WHERE FLAG = 0 and ROWNUM < 3
Also it would be good if you can add some order by clause to your query incase you have any fields like "swipe in"/"emp ID" in your table
Hi Sai
Did u give a try with the query I have mentioned previously??
ROWNUM is the uniq number of every row in the data base. So when you run this query
SELECT * from TABLE_ATTENDANCE WHERE FLAG = 0 and ROWNUM < 3
you will get the first two row with sequence 1 and 2 from list of rows where the flag is 0.
Next use the below query
UPDATE TABLE_ATTENDANCE SET FLAG = 1 WHERE FLAG = 0 and ROWNUM < 3
so it will set the flag 1 for the last two records that is fetched in the select query.
Please give it a try before you search for other valuable inputs.
HI Indrajit,
its not working..
the issue here is during select there is only 1 record in the database so the rownum<3 will satisfy and fetch only one record
during update if anther record is inserted then rownum<3 will satisfy and updates both records so this is failing
What is the time differienct it takes to execute SELECT & UPDATE query in JDBC adapter?
Thanks,
--Sai
Hi Sai,
Go ahead with Gagandeep Batra's advice, a little tip:
Check Dheeraj Kumar's blog http://scn.sap.com/people/dheeraj.kumar5/blog/2010/04/12/pixi-sender-jdbc-select-query-and-update-qu...
Regards.
Message was edited by: Iñaki Vila
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Sai,
Did you try with:
under "advanced" tab the parameter "Transaction Isolation level" to serializable.
Regards
Gagan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.