I have some SPs that can take a long time to run based on the user's input values.

I want to be able to limit the execution time if they take too long, and let the user know the the values they passed in need to be changed.

First, my environment: 1. LLBLGen Pro 2 (licensed) 2. Oracle 10 Database 3. Oracle ODP 10 Client

I can't seem to get generated SP calls to timeout even when passing in the Adapter having set the CommandTimeOut property.

The code in question is below. I'm using a 1 second CommandTimeOut, I figure that's the most extreme case. No matter what I do, the code below never times out (the most extreme input values make the SP run for 5 minutes, and it never does time out).

DataAccessAdapter adapter = new DataAccessAdapter(); adapter.CommandTimeOut = 1;

try { dataSet = RetrievalProcedures.ByStandardInsuredSearch(surname, firstName, dateOfBirth, adapter); } ...

Am I missing something? This all looks pretty straightforward, but I am newer to LLGLGen development.

Thanks, Jason

Which runtime library version are you using?

The runtime library version is obtainable by rightclicking the SD.LLBLGen.Pro.ORMSupportClasses.NETxy.dll in windows explorer and then by selecting properties and the version tab. The version is then enlisted at the top as the fileversion. It has the typical format as 2.0.0.YYMMDD, or starting in 2007, the format 2.0.YY.MMDD

Identity: SD.LLBLGen.Pro.ORMSupportClasses.NET20

Version: v2.0.50727

Path: C:\Program Files\Solutions Design\LLBLGen Pro v2.0\RuntimeLibraries\DotNET20\SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll

Thanks, Jason

Sorry, here's the file version from Explorer: 2.0.07.0611

It might be Oracle's ODP.NET has a minimum # of seconds, e.g. 20. As you can see in the CallRetrievalStoredProcedure routine in DataAccessAdapter, the command timeout is set by the value in the adapter (you can check this in debug mode). In debug mode, do you see the value of 1 in that routine?

Sorry I didn’t reply this weekend, this is for work, which I don’t too do much of over the weekend. LLBLGen is setting the value properly, but the timeout never occurs (and values such as 1 or 2 are valid and work in other scenarios).

This appears to be an issue with the Oracle Provider (sort of, the fix is a server side setting…).

The only scenario where this arises is on long running stored procedures when you want to use the CommandTimeout parameter.

My testing yesterday found that the CommandTimeout works fine for regular queries, but it fails for stored procedure calls (no matter how long they run and regardless of the CommandTimeout value).

Finally, Oracle acknowledges the problem. The recommended “fix” is a server side change, one that I am in no position of having changed (the system where the stored procs. reside is a system we are integrating with).

From the Readme for ODP.Net 10.1.0.3.01 (the version I have is 10.1.0.4 – but it certainly still has this problem):

1. Terminating the execution of a PL/SQL stored procedure or function may not acknowledge the invocation of OracleCommand's Cancel method or expiration of OracleCommand's CommandTimeout property value in a timely manner. For better responsiveness, the init.ora parameter can be modified to have BREAK_POLL_SKIP=3. However, this change can degrade the overall performance of normal SQL and PL/SQL execution by up to about 5%. [bug 3715843]

http://www.oracle.com/technology/docs/tech/windows/odpnet/readme10103.txt

Init.ora is a server side configuration file (the primary one I believe).

Possible Resolution with Discussion:

This issue only arises on Retrieval Procedures. The Action Procedures are fast, and due to security restrictions, I have no other choice but to use the SPs for their functionality. But, I do have the appropriate SELECT grants to allow for flexibility in the Retrieval Procedure calls.

As well, this is a situation that doesn’t lend itself to Entities as I have two or three different providers for this object type, and not all are databases (one is a Java backend that I only have HTTP web service access to). So I’m using a GenericInsured object which the various ServiceAgent classes know how to populate (the remainder of the backend just uses GenericInsured, regardless of the actual source).

And, I need to be able to control: 1. Execution time, specifically catching and handling when queries go longer than expected. 2. Result count returned (implemented like this in the SQL: “AND rownum <= inMaxRecordCount”)

Because I need that level of control, and this type of data isn’t really suited for Entity objects, I’m considering just writing my code data access code to perform these queries. As queries I get all of the control I need.

If this data was suited to Entities then that’s certainly the route I would take, but due to the custom providers for the same type of data it’s easier to implement a simple generic object.