SQL declare view on table twice or more on same thread

I am having bit of a struggle. It can be solved with extensive use of alias-tables, but it will hit again.

Scenario is as follows:
A view declared in one window.
A view on the same table declared in a called procedure on same thread, but with different fields.
This will repeatedly cause what appears to be “hang”, but it is not, it is just very unresponsive, when I get back to the first procedure and the first view, usually for a browse, should refresh. The “hang” lasts for a few minutes, dependant on size of table in question.

I have deducted that this might be Clarion trying to re-use SQL-cursors, or that the RTL declares a view for a table only once per thread, so if the pointer moves to EOF on second view, it has a really hard time locating the position in the first view, being that the buffer not match.

I am not entirely sure on this re-use theory, but to date, the only good way I have found around this is to swap the file for an aliasfile in one of the procedures.

My question is, if I am correct, does a property or method exist that I could use to instruct Clarion on how to allocate memory for a view?

Edit: I have found that the hangs usually happens when I close the window, it waits forever in WindowManager.Kill, so probably has to do with closing cursors.

Not sure about your exact situation, but are you explicitly closing the views after you’re done with them? They won’t implicitly close, even if the declared view loses scope. Their actual scope is at the THREAD level, even if declared within a procedure.

Yes, I always close the views I myself open and process.
The problem is possibly the THREAD level scope, it seems that the first instance of a view over a file is the master of it. I have seen the same stuff when having a browse in first window and then a second browse a few calls deep.

1 Like

Interesting. I would use MSSQL driver trace log from the client side trying to figure out what’s happening. From the server side SQL Profiler and/or sp_whoisactive could be also helpful to track down SQL locks while program hangs. This may give you a clue how to fix it.

does a property or method exist that I could use

I’d recommend to experiment with /ISOLATIONLEVEL driver string. The default value = 2, try to set /ISOLATIONLEVEL=1 and see if that helps. At least, this helped me one day in similar situation.

You might need to save the POSITION() before calling the procedure and restore afterwards.

The views are closed, so REGET won’t do anything. This answer made my remember a problem I used to have decades ago, that a view in a procedure that is called recursively must be closed between calls, else we got the View already open error. However, this is not the same view declaration, so not exactly the same.

Any mismatched PUSHBIND/POPBIND?
Does FLUSH(View) change anything? Oftentimes, that would be the ticket with TPS when working with view filters. Not sure about SQL.

Hi Bjarne

For what is worth, I have used a View on the same main file that the View of the Browse on procedures called by the Browse on the same thread with no problems. In fact that was a solution to the error 78 caused after directly accessing that File (SET/NEXT) on those called procedures. Sum up New issue for Error 78 on Reset in SQL
By the way, any chances you were referenced there?

I solved the error 78 by expanding the viewposition variable to accomodate for large views.
I also have had no problems in many cases, but these are large tables, some have millions of records. If I move the pointer outside the range of the view, it goes on forever trying to relocate the position and can’t find a record that matches any more, I think. Not sure. I’ll stay with alias files for secondary views.

Make sure the filter is sent to the server and not processed client-side