MapInfo Pro

Apologies in advance if this has been discussed (any discussions search results have an unreachable page)

I have a SQL view i connect to in mapinfo. The view contains 2 joined tables.. one holds the geometries, the other attributes. I will only ever want to change data in the geometries table.

I can update that table ok in MI through the view, however i cannot insert into(and probably delete from) it without throwing an sql error despite sufficient permissions. 

I believe the issue is because when i create a new feature, it auto populates some values of the table to which i do not want to update(e.g. default zeros). So MI is probably trying to insert into both tables.

Is there away around this without specifically connecting to just that table?

Thanks in advance
KR
Kieran

------------------------------
Kieran McGowan
Corangamite Shire Council
Camperdown VIC
------------------------------

Kieran,

You may be correct in that you simply can't update data when it's delivered via a view. However, having worked with a few Spatial DBs in MapInfo now there's a few questions I have :
1) is it a linked or live table?  If it's linked is all the correct DNS or connection string in the .tab file.  Is the database password in there too?  Has the tab file got the read only metadata option on.
2) I believe views can be made to be read-only themselves within the DB.  I've connected to an Oracle DB that pulled data from several tables and to stop anyone accidently editing in MapInfo the view itself was set to read-only.  This allowed the user to edit stuff in Pro but wouldn't save properly afterwards.
3) Can you screen grab the error message?

Nick

------------------------------
Nick Hall
Mapchester LTD
nick.hall@mapchester.co.uk
------------------------------

Original Message:
Sent: 11-10-2020 21:49
From: Kieran McGowan
Subject: Updating SQL VIEW Table through MI

Apologies in advance if this has been discussed (any discussions search results have an unreachable page)

I have a SQL view i connect to in mapinfo. The view contains 2 joined tables.. one holds the geometries, the other attributes. I will only ever want to change data in the geometries table.

I can update that table ok in MI through the view, however i cannot insert into(and probably delete from) it without throwing an sql error despite sufficient permissions.

I believe the issue is because when i create a new feature, it auto populates some values of the table to which i do not want to update(e.g. default zeros). So MI is probably trying to insert into both tables.

Is there away around this without specifically connecting to just that table?

Thanks in advance
KR
Kieran

------------------------------
Kieran McGowan
Corangamite Shire Council
Camperdown VIC
------------------------------

Hi Nick,

1) it is a live table. I think the connection must be correct because I can successfully update one of the view tables, as long as i'm only making changes to the fields of that underlying table and not the other(to which i've limited to read-only permissions).
2) In order to update the view table in MI, i needed select, insert, delete and update permissions on both the view and the gis table.(again just 'select' on the attribute table)

Just to be clear, Updating the view table works as i want it to, i.e. im able to update the GIS table but not the attribute table

The problem is when Inserting a new record(geometry), MI will auto populate some of the second tables fields. So when i click save it wants to write back to two view tables which you can't do in sql. The section in green is from the views GIS table, and the red is from the attribute table(notice the default values being populated)


------------------------------
Kieran McGowan
Corangamite Shire Council
Camperdown VIC
------------------------------

Original Message:
Sent: 11-11-2020 08:25
From: Nick Hall
Subject: Updating SQL VIEW Table through MI

Kieran,

You may be correct in that you simply can't update data when it's delivered via a view. However, having worked with a few Spatial DBs in MapInfo now there's a few questions I have :
1) is it a linked or live table?  If it's linked is all the correct DNS or connection string in the .tab file.  Is the database password in there too?  Has the tab file got the read only metadata option on.
2) I believe views can be made to be read-only themselves within the DB.  I've connected to an Oracle DB that pulled data from several tables and to stop anyone accidently editing in MapInfo the view itself was set to read-only.  This allowed the user to edit stuff in Pro but wouldn't save properly afterwards.
3) Can you screen grab the error message?

Nick

------------------------------
Nick Hall
Mapchester LTD
nick.hall@mapchester.co.uk

Original Message:
Sent: 11-10-2020 21:49
From: Kieran McGowan
Subject: Updating SQL VIEW Table through MI

Apologies in advance if this has been discussed (any discussions search results have an unreachable page)

I have a SQL view i connect to in mapinfo. The view contains 2 joined tables.. one holds the geometries, the other attributes. I will only ever want to change data in the geometries table.

I can update that table ok in MI through the view, however i cannot insert into(and probably delete from) it without throwing an sql error despite sufficient permissions.

I believe the issue is because when i create a new feature, it auto populates some values of the table to which i do not want to update(e.g. default zeros). So MI is probably trying to insert into both tables.

Is there away around this without specifically connecting to just that table?

Thanks in advance
KR
Kieran

------------------------------
Kieran McGowan
Corangamite Shire Council
Camperdown VIC
------------------------------