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:[email protected]] On Behalf Of Paul H.
Tarver
Sent: Thursday, April 19, 2018 2:16 PM
To: [email protected]
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]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
