2

I'm using QGIS 2.18.9 to edit my spatial data, my data is saved in Oracle's database.

When I'm editing a spatial layer I can create, edit and delete features without problems, but my database needs work using Workspace Manager, my spatial tables are versioned. When I created a feature and save in my database I received this error message:

Commit errors:
  ERROR: 1 feature(s) not added.

Provider errors:
  Oracle error while adding features: Oracle error: Could not retrieve feature id -3
  SQL:  
    Error: SELECT "CD_AREA" FROM "ALUNO"."VE_AREA" WHERE ROWID=:f

Has anyone received the same error? Can QGIS and Oracle work together with workspace/versioned tables?

1

Looks like QGIS does not work with workspace manager. The reason is this statement:

SELECT "CD_AREA" FROM "ALUNO"."VE_AREA" WHERE ROWID=:f

Workspace manager requires the concerned tables to be "multi-versioned": a multi-versioned table is replaced by a view with the same name, over a table that holds all the versions of each row as it is modified in multiple workspaces. As a result, accessing rows via their ROWID is no longer possible (since an object can exist in multiple copies with different rowids). Access should always be via primary keys.

To work with workspace manager, QGIS will need the following changes:

  1. Work with views in fully transparent way
  2. Access individual rows using their primary key
  3. Provide a way for the end user to choose the workspace to work in.
  4. Provide access to the fundamental long transaction operations: create a workspace (= start a long transaction), merge a workspace (=post changes to the parent workspace, aka commit a long transaction), delete a workspace, handle conflicts, handle long transaction locks ...

Not an easy task. But having QGIS support Oracle Workspace Manager would be a definite plus.

  • First all. Thanks for your answer. I don't know if I understood your point about QGIS doesn't works with workspace because when I created a oracle connection can I define a workspace. I know that a view was created to represent the original table that was versioned. Another point is I don't define ROWID in query this is doing automatic by QGIS. My oralce versioned table has three columns PK, INFORMATION and GEOMETRY. – rbarbalho Jun 21 '17 at 12:02
  • I imagine that if I define workspace at the connection time, QGIS will works all the time in my workspace, is this a not correct idea? – rbarbalho Jun 21 '17 at 12:09
  • Yes, it will. But the issue is precisely that QGIS loads data from the database with their physical identifiers (ROWIDs) and uses this ROWID to update a changed row. That works fine for tables as well as simple (updatable) views. But it does not with complex views such as the ones used by workspace manager. That is what I meant when I said that QGIS needs changes to be able to update multi-versioned tables. – Albert Godfrind Jun 22 '17 at 8:46
  • But it should work just fine when it comes to READ versioned tables, as you discovered. And yes, all you need is a way to specify the workspace you want to work in. I don't know how you can do that with QGIS though. The name of the workspace is not given as part of the connection. Rather you need to invoke the DBMS_WM.GOTOWORKSPACE() function. So if QGIS lets you run some automatic SQL at connection time, that may be a way. Otherwise, you could use a login trigger to do that automatically (from a table that maps users and workspaces). – Albert Godfrind Jun 22 '17 at 8:51
  • I am interested by your experience combining QGIS and Workspace Manager. It would be great if you could document your findings. – Albert Godfrind Jun 22 '17 at 8:54
0

This problem was solved in master code of github, I'll waiting this correction in current release of QGIS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.