[Closed] Using 'OR' in Direct SQL Query problems

[dreamquartz]

Hello All,

I have a simply Query

Code: Select all

SELECT
	 "vIDCheck".*,
	 "vEmailAddress".*
 FROM
	 "vEmailAddress",
	 "vIDCheck"
 WHERE
	 "vEmailAddress"."FKPersonID" = "vIDCheck"."PersonID" AND
	 (
		 "vEmailAddress"."EmailAddress" LIKE '%' || :EmailAddress || '%' OR
		 "vIDCheck"."Person" LIKE '%' || :Person || '%'
	 )

which runs fine in LO 7.4.3.2 and HSQL Split DataBase 2.6.1, but throws an error in DIRECT SQL

1: parameter marker not allowed at /home/buildslave/source/libo-core/connectivity/source/drivers/jdbc/Object.cxx:173

I can run

Code: Select all

SELECT
	 "vIDCheck".*,
	 "vEmailAddress".*
 FROM
	 "vEmailAddress",
	 "vIDCheck"
 WHERE
	 "vEmailAddress"."FKPersonID" = "vIDCheck"."PersonID" AND
	 (
		 "vIDCheck"."Person" LIKE '%' || :Person || '%'
	 )

in a DataBase Manager, called DBeaver, and can even run something like this:

Code: Select all

SELECT
	 "vIDCheck".*,
	 "vEmailAddress".*
 FROM
	 "vEmailAddress",
	 "vIDCheck"
 WHERE
	 "vEmailAddress"."FKPersonID" = "vIDCheck"."PersonID" AND
	 (
		 "vIDCheck"."Nickname Surnames" LIKE '%' || :Person || '%' OR
		 "vIDCheck"."Person" LIKE '%' || :Person || '%'
	 )

as RAW SQL.

A few questions therefore to help me understand.
1. Why does the 1st Query run fine in the split DataBase, and throws an error using TOOLS->SQL...?
2. Is DIRECT SQL the same as RAW SQL?
3. If DIRECT SQL is NOT the same as RAW SQL, what is then the function of Tools->SQL...?
3. How and where to find the compiled version of a Query, like the one above in LibreOffice? I want to understand the 'inner' workings of that process.
4. Why can a UserInput like ':Person' be used in RAW SQL (therefore in DBeaver) and not in DIRECT SQL if they are the 'same'?

Thanks in advance,

Dream

[UnklDonald418]

1. Why does the 1st Query run fine in the split DataBase, and throws an error using TOOLS->SQL...?

When you run your query in the split database, the Base front end does the parameter (:Person) substitution before passing the query to HSQL 2.6.1
When you run the query in Tools>SQL you bypass Base and the query is sent Direct ly to the HSQL back end. Since HSQL doesn't support parameters it doesn't know what to do with :Person thus the failure.

[Mountaineer]

I will start with your
first 3) There is more than queries possible. For example ALTER TABLE - statement. These you can enter in Tools->SQL

1) In direct-SQL-mode the commands you type are not altered by base (even the formatting is kept), so you might use commands, supported by the database, but not by Base max this be a very complex query or a non-standard function of the database.
On the other hand: Parameters are not replaced by Base, so :Person is not possible in direct-SQL with Base

4) Different Programs, different behaviour. DBeaver obviously supports :param-replacement. There is no rule to force AOO/LO to do the same. So you may conclude:
2) names and modes are different.

Whats left is your
second 3) where I'd suggest you open a query in SQL mode, if you seek the sql-version as text. However you will not find a compiled version. You can try the EXPLAIN-command in SQL. Databases show then how they access the givven query...

[dreamquartz]

Just throwing a curveball,

Code: Select all

SELECT
	 "vIDCheck".*,
	 "vEmailAddress".*
 FROM
	 PUBLIC."vIDCheck",
	 PUBLIC."vEmailAddress"
 WHERE
	 "vIDCheck"."PersonID" = "vEmailAddress"."FKPersonID" AND
	 (
	 	 "vEmailAddress"."EmailAddress" LIKE '%' || :Person || '%' OR
		 "vIDCheck"."Person" LIKE '%' || :Person || '%'
	 )

does run in DBeaver with the correct resltset

Dream

[dreamquartz]

1. Why does the 1st Query run fine in the split DataBase, and throws an error using TOOLS->SQL...?

When you run your query in the split database, the Base front end does the parameter (:Person) substitution before passing the query to HSQL 2.6.1
When you run the query in Tools>SQL you bypass Base and the query is sent Direct ly to the HSQL back end. Since HSQL doesn't support parameters it doesn't know what to do with :Person thus the failure.

Would like to know how that part, the 'substitution', works.
The compiler makes a distinction apparently, and now that it turns out that changing all UserInput to the same variable (:Person) the Query does show the correct resultset in DBeaver.
I am now wondering if that 'substitution' is doing something similar, being: changing all provided variables in the Query as Userinput and make it the just 'one and the same', when the Query is run in the Split DataBase.

Need to dig into this further.
Might be a Question for the LO development teams, or if anyone has a better suggestion.

Dream

[Mountaineer]

...
, or if anyone has a better suggestion.

You will find "power-filters", where a separate filter-table is used often in this forum. This works also in direct-SQL as this table is a part of the database, and not provided by the frontend Base/DBeaver/... But you have to create a form......

[dreamquartz]

...
, or if anyone has a better suggestion.

You will find "power-filters", where a separate filter-table is used often in this forum. This works also in direct-SQL as this table is a part of the database, and not provided by the frontend Base/DBeaver/... But you have to create a form......

The thing is the Client has been shown something like

Screenshot from 2022-12-27 23-18-55.png (19.96 KiB) Viewed 3718 times

and that is just part of the 'Parameter Input'.

We are trying to provide an alternative to LO for accessing the Split DataBase, designed in HSQLDB.
What the picture shows are all the different 'parameters' the User can use. It simplifies the principle of finding related info.

[dreamquartz]

@Mountaineer and @UnklDonald418
Maybe I have to re-phrase the original question into a question related to 'Variables'.

Is it possible to show the User still something like the picture above, but still all have the same

Code: Select all

LIKE '%'|| :Person || '%'

parameter in the background?

[Mountaineer]

@Mountaineer and @UnklDonald418
Maybe I have to re-phrase the original question into a question related to 'Variables'.

Is it possible to show the User still something like the picture above, but still all have the same

Code: Select all

LIKE '%'|| :Person || '%'

parameter in the background?

At first: What is a "variable" concerning a SQL query? Obviously not the same as I'm used to in programming languages like BASIC, C, Python...

If you only consider your cited LIKE-statement, then it is not possible, as your client is not only selecting the term to search for

Code: Select all

LIKE 'dream'

, but also the column (as I read the dialogue.
So the result of the dialogue is to be translated to for example

Code: Select all

Address LIKE '%'|| :input || '%'

and selecting the field to search is not possible with parameters like :input.

Solutions for this:

• Cheating a bit: If you append fields first like

  Code: Select all

  SELECT Name || Address AS NamAdr

  you could search in the combined field. But you would not know, if you found George Washington, Washington Blvd or the City/state when you search Washington. So often not desired.
• Power filters again: You have there a form, so you are free to select or enter one or mor fields
• When you use a macro to prepare the SQL-statement all parts of the query are variable (not a variable, but the text of the SQL-statement may be stored in a variable as string). But remember xkcd: You are responsible to sanitize your input. https://xkcd.com/327/

[UnklDonald418]

SQL is an interpreted language, so the DBMS compiles the statement at run time. Base plays no part in that.
When you execute your query in Base

Code: Select all

SELECT
         "vIDCheck".*,
         "vEmailAddress".*
 FROM
         "vEmailAddress",
         "vIDCheck"
 WHERE
         "vEmailAddress"."FKPersonID" = "vIDCheck"."PersonID" AND
         (
                 "vEmailAddress"."EmailAddress" LIKE '%' || :EmailAddress || '%' OR
                 "vIDCheck"."Person" LIKE '%' || :Person || '%'
         )

and the user types abc in the parameter dialog
Base copies the user input from the parameter dialog and in this case substitutes the string 'abc' for :Person and the query becomes

Code: Select all

SELECT
         "vIDCheck".*,
         "vEmailAddress".*
 FROM
         "vEmailAddress",
         "vIDCheck"
 WHERE
         "vEmailAddress"."FKPersonID" = "vIDCheck"."PersonID" AND
         (
                 "vEmailAddress"."EmailAddress" LIKE '%' || :EmailAddress || '%' OR
                 "vIDCheck"."Person" LIKE '%' || 'abc' || '%'
         )

then Base sends the modified command to the DBMS using the OO/LO Statement Service
https://www.openoffice.org/api/docs/com ... ement.html

Filtering the data using a dialog like you show might prove to be quite a challenge.

I concur with Mountaineer's recommendation to try power filtering. Look at this Example which uses a filter table and text box controls for input.
https://forum.openoffice.org/en/forum/v ... 64#p198364
The example uses up to 4 parameters but I have used that approach to filter on 6, so a few more should be fairly straightforward.

[dreamquartz]

@UnklDonald418

Indeed this is what you would expect

Code: Select all

SELECT
         "vIDCheck".*,
         "vEmailAddress".*
 FROM
         "vEmailAddress",
         "vIDCheck"
 WHERE
         "vEmailAddress"."FKPersonID" = "vIDCheck"."PersonID" AND
         (
                 "vEmailAddress"."EmailAddress" LIKE '%' || :EmailAddress || '%' OR
                 "vIDCheck"."Person" LIKE '%' || 'abc' || '%')

However, in comparison to DBeaver, we are exploring, this is what happens:

Code: Select all

SELECT
         "vIDCheck".*,
         "vEmailAddress".*
 FROM
         "vEmailAddress",
         "vIDCheck"
 WHERE
         "vEmailAddress"."FKPersonID" = "vIDCheck"."PersonID" AND
         (
                 "vEmailAddress"."EmailAddress" LIKE '%' || 'abc' || '%' OR
                 "vIDCheck"."Person" LIKE '%' || 'abc' || '%')

Here all the variables are substituted by 'abc'.
This is a manual process, because if there is one variable ':Person' that was entered, the others are substituted by 'NULL' (done by the interpreter). As a result the Query does not RUN (General error), but by filling out 'abc' manually for all variables the Query does run.....

The only solution therefore, needing to do one UserInput, is to change all variables (see picture above) to 1, called (in my case) 'PersonInfo', with all the consequences.

This does make life more difficult for the User.....
They now have to think.....(LOL) what they need to fill out as a variable.

As indicated, this leads to potential serious, and not acceptable 'contamination' of the resultset, if you were e.g. only wanting to to search on 'Washington' if that were the 'Surname'. The resultset does now include the State of Washington, any cities/towns called 'Washington' and so on and so forth.....

My expectation is the 1st example, and definitely not the 2nd one.
What I do not know, if this is the Base compiler 'talking' for the 1st and DBeaver compiler 'talking' for the 2nd, or is it Base substituting before offering the info to the Base compiler and DBeaver substituting before offering this to the DBeaver compiler.

At one point the information is compiled and can be processed by SQL....

Dream

[RPG]

Maybe it is good idea to use form-based-filters. This is only possible when also the toolbar Form-Navigation is active. The user can type in his own search term. form-based-filters is also a button on that toolbar and change the form in a tool where you can type the search in each field.

[UnklDonald418]

I provided an answer your question about how the Base parser handles simple parameter substitution.
When using Base, filtering the data with a popup dialog often works for one or two parameters but beyond that the Form based filtering we have recommended is more versatile. Date pickers, Combo and List Boxes and other formatting options can aid the user in entering relevant parameters.
From your description it appears that dBeaver only supports a single parameter, but since this is a Base forum questions pertaining to any other front end should be directed to support devoted to that software.

[dreamquartz]

@RPG
I am working on that one. I want to make it easy use for the User.....

@UnklDonald418
I am indeed going that route as well. I placed it here because of the Base Layer addition in OO/LO, where is process where this part of the compile appears to take place.
The reason for DBeaver was that it works with more 'Raw' SQL, and therefore provide more in depth research.....

I would like to see if there are any similarities between a program like DBeaver and sqltool, but I am having issues with setting the last one up.
That is for a different topic, when time permits.

Dream

I consider this one closed. Thanks for the input/