formspider has three types of DSDs - Table - View - Query

and four types of DS fetch modes - Fetch all (max row fetch size) - Paging + rows per page (+ max row fetch size, why?) - infinite scroll (+first fetch size) type A) query as needed - infinite scroll (+first fetch size) type B) query all

To be able to code efficient sql I need to know in which of these 12 cases formspider modifies my and what it's doing to it. (Like ORACLE Reports tells the developer what a group by will do to her query)

Background: I found out, that one of three detail-siblings of a test form had its query drastically modified into a multiply nested version, with rownum < :A at the inner level and row > :B at an outer level, although I never declared a DS to be paging. This modification, of course, ruined the performance.

I'm asking about 12 cases, because I found, that not only the Query Type of the DS affects SQL Text mangling but turning a View into a Query in the DSD also helps in making formspider keep its finger out of my query.

asked 08 Dec '14, 04:43

dipr's gravatar image

dipr
1561327
accept rate: 0%


Hi Paul,

Formspider does not run stateful. Therefore we cannot leave an open cursor like Oracle Forms does. For infinite scroll to work we have two options

1) Query all, show in batches This provides a consistent view of the data (sort of) like a cursor. So the grid can be editable

2) Query in batches, show in batches There is no way we can do this without using the wrapper SQL, because we are not in a stateful environment (not yet anyway). However, note that this is not a consistent view of the data. While the user scrolls down, she might encounter new rows that should have shown up earlier (because somebody else modified the data and messed up your order by)

In your case I'd either use 2) or use paging in the Grid and make Grid not editable. I'd create another dialog for edits. If that's not an option, do a Query All but limit the number of rows to be fetched. Warn the user if her query exceeds the limit, saying that she needs to be more specific in her criteria if she wants to see other rows.

Also see what we recommended at the bottom of the infinite scroll tutorial: http://theformspider.com/learningcenter/tutorial-56-how-to-use-infinite-scroll/

Once Formspider runs stateful in verison 1.9, we will have the technical capability to implement a datasource definition with an open cursor. I am not saying we are going to deliver such a datasource definition but it will be technically possible. And then you could build your screen pretty much exactly like in Forms.

Kind Regards, Yalim

link

answered 08 Dec '14, 06:48

Yalim's gravatar image

Yalim ♦♦
2.8k5
accept rate: 21%

edited 08 Dec '14, 06:48

excellent answer, exactly what I saw in SQL-trace! I'm really looking forward to stateful 1.9 (not for a WebShop, mind you) for desktop work.
wink Maybe there will come a 1.9oc (open cursor) then formspider WILL be a perfect Forms successor!

(08 Dec '14, 07:02) dipr

Hi Paul,

I don't think Formspider modifies your Query. In several cases we wrap the SQL of the Datasource Definition around some other SQL, because we have to, but I don't think we ever modify the original query. (The only other thing we do with the original query is to replace bind variables). So I'd be very interested to know more if you spotted a case where we actually modify the query you wrote.

If the datasource fetch mode is Paging, Formspider adds the predicates you noticed. It adds the same predicates if the fetch mode is Infinite Scroll and Query Type is Query As Needed. We have to do this to provide the desired functionality.

If you didn't use paging in your datasource, I suspect you used Infinite Scroll with Query As Needed. Switch to Query All. In this case, Formspider will read all the rows from the database but send the client these rows in batches as the user scrolls down.

Kind Regards,
Yalim

link

answered 08 Dec '14, 05:05

Yalim's gravatar image

Yalim ♦♦
2.8k5
accept rate: 21%

Correctly guessed, I used "Infinite Scroll" with "query as needed", and I must say I'm surprised to read that this added the paging outer queries! But using "Infinite Scroll" + "Query All" is an absolute nono, when this results in formspider internally reading all lines!

We all know the the worst thing an applocation developer can code is a go "last button" (ORACLE Forms course for beginners).

But no developer knows if a (errm, weird) user might actually want to scroll down 3000 lines, once per day, because he "has to".

Thus: What DS setup in formspider would you suggest for a case, when the users is presented with a list of, say, Orders, sorted by Order_ID desc (prefectly optimize via an index). She sees the 10 highest order_ids, can scroll down "as needed" (!) and even might scroll down hundersthousands of lines (until ORACLE Forms runs out of buffer disk space :) )

This is what they are used from all my forms blocks. Default sort shows important rows at the top, but they can scroll down as much as they feel necessary, knowing that in most cases they are happy with top 10 lines.

link

answered 08 Dec '14, 06:24

dipr's gravatar image

dipr
1561327
accept rate: 0%

So with FS 1.9 and stateful session rolled out (congratulation to that),

How are my chances to show the top N "youngest" records in a grid, while giving the user the opportunity to scroll down (AND fetch only "as needed"), WITHOUT "Query all" or pagination? ;)

The OracleForms error when the crazy user was scrolling down through zillions of records or an occassional ORA-1555 are acceptable, "Query all" or a paginating userinterface is not.

link

answered 08 Sep '15, 07:06

dipr's gravatar image

dipr
1561327
accept rate: 0%

edited 08 Sep '15, 07:07

Hi Paul,

The implementation of infinite scroll did not change in Formspider 1.9. So a datasource with infinite scroll in query as needed mode still looks like the correct choice for you.

Scrolling down a lot of records in this scenario might be an issue for two reasons: The database table might not be able to handle that many rows for some reason. Or the client side JavaScript might get crushed. In any case, I guess you have to try to find out.

Are these rows going to be editable or is this Grid read-only? (just curious)

There are a lot of optimisations we can do for datasource definitions if the application runs in stateful mode but we have not done them yet. I don't see us doing them soon on our own any time soon. Our plate is quite full for the next few months.

Kind Regards,
Yalim

link

answered 08 Sep '15, 07:23

Yalim%20Gerger's gravatar image

Yalim Gerger ♦♦
1.8k5
accept rate: 15%

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:

×12
×4
×2
×1

Asked: 08 Dec '14, 04:43

Seen: 1,393 times

Last updated: 08 Sep '15, 07:23


© Copyright Gerger 2017