Issue with Insert-row activity in the Post stage.

Hi,

I am trying to insert data using the "insert rows" activity at post stage

I am using the following query to fetch data from Resources_Date(Table1) and insert the same data in Resource_Request (Table2). This query is working fine in Sql Server 2008 and inserting the same data as desired.

"insert into Resource_Request (RRID,PSNumber,Status,Resource_Comments,RR_Case_ID) (select a.RR_Number,a.PSNumber,a.Resource_Status, a.PrimarySkill,a.RR_Number from Resources_Data a where a.Resource_Status='Filtered');"

Even the same query is working fine in Business Object.

But when i use the same in the Insert rows activity it is showing following error:

Exception 'Invalid column name 'Filtered'.' occurred when attempting to 'Execute MBO non-query'.

I have tried with double quotes, removed the single quotes, used the process variable instead of value but still not able to resolve the issue.

Could any one suggest me how to resolve this issue.please let me know where i am going wrong. Is it the query which is giving the problem or the Filtered part ?

If not suggest me the best approach for acheiving the same functionality in Metastorm

Awaiting comments.

Regards

Sundeep

Find more posts tagged with

BPM

Comments

jpesout

INSERT statement in business object doesn't make sense to me, AFAIK there should be only SELECT.

I prefer code activities instead of visual, so I would implement it as a code activity with SelectSql

SelectSql(
  null,
  "insert into Resource_Request (RRID,PSNumber,Status,Resource_Comments,RR_Case_ID) (select a.RR_Number,a.PSNumber,a.Resource_Status, a.PrimarySkill,a.RR_Number from Resources_Data a where a.Resource_Status=@Resource_Status)",
  SQLArg("@Resource_Status", "Filtered")
);

Edit: SQLArg is just for example how to pass parameters, you can write 'Filtered' string directly to SQL.

Sundeep Dornadula

Thanks for your reply.

the issue with the query has been resolved. It can be done in either ways,(which i have specified and the way you have specified.).

Logic: I will be updating values in grid (Which is linked to table1) of a stage. So when ever there are changes in the grid the changes will be updated in the table1 too, on submitting the form. I want to insert the records which were updated in table1 in table2 before going to next workstep/before opening the form of the next workstep.

I am sure the query i have posted here is working fine.

Implementation:

I have used insert rows in post stage in which grid is used.

But the issue is by the time my insert query is running (Which will insert data in table2), table 1 is not yet updated with the changes made in the grid.

I have tried with implementing the while loop with even 10000 counter. But still the same issue.

Need your suggestions on how to execute the insert query only after updating the grid values in table1.

Regards

Sundeep

jpesout

I am not sure I fully understand, but I would implemented it in database layer with triggers.IMHO you will have a lot of troubles with this design.

Sundeep Dornadula

The issue was resolved.

Found 3 ways to make sure the data submitted in the stage is inserted in the table 1 before queriying table1. As suggested in issue description

Commiting the Business Object on the form Exit
Using while loop in the Pre-stage of the next step: Before querying the database (Table1 ) for the updated data, we can check for the no of rows updated using some logic. When the condition is satisfied the insert will work, if now the while loop goes on until the condition is satisfied.
Using hte System stage: Keeping the logic of checking the updated data in the post stage of the System activity.

to summarize the issue, we just need some time delay to ensure the data updated in the grid to be committed.

Hope i am clear with the solution, let me know if any one needs any clarification.

Regards

Sundeep