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

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 query words: 1.00 VFoxWin

Keywords : FxinteropDbase
Version : 1.00
Platform : winnt
Issue type :