Kelly's Space

Coding 'n stuff

Monthly Archives: March 2008

Improving cursor performance

In NetCOBOL for .NET v3.1 you can get fast forward, read-only cursors provided you configure the ODBC settings in your configuration file accordingly.  By default, all cursors will be read only.  Unfortunately, many people use cursors for updating as well, and usually get this to work by changing the @SQL_CONCURRENCY option to something other than READ_ONLY.  This option will affect all cursors in the application, and turn read only cursors into dynamic ones (which perform much slower than their read only counterparts).

You can get around this by using SQL Options in the NetCOBOL configuration section.  Basically you create two options, one for read only cursors, and the other for updatable cursors.  Then, set the @SQL_ODBC_CURSOR_DEFAULT and @SQL_ODBC_CURSOR_UPDATE settings to point to these two options so that NetCOBOL can pick the correct SQL Option depending on the cursor type.  You are probably asking: How does it know what type the cursor is?  The answer is that it looks for the FOR UPDATE clause in the cursor declaration.  In order for this setup to work, you will need to change all of the cursor declarations for any cursor that is used for updating.

Here’s a sample of what your SQL settings might look like:

   1: <sqlSettings>
   2:     <sqlOptionInf>
   3:         <option name="OPTION1" description="Read Only Cursor Settings
   4:             <add key="@SQL_CONCURRENCY" value="READ_ONLY" />
   5:             <add key="@SQL_ROW_ARRAY_SIZE" value="1" />
   6:             <add key="@SQL_CURSOR_TYPE" value="FORWARD_ONLY" />
   7:         </option>
   8:         <option name="OPTION2" description="Update cursor settings">
   9:             <add key="@SQL_CONCURRENCY" value="ROWVER" />
  10:             <add key="@SQL_ROW_ARRAY_SIZE" value="1" />
  11:             <add key="@SQL_CURSOR_TYPE" value="DYNAMIC" />
  12:         </option>
  13:     </sqlOptionInf>
  14:     <connectionScope>
  15:         <add key="@SQL_CONNECTION_SCOPE" value="THREAD" />
  16:     </connectionScope>
  17:     <serverList>
  18:         <server name="SERVER1" type="odbc" description="SERVER1">
  19:             <add key="@SQL_DATASRC" value="VidRent" />
  20:             <add key="@SQL_USERID" value="" />
  21:             <add key="@SQL_PASSWORD" value="" />
  22:             <add key="@SQL_ODBC_CURSOR_DEFAULT" value="OPTION1" />
  23:             <add key="@SQL_COMMIT_MODE" value="MANUAL" />
  24:             <add key="@SQL_ODBC_CURSOR_UPDATE" value="OPTION2" />
  25:         </server>
  26:     </serverList>
  27:     <sqlDefaultInf>
  28:         <add key="@SQL_SERVER" value="SERVER1" />
  29:         <add key="@SQL_USERID" value="video" />
  30:         <add key="@SQL_PASSWORD" value="AGMEJJBJKNFPGMAOBPNE" />
  31:     </sqlDefaultInf>
  32: </sqlSettings>

Here is a sample SQL cursor declaration for an updatable cursor:

   1: EXEC SQL DECLARE VIDCUSTBR CURSOR FOR                     
   2:     SELECT                                                   
   3:       VIDEO_NUMBER,
   4:       TITLE,
   5:       MEDIA_TYPE,
   6:       STATUS,
   7:       CUSTOMER_NUMBER,
   8:       DATE_OUT                                     
   9:     FROM                                                     
  10:       VIDMAST                                         
  11:     WHERE                                                    
  13:     FOR UPDATE                                
  14: END-EXEC.

cscript error when running under a non-interactive user session

If you are launching cscript on behalf of a user using a delegated token or by creating a token via LogonUser on a server that the user has never interactively logged on before, you will probably get an error like "Loading your settings failed. (Access is denied)."

This problem is caused by not having the Windows Scripting Host registry settings in the users HKCU registry hive. You can solve this for all users by adding the following keys:

HKEY_USERS\.DEFAULT\Software\Microsoft\Windows Scripting Host\Settings\
HKEY_USERS\.DEFAULT\Software\Microsoft\Windows Script Host\Settings\

You’ll want to make sure that the users also have read access to these keys as well.