I posted a longer version of this situation previously, but it may have been mis-titled or included too much detail because I've gotten no responses.
Here's the low down and dirty version:
When you use INSERT INTO.SELECT from a remote data source that includes fieldnames that begin with numbers in the development environment, VFP9 automatically changes the leading digit in the field name to an underscore ('_'). However, when executing the same command as a compiled executable this automatic substitution does not occur and VFP will allow fieldnames with a leading digit to be returned in the resultant cursor. This causes major problems later if you use the remote data cursor to create a new cursor because Foxpro won't allow field names with a number in the first position. ALTER TABLE cannot be used on the remote data cursor and even if you make the cursor READWRITE, the length of the fieldnames can often exceed 10 characters.
My question is this: Has anyone here run into this issue and if so, Is there any another way or procedure around this situation that would allow me to manually fix the fieldnames which start with a number assuming I have no way to change the source data.
Thanks in advance.
Paul
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
Can you rename the field in your SELECT like this:
SELECT BadFieldName AS GoodFieldName
?
Frank.
Frank Cazabon
On 19/04/2018 09:38 AM, Paul H. Tarver wrote:
I posted a longer version of this situation previously, but it may have been mis-titled or included too much detail because I've gotten no responses.
Here's the low down and dirty version:
When you use INSERT INTO.SELECT from a remote data source that includes fieldnames that begin with numbers in the development environment, VFP9 automatically changes the leading digit in the field name to an underscore ('_'). However, when executing the same command as a compiled executable this automatic substitution does not occur and VFP will allow fieldnames with a leading digit to be returned in the resultant cursor. This causes major problems later if you use the remote data cursor to create a new cursor because Foxpro won't allow field names with a number in the first position. ALTER TABLE cannot be used on the remote data cursor and even if you make the cursor READWRITE, the length of the fieldnames can often exceed 10 characters.
My question is this: Has anyone here run into this issue and if so, Is there any another way or procedure around this situation that would allow me to manually fix the fieldnames which start with a number assuming I have no way to change the source data.
Thanks in advance.
Paul
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
I could, but that would require expanding the field name definitions out in advance of calling the routine which would be very difficult because at the time the command is issued, the program may or may not know all of the fields in the import file.
This code comes from the AppendXLSX.prg and there are often times when importing an Excel file, the main program won't know the full context of the Excel file to imported.
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Frank Cazabon Sent: Thursday, April 19, 2018 8:52 AM To: profoxtech@leafe.com Subject: Re: INSERT INTO...SELECT Issue
Can you rename the field in your SELECT like this:
SELECT BadFieldName AS GoodFieldName
?
Frank.
Frank Cazabon
On 19/04/2018 09:38 AM, Paul H. Tarver wrote:
I posted a longer version of this situation previously, but it may have
been
mis-titled or included too much detail because I've gotten no responses.
Here's the low down and dirty version:
When you use INSERT INTO.SELECT from a remote data source that includes fieldnames that begin with numbers in the development environment, VFP9 automatically changes the leading digit in the field name to an underscore ('_'). However, when executing the same command as a compiled executable this automatic substitution does not occur and VFP will allow fieldnames with a leading digit to be returned in the resultant cursor. This causes major problems later if you use the remote data cursor to create a new cursor because Foxpro won't allow field names with a number in the first position. ALTER TABLE cannot be used on the remote data cursor and even if you make the cursor READWRITE, the length of the fieldnames can often
exceed
10 characters.
My question is this: Has anyone here run into this issue and if so, Is
there
any another way or procedure around this situation that would allow me to manually fix the fieldnames which start with a number assuming I have no
way
to change the source data.
Thanks in advance.
Paul
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
If you have the source code to this program, can you change the processing that builds up lcNvlFieldList to check for bad characters?
On 19 April 2018 11:42:55 GMT-04:00, "Paul H. Tarver" paul@tpcqpc.com wrote:
I could, but that would require expanding the field name definitions out in advance of calling the routine which would be very difficult because at the time the command is issued, the program may or may not know all of the fields in the import file.
This code comes from the AppendXLSX.prg and there are often times when importing an Excel file, the main program won't know the full context of the Excel file to imported.
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Frank Cazabon Sent: Thursday, April 19, 2018 8:52 AM To: profoxtech@leafe.com Subject: Re: INSERT INTO...SELECT Issue
Can you rename the field in your SELECT like this:
SELECT BadFieldName AS GoodFieldName
?
Frank.
Frank Cazabon
On 19/04/2018 09:38 AM, Paul H. Tarver wrote:
I posted a longer version of this situation previously, but it may
have been
mis-titled or included too much detail because I've gotten no
responses.
Here's the low down and dirty version:
When you use INSERT INTO.SELECT from a remote data source that
includes
fieldnames that begin with numbers in the development environment,
VFP9
automatically changes the leading digit in the field name to an
underscore
('_'). However, when executing the same command as a compiled
executable
this automatic substitution does not occur and VFP will allow
fieldnames
with a leading digit to be returned in the resultant cursor. This
causes
major problems later if you use the remote data cursor to create a
new
cursor because Foxpro won't allow field names with a number in the
first
position. ALTER TABLE cannot be used on the remote data cursor and
even if
you make the cursor READWRITE, the length of the fieldnames can often
exceed
10 characters.
My question is this: Has anyone here run into this issue and if so,
Is there
any another way or procedure around this situation that would allow
me to
manually fix the fieldnames which start with a number assuming I have
no way
to change the source data.
Thanks in advance.
Paul
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
I've been giving that some thought and I'm leaning toward something like this as a pre-processor to overwrite the default Foxpro functionality:
SQLEXEC(m.lnSQL,[SELECT ] + m.tcExcelFieldList + [ FROM "] + m.tcSheet + [" Where ] + m.tcExcelWhereExpr, m.lcSQLAlias) AFIELDS( laArrayName, m.lcSQLAlias) ....Loop through fields to fix bad fieldnames CREATE CURSOR FROM ARRAY laArrayName APPEND FROM DBF(m.lcSQLAlias) ...and so on
I did take a few minutes this morning to test whether AFIELDS would blow up and it does not! That means the logic I've outlined here might work.
Once I get a few more minutes I'll see if I can flesh out this line of logic further. Suggestions are appreciated.
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Frank Cazabon Sent: Thursday, April 19, 2018 11:01 AM To: profoxtech@leafe.com Subject: RE: INSERT INTO...SELECT Issue
If you have the source code to this program, can you change the processing that builds up lcNvlFieldList to check for bad characters?
On 19 April 2018 11:42:55 GMT-04:00, "Paul H. Tarver" paul@tpcqpc.com wrote:
I could, but that would require expanding the field name definitions out in advance of calling the routine which would be very difficult because at the time the command is issued, the program may or may not know all of the fields in the import file.
This code comes from the AppendXLSX.prg and there are often times when importing an Excel file, the main program won't know the full context of the Excel file to imported.
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Frank Cazabon Sent: Thursday, April 19, 2018 8:52 AM To: profoxtech@leafe.com Subject: Re: INSERT INTO...SELECT Issue
Can you rename the field in your SELECT like this:
SELECT BadFieldName AS GoodFieldName
?
Frank.
Frank Cazabon
On 19/04/2018 09:38 AM, Paul H. Tarver wrote:
I posted a longer version of this situation previously, but it may
have been
mis-titled or included too much detail because I've gotten no
responses.
Here's the low down and dirty version:
When you use INSERT INTO.SELECT from a remote data source that
includes
fieldnames that begin with numbers in the development environment,
VFP9
automatically changes the leading digit in the field name to an
underscore
('_'). However, when executing the same command as a compiled
executable
this automatic substitution does not occur and VFP will allow
fieldnames
with a leading digit to be returned in the resultant cursor. This
causes
major problems later if you use the remote data cursor to create a
new
cursor because Foxpro won't allow field names with a number in the
first
position. ALTER TABLE cannot be used on the remote data cursor and
even if
you make the cursor READWRITE, the length of the fieldnames can often
exceed
10 characters.
My question is this: Has anyone here run into this issue and if so,
Is there
any another way or procedure around this situation that would allow
me to
manually fix the fieldnames which start with a number assuming I have
no way
to change the source data.
Thanks in advance.
Paul
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
That looks like it should work. Are you stuck on something specific?
Frank.
Frank Cazabon
On 19/04/2018 12:49 PM, Paul H. Tarver wrote:
I've been giving that some thought and I'm leaning toward something like this as a pre-processor to overwrite the default Foxpro functionality:
SQLEXEC(m.lnSQL,[SELECT ] + m.tcExcelFieldList + [ FROM "] + m.tcSheet + [" Where ] + m.tcExcelWhereExpr, m.lcSQLAlias) AFIELDS( laArrayName, m.lcSQLAlias) ....Loop through fields to fix bad fieldnames CREATE CURSOR FROM ARRAY laArrayName APPEND FROM DBF(m.lcSQLAlias) ...and so on
I did take a few minutes this morning to test whether AFIELDS would blow up and it does not! That means the logic I've outlined here might work.
Once I get a few more minutes I'll see if I can flesh out this line of logic further. Suggestions are appreciated.
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Frank Cazabon Sent: Thursday, April 19, 2018 11:01 AM To: profoxtech@leafe.com Subject: RE: INSERT INTO...SELECT Issue
If you have the source code to this program, can you change the processing that builds up lcNvlFieldList to check for bad characters?
On 19 April 2018 11:42:55 GMT-04:00, "Paul H. Tarver" paul@tpcqpc.com wrote:
I could, but that would require expanding the field name definitions out in advance of calling the routine which would be very difficult because at the time the command is issued, the program may or may not know all of the fields in the import file.
This code comes from the AppendXLSX.prg and there are often times when importing an Excel file, the main program won't know the full context of the Excel file to imported.
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Frank Cazabon Sent: Thursday, April 19, 2018 8:52 AM To: profoxtech@leafe.com Subject: Re: INSERT INTO...SELECT Issue
Can you rename the field in your SELECT like this:
SELECT BadFieldName AS GoodFieldName
?
Frank.
Frank Cazabon
On 19/04/2018 09:38 AM, Paul H. Tarver wrote:
I posted a longer version of this situation previously, but it may
have been
mis-titled or included too much detail because I've gotten no
responses.
Here's the low down and dirty version:
When you use INSERT INTO.SELECT from a remote data source that
includes
fieldnames that begin with numbers in the development environment,
VFP9
automatically changes the leading digit in the field name to an
underscore
('_'). However, when executing the same command as a compiled
executable
this automatic substitution does not occur and VFP will allow
fieldnames
with a leading digit to be returned in the resultant cursor. This
causes
major problems later if you use the remote data cursor to create a
new
cursor because Foxpro won't allow field names with a number in the
first
position. ALTER TABLE cannot be used on the remote data cursor and
even if
you make the cursor READWRITE, the length of the fieldnames can often
exceed
10 characters.
My question is this: Has anyone here run into this issue and if so,
Is there
any another way or procedure around this situation that would allow
me to
manually fix the fieldnames which start with a number assuming I have
no way
to change the source data.
Thanks in advance.
Paul
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
On 19/04/2018 14:38, Paul H. Tarver wrote:
<snip>
My question is this: Has anyone here run into this issue and if so, Is there any another way or procedure around this situation that would allow me to manually fix the fieldnames which start with a number assuming I have no way to change the source data.
Not run into that issue but just want to check what actually happens, as not quite sure from your description. The client gives you a spreadsheet file. Are you then appending that into a cursor and trying to select from the cursor into some other table?
Peter
,"This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office: 101 St. Martin's Lane,London, WC2N 4AZ Tel:0207 299 7960
I posted a full detailed description of the process and all the testing I've done so far in this thread:
https://leafe.com/archives/msg/510840
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Peter Cushing Sent: Thursday, April 19, 2018 8:53 AM To: profoxtech@leafe.com Subject: Re: INSERT INTO...SELECT Issue
On 19/04/2018 14:38, Paul H. Tarver wrote:
<snip>
My question is this: Has anyone here run into this issue and if so, Is there any another way or procedure around this situation that would allow me to manually fix the fieldnames which start with a number assuming I have no way to change the source data.
Not run into that issue but just want to check what actually happens, as not quite sure from your description. The client gives you a spreadsheet file. Are you then appending that into a cursor and trying to select from the cursor into some other table?
Peter
,"This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office: 101 St. Martin's Lane,London, WC2N 4AZ Tel:0207 299 7960
[excessive quoting removed by server]
Hi Paul:
The problem is that Visual FoxPro explicitly says in documentation that field names must start with a letter or underscore, so if in some rare situation VFP respects a field name starting with a number it's really a bug, and that's why this do not work allways.
Here is the spec on VFP name creation: https://docs.microsoft.com/en-us/previous-versions/visualstudio/foxpro/d7aa5...)
El jue., 19 abr. 2018 17:45, Paul H. Tarver paul@tpcqpc.com escribió:
I posted a full detailed description of the process and all the testing I've done so far in this thread:
https://leafe.com/archives/msg/510840
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Peter Cushing Sent: Thursday, April 19, 2018 8:53 AM To: profoxtech@leafe.com Subject: Re: INSERT INTO...SELECT Issue
On 19/04/2018 14:38, Paul H. Tarver wrote:
<snip>
My question is this: Has anyone here run into this issue and if so, Is
there
any another way or procedure around this situation that would allow me to manually fix the fieldnames which start with a number assuming I have no
way
to change the source data.
Not run into that issue but just want to check what actually happens, as not quite sure from your description. The client gives you a spreadsheet file. Are you then appending that into a cursor and trying to select from the cursor into some other table?
Peter
,"This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office: 101 St. Martin's Lane,London, WC2N 4AZ Tel:0207 299 7960
[excessive quoting removed by server]
Yes, I understand. But the "bug" comes in to play because apparently Foxpro allows you to recreate cursors of REMOTE DATA that include fields that start with numbers. I'm guessing that is to remain compatible with SQL. However, the conflict or bug occurs if you try to then use that remote data cursor to create a new cursor that complies with Foxpro's rule to not support fieldnames that start with a number.
If there is any bug here, I think it is that Foxpro handles this situation perfectly in the development environment, but fails to adjust for this in the runtime.
Otherwise, I think it is more of a specification conflict between SQL and Foxpro.
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Fernando D. Bozzo Sent: Thursday, April 19, 2018 11:54 AM To: profoxtech@leafe.com Subject: Re: INSERT INTO...SELECT Issue
Hi Paul:
The problem is that Visual FoxPro explicitly says in documentation that field names must start with a letter or underscore, so if in some rare situation VFP respects a field name starting with a number it's really a bug, and that's why this do not work allways.
Here is the spec on VFP name creation: https://docs.microsoft.com/en-us/previous-versions/visualstudio/foxpro/d7aa5...)
El jue., 19 abr. 2018 17:45, Paul H. Tarver paul@tpcqpc.com escribió:
I posted a full detailed description of the process and all the testing I've done so far in this thread:
https://leafe.com/archives/msg/510840
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Peter Cushing Sent: Thursday, April 19, 2018 8:53 AM To: profoxtech@leafe.com Subject: Re: INSERT INTO...SELECT Issue
On 19/04/2018 14:38, Paul H. Tarver wrote:
<snip>
My question is this: Has anyone here run into this issue and if so, Is
there
any another way or procedure around this situation that would allow me to manually fix the fieldnames which start with a number assuming I have no
way
to change the source data.
Not run into that issue but just want to check what actually happens, as not quite sure from your description. The client gives you a spreadsheet file. Are you then appending that into a cursor and trying to select from the cursor into some other table?
Peter
,"This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office: 101 St. Martin's Lane,London, WC2N 4AZ Tel:0207 299 7960
[excessive quoting removed by server]
I may have mis-spoke about this being a SQL Spec, but I found this document from Microsoft that states:
"Column names can contain any valid characters (for example, spaces). If column names contain any characters except letters, numbers, and underscores, the name must be delimited by enclosing it in back quotes (`). When the Microsoft Access or Microsoft Excel driver is used, column names are limited to 64 characters, and longer names generate an error. When the Paradox driver is used, the maximum column name is 25 characters. When the Text driver is used, the maximum column name is 64 characters, and longer names are truncated. When the dBASE driver is used, characters with an ASCII value greater than 127 are converted to underscores. When the dBASE driver is used, characters with an ASCII value greater than 127 are converted to underscores. "
So maybe this is an Excel ODBC Driver specification that is conflicting. Either way, the last sentence seems to mean that the conversion to an underscore only takes place within the ODBC driver for dBASE and not the Excel ODBC Driver. I take that as confirmation that the conversion to an underscore I'm seeing is driven by Foxpro not by the ODBC driver because if it was the ODBC driver doing the substitution, it wouldn't even know if I were calling it from a development platform or a compiled runtime since it is by definition: remote.
My understanding of the why is growing clearer, but my working around is still elusive at this point.
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Paul H. Tarver Sent: Thursday, April 19, 2018 1:17 PM To: profoxtech@leafe.com Subject: RE: INSERT INTO...SELECT Issue
Yes, I understand. But the "bug" comes in to play because apparently Foxpro allows you to recreate cursors of REMOTE DATA that include fields that start with numbers. I'm guessing that is to remain compatible with SQL. However, the conflict or bug occurs if you try to then use that remote data cursor to create a new cursor that complies with Foxpro's rule to not support fieldnames that start with a number.
If there is any bug here, I think it is that Foxpro handles this situation perfectly in the development environment, but fails to adjust for this in the runtime.
Otherwise, I think it is more of a specification conflict between SQL and Foxpro.
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Fernando D. Bozzo Sent: Thursday, April 19, 2018 11:54 AM To: profoxtech@leafe.com Subject: Re: INSERT INTO...SELECT Issue
Hi Paul:
The problem is that Visual FoxPro explicitly says in documentation that field names must start with a letter or underscore, so if in some rare situation VFP respects a field name starting with a number it's really a bug, and that's why this do not work allways.
Here is the spec on VFP name creation: https://docs.microsoft.com/en-us/previous-versions/visualstudio/foxpro/d7aa5...)
El jue., 19 abr. 2018 17:45, Paul H. Tarver paul@tpcqpc.com escribió:
I posted a full detailed description of the process and all the testing I've done so far in this thread:
https://leafe.com/archives/msg/510840
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Peter Cushing Sent: Thursday, April 19, 2018 8:53 AM To: profoxtech@leafe.com Subject: Re: INSERT INTO...SELECT Issue
On 19/04/2018 14:38, Paul H. Tarver wrote:
<snip>
My question is this: Has anyone here run into this issue and if so, Is
there
any another way or procedure around this situation that would allow me to manually fix the fieldnames which start with a number assuming I have no
way
to change the source data.
Not run into that issue but just want to check what actually happens, as not quite sure from your description. The client gives you a spreadsheet file. Are you then appending that into a cursor and trying to select from the cursor into some other table?
Peter
,"This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office: 101 St. Martin's Lane,London, WC2N 4AZ Tel:0207 299 7960
[excessive quoting removed by server]
On 04/19/18 9:38 AM, Paul H. Tarver wrote:
My question is this: Has anyone here run into this issue and if so, Is there any another way or procedure around this situation that would allow me to manually fix the fieldnames which start with a number assuming I have no way to change the source data.
HUGE CAVEAT: *IF* I am understanding your question, *AND IF* I'm remembering correctly, (I don't have time to play with it) I think you can do something like:
Select "2Bad" as Mybad from goofydata.
Enclose the bad name in quotes.
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
On Thu, Apr 19, 2018 at 9:38 AM, Paul H. Tarver paul@tpcqpc.com wrote:
I posted a longer version of this situation previously, but it may have been mis-titled or included too much detail because I've gotten no responses. ...
My question is this: Has anyone here run into this issue and if so, Is there any another way or procedure around this situation that would allow me to manually fix the fieldnames which start with a number assuming I have no way to change the source data.
No, I think you've found one of those genuine "BUGS" in Visual FoxPro, rare as hen's teeth.
I think you need to create another step in your process, where you query the bad source, review the column names, and create a set of acceptable aliases, then build a query that uses those aliases rather than the underlying (and sometimes bad) remote column names.
If you were working with the remote data directly, SQLColumns() can return a list of columns, as of course can an empty cursor "SELECT *
From TargetTable WHERE 1=0" But since you're creating the cursor in
more-or-less of a black box, run AFIELDS() on the resulting cursor. Then, make a plays-well-with-others cursor for you to use from there. Or you could build the logic directly into the INSERT INTO... SELECT statement, though it may get long and unwieldy.
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, ...
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]
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]
On Thu, Apr 19, 2018 at 4:26 PM, Paul H. Tarver paul@tpcqpc.com wrote:
Unfortunately, now the ODBC Driver is throwing an error:
"[Microsoft][ODBC Excel Driver] Syntax error (missing operator) inquery expression '3rdPartySickPay'."
Unsurprisingly, ODBC thinks it's a dumb name for a field, too.
You'll probably need to "escape" it. I'm decades out of ODBC, so I don't recall if ODBC wants you to enclose the field name in single quotes, square brackets or back ticks (that `` funny key in the upper left corner no one ever uses). A web search might yield a suggestion, or you can just try the Usual Suspects...
Something like this: https://stackoverflow.com/questions/2901453/sql-standard-to-escape-column-na...
On Thu, Apr 19, 2018 at 4:32 PM, Ted Roche tedroche@gmail.com wrote:
You'll probably need to "escape" it. I'm decades out of ODBC, so I don't recall if ODBC wants you to enclose the field name in single quotes, square brackets or back ticks (that `` funny key in the upper left corner no one ever uses). A web search might yield a suggestion, or you can just try the Usual Suspects...
2018-04-19 21:15 GMT+02:00 Paul H. Tarver paul@tpcqpc.com:
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!
I've found other bugs, in example: the collection object support ordering, but doesn't work if you use the FOXOBJECT keyword :-(
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
-
Fernando D. Bozzo -
Frank Cazabon -
Paul H. Tarver -
Peter Cushing -
Ted Roche -
Vince Teachout