When I try to execute a SQL command with INSERT or UPDATE on a table trough NotesSQL, I have always this message : “Column is not updatable”. All my tables have been created with Domino Designer. Is it impossible to update fields of such tables ? Thx
Subject: [NotesSQL] Column not updatable
Is the table you’re referring to a Form or a View? View data is not updatable since that data is not necessarily exact Form data, Form data is updatable, but it depends on the field (a Computed For Display field is not updatable, etc). What application are you performming the SQL from? I know MS Access won’t be able to update ODBC data if you don’t set a primary key. So the problem could be the application.
Ron
Subject: RE: [NotesSQL] Column not updatable
Thanks for your answer Ron.I don’t know how to see what is the kind of my table. I don’t think it’s a view but I’m not sure. I’m using a Php script to execute my SQL queries and I’ve tried also with a software called Query tool, the error is the same.
Subject: RE: [NotesSQL] Column not updatable
The easiest way to see whether it’s a Form or a View is to look it up in the Domino Designer.
If you don’t have access to the Designer, you can tell what type of table it is by performing the ODBC function SQLTables:
If it’s a Notes View, TABLE_TYPE will return VIEW, if it’s a Notes Form, TABLE_TYPE will return TABLE.
The only problem with this is table is an Alias, TABLE_TYPE will return SYNONYM and you can’t tell if it’s a view or form. In this case, you would need to find out via the Domino Designer.
Chances are you are going against a View, but you can update it’s underlying data.
Easiest way to describe is using Notes Name and Address book. When you create a new Address reocrd, you create a document basesd off the Person Form. When you view it in Notes, you use the People View. If you were to do a Select * from Person, you would retrieve all the person documents, these are updatable because you are working with the direct document.
If you were to do a Select * from People, you would retrieve all rows as defined in the Notes View name People and you would only retrieve those fields that are columns in the Notes View. Because a Notes View’s columns can contain not only the fields that the underlying document has but also supports formulas and constants, there’s really not a way to update those fields.
So, what do you do. Well, If you need to use the View and update it’s underlying document, you can use information the row returns to modify that document. NotesSQL will return the Unique Document ID in both the View Row and the Document. You can also find out what Form the View Row’s underlying document is so that you can use both the NoteUNID and the NoteForm in a selection/update.
SELECT NoteUNID, NoteForm, * FROM People
use strings to build your new sql statement… something to the effect of…
szForm = row.NoteForm
szUNID = row.NoteUNID
So your resulting update statement would be something like
UPDATE szForm SET …=…, …=… WHERE NoteUNID=szUNID
You do need to know the fields for the underlying document, which may be different than the fields the view contains. But that’s how to update an individual record.
Hope this makes sense, It’s early and I’m tired =)
Ron
Subject: RE: [NotesSQL] Column not updatable
Thanks really Ron. Very interesting.I have access to the Designer and I have seen what kind of fields I can update.
I have successed in updating the data I wanted.