Discussion:
INSERT INTO...SELECT Issue
Paul H. Tarver
2018-04-19 13:38:21 UTC
Permalink
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
---

_______________________________________________
Post Messages to: ***@leafe.com
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/082601d3d7e3$aec96aa0$0c5c3fe0$@tpcqpc.com
** 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.
Frank Cazabon
2018-04-19 13:51:40 UTC
Permalink
Can you rename the field in your SELECT like this:

SELECT BadFieldName AS GoodFieldName

?

Frank.

Frank Cazabon
Post by Paul H. Tarver
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.
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]

_______________________________________________
Post Messages to: ***@leafe.com
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/b17e49a0-e2da-6733-74f2-***@gmail.com
** 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.
Paul H. Tarver
2018-04-19 15:42:55 UTC
Permalink
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-***@leafe.com] On Behalf Of Frank
Cazabon
Sent: Thursday, April 19, 2018 8:52 AM
To: ***@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
Post by Paul H. Tarver
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.
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]

_______________________________________________
Post Messages to: ***@leafe.com
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/08ce01d3d7f5$15978fd0$40c6af70$@tpcqpc.com
** 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.
Frank Cazabon
2018-04-19 16:00:40 UTC
Permalink
If you have the source code to this program, can you change the processing that builds up lcNvlFieldList to check for bad characters?
Post by Paul H. Tarver
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-----
Cazabon
Sent: Thursday, April 19, 2018 8:52 AM
Subject: Re: INSERT INTO...SELECT Issue
SELECT BadFieldName AS GoodFieldName
?
Frank.
Frank Cazabon
Post by Paul H. Tarver
I posted a longer version of this situation previously, but it may
have
been
Post by Paul H. Tarver
mis-titled or included too much detail because I've gotten no
responses.
Post by Paul H. Tarver
When you use INSERT INTO.SELECT from a remote data source that
includes
Post by Paul H. Tarver
fieldnames that begin with numbers in the development environment,
VFP9
Post by Paul H. Tarver
automatically changes the leading digit in the field name to an
underscore
Post by Paul H. Tarver
('_'). However, when executing the same command as a compiled
executable
Post by Paul H. Tarver
this automatic substitution does not occur and VFP will allow
fieldnames
Post by Paul H. Tarver
with a leading digit to be returned in the resultant cursor. This
causes
Post by Paul H. Tarver
major problems later if you use the remote data cursor to create a
new
Post by Paul H. Tarver
cursor because Foxpro won't allow field names with a number in the
first
Post by Paul H. Tarver
position. ALTER TABLE cannot be used on the remote data cursor and
even if
Post by Paul H. Tarver
you make the cursor READWRITE, the length of the fieldnames can often
exceed
Post by Paul H. Tarver
10 characters.
My question is this: Has anyone here run into this issue and if so,
Is
there
Post by Paul H. Tarver
any another way or procedure around this situation that would allow
me to
Post by Paul H. Tarver
manually fix the fieldnames which start with a number assuming I have
no
way
Post by Paul H. Tarver
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]

_______________________________________________
Post Messages to: ***@leafe.com
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/BCFD5B3E-71C6-462C-8E81-***@gmail.com
** 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.
Paul H. Tarver
2018-04-19 16:49:21 UTC
Permalink
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-***@leafe.com] On Behalf Of Frank
Cazabon
Sent: Thursday, April 19, 2018 11:01 AM
To: ***@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?
Post by Paul H. Tarver
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-----
Cazabon
Sent: Thursday, April 19, 2018 8:52 AM
Subject: Re: INSERT INTO...SELECT Issue
SELECT BadFieldName AS GoodFieldName
?
Frank.
Frank Cazabon
Post by Paul H. Tarver
I posted a longer version of this situation previously, but it may
have
been
Post by Paul H. Tarver
mis-titled or included too much detail because I've gotten no
responses.
Post by Paul H. Tarver
When you use INSERT INTO.SELECT from a remote data source that
includes
Post by Paul H. Tarver
fieldnames that begin with numbers in the development environment,
VFP9
Post by Paul H. Tarver
automatically changes the leading digit in the field name to an
underscore
Post by Paul H. Tarver
('_'). However, when executing the same command as a compiled
executable
Post by Paul H. Tarver
this automatic substitution does not occur and VFP will allow
fieldnames
Post by Paul H. Tarver
with a leading digit to be returned in the resultant cursor. This
causes
Post by Paul H. Tarver
major problems later if you use the remote data cursor to create a
new
Post by Paul H. Tarver
cursor because Foxpro won't allow field names with a number in the
first
Post by Paul H. Tarver
position. ALTER TABLE cannot be used on the remote data cursor and
even if
Post by Paul H. Tarver
you make the cursor READWRITE, the length of the fieldnames can often
exceed
Post by Paul H. Tarver
10 characters.
My question is this: Has anyone here run into this issue and if so,
Is
there
Post by Paul H. Tarver
any another way or procedure around this situation that would allow
me to
Post by Paul H. Tarver
manually fix the fieldnames which start with a number assuming I have
no
way
Post by Paul H. Tarver
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]

