View of a Query; when refreshed?

Discuss the database features
Post Reply
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

View of a Query; when refreshed?

Post by dreamquartz »

Hi All,

Just want to know when a view of a Query is refreshed?
It appears that I had a view that was not up to date.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: View of a Query; when refreshed?

Post by UnklDonald418 »

A View works much the same as a Query. Generally a View is used as a data source for a form. When the form is refreshed the view data should be reloaded.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: View of a Query; when refreshed?

Post by Villeroy »

A view of a query does not exist.
A view is a SELECTion that is stored in the backend.
A query is a SELECTion that is stored in the frontend.
The frontend can see the backend but the backend can not see the frontend, so you can create a query that is based on a view but not the other way round.

A value of a calculated field (SELECT ... "X"+"Y" ... )is refreshed instantly when the current record is stored.
A subform refreshes when you modified and stored the parent form's current record or move to another record.
Anything else requires the click on some refresh button or macro code.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: View of a Query; when refreshed?

Post by Nocton »

so you can create a query that is based on a view but not the other way round.
I think you are confusing the OP, Villeroy. The normal way of creating a View is from a Query - Right-click on the Query and select 'Create View'.
The answer to dreamquartz's question is that the View should be refreshed automatically when a record is changed so that the Query and the View should show the same results. I use Views mostly for reports as sometimes if there are compicated chains of queries the report cannot evaluate the result from a Query.
Of course if you alter the Query, you will need to recreate the View.
OpenOffice 4.1.12 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: View of a Query; when refreshed?

Post by Villeroy »

Sorry, I intrpreted "view of a query" as the query result as seen on screen.
If "view of a query" means "SELECT statement that has been forked from a query into a view", then the answer is that the view's SELECT statement will never be updated. You have to delete the view and save the query again under the same view name.
Alternative #1: Use a DROP VIEW and then a CREATE VIEW statement
Alternative #2: Close all connections to the database and edit the database's script file.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: View of a Query; when refreshed?

Post by dreamquartz »

As Nocton indicated.
I use many views for that reason as described.
OK. Now you , Villeroy indicated, can this be automated the moment you the moment you do a shutdown?
I use 16 views I want to have refreshed when ever I want, but definitely @ startup.

Is there a way to deal with this one way or an other?

Villeroy, I do not quite understand alternative #2.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: View of a Query; when refreshed?

Post by Villeroy »

A HSQLDB consists of files. One of them is the script file where the whole definition of tables, indices, relations and views is written as a sequence of SQL commands.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: View of a Query; when refreshed?

Post by Villeroy »

I forgot that you can edit a view when using an embedded HSQL. Right-click>"Edit in SQL View" is not availlable with "normal" (to me this means not embedded) databases. Without knowing the actual type of database, we spread more and more confusion. In this special case of embedded HSQL it might be easiest to copy and paste SQL.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: View of a Query; when refreshed?

Post by dreamquartz »

@Villeroy
I am indeed aware of the script file.
I use it on a regular basis.
I do not use embedded databases anymore.
I use the HSQLDB 2.4.1.

Is anything accomplished with:
Checkpoint Defrag and
Shutdown Compact?

I am under the impression that everything is 'rebuiild' when re-starting the database.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: View of a Query; when refreshed?

Post by Villeroy »

A view that used to be a query, saved with right-click>"Create as view", will never be "refreshed" in terms of "synchronized with the modified query" just like a file that has been saved in some other file format will never be synchronized with its source.

I know of 4 ways to edit the definition (the SELECT statement) of a view.
1. Delete view and save the query again with right-click>"Create as view".
2. Do the same on the command line with DROP VIEW and CREATE VIEW.
3. Edit the view definition in the script file.
4. Copy the modified SELECT statement from the query into the SQL view of the view.
The last option works with embedded HSQLDB only.

As far as I know, this has absolutely nothing to do with anything else that you do to your database such as SHTDOWN COMPACT.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: View of a Query; when refreshed?

Post by Sliderule »

Villeroy wrote:A view that used to be a query, saved with right-click>"Create as view", will never be "refreshed" in terms of "synchronized with the modified query" just like a file that has been saved in some other file format will never be synchronized with its source.

I know of 4 ways to edit the definition (the SELECT statement) of a view.
1. Delete view and save the query again with right-click>"Create as view".
2. Do the same on the command line with DROP VIEW and CREATE VIEW.
3. Edit the view definition in the script file.
4. Copy the modified SELECT statement from the query into the SQL view of the view.
The last option works with embedded HSQLDB only.

As far as I know, this has absolutely nothing to do with anything else that you do to your database such as SHTDOWN COMPACT.
Villeroy:

I strongly disagree with your comment above, 3. Edit the view definition in the script file. .

The only approved ( and therefore safe AND therefore should be used in all situations ) is defined in HSQL documentation:

http://www.hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_view_creation
[b]HSQL Documentation:[/b] [url]http://www.hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_view_creation[/url] wrote:
ALTER VIEW

alter view statement

<alter view statement> ::= ALTER VIEW <table name> <view specification> AS <query expression> [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

Alter a view. The statement is otherwise identical to CREATE VIEW. The new definition replaces the old. If there are database objects such as routines or views that reference the view, then these objects are recompiled with the new view definition. If the new definition is not compatible, the statement fails.
Of course, one can always get VIEW DEFINITIONS with the following Queries:
  1. Code: Select all

    -- Return all View Definitions from the CURRENT_SCHEMA
    Select 
       INFORMATION_SCHEMA.VIEWS.TABLE_NAME as VIEW_NAME, 
       INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION 
    From INFORMATION_SCHEMA.VIEWS 
    Where INFORMATION_SCHEMA.VIEWS.TABLE_SCHEMA = CURRENT_SCHEMA
  2. Code: Select all

    -- Return View Definition For a Specific VIEW
    Select 
       INFORMATION_SCHEMA.VIEWS.TABLE_NAME as VIEW_NAME, 
       INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION 
    From INFORMATION_SCHEMA.VIEWS 
    Where INFORMATION_SCHEMA.VIEWS.TABLE_NAME = 'MY_VIEW_NAME'  -- Change to the name of VIEW you desire
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: View of a Query; when refreshed?

Post by dreamquartz »

I think this is going to be side tracked, because I may have asked it the wrong way.

Our Client uses e.g. a view called 'vAddress' (based on a Query called 'qAddress'; and created via 'Create as View') for standardization purposes.
The Query qAddress does not require User input.
For test purposes, I just added information to the table 'tAddress', and that information is processed by 'qAddress' to show up in vAddress instantly (this is the expected outcome).
In the Database 'vAddress' is used is many situations.
Big advantage for our Client is that if we update/modify 'qAddress', everywhere where 'vAddress' is used, it will show the newly implemented results, based on 'qAddress'.
It might be an extra step, but for standardization purposes, we have chosen it to do it this way.
It does not really affect the performance of the database except in one location, which we are trying to solve still.

What we found, however, is that 'vAddress' is not always updated instantly, and we were wondering what the problem could be.
It appears to be a potentially random issue (arghh; WCS [Worst Case Scenario]), but so far nothing points in a certain direction.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: View of a Query; when refreshed?

Post by Villeroy »

Structural changes should be done when nobody is working with the database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply