[Solved] Query Syntax embedded versus backend db

Creating tables and queries

[Solved] Query Syntax embedded versus backend db

Postby gkick » Sat Aug 10, 2019 8:44 am

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
gkick
 
Posts: 268
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Query Syntax embedded versus backend db

Postby Villeroy » Sun Aug 11, 2019 4:18 pm

Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query Syntax embedded versus backend db

Postby gkick » Mon Aug 12, 2019 7:11 am

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 58 times
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
 
Posts: 268
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Query Syntax embedded versus backend db

Postby Villeroy » Mon Aug 12, 2019 9:52 am

This is the parameter query I stored as Query1 to be used with my Form1:
Code: Select all   Expand viewCollapse view
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 66 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query Syntax embedded versus backend db

Postby Villeroy » Mon Aug 12, 2019 4:49 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query Syntax embedded versus backend db

Postby Villeroy » Mon Aug 12, 2019 6:01 pm

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 67 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query Syntax embedded versus backend db

Postby gkick » Tue Aug 13, 2019 1:18 am

: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: 268
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Query Syntax embedded versus backend db

Postby gkick » Thu Aug 15, 2019 10:01 am

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
gkick
 
Posts: 268
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: [Solved] Query Syntax embedded versus backend db

Postby Villeroy » Thu Aug 15, 2019 5:53 pm

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   Expand viewCollapse view
SELECT DISTINCT DATABASE_VERSION( ) "HSQL_VERSION" FROM "INFORMATION_SCHEMA"."SYSTEM_TABLES"

Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests