ODBC Error Invalid Column Name when adding new Field

This Q&A forum allows users to post and respond to "How Do I Do ....." questions. Please do not use to report (suspected) errors - you must use your regional help desk for this. The information contained in this forum has not been validated by LANSA and, as such, LANSA cannot guarantee the accuracy of the information.
Post Reply
jyoung
Posts: 642
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

ODBC Error Invalid Column Name when adding new Field

Post by jyoung » Thu Apr 11, 2019 2:03 am

I have a LANSA defined table that I am trying to add a Field "LastName" to.

The table has data in it that I don't want to loose, I just need to add a column to it.

When compiling the Table, if I choose Rebuild Table, Rebuild indexes and views, Rebuild OAMs, the new column does not show up in the table, although it compiles fine.

If I choose Drop existing tables/Index with Save Table Data and Reload Table data, then I get an ODBC Error stating that 'LastName' is not a valid column name.
error.PNG
error.PNG (14.3 KiB) Viewed 18924 times
Most (all) of the Files I work with are "Other Files" so LANSA does not maintain the structure, here however this is a LANSA defined File.

How do I add a Field to the File AND keep the current data?

Thanks,
Joe

User avatar
lawingo
Posts: 50
Joined: Fri Dec 04, 2015 6:41 am

Re: ODBC Error Invalid Column Name when adding new Field

Post by lawingo » Thu Apr 11, 2019 4:49 am

Hey Joe,
The collation of the database is case-sensitive and the case of the column name specified in the query does not match the case of the column defined in the table. For example, when a column is defined in a table as LastName and the database uses a case-sensitive collation, queries that refer to the column as Lastname or lastname will cause error 207 to return because the column name does not match.
That comes from this article:
https://docs.microsoft.com/en-us/sql/re ... rver-2017

Most everything I do is Lansa defined. Everything I define is all uppercase. Maybe start with that?

Best,
Chad

jyoung
Posts: 642
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

Re: ODBC Error Invalid Column Name when adding new Field

Post by jyoung » Thu Apr 11, 2019 11:20 pm

Hey Chad,

I did not think about the collation, after all this is a LANSA created database and LANSA created Table with LANSA created Fields. Nothing changed.

I ran the query to check the collation and got SQL_Latin1_General_CP1_CI_AS so it should be case insensitive and accent sensitive. Nothing out of the ordinary here.

What I find interesting is the failure seems to occur when unloading the data. At this point I feel like I'm stuck and will be contacting support.

Thanks,
Joe

jyoung
Posts: 642
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

Re: ODBC Error Invalid Column Name when adding new Field

Post by jyoung » Fri Apr 12, 2019 1:26 am

Ok, so this kinda thing is super frustrating.

I removed the Field and was still getting the error. Thinking that something had to be cached somewhere, I killed of all the processes and rebooted the PC.

Still same error WITHOUT the Field in the File.

All the while I am compiling with the drop / reload options checked.

On a whim, I unchecked the drop / reload options and just recompiled the OAM.

It compiled fine with no errors as expected.

I added the LastName Field back. Recompiled with drop / reload options checked and it WORKED.

EDIT

I can replicate this now.

Take and existing LANSA File and add a Field to it. Recompile the File WITHOUT the drop/reload options.
Capture1.PNG
Capture1.PNG (13.74 KiB) Viewed 18896 times
Now, try to compile WITH the drop / reload options.
The compile will likely fail with the above error.

Dominik
Posts: 11
Joined: Fri Jun 10, 2016 12:14 pm

Re: ODBC Error Invalid Column Name when adding new Field

Post by Dominik » Fri Apr 12, 2019 5:29 pm

Joe, when you compiled the file without the drop table option (as per that last pic), the rebuild table/indexes options would have failed/skipped because the tables already exist. But the OAM would have been replaced (this overwrites regardless). So you have a situation where the database table doesnt match the OAM now.

You need to remove the field you added to the file, then compile it selecting *only* rebuild OAM (do not drop/recreate table or save/restore data).
This will put the OAM back in line with the actual database table.

Then if you want to add the field again, do so but when recompiling, you should select:
Rebuild Table
Rebuild Indexes and Views
Rebuild OAM
and
Drop existing table/indexes
Save Table Data
Reload Table data

The Keep saved data (DAT file) option is only so you can see the data that was saved, and possibly for manual loading of data later (using the LOAD_FILE_DATA BIF). It is entirely optional, but those six options i specified above should be considered mandatory when making table changes to existing tables.

jyoung
Posts: 642
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

Re: ODBC Error Invalid Column Name when adding new Field

Post by jyoung » Fri Apr 12, 2019 11:48 pm

That confirms what I thought I was happening and what the fix was.

I typically don't work with LANSA created Tables, so I normally only have "Rebuild OAM" selected and as you mentioned this is what caused the issue.

I would be really nice IMHO, if the compile errored or warned me that something was wrong or out of sync.

Thanks for help.
Joe

Post Reply