Hi,

i have a datasource based on a view and checkt "Use Oracle Row ID" for the DML-operations. The primary key value is None.

The view does NOT contain the rowid.But it works... And i do not understand why.

Perhaps it is because in this case it is a simple view on one table and there are no instead of triggers.

I would expect that i have to define ROWID in the view to get "Use Oracle ROW ID" working. Or not? If not, how does Formspider know which ROWID to take?

Is there any way to see exactly which DML-statements Formspider is executing?

Kind regards, Jan Willem Vermeer

asked 21 Sep '15, 07:25

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1431436
accept rate: 0%


Hi Jan,

In Formspider, as far as your view is updatable you can use Oracle Row ID for the DML operations. If your view is not updatable (because it contains a DISTINCT operator, GROUP BY clause..etc) and the "Use Oracle Row ID" checkBox is checked, Formspider does not allow you to save your datasource definition and raises an error message.

You don't need to define the rowid in your view, Formspider handles it automatically. In fact, if "Use Oracle Row ID" checkbox is checked, Formspider uses an internal query to get the rowid such as:

select v.*, rowid from <yourView> v

This way it retrieves the rowid of the base table of your view.

Hope this helps,
Ibrahim

link

answered 21 Sep '15, 07:46

Ibrahim%20Sandalli's gravatar image

Ibrahim Sand... ♦♦
1.5k5
accept rate: 25%

edited 21 Sep '15, 07:47

Hi Ibrahim,

do i understand it correctly that i can only use Oracle ROWID on tables and updateable views? I hope not...

In my datamodel almost every table has a meaningful primary key based on 1 to 7 columns. Indeed, i do not believe in meaningless numeric keys and never will.

As far as i know, Formspider offers no function like "post query" to display lookup values from other tables. I've been told to create views to show lookup values and instead of triggers to update the base table. How do i handle these views for DML-operations?

Kind regards, Jan Willem

link

answered 21 Sep '15, 08:22

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1431436
accept rate: 0%

Hi Jan,

Yes, it's correct, you can only use Oracle Row ID on tables and updateable views, otherwise, it's not possible to obtain the rowid value itself.

For the kind of views you have described, you can still use the primary key and instead of triggers combination option. You can define an artificial primary key in your view by concenating your primary key columns such as column1||'$'||column2||'$'||column3 and perform your DML in the instead of triggers of your view.

Best Regards,
Ibrahim

link

answered 21 Sep '15, 08:57

Ibrahim%20Sandalli's gravatar image

Ibrahim Sand... ♦♦
1.5k5
accept rate: 25%

It would have been possible if it was allowed to add the rowid to the view definition by the developer. To be honest i do not see any advantage of the rowid anymore... That was especially meant to be used for tables with complex keys and there are always lookup tables. It's a pity that this new function has not been discussed earlier.

(21 Sep '15, 09:51) Jan Willem V...

Hi Jan,

You can have multiple column primary keys and look up values via joins in a view and still have the base table updateable. An updateable view can have joins to other tables to retrieve column values.

Kind Regards, Yalim

link

answered 21 Sep '15, 10:00

Yalim's gravatar image

Yalim ♦♦
2.8k5
accept rate: 21%

Hi Yalim,

that's true but there are many limitations. See this documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28310/views001.htm#i1006232.

I will try it datasource after datasource. If it does not work, i know the work-around with the surrogate key and instead of triggers.

Kind regards, Jan Willem

link

answered 21 Sep '15, 11:17

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1431436
accept rate: 0%

Hi Yalim, i am afraid this does not work...

I have done this:

1) Create a join view on a table with multiple column primary key
2) Create datasource on the view
3) Check "Use Oracle Row ID"
4) Check "Issue DML for" Delete, Insert and Update

When i try to commit changes, Formspider raises an alert with an empty screen except for a red cross.

I have tried to figure out the exact error message, but even debug mode shows nothing.

What can be wrong? I get the impression that the Oracle Row ID can not be used for join views... Or should this work?

Kind regards, Jan Willem

link

answered 22 Sep '15, 05:21

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1431436
accept rate: 0%

Hi Jan,

Can you send the DDL of your table and your view to contact@theformspider.com? This will help us to replicate and investigate the problem.

Regards,
Ibrahim

(22 Sep '15, 07:09) Ibrahim Sand... ♦♦

Hi Ibrahim,

Offcourse i can do that, but is there a way to see to error messsage? Perhaps something else is wrong... When i try to insert a new record and purposely forget a mandatory item, Formspider raises a similar error with a yellow exclamation mark right above and a popup with a red cross, no message and a button OK. If i would know the error message, i could first investigate the problem further.

Kind regards, Jan Willem

(22 Sep '15, 08:03) Jan Willem V...

Hi Jan,

Even an unhandled/unexpected error occurs, an error message should be logged in Formspider APIs and shown on the popup, this is the default behavior of Formspider. But in your case, somehow, the error message cannot be retrieved and logged in the APIs, this is a rare case indeed.

Therefore, in this case, the only way to know the error message is to debug the API code internally.

In the meantime, can you make sure that your DML statement works outside of the Formspider? For example if you are updating a row, please execute the same update statement from your plsql editor such as update yourView set... where rowid=<yourrowid> and issue a commit. This may give you some clues if the problem is not related with Formspider.

Regards,
Ibrahim

(22 Sep '15, 09:33) Ibrahim Sand... ♦♦

Hi Ibrahim,

The problem is that i can not see exactly what FS is updating. I tried to use a DBA-tool to intercept the update-statement, but did not succeed.

The view has a where clause with a check on a session variable (where <column> = api_session.getvaluenr) so i can not exactly check the update in sqlplus, however, without that check i can update the view. So i think the view is not the problem.

Until now i have never seen database errors in the popup! For example if i database trigger issues a raise_application_error(-20000,'xxxx') i can not see it.

I hope there is a way to see exactly what FS is doing.

Kind regards, JW

(22 Sep '15, 11:12) Jan Willem V...

Is there perhaps somekind of "debug level" in the debugger to follow the statements FS is executing?

I tried

pdate bdf_setup set value_tx = 'Debug' where var_cd = 'BDF_DebugLevel';

But that is not working :(

(22 Sep '15, 11:17) Jan Willem V...

Hi Ibrahim,

i opened a separate thread for the issue: http://osqa.theformspider.com/questions/4541/how-can-i-see-the-insert-and-update-statements-done-by-formspider.

It appears that when i ADD columns to views and refresh the query in the datasource definition, it is not 100% certain that the dml-statements are correct. In this case columns columns that were already present, were filled with values of added columns. This worries me a lot and i think it is absolutely necessary to be able to check the dml-statements formspider is doing. Preferably in debug mode, but api is also fine.

KR JW

(25 Sep '15, 03:36) Jan Willem V...
showing 5 of 6 show 1 more comments

I just came across with this topic and I'm really sorry that "using ROWID" has so little added value. I agree with Jan that you should have let developers to define ROWID in view definition and handle it in instead of triggers.

A little bit off topic, please take into consideration "IDENTITY columns always generated" feature of Oracle 12c and implement a "returnig into..." in docommit for autogenerated primary keys.

link

answered 09 Aug '16, 08:56

brg's gravatar image

brg
2115
accept rate: 0%

edited 09 Aug '16, 10:54

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×64
×6
×6

Asked: 21 Sep '15, 07:25

Seen: 1,784 times

Last updated: 09 Aug '16, 10:54


© Copyright Gerger 2017