No joy in Mudville.
My original logic failed because a "local" Foxpro Select will not accept the leading digit on any field name.
Therefore, I took Ted's advice, captured the column headers using the NATIVE feature of the SQLColumns() function, tested the fields and created a field list with aliases.
Unfortunately, now the ODBC Driver is throwing an error:
"[Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression '3rdPartySickPay'."
The relevant field with alias looks like this:
"...,3rdPartySickPay AS xrdPartySickPay,..."
So, now I'm wondering if the bug is even deeper because it looks like the ODBC driver is rejecting the request if I specify the fields rather than just use the all-inclusive '*' wildcard.
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Paul H. Tarver Sent: Thursday, April 19, 2018 2:16 PM To: profoxtech@leafe.com Subject: RE: INSERT INTO...SELECT Issue
Ted:
No, I think you've found one of those genuine "BUGS" in Visual FoxPro, rare as hen's teeth.
It's one of the only ones I've ever encountered!
Or you could build the logic directly into the INSERT INTO... SELECT statement, though it may get long and unwieldy.
This is what I wanted to avoid!
Scan the result of AFIELDS() and built a set of ColumnName-AliasName field, testing for the bad situations you've identified (integer first character, excessive length, and also duplication, since you're truncating) and then build the field list using textmerge or string concatenation as:
SELECT ColumnName as AliasName, ColumnName as AliasName, ColumnName as AliasName, ...
I think you hit on exactly what I was looking for because this will easily and quickly transfer the data from the remote cursor to the new fieldname list that I can use to create a brand new compliant cursor.
I think this new logic will work for any situation and provide a nice place to hook in other fixes later if they are needed.
SQLEXEC(m.lnSQL,[SELECT ] + m.tcExcelFieldList + [ FROM "] + m.tcSheet + ["Where ] + m.tcExcelWhereExpr, m.lcSQLAlias) AFIELDS( laArrayName, m.lcSQLAlias) FOR...NEXT through laArrayName to create lcCorrectedFieldList SELECT &lcCorrectedFieldList From m.lcSQLAlias into cursor m.lcNewSQLAlias USE (m.lcSQLAlias) m.lcSQLAlias = m.lcNewSQLAlias
Paul
[excessive quoting removed by server]