View of a Query; when refreshed?

[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

[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.

[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.

[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.

[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.

[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.

[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.

[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.

[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

[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.

[Sliderule]

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

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   Expand viewCollapse view

   -- 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   Expand viewCollapse view

   -- 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]

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

[Villeroy]

Structural changes should be done when nobody is working with the database.