[Solved] Query Syntax embedded versus backend db

Creating tables and queries
Post Reply
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

[Solved] Query Syntax embedded versus backend db

Post by gkick »

Hi,

are there known differences in the sql syntax between embedded and HSQL backend ? I am recreateing queries and Views for the backend and seem to have some issues with date and boolean type fields as well as the following:

Working criteria syntax used in embedded frontend ViewContactsSearchResults using GUI

LIKE IFNULL( ( SELECT '%' || UCASE( [SearchItem] ) || '%' FROM [tblFindContacts] ), UCASE( [LastName] ) )

the same in the split database results in this

'LIKE IFNULL( ( SELECT ''%'' || UCASE( [SearchItem] ) || ''%'' FROM [tblFindContacts] ), UCASE( [LastName] ) )'

not producing any error but not showing a recordset, removing the extra bits does not help

Thanks for your thoughts
Last edited by Hagar Delest on Thu Aug 15, 2019 10:47 am, edited 1 time in total.
Reason: tagged solved
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query Syntax embedded versus backend db

Post by Villeroy »

Code: Select all

SELECT ... WHERE UCASE("LastName") LIKE  '%' || UCASE(COALESCE( "SearchItem", "LastName" ) ) || '%'
Something like this: viewtopic.php?t=19440&p=88695#p88695 :?:
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Query Syntax embedded versus backend db

Post by gkick »

Hi and thank you Villeroy,
but no as I am sort of trying to get a searchform working which works perfectly in the embedded version but I don t seem to be able to replicate it in the backend version. The sql code is some kilometers long therefor using the gui. If time permits you can see the ViewContactsSearchResults in the attached mockup db which utilises a fair bit of the media db. The code in question is within ViewContactsSearchresults which feeds the ContactsSearchForm. The menu form also contains the output of a union query which also refuses to work in the backend.
Thanks again for your help
On another note, would github be ok for the backend frontend approach, I use it extensively for my flightgear project for version control and debugging. But in the db all is in just one file???
Gerhard
Attachments
MyPA_dev.odb
mockup db
(126.67 KiB) Downloaded 246 times
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query Syntax embedded versus backend db

Post by Villeroy »

This is the parameter query I stored as Query1 to be used with my Form1:

Code: Select all

SELECT DISTINCT "B"."nid", "A"."LastName" FROM "ViewContactsAll"AS"A","tblFindContacts"AS"B"
WHERE "B"."nid"=0 AND (
    UCASE( "A"."LastName" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."FirstName" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."MiddleName" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."MiddleName" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."Contact" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."NickName" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."2ndLastName" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."DateOfBirth" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."Gender" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."Languages" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."Origin" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."Predicaments" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."Predicaments" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."City" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."Region" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."Country" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."NickName" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."Profession" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."GeneralNotes" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."Origin" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."RsType" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."Type" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."Status" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."LifeCycle" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR UCASE( "A"."Faith" ) LIKE '%' || UCASE( :pSearchItem ) || '%'
    OR :pSearchItem IS NULL
)
General recipe for working filter forms free of silly macro code: viewtopic.php?f=13&t=98623&p=474172#p474172
 Edit: I stripped your other forms because of the file size 
 Edit: You could have editable records in the filtered forms if you would use single tables rather than views and sub-subforms for related table data. OR if you would use views and sub-subforms for editable table records 
Attachments
MyPA_dev_Villeroy.odb
(59.12 KiB) Downloaded 270 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query Syntax embedded versus backend db

Post by Villeroy »

When I convert this database to HSQL2, I get an error in ViewContactsAll because there are one or more inadequate data types such as "BirthDate". LIKE and UPPER fails with non-text types.
See http://www.hsqldb.org/doc/2.0/guide/dat ... collations about how to avoid this problem by making the whole database case-insensitive.
 Edit: The conversion of my above linked database to HSQL2 worked after I removed all views execpt for "ViewContactsAll" and after removing all references to the DateOfBirth column in that view and in Query1. 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query Syntax embedded versus backend db

Post by Villeroy »

The attached zip contains a full HSQL2 database without driver. The driver is too big to upload.
1. Unzip to a trusted directory where document macros are allowed to run. It contains a self-installing routine to connect the database document with its database and driver.
2. Add a copy of or link to hsqldb.jar to the driver directory. I used the latest version 2.5.
3. Open the database document. The macro should end with a success message or some descriptive error.

SInce everything is read-only anyway, I changed the parameter query to a normal query.
I removed the references to the birth date column in view and query.
SET DATABASE COLLATION "SQL_TEXT_UCC" makes everything case-insensitive so I could eliminate the use of UPPER. According to the documentation, you can also combine human language with UUC as in SET DATABASE COLLATION "English UCC", however I could not make this work.
Attachments
MyPA_dev_HSQL2.5.zip
(65.92 KiB) Downloaded 254 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Query Syntax embedded versus backend db

Post by gkick »

:D Thank you so much, now I can roll up my sleeves. Very much appreciated. Cheers
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Query Syntax embedded versus backend db

Post by gkick »

Thanks again, all working like a treat, just a couple of questions, how do I get the collation and version View into my db as it seems a view can not be copied nor renamed?
The other more important question - since LIKE only is applicable for text data, how would one interrogate date and or boolean datatype? Even the inbuild searchform only seems to handle text.
Saludos
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Query Syntax embedded versus backend db

Post by Villeroy »

You find all the view definitions in the script file. Search for lines with CREATE VIEW (column list) AS SELECT statement

The 2 views in question can be stored as queries as well:

Code: Select all

SELECT DISTINCT DATABASE_VERSION( ) "HSQL_VERSION" FROM "INFORMATION_SCHEMA"."SYSTEM_TABLES"

Code: Select all

SELECT INFORMATION_SCHEMA.COLLATIONS.COLLATION_CATALOG,INFORMATION_SCHEMA.COLLATIONS.COLLATION_SCHEMA,INFORMATION_SCHEMA.COLLATIONS.COLLATION_NAME,INFORMATION_SCHEMA.COLLATIONS.PAD_ATTRIBUTE FROM INFORMATION_SCHEMA.COLLATIONS
The inbuilt search form should handle ISO dates (yyyy-m-d) properly.
For the other question see download/file.php?id=38835 Form "FilterData" demonstrates how to filter by optional from...until dates ignoring missing data.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply