Paul H. Tarver
2018-04-20 15:00:08 UTC
Apparently, all of the SQL sins covered by the ODBC driver with the '*'
wildcards are laid bare when you start trying to expand your SQL command
with explicit field statements.
Before I got Ted's message, I tried to delimit fieldnames with brackets like
'[abc]' and I got a new ODBC error indicating I had created a circular
reference. After I got Ted's email I tried again with the backtick '`abc`'
and again got the circular reference error.
On a whim, I decided to add an alias to the table (ie: sheet) name and then
update the field names to the full bracketed column name notation
'[xs].[abc]' along with the table/sheet alias in the query and incredibly it
worked! Of course I was still in the development environment at the time, so
I quickly compiled the new code to an exe and it worked there too! The
relevant section of the query with the troublesome field now looks like
this: "..., [xs].[3rdPartySickPay] AS xrdPartySickPay,..."
Upon running the code with another Excel spreadsheet, I got a new error that
was related to the fact that the column name aliases were the same as the
original fieldnames unless the first character was a digit. Upon review, I
just changed the program so that an underscore is added to the original
column name which eliminates the need to check for first digits or keywords
or uniqueness. Additional checks can be added going forward, but this is the
fastest solution that covers multiple potential issues. Since these column
headers are only used during the import process and are discarded after
control is returned to the original program, the column name is irrelevant.
So I've now have the code working to make Craig Boyd's AppendXLSX.prg work
with column headers that start with numeric values instead of alpha
characters. I'm sure my code could use some refining, but my modifications
are now working in both development and compiled versions.
If anyone is interested in getting a copy of the modified version of
APPENDXLSX.prg, you can get it at https://pastebin.com/wxDnwFw2. Changes
start on line 110 and go through line 170. Or you can just email me at paul
AT tpcqpc DOT com.
Paul
-----Original Message-----
From: ProfoxTech [mailto:profoxtech-***@leafe.com] On Behalf Of Ted
Roche
Sent: Thursday, April 19, 2018 3:32 PM
To: ***@leafe.com
Subject: Re: INSERT INTO...SELECT Issue
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
[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/19ce01d3d8b8$461ca1e0$d255e5a0$@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.
wildcards are laid bare when you start trying to expand your SQL command
with explicit field statements.
Before I got Ted's message, I tried to delimit fieldnames with brackets like
'[abc]' and I got a new ODBC error indicating I had created a circular
reference. After I got Ted's email I tried again with the backtick '`abc`'
and again got the circular reference error.
On a whim, I decided to add an alias to the table (ie: sheet) name and then
update the field names to the full bracketed column name notation
'[xs].[abc]' along with the table/sheet alias in the query and incredibly it
worked! Of course I was still in the development environment at the time, so
I quickly compiled the new code to an exe and it worked there too! The
relevant section of the query with the troublesome field now looks like
this: "..., [xs].[3rdPartySickPay] AS xrdPartySickPay,..."
Upon running the code with another Excel spreadsheet, I got a new error that
was related to the fact that the column name aliases were the same as the
original fieldnames unless the first character was a digit. Upon review, I
just changed the program so that an underscore is added to the original
column name which eliminates the need to check for first digits or keywords
or uniqueness. Additional checks can be added going forward, but this is the
fastest solution that covers multiple potential issues. Since these column
headers are only used during the import process and are discarded after
control is returned to the original program, the column name is irrelevant.
So I've now have the code working to make Craig Boyd's AppendXLSX.prg work
with column headers that start with numeric values instead of alpha
characters. I'm sure my code could use some refining, but my modifications
are now working in both development and compiled versions.
If anyone is interested in getting a copy of the modified version of
APPENDXLSX.prg, you can get it at https://pastebin.com/wxDnwFw2. Changes
start on line 110 and go through line 170. Or you can just email me at paul
AT tpcqpc DOT com.
Paul
-----Original Message-----
From: ProfoxTech [mailto:profoxtech-***@leafe.com] On Behalf Of Ted
Roche
Sent: Thursday, April 19, 2018 3:32 PM
To: ***@leafe.com
Subject: Re: INSERT INTO...SELECT Issue
"[Microsoft][ODBC Excel Driver] Syntax error (missing operator) in
query expression '3rdPartySickPay'."
Unsurprisingly, ODBC thinks it's a dumb name for a field, too.query expression '3rdPartySickPay'."
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
[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/19ce01d3d8b8$461ca1e0$d255e5a0$@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.