[Solved] SQL error in setting listbox values

[ufalke]

Hi,
I have the following problem (OO2.4, accessing a MySQL server via JDBC):
In a form, assigned to table "CUSTOMER", I need to combine values from two tables (ACM and LOC) in listbox entries. The Listbox Data settings are:
Data field: ACM_ID
Type of list cont.:Sql
List content: SELECT CONCAT(A1.FIRSTNAME,' ',A1.LASTNAME,' (',A2.NAME,')'),A1.ID FROM ACM A1, LOC A2 WHERE A1.LOC_ID = A2.ID;
Bound field: 1

I can submit that command directly to the MySQL server and it gets executed without any error:

Code: Select all

mysql> SELECT CONCAT(A1.FIRSTNAME,' ',A1.LASTNAME,' (',A2.NAME,')'),A1.ID  FROM ACM A1, LOC A2 WHERE A1.LOC_ID = A2.ID; Empty set (0.00 sec)

(the DB tables are empty as I first want to build some forms for data input).

BUT: when I open the form, I receive the following error messages/infos:
A window pops up saying: "The data could not be loaded.
Unknown column 'ACM_ID' in 'where clause'"
Clicking on a softbutton [More] in that window, further information is revealed:
An error list with three entries is seen left with related info in the right window:

Error :
The data content could not be loaded.

Error :
SQL Status: 42S22
Error code: 1054

Unknown column 'ACM_ID' in 'where clause'

Information:
The SQL command leading to this error is:

SELECT CONCAT( `A1`.`FIRSTNAME`, ' ', `A1`.`LASTNAME`, ' (', `A2`.`NAME`, ')' ) FROM `ACM` AS `A1`, `LOC` AS `A2` WHERE ( `ACM_ID` = `ACM`.`ID` AND `ACM`.`LOC_ID` = `LOC`.`ID` ) AND ( 0 = 1 )

Interestingly, that SQL command differs from the one I have set in the Listbox properties and which could be executed without problems. The Sql statement reported in the error message gets obviously compiled by OO (the JDBC driver?); when I feed this to the MySQL server directly, I get the same error as OO:

Code: Select all

mysql> SELECT CONCAT( `A1`.`FIRSTNAME`, ' ', `A1`.`LASTNAME`, ' (', `A2`.`NAME`, ')' ) FROM `ACM` AS `A1`, `LOC` AS `A2` WHERE ( `ACM_ID` = `ACM`.`ID` AND `ACM`.`LOC_ID` = `LOC`.`ID` ) AND ( 0 = 1 );
ERROR 1054 (42S22): Unknown column 'ACM_ID' in 'where clause'

Of course does the table customer hold a field ACM_ID:

Code: Select all

mysql> DESCRIBE CUSTOMER;
+---------------+----------------+------+-----+---------+----------------+
| Field         | Type           | Null | Key | Default | Extra          |
+---------------+----------------+------+-----+---------+----------------+
| ID            | int(11)        | NO   | PRI | NULL    | auto_increment |
| COMPANY       | varchar(255)   | YES  |     | NULL    |                |
| COUNTRY_ID    | int(11)        | YES  | MUL | NULL    |                |
| PROVINCE      | varchar(255)   | YES  |     | NULL    |                |
| CITY          | varchar(255)   | YES  |     | NULL    |                |
| POSTCODE      | varchar(24)    | YES  |     | NULL    |                |
| ADDRESS       | varchar(255)   | YES  |     | NULL    |                |
| FIRSTNAME     | varchar(50)    | YES  |     | NULL    |                |
| LASTNAME      | varchar(60)    | YES  |     | NULL    |                |
| EMAIL         | varchar(255)   | YES  |     | NULL    |                |
| PHONELANDLINE | varchar(40)    | YES  |     | NULL    |                |
| PHONEMOBILE   | varchar(40)    | YES  |     | NULL    |                |
| FAX           | varchar(40)    | YES  |     | NULL    |                |
| ACM_ID        | int(11)        | YES  |     | NULL    |                |
| COMMENTS      | varchar(32766) | YES  |     | NULL    |                |
+---------------+----------------+------+-----+---------+----------------+
15 rows in set (0.03 sec)

Why does OO modify the SQL statement at all, and why does it change a valid SQL statement into an invalid one?
How could I achieve here what I want?

Thanks for any hints and advice.

Uwe

[r4zoli]

It is known bug for listboxes, in OOo 2.4 version see: http://user.services.openoffice.org/en/ ... =13&t=4463

[ufalke]

Sorry, but I can't see that the known bug causes the behaviour I see.
The report in viewtopic.php?f=13&t=4463 does not state any error message, it just says the list box remains empty.

Citing from http://qa.openoffice.org/issues/show_bug.cgi?id=88034:

* Summary: certain list boxes in forms do not fill there list from a database table
note: the "certain" in the issue summary refers to the fact that the problematic
list boxes are
- bound to a statement SELECT "column", "column" FROM "table"
(note the duplicate column name)
- are bound to a HSQL database
(probably other DB types are affected, too, but dBase for instance isn't)

My statement is certainly not of the described form (i.e. "SELECT A, A FROM B").
The issue http://qa.openoffice.org/issues/show_bug.cgi?id=88055, marked as duplicate issue to 88034, is described by:

* Summary: Listboxes not populated if attached to datafield after update to 2.4
...
The only difference between the listboxes that work and those that don't work.
Is that those that work are not directly linked to a Datafield (Datafield (in
Control properties/data) is blank).

If I choose a datafield from the select list (in Control properties/data), the
listbox that does show its items, suddenly doesn't show them....

To be sure, I deleted the data field entry in the properties of the listbox, but the error occurs also then. This is clearly different from the description for 88055 above.
And, for me at least, it appears also distinct from http://qa.openoffice.org/issues/show_bug.cgi?id=87836 (if you look for further duplicates


Thanks
Uwe

[Villeroy]

Why does OO modify the SQL statement at all, and why does it change a valid SQL statement into an invalid one?
How could I achieve here what I want?

Change "Type of list contents" in the listbox' properties from "Sql" to "Sql [Native]".

[ufalke]

Change "Type of list contents" in the listbox' properties from "Sql" to "Sql [Native]".

It makes no difference whether I set that to Sql, Sql[Native], or Query. Always the same behaviour.

[Villeroy]

Change "Type of list contents" in the listbox' properties from "Sql" to "Sql [Native]".

It makes no difference whether I set that to Sql, Sql[Native], or Query. Always the same behaviour.

Next try: Compose a working query (native or not) and use that query as source of your listbox.

[ufalke]

It turns funny now (and might give a clue why I saw a Sql statement in the error info different from the one I had entered):
I have set the Sql statement in the Listbox data section to
SELECT "LASTNAME","ID" FROM ACM;
but -- first surprise -- the error, including the reporting of the causing Sql statement, is unchanged (i.e. it still lists that "The SQL command leading to this error is: SELECT CONCAT( `A1`.`FIRSTNAME`, ' ', `A1`.`LASTNAME`, ' (', `A2 ...").

Next surprise: I removed the listbox completely from the form, and the error is still there.

I carefully expected the form for all data references in controls: there was just one more field just showing plain CUSTOMER.ACM_ID which I also removed: error still there.

Is there any way to display the logical structure of a form or debug its processing so that I could see where this wicked statement occurs from? Of course I am going to set up a new form from scratch hoping this time I will succedd straightforwardly, but still there must be a cause for that strange phenomenon which might be a OO bug which again deserves some attention ...

Uwe

[Villeroy]

Open the form in edit mode, get toolbar "Form Design" and there the "forms navigator", which provides a tree view on the collection of forms, subforms and controls.

[ufalke]

Open the form in edit mode, get toolbar "Form Design" and there the "forms navigator", which provides a tree view on the collection of forms, subforms and controls.

Very good point.
For some reasons, there was a (sub)form with the mentioned SQL statement in the content property which I found just now in that tree display of the Frame Navigator.

Having removed that unnecessary frame it works like a charm now. Thanks a lot for your patient help.

Uwe