This would allow you to alias the column names (assuming Pervasive is similar to Oracle) so that R:Base has absolutely no idea of the bad column name.
As long as this is a new view you wouldn't be impacting the third-party package that you are attempting to extract data from.
Bob
[EMAIL PROTECTED] wrote:
Gunnar,
Thank you for the reply. Indeed, I do connect to this very same
table on a daily basis and make both queries and updates without
any problems. It is not just this one column named REFERENCE.
As mentioned, either with ANSI on or off, I cannot get to the data
or update it. The database is Pervasive and is used by AccPac accounting software. I have several fairly complex applications that are integrated with AccPac / Pervasive and they work quite well, both updating and retrieving data.
This column is proving to be a problem though.
Does any one know if this might be an issue to submit as a fix or would it be related more to the Pervasive ODBC driver not doing something correctly?
Thanks,
-Bob
-------------- Original message --------------
Bob
I was involved in this issue a while back. I my case it was ACCESS DB with field name that broke the naming convention.
When I first tried with alias name I was happy for reading, but I could use SSQL UPDATE. In my case it was solve by SET ANSI OFF as you suggest. Then in my cas both select update declare cursor or whatever works. (Sometimes with not so good speed since as far as I understand I have only on one primary key in the ODBC table.
If SET ANSI off does not help you, what is your Foregin DB, have you tried an other table without a reserved word, if that works maybe there is an issue for setting. ODBC seems to be very specific for certain settings.
Gunnar Ekblad
-----Original Message----- *From:* [email protected] [mailto:[EMAIL PROTECTED] *On Behalf Of [EMAIL PROTECTED] *Sent:* den 16 mars 2005 15:39 *To:* RBG7-L Mailing List *Subject:* [RBG7-L] - Invalid Column Name & ODBC
I have a foreign database that I must connect to via ODBC. This database has a column name of REFERENCE which is a reserved word. This column is a TEXT 60 data type. If I attach to the table with ANSI OFF, the table seems to attach OK, but I cannot select any values from this column. It does not give any errors and no output.
R>sho ansi (ANSI ) OFF ANSI SQL name restrictions
R>set messages on
R>set error messages on
R>sel ORDNUMBER REFERENCE from orders
(No output, no errors, no information at all is shown)
If I attach with ANSI ON, I have to alias the column name and this gives no error. I made the alias name OrderRef during the SATTACH. However when I try to select data, it gives me incorrect data, even a different data type - Double
R>set ansi on
R>sel ORDNUBER ORDERREF from ORDERS
ordnumber orderref ----------------------
------------------------------------------------------
0.000
OR319282 0.000
OR298674 0.000.
I also cannot update or change the data in this field as I get an
ODBC error.
Anyone have any ideas or can confirm. I connect and update data on this very same table on a daily basis in other columns. This one column with a reserved name is the only one giving me problems. Unfortunately it is very important and I cannot change the name in the foreign database as it is a canned package.
Thanks,
-Bob
