Friday, December 22, 2006

Sometimes my update queries don't work...

I added a query to the Work_StatsTableAdapter(TA) that did an update to Work Stats. The problem was that sometimes the update didn't work because the WHERE clause specified rows that didn't exist in the database. There was no signal back to the calling procedure that update didn't. I decided that we needed a number-of-rows counter passed back. The problem is I don't see a way to do this using Oracle. There's no rowcount that can be returned back immediately after a sql statement. You can use SQL%Rowcount, but that has to be used in a begin-end block and the TA query parser doesn't understand this construct. So I guess I'm stuck trying to implement a stored proc and calling it.

My stored proc on Oracle looks like this:
CREATE OR REPLACE PROCEDURE UPDATE_WORK_STATS(
P_COUNT IN WORK_STATS.COUNT%TYPE,
P_USER_ID IN WORK_STATS.USER_ID%TYPE.
P_WORK_DATE IN WORK_STATS.WORK_DATE%TYPE,
P_WORKTYPE IN WORK_STATS.WORKTYPE%TYPE,
P_COMM_MTHD IN WORK_STATS.COMM_MTHD%TYPE,
P_ROWCOUNT OUT NUMBER) AS
BEGIN
UPDATE WORK_STATS SET COUNT=P_COUNT
WHERE USER_ID = P_USER_ID
AND WORK_DATE = P_WORK_DATE
AND WORKTYPE = P_WORK_TYPE
AND COMM_MTHD = P_COMM_MTHD;
P_ROWCOUNT := SQL%ROWCOUNT;
COMMIT;
END UPDATE_WORK_STATS;

Now let's see if I can get VS to invoke this SP and give me back a rowcount.

No comments: