[Solved] Query Reference Bug?

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

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

[DACM]

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

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

[bluechip]

Thanks FJCC and DACM! I'm going to mark this as solved.

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

[Sliderule]

bluechip:

You said:

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.

1. 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
2. Your Queries ( Query Definitions ) also reside in the database front-end ( *.odb file )
3. 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.

1. 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)

2. Copy the VIEW_DEFINITION you are interested in, to a TEXT EDITOR, so, you can make the modification(s) you wish to it
3. According to HSQL documentation:
   

   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.

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

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

[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

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

Explanation: Two errors, in my statement ( post ) above:

1. I wrote, ALTER TABLE and it should have been: ALTER VIEW
2. 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

[bluechip]

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!