Issue 105872

Hi,

I can't reproduce this in 3.1.1(OOO310_m18) nor in 3.2 (OOO320_m1).

The Form Filter works with hsql 1.9 rc6 in the same way as with 1.8. (build in
engine)

On which platform do you test this?

Do you have any other information to reproduce this.

Bye Marc

Thank a lot for comparing notes! I didn't believe a working configuration with
HSQL 1.9 existed, but apparently there must be one.

I will try extra hard to either find a HSQL build which works for me too, or try
to document the precise steps and configuration I used before encountering this
error. Will report back in either case.

The HSQL build I tried with was 3212 (which was then equivalent to RC6, but
development has moved onwards since then, I think they're now at build 3240).

P.S. The platform I tested with was a HSQL 1.9 server running on Debian Lenny
(OpenJDK) and OpenOffice running on Windows XP (Sun 1.6 series JDK). If it still
occurs with the latest versions I can test with, I'll report the exact numbers.

This is my complete transcript of the steps I undertook
to create this error on Windows XP (both server and client
on the same box). For your convenience, I'm attaching the working directory
which I used for the experiment as a .zip archive.


1) Create directory "c:\demo".

2) Deploy hsqldb.jar from http://www.hsqldb.org/support/hsqldb.jar
(version: 1.9 post RC6) into "c:\demo".

3) Create runner script "server.bat" with the following content:

java -cp hsqldb.jar org.hsqldb.server.Server -database.0 demo/demo -dbname.0 demo

4) Create runner script "client.bat" with the following content:

5) Run server.bat
6) Run client.bat

7) Create the following test table and index

CREATE CACHED TABLE product (
id INTEGER IDENTITY PRIMARY KEY,
code VARCHAR(50),
name VARCHAR(50)
);

CREATE INDEX idx_product ON product (code);

8) Insert the following sample data:

INSERT INTO product (code,name) VALUES ('001','First product');
INSERT INTO product (code,name) VALUES ('002','Second product');
INSERT INTO product (code,name) VALUES ('003','Third product');

9) Shut down HSQL in anticipation of OpenOffice installation
and system restart by issuing:

SHUTDOWN COMPACT;

9) Download OpenOffice 3.2 beta, version OOO320_m2 (OOo 3.2 Beta) from:

http://download.services.openoffice.org/files/extended/developer/
OOO320_m2_3.2_beta/OOo-Dev_OOO320_m2_3.2_beta_Win32Intel_install_en-US.exe

10) Purge any old OpenOffice installations by uninstalling them,
deleting their user settings directory
"C:\Documents and Settings\$USERNAME\Application Data"
and restarting the system.

11) Install the new OpenOffice (default, except no QuickStarter).
Start Ooo-Dev (not Base). Fill in initials. Do not register.

Pick "Tools" > "Options" > "Java". It should show "1.6.0_16" for the VM.
Click "Class Path". It should be empty.
Click "Add archive", direct it to "C:\demo\hsqldb.jar".
Close with OK and close OpenOffice.

12) Start the HSQL server. Select "Connect to an existing database",
and pick JDBC for the type. Set "jdbc:hsqldb:hsql://127.0.0.1/demo" as the URL,
set "org.hsqldb.jdbcDriver" as the driver class. Test the class, it should load.
Supply no password and test the connection, it should establish.
Specify "Register the database for me" and "Open the database for editing"
and click "Finish". First try saving it as "C:\demo\demo.odb",
but fail since HSQL has created a subdirectory "demo", which gets
entered instead of saving the file. Try saving as "demo.odf",
to get a file created which is named "demo.odf.odb".
Answer "no" to the question about the improvement program.
Base should finally start up, showing the database namespace
"PUBLIC ---> PUBLIC" which contains the table "PRODUCT".

13) Open the table, verify that data is visible.

Create a query in SQL view named "product" as "SELECT * FROM PRODUCT".
Run query, try to apply standard filter, observe that no field names
are offered in the field names menu (ATTN: this might require a
separate bug report).

Create a form in design view.
Click the Form Navigator tool from the toolbar.
No forms should be visible in the navigator window.

Right-click the root element "FORMS" and select "New" > "Form".
A form entry should appear, initially named "Form".
Right-click it and select "Properties".
Go to the "Data" tab and select the following:

Content type: Table.
Content: PUBLIC.PUBLIC.PRODUCT
Analyze SQL comment: Yes

Close the form properties dialog, close the form navigator.
Select the text box tool, create a text box on the form.
Observe that something is wrong (you cannot place it freely,
instead it automatically docks itself to the left edge of the form workspace)
but create it anyway. Right-click it and select "Control".
In the "Data" tab select "ID" into the "Data Field" listbox.

Add two more form fields, selecting "CODE" and "NAME" as their
respective data fields. Observe that your ability to place
them freely in the form workspace seems haphazard and random
(this might require a separate bug report).

Finally close the form, save it under the name "formProduct".
Now open the form for use, observe that it correctly displays
values entered into the table.

Try browsing back and forth using navigation buttons,
observe that they work.

Click the "Form based filters" tool from the toolbar,
observe that the filter pop-up toolbar appears.
Try entering something into the ID field, observe that it's not possible.
Ascertain that the same happens with the CODE and NAME field.

...and that is the essence of the problem for me.
No way to enter the keyword to start filtering data.

For a change, try switching the data source of the form
from "Table" to "SQL Command" and using "SELECT * FROM PRODUCT".
Observe that it still displays data, but the filter problem remains.

Created attachment 65470 [details]
Archive containing the working directory I used to test

One little addition: the content of the runner script "client.bat",
missing from my transcript, should be as follows:

java -cp hsqldb.jar org.hsqldb.util.DatabaseManagerSwing --driver
org.hsqldb.jdbcDriver --url jdbc:hsqldb:hsql://127.0.0.1/demo

And of course, I meant to write "from C:\Documents and
Settings\$USERNAME\Application Data\", meaning I didn't wipe out all of my
"Application Data", just subdirectories related to OpenOffice, but doing that
seems to make no difference -- I'm quite certain it's a code bug, not a settings
bug.

What causes it, and whether it's on the OpenOffice side or HSQL side, is
something I cannot figure out so far.

In addition, I can report that on Debian Lenny
(HSQL 1.8 latest public stable version running on the same machine
with OpenOffice 2.4.1, the latter originating from a Debian repository),
I get a similar, perhaps related error:

1) I create the test table

2) I connect OpenOffice Base to my database

3) I create a test query as "SELECT * FROM PRODUCT"

4) I run the query, see some result data

5) I try to apply a standard filter

6) The filter dialog opens, shows column names

7) I try filtering by "ID = 2" or "CODE = 003"
The following error occurs:

> SQL Status: 37000
> Error code: -11
> Unexpected token:  in statement [SELECT * FROM "PRODUCT" WHERE ( "" = 2 )]

Observing HSQL trace mesages confirms that too - basically,
something in OpenOffice failed adding a field name
to the filtering statement, possibly since it earlier failed querying
field names from HSQL, yet ability to display them in the combo box
of the standard filter dialog indicates this failure is not global.

Having studied it closer, I can report that the last error I described
is related to problems with database schema selection,
and seems to be fully alleviated by adding ";default_schema=true"
to the JDBC URL.

I'll check immediately whether the same might apply to 1.9 too.

Additional report:

While adding ";default_schema=true" solved the standard filter issue
which I accidentally stumbled upon with HSQL 1.8,
it does not seem to help for 1.9.

On HSQL 1.9 and Linux, OpenOffice 2.4 behaves as follows:

- when viewing the result data of a query,
filter buttons are disabled (grayed out)

- when attempting to filter form data using form-based filters,
it is impossible to write a keyword into any data field,
just like reported earlier

In yet another attempt to get more information, I'll probably grab a copy of
HSQL trace messages generated in otherwise identical situations on 1.8 and 1.9,
diff them and go manually analyzing them query by query.

By comparing server-reported trace messages for HSQL 1.8 and 1.9
(and results of queries thusly obtained) while I performed
identical actions in OpenOffice, I have found one difference
which *might* cause failures like I observe.

I have NOT ascertained that the difference I observe is the actual
cause of failure. Neither have I yet ascertained whether this difference
is by design, or would be considered a bug by HSQL developers.
I only asked about it about an hour ago, at:

http://sourceforge.net/tracker/?func=detail&aid=2883838&group_id=23316&atid=378131

I notice that at some point during either form creation
or opening a form to display data, or attempting to apply a filter,
OpenOffice performs the following queries (they look a little bit
different for 1.8 and 1.9, but the data queried are the same):

1.8:
SELECT * FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS
WHERE 1=1 AND TABLE_SCHEM = 'PUBLIC'
AND TABLE_NAME = 'PRODUCT' AND COLUMN_NAME LIKE '%'

1.9:
SELECT * FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS
WHERE TRUE AND TABLE_CAT = 'PUBLIC' AND TABLE_SCHEM = 'PUBLIC'
AND TABLE_NAME = 'PRODUCT' AND COLUMN_NAME LIKE '%'

Result columns differ more significantly, however, since on 1.9:

- COLUMN_SIZE appears for integer columns.
- BUFFER_LENGTH disappears for all columns.
- DECIMAL_DIGITS value appears for integer columns.
- NUM_PREC_RADIX changes for integer columns.
- CHAR_OCTET_LENGTH appears with a value of 0 for integer columns.
- TYPE_SUB column changes position.

Raw result data from 1.8 look like this:

TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE
BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF
SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE
SCOPE_CATLOG SCOPE_SCHEMA SCOPE_TABLE SOURCE_DATA_TYPE TYPE_SUB 
--------- ----------- ---------- ----------- --------- --------- -----------
------------- -------------- -------------- -------- ------- ----------
------------- ---------------- ----------------- ---------------- -----------
------------ ------------ ----------- ---------------- -------- 
[null]    PUBLIC      PRODUCT    ID          4         INTEGER               4 
                          10             0        [null]  [null]     4         
                                      1                NO          [null]      
[null]       [null]      [null]           1        
[null]    PUBLIC      PRODUCT    CODE        12        VARCHAR   50          100
                                        1        [null]  [null]     12         
                   100               2                YES         [null]      
[null]       [null]      [null]           1        
[null]    PUBLIC      PRODUCT    NAME        12        VARCHAR   50          100
                                        1        [null]  [null]     12         
                   100               3                YES         [null]      
[null]       [null]      [null]           1        


Raw result data from 1.9 look like this:

TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE
BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF
SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE
SCOPE_CATLOG SCOPE_SCHEMA SCOPE_TABLE SOURCE_DATA_TYPE IS_AUTOINCREMENT TYPE_SUB 
--------- ----------- ---------- ----------- --------- --------- -----------
------------- -------------- -------------- -------- ------- ----------
------------- ---------------- ----------------- ---------------- -----------
------------ ------------ ----------- ---------------- ---------------- -------- 
PUBLIC    PUBLIC      PRODUCT    ID          4         INTEGER   32            
           0              2              0        [null]  [null]     4         
                    0                 1                NO          [null]      
[null]       [null]      [null]           YES              1        
PUBLIC    PUBLIC      PRODUCT    CODE        12        VARCHAR   50            
                                         1        [null]  [null]     12        
                    50                2                YES         [null]      
[null]       [null]      [null]           NO               1        
PUBLIC    PUBLIC      PRODUCT    NAME        12        VARCHAR   50            
                                         1        [null]  [null]     12        
                    50                3                YES         [null]      
[null]       [null]      [null]           NO               1        

Unfortunately, I cannot assist with diagnosing this issue any more, since I
wrote my way around it, by having macro code apply and remove filters.