_______________________________________________
Post Messages to: ***@leafe.com
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/09d401d3d7fe$5df7c3e0$19e74ba0$@tpcqpc.com
** 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.
Frank Cazabon
2018-04-19 18:46:56 UTC
Permalink
That looks like it should work. Are you stuck on something specific?

Frank.

Frank Cazabon
Post by Paul H. Tarver
I've been giving that some thought and I'm leaning toward something like
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-----
Cazabon
Sent: Thursday, April 19, 2018 11:01 AM
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?
Post by Paul H. Tarver
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-----
Cazabon
Sent: Thursday, April 19, 2018 8:52 AM
Subject: Re: INSERT INTO...SELECT Issue
SELECT BadFieldName AS GoodFieldName
?
Frank.
Frank Cazabon
Post by Paul H. Tarver
I posted a longer version of this situation previously, but it may
have
been
Post by Paul H. Tarver
mis-titled or included too much detail because I've gotten no
responses.
Post by Paul H. Tarver
When you use INSERT INTO.SELECT from a remote data source that
includes
Post by Paul H. Tarver
fieldnames that begin with numbers in the development environment,
VFP9
Post by Paul H. Tarver
automatically changes the leading digit in the field name to an
underscore
Post by Paul H. Tarver
('_'). However, when executing the same command as a compiled
executable
Post by Paul H. Tarver
this automatic substitution does not occur and VFP will allow
fieldnames
Post by Paul H. Tarver
with a leading digit to be returned in the resultant cursor. This
causes
Post by Paul H. Tarver
major problems later if you use the remote data cursor to create a
new
Post by Paul H. Tarver
cursor because Foxpro won't allow field names with a number in the
first
Post by Paul H. Tarver
position. ALTER TABLE cannot be used on the remote data cursor and
even if
Post by Paul H. Tarver
you make the cursor READWRITE, the length of the fieldnames can often
exceed
Post by Paul H. Tarver
10 characters.
My question is this: Has anyone here run into this issue and if so,
Is
there
Post by Paul H. Tarver
any another way or procedure around this situation that would allow
me to
Post by Paul H. Tarver
manually fix the fieldnames which start with a number assuming I have
no
way
Post by Paul H. Tarver
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]

_______________________________________________
Post Messages to: ***@leafe.com
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/4a082202-d013-5745-df2b-***@gmail.com
** 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.
Peter Cushing
2018-04-19 13:53:30 UTC
Permalink
<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




_______________________________________________
Post Messages to: ***@leafe.com
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/3a8d81b1-3c03-c57f-3e15-***@whisperingsmith.com
** 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
Paul H. Tarver
2018-04-19 15:45:13 UTC
Permalink
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-***@leafe.com] On Behalf Of Peter Cushing
Sent: Thursday, April 19, 2018 8:53 AM
To: ***@leafe.com
Subject: Re: INSERT INTO...SELECT Issue
<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]

_______________________________________________
Post Messages to: ***@leafe.com
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/08d501d3d7f5$67d359a0$377a0ce0$@tpcqpc.com
** 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.
Fernando D. Bozzo
2018-04-19 16:54:14 UTC
Permalink
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/d7aa568a(v%3dvs.71)
Post by Paul H. Tarver
I posted a full detailed description of the process and all the testing
https://leafe.com/archives/msg/510840
Paul
-----Original Message-----
Sent: Thursday, April 19, 2018 8:53 AM
Subject: Re: INSERT INTO...SELECT Issue
<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]

_______________________________________________
Post Messages to: ***@leafe.com
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/CAGQ_Juky6x4P2OHnq6++YYJ6Xqsxka-***@mail.gmail.com
** 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 thos
Paul H. Tarver
2018-04-19 18:17:13 UTC
Permalink
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-***@leafe.com] On Behalf Of Fernando D. Bozzo
Sent: Thursday, April 19, 2018 11:54 AM
To: ***@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/d7aa568a(v%3dvs.71)
Post by Paul H. Tarver
I posted a full detailed description of the process and all the testing
https://leafe.com/archives/msg/510840
Paul
-----Original Message-----
Sent: Thursday, April 19, 2018 8:53 AM
Subject: Re: INSERT INTO...SELECT Issue
<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]

