SQL cursor scrolling options

questions
,
1

Hello,
is it possible to change the scroll option with which Clarion prepares the cursor for loading data in ABC Browse?
If I see correctly, the default options used are: AUTO_CLOSE + FORWARD_ONLY + PARAMETRIZED_STMT. For performance reasons I would need to add STATIC option here.

I’m afraid it can’t be controlled from Clarion, but maybe I’m missing something?

Have a nice weekend!

Andrzej

2

Hi

This would be related
http://clarion.help/doku.php?id=prop_stmtattr.htm

For static cursor it would be:
Customer{PROP:StmtAttr,6} = 3

But it seems either it doesn’t work or Clarion redefines again its own parameters previous to the queries.

One thing that does work is a technique to not use cursors, if it worth to you, here there is information about the source where it was discussed
https://clarionhub.com/t/mssql-json-result-question/4807/9

3

Well that conclusion seemed by looking on the trace generated by Clarion

03B20H(1) 16:33:25.506 Setting Concurrency to Row Version for Statement 0BBBC50H Time Taken:0.00 secs
03B20H(1) 16:33:25.506 Setting Cursor Type to Forward Only for Statement 0BBBC50H Time Taken:0.00 secs
03B20H(1) 16:33:25.506 Setting Concurrency to Row Version for Statement 0BBBC50H Time Taken:0.00 secs
03B20H(1) 16:33:25.506 Setting Cursor Type to Forward Only for Statement 0BBBC50H Time Taken:0.00 secs
03B20H(1) 16:33:25.506 Setting Cursor Type to Forward Only for Statement 0BBBC50H Time Taken:0.00 secs

More repetitions of forward only, but it may be working, on MS Profiler it shows this line after sp_cursorprepexec and before sp_cursorfetch

exec sp_cursoroption 180150015,1,3

Please try and comments on your findings.

4

That seems related to a blob in that table and not about the cursor type
https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cursoroption-transact-sql?view=sql-server-ver16

5

Hi Federico,

thank you for your help, unfortunately I couldn’t get it to work. It seems Clarion sends to sp_cursorprepexec the same parameters for the cursor all the time, regardless of the values set by PROP:StmtAttr.

Regards,
Andrzej