So I wonder if somebody knows why the following is happening:

pnPk = 49
*** This works
SELECT * FROM customers WHERE pk = ?pnPk

*** this doesn't work - 'Missing operand'
update customers set FirstName = 'rick' where pk = ?pnPk

*** Both of these work
update customers set FirstName = 'rick' where pk = 49
update customers set FirstName = 'rick' where pk = pnPk

I realize the ? isn't required in ?pnPK, but it is needed for SQL commands sent via SQL Passthrough so that the values can be parameterized properly.

I know I figured this out at some point in the past, but I can't recall what the rules are for this.

Anybody know?

+++ Rick ---

Hi Rick,
To my recollection, and I checked some of my code to confirm, I do not use, and have never been able to get this to work with the VFP SQL Update statements.

I have always used Blocking Code such as:

IF glRemote  
   *** SQL Backend  
   update customers set FirstName = 'rick' where pk = ?pnPk
ELSE  
   *** VFP Backend  
   update customers set FirstName = 'rick' where pk = pnPk
ENDIF  

As you said, the ?VarName technique does work with VFP SQL SELECTS, but for whatever reason, it doesn't seem to work with the VFP SQL UPDATE statements. Why? Dunno!! I hope someone does know. To me, this seems like a bug with VFP.

Thanks,
Steve

Steve,

The scenario is a business object that uses SQL strings. The same logic works for that, but I just ran into this issue again since I haven't been using FoxPro for data access in a while and ran into this. It's the inconsistency that's so annoying and as you say it sure seems like a bug, but weird that if it was it didn't get fixed. After all SQL Passthrough was in there for a long time before VFP got put to bed...

+++ Rick ---

I don't know if this is apples to apples but I just did a little prg fun and it does both select and update the appropriate records using plain old SQL passthrough. Obviously there are other environmental differences here so what's different about this scenario versus the behavior you are seeing is the crux of the biscuit. I don't have AdventureWorks or Northwind installed in any of my SQL instances so I've obscured some details. Basically the table I'm hitting has an integer primary key and the column I'm updating is text.

ACTIVATE SCREEN  
CLEAR  
ASSERT .f. MESSAGE [test me now?]  
connstring=[Driver={ODBC Driver 17 for SQL Server};server=myserver;uid=myuser;pwd=mypw;database=myDB;Network Library=DBMSSOCN]  
nHandle=SQLSTRINGCONNECT(connstring)  
TEXT TO lcResults TEXTMERGE NOSHOW FLAGS 1+2 PRETEXT 1+2  
handle is <<TRANSFORM(nhandle)>>  
ENDTEXT  
npk=32  
csql=[select * from mytable where pk=?npk]  
csqlupdate=[update mytable set mycolumn='test4' where pk=?npk]  
* grab a record  
SQLEXEC(nhandle,csql)  
TEXT TO lcResults ADDITIVE TEXTMERGE NOSHOW FLAGS 1+2 PRETEXT 1+2  
pk is <<TRANSFORM(pk)>>  
data is <<NVL(mycolumn,[nUlL])>>  
ENDTEXT  
* grab a different record  
npk=22  
SQLEXEC(nhandle,csql)  
TEXT TO lcResults ADDITIVE TEXTMERGE NOSHOW FLAGS 1+2 PRETEXT 1+2  
pk is <<TRANSFORM(pk)>>  
data is <<NVL(mycolumn,[nUlL])>>  
ENDTEXT  
* update some data in the second pk  
SQLEXEC(nhandle,csqlupdate)  
SQLEXEC(nhandle,csql)  
TEXT TO lcResults ADDITIVE TEXTMERGE NOSHOW FLAGS 1+2 PRETEXT 1+2  
pk is <<TRANSFORM(pk)>>  
data is <<NVL(mycolumn,[nUlL])>>  
ENDTEXT  
npk=32  
* update that record  
SQLEXEC(nhandle,csqlupdate)  
SQLEXEC(nhandle,csql)  
TEXT TO lcResults ADDITIVE TEXTMERGE NOSHOW FLAGS 1+2 PRETEXT 1+2  
pk is <<TRANSFORM(pk)>>  
data is <<NVL(mycolumn,[nUlL])>>  
ENDTEXT  
?lcResults  
SQLDISCONNECT(nHandle)  

Richard,

Yes the ?pnPk syntax always works with SQL Passthrough passed as a string. But it doesn't always work FoxPro DAL commands which is the inconsistency.

The use case is that in the wwBusinessObject class I can pass SQL strings that are either evaluated in FoxPro (via a macro) or passed to SQL Passthrough. It would be nice if this was consistent.

As it is you have to separate out the code using conditional blocks as Steve mentioned, for the FoxPro code and SQL code which is a bummer.

That said - it's getting more and more rare to do multi-platform data or at least Fox and some SQL backend code. It's more as a point of reference and especially for those that are migrating from pure Fox data to a SQL backend.

+++ Rick ---

Ah, I missed that. And indeed VFP flags the ? syntax as an error in the command window as you type asn UPDATE statement. For grins I tried this in VFPA and it's not supported there either. Maybe Chen will fix it if someone asks...