How To Return Multiple Results with HTTP ODBC/VFP ODBC Driver

ID: Q152245

1.00 WINDOWS kbprg kbinterop kbhowto

The information in this article applies to:

SUMMARY

This article describes how to make additions and modifications to Visual FoxPro ODBC driver Registry entries so it is possible to configure the driver not to return row count data from commands such as INSERT, UPDATE, and DELETE, that cause complications when executing multiple SQL commands in an .IDC file.

MORE INFORMATION

Multiple SQL commands can be placed within an .IDC file for execution and each command will get executed. However, when using HTTP ODBC, the .IDC file does not support multiple-result sets. This includes row sets that return a row count such as SQL commands INSERT, UPDATE, and DELETE.

By design, HTTP ODBC only allows you to retrieve one result. Therefore, if the .IDC file contains multiple statements, only the first SQL statement will return a result. For example, if the SQL Statement was: 

   SQLStatement:
   +UPDATE customer SET contact_name="Joe" WHERE customer_id="ALFKI" ;
    SELECT contact_name FROM CUSTOMER
the UPDATE command would execute and return a row count and the SQL SELECT command would never return a result.

By adding a registry entry, the Visual FoxPro ODBC driver will not ignore the values returned from commands such as UPDATE, INSERT, and DELETE, and only return values for the SQL Select command.

How to Make Registry Entries

WARNING: Be extremely careful when editing registry information. Mistakes could cause serious system problems.

1. Run REGEDT32.EXE, which is in your Windows\System32 directory.

2. Select: 

   HKEY_LOCAL_MACHINE
     SOFTWARE
       ODBC
         ODBC.INI
           <<Data Source Name>>

   where <<Data Source Name>> is the name of the data source you have set
   up for HTTP ODBC. Also note that this may be under HKEY_CURRENT_USER
   instead of under HKEY_LOCAL_MACHINE, depending on your configuration. If
   you have multiple users logging onto one machine, then the entry will be
   HKEY_CURRENT_USER.
3. From the Edit menu, select Add Value.

4. Input the following for Key Name and Class: 

   Key Name:  SetNoCountOn
   Class:     REG_SZ
5. For the String, input "Yes" without the quotation marks.

Now you will be able to execute multiple SQL commands as requested. For example: 

   SQLStatement:
   +UPDATE customer SET contact_name="Joe" WHERE customer_id="ALFKI" ;
    SELECT contact_name FROM CUSTOMER
will now execute and return the results of the SQL Select command.

Additional reference words: 1.00 VFoxWin KBCategory: kbprg kbinterop kbhowto KBSubcategory: FxinteropDbase 

Keywords          : FxinteropDbase 
Version           : 1.00
Platform          : winnt

Last Reviewed: May 1, 1998