[libreoffice-users] Re: LibréOffice Base Forms, PostgreSQL (JDBC) and Table Joins
Le 23/02/2014 16:05, Jim Seymour a écrit : Hi Jim, A sub-form would (could?) be presented, allowing one to alter the status table. Btw: When I try to associate hdwr_assets.status_id to hdwr_status.status_id using the Forms Wizard, execution results in Error: The data content could not be loaded. Error: SQL Status: 22023 The column index is out of range: 1, number of columns: 0. Information: The SQL command leading to this error is: SELECT * FROM public.hdwr_status WHERE ( status_id = :link_from_status_id ) How are your table indexes defined ? Are they multiple column indexes? Which type of UI control are you using in your form to display the result - Combobox or ListBox ? You might be better off trying to do this manually rather than via the wizard, i.e. in Form design mode using the Form Navigator to create your subforms and then binding the corresponding data fields to UI controls, as the subforms wizard hasn't always produced the expected results throughout the various versions of LO. At the moment, I can't tell whether this is a bug in the version of LO that you are using, or whether it is a combination of other factors (type of UI control in form, binding of data field to UI control, etc). Alex T -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LibréOffice Base Forms, PostgreSQL (JDBC) and Table Joins
Le 25/02/2014 10:00, Alex Thurgood a écrit : In fact, further reading of the post shows that people using OpenOffice.org 3.1, Apache OpenOffice 4.01 and LibreOffice have all had this problem. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LibréOffice Base Forms, PostgreSQL (JDBC) and Table Joins
Le 25/02/2014 10:08, Alex Thurgood a écrit : Hunting this down a bit more : https://issues.apache.org/ooo/show_bug.cgi?id=114280#c6 so it would appear that there should be an extra option in JDBC connection settings Advanced Settings dialog that allows you to switch on replace named parameters with '?' At least, that was the case for OOo 3.3.0. If it is no longer there, then that option was either deleted from the dialog for some reason, or the code that reads the available driver options fails (for some other reason) or else ignores that setting possibility and so is not present in the UI dialog. See also : https://issues.apache.org/ooo/show_bug.cgi?id=113631 If the UI dialog no longer allows this to be set, it might be a good idea to file a bug request and ask that it be reinstated, or else if the UI does allow it to be set, but that setting it doesn't work, then this would be another bug. Alternatively, it is possible to pass connection parameters directly in the connection string when setting up the database connection, e.g. by adding the connection parameter statement in the URL of the db connection wizard : ? Also check whether, as per Frank Schönheit's (former OOo Base dev) comment here : https://issues.apache.org/ooo/show_bug.cgi?id=113631#c19 the two ID fields have different INT types, which Frank believed to be responsible for triggering the linked ID display problem as reported by Steve Shelton : https://issues.apache.org/ooo/show_bug.cgi?id=113631#c17 Seeing as Frank's last report was made just before Oracle donated the code to the ASF and just around the time the LibreOffice was created. In comment 19, Frank reports that he is going to open a separate bug report for the failed display of JOIN linked data in a subform, but he may/may not have got around to that. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LibréOffice Base Forms, PostgreSQL (JDBC) and Table Joins
Le 25/02/2014 12:35, Alex Thurgood a écrit : More info, not sure whether it is of any use, or just adds to the confusion : From my understanding of reading this : http://stackoverflow.com/questions/19721238/callablestatement-with-parameter-names-on-postgresql and several other related posts, it appears that the JDBC4 postgresql driver does not implement named parameter support because it is not supported at the protocol level by the back end. Also see this discussion in relation to ISNULL parameter type testing: http://postgresql.1045698.n5.nabble.com/Bug-Cannot-pass-null-in-Parameter-in-Query-for-ISNULL-td5034727.html Apologies to all if I appear to be going off-topic. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LibréOffice Base Forms, PostgreSQL (JDBC) and Table Joins
Le 22/02/2014 19:11, Jim Seymour a écrit : Hi Jim, For starters: LibréOffice 3.5.7.2, Build ID: 350m1 (Build: 2) Before anybody suggests a newer version: I'm currently on Ubuntu 10.04 LTS (Lucid Lynx), so I'm stuck with it. (Even my much newer Linux Mint desktop install at work is still 3.x, I believe.) You should be able to install newer versions even on Ubuntu 10.04 LTS, if you download them from the LibreOffice website. Of course, they won't necessarily have full desktop integration, although Unity came after 10.04, so it should be OK. As for Linux Mint, I have Mint 15 and 16 on two separate machines. Both have (at least I think they do) more recent packaged versions of LO in their repos than 3.5.7 (Mint 16 : LO 4.1.3.2). Anyway, as that is not necessarily an immediate solution to your problem, on to the meat of the question : I have a database with a couple tables, as in create table hdwr_assets ( atag text primary key, serno text unique, status_id int references hdwr_status, . . . ); -- e.g.: deployed, in stock create table hdwr_status ( status_id serial primary key, descr textnot null, ); The plan was to create a form that could query and alter hardware assets, selecting values for status_id from a drop-down list, the available selection being from the hardware status table. Does the LEFT JOIN query with a single join actually run from the Query Designer or do you get an error ? Does it make any difference if you turn off/on the Direct SQL button (which causes the query to bypass the internal LO SQL parser, or alternatively, ask it to interpret the query before fielding it to the JDBC driver) ? What are the settings in the Advanced Options of your db connection for that ODB file ? Which driver version of JDBC are you using ? Which version of postgres ? Alex (yes, yet another) -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: LibréOffice Base Forms, PostgreSQL (JDBC) and Table Joins
On Mon, 24 Feb 2014 11:00:45 +0100 Alex Thurgood alex.thurg...@gmail.com wrote: Le 22/02/2014 19:11, Jim Seymour a écrit : Hi Jim, For starters: LibréOffice 3.5.7.2, Build ID: 350m1 (Build: 2) Before anybody suggests a newer version: I'm currently on Ubuntu 10.04 LTS (Lucid Lynx), so I'm stuck with it. (Even my much newer Linux Mint desktop install at work is still 3.x, I believe.) You should be able to install newer versions even on Ubuntu 10.04 LTS, if you download them from the LibreOffice website. Of course, they won't necessarily have full desktop integration, although Unity came after 10.04, so it should be OK. I followed the instructions for adding the proper repository and installed with apt-get. 3.5.7.2 is what it gave me. As for Linux Mint, I have Mint 15 and 16 on two separate machines. Both have (at least I think they do) more recent packaged versions of LO in their repos than 3.5.7 (Mint 16 : LO 4.1.3.2). I have Mint 13 at work. It isn't all *that* old. Only installed about a year ago, if memory serves. Yes: I see 15 and 16 are now available. Wow. Anyway: 3.5.7.2 at work, on my Mint 13 machine, as well. I have 4.1.something under MS-Win on my work laptop. Haven't played with that, yet. (Have yet to work out the self-signed CA cert with keytool under MS-Win.) Anyway, as that is not necessarily an immediate solution to your problem, on to the meat of the question : I have a database with a couple tables, as in create table hdwr_assets ( atag text primary key, serno text unique, status_id int references hdwr_status, . . . ); -- e.g.: deployed, in stock create table hdwr_status ( status_id serial primary key, descr textnot null, ); The plan was to create a form that could query and alter hardware assets, selecting values for status_id from a drop-down list, the available selection being from the hardware status table. Does the LEFT JOIN query with a single join actually run from the Query Designer or do you get an error ? The left join works in Query Designer. I get the same thing as I would with an ad hoc query via psql. Does it make any difference if you turn off/on the Direct SQL button (which causes the query to bypass the internal LO SQL parser, or alternatively, ask it to interpret the query before fielding it to the JDBC driver) ? I did not try those things. Didn't know about them. I'll look into it. What are the settings in the Advanced Options of your db connection for that ODB file ? Edit - Advanced Settings - Database - Special Settings, perhaps? Options checked are: Ignore the privileges from the database driver Use catalog name in SELECT statements Use schema name in SELECT statements Create index with ASC or DESC statement Form data input checks for required fields Use ODBC conformant data/time literals Comparison of Boolean values: Default Which driver version of JDBC are you using ? Which version of postgres ? PostgreSQL v8.4.19 w/postgresql-jdbc4-9.1.jar at work PostgreSQL v8.4.19 w/postgresql-jdbc3-8.4.jar at home Behaviour is the same at both locations. Alex (yes, yet another) LOL! Thanks for the follow-up, Alex the III :) Regards, Jim -- Note: My mail server employs *very* aggressive anti-spam filtering. If you reply to this email and your email is rejected, please accept my apologies and let me know via my web form at http://jimsun.LinxNet.com/contact/scform.php. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
