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

Last reviewed: July 11, 1996
Article ID: Q152245

The information in this article applies to:

Microsoft Visual FoxPro ODBC Driver, version 1.0

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.

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

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.

From the Edit menu, select Add Value.
Input the following for Key Name and Class:
Key Name: SetNoCountOn
```
   Class:     REG_SZ
```
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

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 11, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.