I have browsed many postings but as far a I can see, nobody has successfullt used this setting and received back the number of rows deleted in &records.

I was not sure of format so I tried quite a few and all return zero regardless of how many rows are deleted. Here are a couple of attempts:

  ENGINE SQLORA SET DEFAULT_CONNECTION financials
SQL SQLORA SET PASSRECS ON
END
-RUN
ENGINE SQLORA SET DEFAULT_CONNECTION financials
SQL SQLORA DELETE FROM reporting.sp_filter_ls_vw;
SQL SQLORA COMMIT;
END
-RUN
-TYPE '******* ROWS DELETED' &RECORDS

 ENGINE SQLORA SET DEFAULT_CONNECTION financials
SQL SQLORA SET PASSRECS ON
SQL SQLORA DELETE FROM reporting.sp_filter_ls_vw;
SQL SQLORA COMMIT;
END
-RUN
-TYPE '******* ROWS DELETED' &RECORDS 

Did you try querying &RECORDS *before* issuing COMMIT?

Great suggestion! And I was sure that would fix it.

But no such luck. Same results

 ENGINE SQLORA SET DEFAULT_CONNECTION financials
SQL SQLORA SET PASSRECS ON
SQL SQLORA DELETE FROM reporting.sp_filter_ls_vw;
END
-RUN
-TYPE '******* ROWS DELETED' &RECORDS
ENGINE SQLORA SET DEFAULT_CONNECTION financials
SQL SQLORA COMMIT;
END
-RUN 

The info I could find on PASSRECS indicates it must be set in the global profile (edasprof.prf?) before the data adapter is activated.

Global Setting PASSRECS

I tried a sample code both with and without SET PASSRECS ON and I cannot seem to be able to get the value in &RECORDS or any other variable for that matter.

SQL SQLORA SET PASSRECS ON
SQL SQLORA
delete from myuser.mytable where myfield > 1;
END
-RUN
-* Show & variables
-? &

The only difference is that when SET PASSRECS is set to ON for a request, after executing it I get the following message:

(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND  : 4/delete

Well, at least that proves that PASSRECS is indeed ON but &RECORDS is still 0.

I actually added a:

-? &

Right after just to check is there was any variable with a value of 4 with no success.

- Neftali.

You may be right. But there seems to be a lot of documentation out there showing it can be used on the "request" level.

My "workaround" has been to follow the delete with a select to see if there are any rows on. In this instance, &LINES should always be zero if delete worked.

If I was deleting a subset, I could select before and after the delete and subtract to determine how many rows are deleted.

But I was curious if anyone had gotten PASSRECS to work as documented.

From documentation:

quote:

Global usage will determine the initial PASSRECS settings for all data adapters that have not been initialized. Adapters that have been already initialized are not affect by global usage.

quote:

Individual SET PASSRECS commands are still supported

So, it seems that even if there are no global settings to activate the feature for the adapter, it is still possible to enable/disable individually via SQL [adapter] PASSREC ON|OFF.

The sample I ran actually shows that the statement above is true except for the fact that &RECORDS is not populated although an informative message is sent to the console indicating the no. of records involved in the statement's execution. If only I knew how to capture those messages ....

- Neftali.

quote:

My "workaround" has been to follow the delete with a select to see if there are any rows on. In this instance, &LINES should always be zero if delete worked.

If I was deleting a subset, I could select before and after the delete and subtract to determine how many rows are deleted.

That may work in either a single-user or read-only environment. If you have other users inserting/deleting data to your table, then counting records before and after the delete as a way to determine how many records were removed by *your* transaction may give you inconsistent values if another concurrent transaction deletes or inserts records to your table (committing their changes of course) right after your first count and before your second one.

The only way to guarantee that your approach works would be to change the isolation level your transaction would run at while handled by Oracle by I don't know if you can actually adjust that through SQLORA attributes.

If you cannot get &RECORDS to work as expected the safest approach would be to transfer your delete logic to an Oracle PL/SQL stored procedure and "return" the number of deleted records in a REFCURSOR which can be read by WebFOCUS via SQLOUT.

If yours is a read-only environment or if you can guarantee that no other session would be changing records in your table then your workaround might work; just be extra careful.

- Neftali.

This message has been edited. Last edited by: njsden,

Hmmm, based on this case it seems as though the functionality of setting &RECORDS is not indeed working as has been set as Corrected by 770.

You may also want to read [SOLVED] doing a Sql Pass thru Update and Getting a return Code for a more detail discussion on this same subject which does not seem limited only to SQLORA unfortunately.

Hope that helps,
- Neftali.

Since it seems that &RECORDS does not work with PASSRECS, you might consider a different approach. I haven't tried this myself, but it might be worth a try. You can turn on error message tracing for your SQL statements with the use of FILEDEF FSTRACE path/sql.trc (or maybe APP FILEDEF FSTRACE app/sql.trc). Hopefully the "FOC1364 ROWS AFFECTED ..." message or its SQL equivalent will be captured in the trace file. You could then -READ the trace file to find and extract the number of rows affected by your SQL statements. There are four trace file components (FSTRTACE1, FSTRACE2, etc.) If you can trap the ROWS AFFECTED message you may need only one of these and not all of them (from FSTRACE), thus reducing the number of lines written to the trace file.

This message has been edited. Last edited by: Dan Satchell,

quote:

If only I knew how to capture those messages ....

Well, it seems like now I may know how to do it.

Thanks Dan!

- Neftali.