VFP 9 - MS SQL 2005 Remote View Requery

[asergeev]

Hi,
I posted this following message several days ago at MSDN-VFP forum. Unfortunately I didn't get any responces.

This is my original message from MSDN forum.

I have an interesting puzzle.

I'm using Remote Views in VFP9 for the MS SQL Server database access. I'm trying not to store the connection string within DBC with remote views. When I'm opening remote views I'm supplying a nStatementHandle ("USE a!b CONNSTRING nStatementHandle") from the established shared SQLSTRING connection. Everything works OK till I'm doing REQUERY() for some parameterized views. Program gives me and error: "Base table fields has been changed and no longer match view fields. View field properties cannot be set".

But I didn't not change my basic table!

Trying to solve this "mystery" I have created a test project: ETEST dbc and ETEST program. Etest.dbc contains three parameterized views against SQL2005 sample Database AdventureWorks:

First view: contact_rec_contactid
SELECT Contact.ContactID, Contact.NameStyle, Contact.Title,Contact.FirstName, Contact.MiddleName, Contact.LastName, Contact.Suffix, Contact.EmailAddress, Contact.EmailPromotion, Contact.Phone, Contact.PasswordHash, Contact.PasswordSalt, Contact.AdditionalContactInfo, Contact.rowguid, Contact.ModifiedDate FROM Person.Contact Contact WHERE Contact.ContactID = ( ?parm1 )

Second view: customer_rec_customerid
SELECT Customer.CustomerID, Customer.TerritoryID, Customer.AccountNumber, Customer.CustomerType, Customer.rowguid, Customer.ModifiedDate FROM Sales.Customer Customer WHERE Customer.CustomerID = ( ?parm1 )

Third view: stateprovince_lst_territoryid
SELECT Stateprovince.StateProvinceID, Stateprovince.StateProvinceCode, Stateprovince.CountryRegionCode, Stateprovince.IsOnlyStateProvinceFlag, Stateprovince.Name, Stateprovince.TerritoryID, Stateprovince.rowguid, Stateprovince.ModifiedDate FROM Person.StateProvince Stateprovince WHERE Stateprovince.TerritoryID = ( ?parm1 )

Etest Program:

* Program....: ETEST.PRG
* Author.....: Andrei Sergeev
* Date.......: May 31, 2006
* Notice.....: Copyright (c) Education Systems, Inc., All Rights Reserved.
* Compiler...: Visual FoxPro 9

lncon = "Description=ETEST;DRIVER=SQL Server;SERVER=(local);UID=sa;PWD=sa;APP=ETEST;WSID= ;DATABASE=AdventureWorks"

lerror = .F.

TRY
STORE SQLSTRINGCONNECT(lncon, .T.) TO lnConHandle
CATCH
lerror = .T.
ENDTRY

IF lnConHandle < 0
LOCAL ARRAY laError[1]
AERROR(laError)
MESSAGEBOX("Can not connect to SQL Database. Please verify Database Connection settings.",16,"Error Connection", 5000)
RETURN -1
ENDIF

OPEN DATABASE etest
SET DATABASE TO etest



USE etest!contact_rec_contactid IN 0 SHARED CONNSTRING lnConHandle NODATA

parm1=1345
USE etest!customer_rec_customerid IN 0 SHARED CONNSTRING lnConHandle

parm1=3456
REQUERY('contact_rec_contactid')

USE etest!stateprovince_lst_territoryid IN 0 SHARED CONNSTRING lnConHandle NODATA

REQUERY('customer_rec_customerid')
CLOSE DATABASES
SQLDISCONNECT(lnConHandle )

************************************************

I was getting an error just on the first REQUERY() !

I was also monitoring SQL 2005 using Activity Monitor (for a connection) and SQL Server profiler (for backend calls and activity). Both these tools were setup only for my test application. After I got an error I was very surprised to see that SQL server was trying to requery customer_rec_customerid view statement with parm1=3456 instead of contact_rec_contactid view statement. This is why VFP gives such strange error - SQL server sends back the result set for the different view!

If you try to run a slightly modified code for a DBC with a connection string everything will run fine.

I have a nice desktop snapshot that you can view at

http://www.emaspro.com/Misc/RVrequery.mht.

Important notes:
1. If somebody is going to test the code example, please delete the the connection string from DBC (e.g. DBSETPROP('etest','Connection',Connstring','')) before testing.

2. Parm1 values don't matter, they could be any integers.

Thoughts?

Thanks,
Andrei.