BASE as frontend for MySQL views

[akage]

Hi,

I use OpenOffice 3.3 in a windows XP system, a XAMPP with MySQL 5.5.8 and ODBC 3.51 installation (as BASE database access mode ODBC or ODBC MySQL).

When I define a VIEW as a virtual table in MySQL with all rights for the user, I am able to see the fields of the view in the tables of BASE, I am able to send an INSERT command in the SQL field of the BASE frontend to add a new field but I could NOT find a way to add a value through the BASE frontend table directly. The status of the table remains as view only.

Any suggestions to overcome this trouble ?
the openoffice forum says that BASE sets autonomously the access to any VIEW always to read-only, which is difficult to understand. Any comments or differences in libreoffice BASE ....

andy

[AndreasJBittner]

Hi Andy,

this is a feature, not a failure. A VIEW is nothing more than a SELECT-statement and produces a virtual table, the result set. How should an insert work on a result-set? You have to insert your new data into the table.

Greetings
Andreas

[akage]

Hi Andreas,

if you check the MySQL (and many other SQL) databases you will see that VIEWS can be treated like regular tables although they are virtual tables. I use these type of table to regulate access to the tables behind instead of column access roles because you can define VIEW access roles like regular tables.

you can define a VIEW and try to set an INSERT via direct BASE SQL command - it works well

[AndreasJBittner]

Hi,

ok, I see, but if the VIEW is a view across some related tables you have to define the relationship as ... on delete | insert cascade; if 'no action' is defined no action will proceed. Have a look at
http://dev.mysql.com/doc/refman/5.5/en/ ... aints.html

Greetings
Andreas

[akage]

Hi,
the read-only setting occurs also if you do not use complex JOIN request to form the VIEW.