_______________________________________________
Post Messages to: ***@leafe.com
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/0be201d3d80a$a42001f0$ec6005d0$@tpcqpc.com
** 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 fo
Paul H. Tarver
2018-04-19 18:34:57 UTC
Permalink
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-***@leafe.com] On Behalf Of Paul H. Tarver
Sent: Thursday, April 19, 2018 1:17 PM
To: ***@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-***@leafe.com] On Behalf Of Fernando D. Bozzo
Sent: Thursday, April 19, 2018 11:54 AM
To: ***@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/d7aa568a(v%3dvs.71)
Post by Paul H. Tarver
I posted a full detailed description of the process and all the testing
https://leafe.com/archives/msg/510840
Paul
-----Original Message-----
Sent: Thursday, April 19, 2018 8:53 AM
Subject: Re: INSERT INTO...SELECT Issue
<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]

_______________________________________________
Post Messages to: ***@leafe.com
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/0c8101d3d80d$1ea2da40$5be88ec0$@tpcqpc.com
** 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 se
Ted Roche
2018-04-19 18:43:10 UTC
Permalink
Post by Paul H. Tarver
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 *
Post by Paul H. Tarver
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 Roche
Ted Roche & Associates, LLC
http://www.tedroche.com

_______________________________________________
Post Messages to: ***@leafe.com
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/CACW6n4vOfeaj7e6sMgKTZ1b1Y-***@mail.gmail.com
** 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.
Paul H. Tarver
2018-04-19 19:15:54 UTC
Permalink
Post by Ted Roche
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!
Post by Ted Roche
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!
Post by Ted Roche
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
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: ***@leafe.com
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/0d5801d3d812$d6911680$83b34380$@tpcqpc.com
** 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.
Paul H. Tarver
2018-04-19 20:26:45 UTC
Permalink
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-***@leafe.com] On Behalf Of Paul H.
Tarver
Sent: Thursday, April 19, 2018 2:16 PM
To: ***@leafe.com
Subject: RE: INSERT INTO...SELECT Issue
Post by Ted Roche
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!
Post by Ted Roche
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!
Post by Ted Roche
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
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: ***@leafe.com
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/0f9301d3d81c$bc716bb0$35544310$@tpcqpc.com
** 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.
Ted Roche
2018-04-19 20:32:24 UTC
Permalink
Post by Paul H. Tarver
"[Microsoft][ODBC Excel Driver] Syntax error (missing operator) in
query 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...
--
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com

_______________________________________________
Post Messages to: ***@leafe.com
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/CACW6n4tX4Y1c_2syMc6c_Ydcv10p0L2+***@mail.gmail.com
** 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.
Ted Roche
2018-04-19 20:34:21 UTC
Permalink
Something like this:
https://stackoverflow.com/questions/2901453/sql-standard-to-escape-column-names#2901499
Post by Ted Roche
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...
--
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com

_______________________________________________
Post Messages to: ***@leafe.com
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/CACW6n4seShDpvX3W8RMrf=mjHMB4pc3Hy1d2jDD+***@mail.gmail.com
** 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.
Gene Wirchenko
2018-04-19 23:38:13 UTC
Permalink
At 13:32 2018-04-19, Ted Roche <***@gmail.com> wrote:

[snip]
Post by Ted Roche
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...
Or maybe double-quotes. Or maybe, doubled single-quotes (like
''this''). Or maybe escaped quotes (of various sorts) such as '\"'.

Isn't it nice to have options? <GD&RVVF>

That funny key is used in UNIX.

Sincerely,

Gene Wirchenko


_______________________________________________
Post Messages to: ***@leafe.com
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/***@mtlp000086
** 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.
Fernando D. Bozzo
2018-04-19 21:17:56 UTC
Permalink
Post by Paul H. Tarver
Post by Ted Roche
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
---

_______________________________________________
Post Messages to: ***@leafe.com
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/CAGQ_JunpfQSEQAfYTyszeOVXS3DKdt3c9N6SWzhyxhB-***@mail.gmail.com
** 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.
Vince Teachout
2018-04-19 15:45:05 UTC
Permalink
Post by Paul H. Tarver
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


_______________________________________________
Post Messages to: ***@leafe.com
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/1b5a19aa-396a-13cd-2b0a-***@taconic.net
** 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.
Continue reading on narkive:
Loading...