Linux Archive

Linux Archive (http://www.linux-archive.org/)
-   Kubuntu User (http://www.linux-archive.org/kubuntu-user/)
-   -   Off Topic: OpenOffice.org Base HSQLD Help (http://www.linux-archive.org/kubuntu-user/26127-off-topic-openoffice-org-base-hsqld-help.html)

Larry Hartman 01-01-2008 10:09 AM

Off Topic: OpenOffice.org Base HSQLD Help
 
I have a table that holds generic personal info:

TABLE1:
AUTHID: INTEGER auto-indexed, key
LNAME: VARCHAR
FNAME: VARCHAR
MNAME: VARCHAR

I have LISTBOX1 in a subform that uses QUERY1 for listing contents.

QUERY1 has this HSQLDB code in it:

SELECT CASE WHEN "MNAME" <> ' THEN CONVERT("AUTHID",
VARCHAR) || '-' || "LNAME" || ', ' || "FNAME" || ' ' || "MNAME" ELSE
CONVERT("AUTHID", VARCHAR) || '-' || "LNAME" || ', ' || "FNAME" END AS "NAME"
FROM "TBL-AUTHORS"

The HSQLDB code converts the "AUTHID" INTEGER to VARCHAR and concatenates it
with LNAME, FNAME, and MNAME and places the results into a single VARCHAR
field NAMEID. The codes has a test to determine if MNAME is empty. I tested
QUERY1 by itself and it appears functional.

The LISTBOX1 is intended to take output from QUERY1 for its list. When
selected information will go into NAMEID field of TABLE2.

Despite the fact that QUERY1 works by itself, LISTBOX1 will not load its list
when pointed to QUERY1 as its list source. Neither will it update its list
if I use the code from QUERY1 as an SQL [Native] statement. I have also
tried making a table view of QUERY1 as TABLE3. When I set LISTBOX1 to source
its list to TABLE3, I still get an empty list.

Any ideas what I am missing here?

Larry

--
kubuntu-users mailing list
kubuntu-users@lists.ubuntu.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/kubuntu-users

Larry Hartman 01-01-2008 08:34 PM

Off Topic: OpenOffice.org Base HSQLD Help
 
On Tuesday 01 January 2008 03:09:09 am Larry Hartman wrote:
> I have a table that holds generic personal info:
>
> TABLE1:
> AUTHID: INTEGER auto-indexed, key
> LNAME: VARCHAR
> FNAME: VARCHAR
> MNAME: VARCHAR
>
> I have LISTBOX1 in a subform that uses QUERY1 for listing contents.
>
> QUERY1 has this HSQLDB code in it:
>
> SELECT CASE WHEN "MNAME" <> ' THEN CONVERT("AUTHID",
> VARCHAR) || '-' || "LNAME" || ', ' || "FNAME" || ' ' || "MNAME" ELSE
> CONVERT("AUTHID", VARCHAR) || '-' || "LNAME" || ', ' || "FNAME" END AS
> "NAME" FROM "TBL-AUTHORS"
>
> The HSQLDB code converts the "AUTHID" INTEGER to VARCHAR and concatenates
> it with LNAME, FNAME, and MNAME and places the results into a single
> VARCHAR field NAMEID. The codes has a test to determine if MNAME is empty.
> I tested QUERY1 by itself and it appears functional.
>
> The LISTBOX1 is intended to take output from QUERY1 for its list. When
> selected information will go into NAMEID field of TABLE2.
>
> Despite the fact that QUERY1 works by itself, LISTBOX1 will not load its
> list when pointed to QUERY1 as its list source. Neither will it update its
> list if I use the code from QUERY1 as an SQL [Native] statement. I have
> also tried making a table view of QUERY1 as TABLE3. When I set LISTBOX1 to
> source its list to TABLE3, I still get an empty list.
>
> Any ideas what I am missing here?
>
> Larry

As an answer to my earlier question above for anyone looking to load a single
column list box with data from multiple columns. I placed the code below in
a BASE Query, then assigned that query as the source of the listbox.

SELECT COALESCE( "TBL-AUTHORS"."LNAME", ' ) + CHAR( 44 ) + CHAR ( 32 ) +
COALESCE( "TBL-AUTHORS"."FNAME", ' ) + CHAR( 32 ) +
COALESCE( "TBL-AUTHORS"."MNAME", ' ) AS "NAMEID", "AUTHID"
FROM "TBL-AUTHORS" ORDER BY "NAMEID" ASC

The listbox shows a singel column with all three fields LNAME, FNAME, and
MNAME. The AUTHID field is not shown but loaded. The bound property of the
Listbox is set to "1" and looks toward "AUTHID" for the listbox value when
selection is made. Listbox apparently does not recognize any SQL concatenate
function statements....I don't know the internals well enough to understand
why. CHAR ( 44) is a comma, CHAR( 32 ) is a space.



Larry

--
kubuntu-users mailing list
kubuntu-users@lists.ubuntu.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/kubuntu-users

Donn 01-01-2008 08:47 PM

Off Topic: OpenOffice.org Base HSQLD Help
 
> concatenate function statements....I don't know the internals well enough
> to understand why. CHAR ( 44) is a comma, CHAR( 32 ) is a space.
I don't know OOO Base from a hole in the ground, but those chars are ascii
values. 32 is space, 44 a comma, 65 is A, 66 B and so on.

d

--
kubuntu-users mailing list
kubuntu-users@lists.ubuntu.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/kubuntu-users

Larry Hartman 01-02-2008 12:38 AM

Off Topic: OpenOffice.org Base HSQLD Help
 
On Tuesday 01 January 2008 01:47:44 pm Donn wrote:
> > concatenate function statements....I don't know the internals well enough
> > to understand why. CHAR ( 44) is a comma, CHAR( 32 ) is a space.
>
> I don't know OOO Base from a hole in the ground, but those chars are ascii
> values. 32 is space, 44 a comma, 65 is A, 66 B and so on.
>
> d

I assumed as much but nothing definitive to tell me.

</RANT>

Took me three hours or so today to find a sample code for making a button open
another form. While I wish not to say this, it does appear that MS VBA is
far easier and more intuitive to learn than Star Basic, and the MS
documentation is much better structured and easier to digest....such is the
dismal state of Star Basic used in Open Office.

</END RANT>

--
kubuntu-users mailing list
kubuntu-users@lists.ubuntu.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/kubuntu-users

Larry Hartman 01-02-2008 12:43 AM

Off Topic: OpenOffice.org Base HSQLD Help
 
On Tuesday 01 January 2008 05:38:52 pm Larry Hartman wrote:
> On Tuesday 01 January 2008 01:47:44 pm Donn wrote:
> > > concatenate function statements....I don't know the internals well
> > > enough to understand why. CHAR ( 44) is a comma, CHAR( 32 ) is a
> > > space.
> >
> > I don't know OOO Base from a hole in the ground, but those chars are
> > ascii values. 32 is space, 44 a comma, 65 is A, 66 B and so on.
> >
> > d
>
> I assumed as much but nothing definitive to tell me.
>
> </RANT>
>
> Took me three hours or so today to find a sample code for making a button
> open another form. While I wish not to say this, it does appear that MS
> VBA is far easier and more intuitive to learn than Star Basic, and the MS
> documentation is much better structured and easier to digest....such is the
> dismal state of Star Basic used in Open Office.
>
> </END RANT>


--
kubuntu-users mailing list
kubuntu-users@lists.ubuntu.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/kubuntu-users

Larry Hartman 01-02-2008 12:49 AM

Off Topic: OpenOffice.org Base HSQLD Help
 
On Tuesday 01 January 2008 05:38:52 pm Larry Hartman wrote:
> On Tuesday 01 January 2008 01:47:44 pm Donn wrote:
> > > concatenate function statements....I don't know the internals well
> > > enough to understand why. CHAR ( 44) is a comma, CHAR( 32 ) is a
> > > space.
> >
> > I don't know OOO Base from a hole in the ground, but those chars are
> > ascii values. 32 is space, 44 a comma, 65 is A, 66 B and so on.
> >
> > d
>
> I assumed as much but nothing definitive to tell me.
>
> </RANT>
>
> Took me three hours or so today to find a sample code for making a button
> open another form. While I wish not to say this, it does appear that MS
> VBA is far easier and more intuitive to learn than Star Basic, and the MS
> documentation is much better structured and easier to digest....such is the
> dismal state of Star Basic used in Open Office.
>
> </END RANT>

For anyone interested....below is the code snippet I found in the
openoffice.org foums for opening another form in Base via a button planted on
one form. Just replace the Sub lien with you own name and
the "FRM-DOCUMENTS" with your form's name. I don't know how it all works
yet, but it does seem to work well.

Larry

================================================== ==

Sub OpenFormDocuments
OpenForm(getFormsTC, getConnectionTC,"FRM-DOCUMENTS")
End Sub

Function OpenForm(formContainer as variant, oConnection as variant, sFormName
as string) as variant
Dim aProp(1) As New com.sun.star.beans.PropertyValue
aProp(0).Name = "ActiveConnection"
aProp(0).Value = oConnection
aProp(1).Name = "OpenMode"
aProp(1).Value = "open"
OpenForm = formContainer.loadComponentFromURL(sFormName,"_bla nk",0,aProp())
End Function

Function getFormsTC() as variant
getFormsTC = thisComponent.Parent.getFormDocuments
End Function

Function getConnectionTC() as variant
getConnectionTC = thisComponent.Drawpage.Forms(0).ActiveConnection
End Function

--
kubuntu-users mailing list
kubuntu-users@lists.ubuntu.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/kubuntu-users


All times are GMT. The time now is 01:37 PM.

VBulletin, Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO ©2007, Crawlability, Inc.