Page 1 of 1
[Solved] Query Reference Bug?
Posted: Mon Jul 15, 2013 1:40 am
by bluechip
I think I found a bug in OpenOffice Base. I want to know if others are experiencing this problem. I performed the following steps. Hopefully, they will reproduce it for you.
Steps to Reproduce:
1. Install OpenOffice.org 3.4.1 (perhaps on Windows 7).
2. Unzip HSQLDB 2.3 and add it to your classpath.
3. Open the "Split_HSQLDB_2.3.0_v3c.odb" file to create a split database.
4. Create a table (perhaps paste a 1.8 database into it).
5. Create a query with the "Run SQL command directly" option checked. Query1: "SELECT * FROM Table1"
6. Create another query with the "Run SQL command directly" option checked. Query2: "SELECT * FROM Query1"
7. Run Query2.
Expectation: You would expect it to show Table1 data, but instead an error message appears saying, "The data content could not be loaded. user lacks privilege or object not found: Query1"
Additional Information: If you uncheck the "Run SQL command directly" option for Query2, the query will work as expected. Rechecking it breaks it again. For Query1, it doesn't seem to matter if the "Run SQL command directly" option is checked or not. Query1 works as expected either way.
Is this a bug? Should I file a bug report? Is there a simple workaround that doesn't remove your SQL formatting (like unchecking the "Run SQL command directly" option does)? Have I simply done something wrong to cause this error?
Re: Query Reference Bug?
Posted: Mon Jul 15, 2013 2:04 am
by FJCC
I believe this is the expected behavior. Query1 is stored in the Base file and the HSQLDB database doesn't know anything about it. By setting "Run SQL command directly" you prevent the interpreter from telling HSQSDB what "Query1" means. The solution is to create a View using the contents of Query1. See
here. Select * from "View1" will work in direct SQL mode.
Re: Query Reference Bug?
Posted: Mon Jul 15, 2013 4:03 pm
by DACM
bluechip wrote:...
2. Unzip HSQLDB 2.3 and add it to your classpath.
3. Open the "Split_HSQLDB_2.3.0_v3c.odb" file to create a split database.
...
BTW, those two steps are mutually exclusive; you really can't mix
manual setup procedures with those of the
automated templates.
The current template (Split_HSQLDB_2.3.0_v3c.odb) installs HSQLDB 2.3.0 (
hsqldb.jar and
sqltool.jar) to your dedicated database folder and sets the
session class path, all automatically. And since a 'session' class path is overridden by a 'global' class path, it becomes tantamount that we remove any
global class path that we may have setup manually to hsqldb.jar. This is the reason for step 2 of the '
Instructions for use' quoted here:
[Wizard] Create a new 'split' HSQL database wrote:
(2) Remove any global Class Path to hsqldb.jar that may have been setup manually in *Office:
- Tools (*Office) > Options (Preferences) > *Office > Java/Advanced > Class Path > Remove
So in the context of macro-enhanced templates (.odb) such as this, this step allows you to run a split HSQL 2.3.0 database along-side a legacy 'embedded database' file, in separate sessions of Base, such as for drag-&-drop migration tasks.
Re: Query Reference Bug?
Posted: Mon Jul 15, 2013 6:41 pm
by bluechip
Thanks FJCC and DACM! I'm going to mark this as solved.
Re: [Solved] Query Reference Bug?
Posted: Fri Aug 02, 2013 12:12 am
by bluechip
I need to reopen this. All I need is a Query of a Query in SQL Mode (to prevent formatting loss). I tried moving some of the Queries into Views, but it doesn't allow me to create or modify the Views in SQL. Anytime I try to execute SQL through 'Tools' -> 'SQL...', I get "user lacks privilege or object not found". I think the Queries are too sophisticated to create in Design Mode. Running Queries against Queries seems like it would be an important feature.
How can I implement this Query hierarchy in OpenOffice.org Base?
Re: Query Reference Bug?
Posted: Fri Aug 02, 2013 1:46 am
by Sliderule
bluechip:
You said:
bluechip wrote:I tried moving some of the Queries into Views, but it doesn't allow me to create or modify the Views in SQL.
Actually, it does, if you know how.
Let me first, attempt to explain a few things, that, I think are important for you to understand.
- Open Office / LibreOffice Base ( *.odb ), is the database front-end. That is, it is the tool used to 'read / write' to your database engine . . . in you case . . . HSQL 2.3.0
- Your Queries ( Query Definitions ) also reside in the database front-end ( *.odb file )
- HSQL 2.3.0 is the database back-end, where the Date Definitions ( DDL ) and data actually reside - this also includes the VIEW definitions
Now, you CAN change a VIEW definition, and, you can SEE it too, if you wish.
- To see your VIEW definitions, create a Query from the OpenOffice / LibreOffice Query sub-system, with the following command:
Code: Select all
Select TABLE_NAME, VIEW_DEFINITION From INFORMATION_SCHEMA.VIEWS Where TABLE_SCHEMA = CURRENT_SCHEMA Order By UPPER(TABLE_NAME)
- Copy the VIEW_DEFINITION you are interested in, to a TEXT EDITOR, so, you can make the modification(s) you wish to it
- According to HSQL documentation:
HSQL 2.3.0 Documentation 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.
- From the OpenOffice / LibreOffice Base file Menu: Tools -> SQL...
issue the command -- BUT CHANGE -- the name of the VIEW ( "View Name"), and, the VIEW_DEFINITION ( starting with the word Select to meet you requirements, for example:
Code: Select all
ALTER TABLE "View Name" Select * From "MY_TABLE" Where "MY_DATE_FIELD">=DATEADD('dd',-7,CURRENT_DATE)
Explanation: Now that you have your VIEWS defined the way you want . . . you can create Queries from your TABLES, VIEWS exactly as want / need.
I hope this helps, please be sure to let me / us know.
Sliderule
Re: Query Reference Bug?
Posted: Fri Aug 02, 2013 2:56 am
by bluechip
That's interesting. I'd like to try it, but I still get the error: "user lacks privilege or object not found" when I try to execute any SQL statement in the 'Tools' -> 'SQL...' dialog. I haven't modified any permissions. Is this is unusual? I can create a separate thread.
Re: Query Reference Bug?
Posted: Fri Aug 02, 2013 3:04 am
by Sliderule
Please, post the statement you are trying to run in
Tools -> SQL...
Remember, any, all TABLE and COLUMN names, that are NOT ALL UPPER CASE ( capital letters A-Z ) . . . MUST . . . be enclosed in double quotes . . . for example . . .
"My Table" . . . since, it is NOT all UPPER CASE, and, it contains a space ( as I used in my sample command above ) .
Sliderule
Re: Query Reference Bug?
Posted: Fri Aug 02, 2013 4:49 am
by Sliderule
OK, sorry, MY MISTAKE above . . . sorry.
The
ALTER statement ( sample ) I wrote above is INCORRECT . . . sorry.
It should have been ( correct syntax is below ):
Code: Select all
ALTER VIEW "View Name" AS Select * From "MY_TABLE" Where "MY_DATE_FIELD">=DATEADD('dd',-7,CURRENT_DATE)
Two errors, in my statement ( post ) above:
- I wrote, ALTER TABLE and it should have been: ALTER VIEW
- I wrote, after the view name ( "View Name" ), Select and it should include the key word AS before the Select
I hope this helps, please be sure to let me / us know.
Sliderule
Re: Query Reference Bug?
Posted: Sat Aug 03, 2013 12:27 am
by bluechip
Sliderule wrote:all TABLE and COLUMN names, that are NOT ALL UPPER CASE ( capital letters A-Z ) . . . MUST . . . be enclosed in double quotes
It works! I thought I tried this, but I guess I didn't. Thank you!