[Solved] Query Reference Bug?

Discuss the database features

[Solved] Query Reference Bug?

Postby bluechip » Mon Jul 15, 2013 1:40 am

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?
Last edited by bluechip on Sat Aug 03, 2013 12:28 am, edited 3 times in total.
OpenOffice 3.4.1 on Windows 7
bluechip
 
Posts: 5
Joined: Mon Jul 15, 2013 1:00 am

Re: Query Reference Bug?

Postby FJCC » Mon Jul 15, 2013 2:04 am

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.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6948
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Query Reference Bug?

Postby DACM » Mon Jul 15, 2013 4:03 pm

bluechip wrote:...
2. Unzip HSQLDB 2.3 and add it to your classpath. :shock:
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.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Query Reference Bug?

Postby bluechip » Mon Jul 15, 2013 6:41 pm

Thanks FJCC and DACM! I'm going to mark this as solved.
OpenOffice 3.4.1 on Windows 7
bluechip
 
Posts: 5
Joined: Mon Jul 15, 2013 1:00 am

Re: [Solved] Query Reference Bug?

Postby bluechip » Fri Aug 02, 2013 12:12 am

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?
OpenOffice 3.4.1 on Windows 7
bluechip
 
Posts: 5
Joined: Mon Jul 15, 2013 1:00 am

Re: Query Reference Bug?

Postby Sliderule » Fri Aug 02, 2013 1:46 am

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.

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

    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.


  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   Expand viewCollapse view
    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
User avatar
Sliderule
Volunteer
 
Posts: 1159
Joined: Thu Nov 29, 2007 9:46 am

Re: Query Reference Bug?

Postby bluechip » Fri Aug 02, 2013 2:56 am

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.
OpenOffice 3.4.1 on Windows 7
bluechip
 
Posts: 5
Joined: Mon Jul 15, 2013 1:00 am

Re: Query Reference Bug?

Postby Sliderule » Fri Aug 02, 2013 3:04 am

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
User avatar
Sliderule
Volunteer
 
Posts: 1159
Joined: Thu Nov 29, 2007 9:46 am

Re: Query Reference Bug?

Postby Sliderule » Fri Aug 02, 2013 4:49 am

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   Expand viewCollapse view
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
User avatar
Sliderule
Volunteer
 
Posts: 1159
Joined: Thu Nov 29, 2007 9:46 am

Re: Query Reference Bug?

Postby bluechip » Sat Aug 03, 2013 12:27 am

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!
OpenOffice 3.4.1 on Windows 7
bluechip
 
Posts: 5
Joined: Mon Jul 15, 2013 1:00 am


Return to Base

Who is online

Users browsing this forum: No registered users and 0 guests