Apache OpenOffice Community Forum

Posted: **Wed Jan 09, 2019 9:09 am**

(Split HSQLDB database v. 2.3.2)

Hi all,

is it possible to rename a view that has dependent views?

Imagine this:

Table named tblData
View named vwData_TB based on table tblData
View named vwData_RB based on view vwData_TB

When I tried to rename vwData_TB, I received the following error:

1: dependent objects exist: PUBLIC.VWDATA_RB in statement
[ALTER TABLE "VWDATA_TB" RENAME TO "DELETE_VWDATA_TB"]

I need to make some changes to (recreate) vwData_TB, but I don't want to delete the original until I know the new version is working.

Thanks for any help anyone can provide,

CTB

Posted: **Thu Jan 10, 2019 9:39 pm**

Since views are created from queries, I'm assuming you didn't save copies of the queries used to create the views (a recommended practice when using a split database), otherwise you can rely on the queries for backup and first delete "vwData_RB" and then "vwData_TB".

Lacking the queries there are a couple of options.
The easiest would be to simply make a backup copy of the .odb file. If your edits don't work out you have the backup file to return to the original state.

For the other option, look in the database directory of your split database for the .script file. Open the .script file with Windows WordPad and locate the CREATE VIEW statements. Don't try to edit the .script file because you could lose the connection to your tables, but you can copy those statements into the clipboard. I would use Create Form in Design View and paste them into a form document that you can save for later use if needed. Since the form document is part of the database you don't need to search you computer for a file containing the saved statements.
If you find you need to recreate those views, use Tools>SQL to execute the saved CREATE VIEW statements.

Posted: **Fri Jan 11, 2019 1:13 am**

Always, always, always, make a backup of your working database - the four files:

*.backup
*.data
*.properties
*.script

before making any changes.

To see the Select statement(s) that comprise your current VIEW, run the Query below . . . and . . . store ( for safekeeping in case you need it again ) the contents of the column VIEW_DEFINITION

Code: Select all

Select 
   INFORMATION_SCHEMA.VIEWS.TABLE_NAME, -- This is the name of your View
   INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION  -- This is the DDL (Data Definition Language ) to define the View
From INFORMATION_SCHEMA.VIEWS 
Where INFORMATION_SCHEMA.VIEWS.TABLE_NAME = 'vwData_TB'  -- This will display the information for your desired View

Explanation:

The above table INFORMATION_SCHEMA.VIEWS is a standard SQL table in your Relational Database, HSQL
Use the command of your database back-end, from the Menu: Tools -> SQL... . . . per HSQL documentation . . .
1. 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.
2. Therefore, you should use a statement something like, and, change the words YOUR MODIFIED SELECT STATEMENT(S) HERE, to your desired code:
  Code: Select all
```
ALTER VIEW "vwData_TB" AS YOUR MODIFIED SELECT STATEMENT(S) HERE
```

Just as an FYI (For Your Information), if you want to know the Tables used in a View, run the following Query:

Code: Select all

Select 
   INFORMATION_SCHEMA.VIEW_TABLE_USAGE.VIEW_NAME,
   GROUP_CONCAT('"' || INFORMATION_SCHEMA.VIEW_TABLE_USAGE.TABLE_NAME || '"') as TABLE_NAME
From INFORMATION_SCHEMA.VIEW_TABLE_USAGE
Where INFORMATION_SCHEMA.VIEW_TABLE_USAGE.TABLE_SCHEMA = CURRENT_SCHEMA
Group By INFORMATION_SCHEMA.VIEW_TABLE_USAGE.VIEW_NAME
Order By UPPER(INFORMATION_SCHEMA.VIEW_TABLE_USAGE.VIEW_NAME)

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Apache OpenOffice Community Forum

How to rename a view with dependent views

How to rename a view with dependent views

Re: How to rename a view with dependent views

Re: How to rename a view with dependent views