[Solved] Query with user input

Discuss the database features

[Solved] Query with user input

Postby eristo » Sat Mar 02, 2013 5:06 pm

I would like to query my table with a user input, instead of 100+ query each with different variable.

1) I have tried
Code: Select all   Expand viewCollapse view
SELECT "field1", "field2" FROM "mytable" WHERE "field1" LIKE :input

there is no error if I select run SQL directly, but nothing happens, there is no prompt for user input, just an empty table.

2) and I would like to do the same with a form, I have added a text box and a table, how do I put the query in the table and make it show different result depending on what is written on the text box?

thanks in advance

 Edit: update 1:
I know SQL syntax, but have almost no knowledge of openoffice base interface. so I would need a more detailed answer when it's not about the syntax 


 Edit: update 2:
please, do not mind the LIKE clause, I only want to know how to have a user input, how can I have a more dynamic query that can accept input instead of a static query that always give me the same result (until I change the table).
and I will probably need a tutorial on how to make forms, but that's an other topic. 
Last edited by eristo on Sat Mar 02, 2013 11:55 pm, edited 3 times in total.
openoffice 3.3 on windows 7 home
eristo
 
Posts: 8
Joined: Sat Mar 02, 2013 4:55 pm

Re: query with user input

Postby Villeroy » Sat Mar 02, 2013 5:33 pm

Base ignores any direct query and passes the query string directly to the database engine. The database does not understand named parameters such as :input.
You can use the parameter names in subforms. Bind the parent form's master field to the subform's slave field.
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.x
User avatar
Villeroy
Volunteer
 
Posts: 26874
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: query with user input

Postby Nocton » Sat Mar 02, 2013 6:41 pm

You should make up a query putting the parameter in the Criterion of the field. Thus below I give the SQL for querying a membership database inputting the JoinDate field value when it runs.
Code: Select all   Expand viewCollapse view
SELECT "FirstName", "LastName", "JoinDate" FROM "MemberDetails" AS "MemberDetails" WHERE "JoinDate" >= :StartDate

Regards,
Nocton
Last edited by Nocton on Fri Jan 10, 2014 10:13 am, edited 1 time in total.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 504
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: query with user input

Postby eristo » Sat Mar 02, 2013 8:42 pm

@Villeroy
can you tell me in more detail how to bind? or just give me a link if it's too much.

@Nocton
I don't see the difference between your statement and mine
openoffice 3.3 on windows 7 home
eristo
 
Posts: 8
Joined: Sat Mar 02, 2013 4:55 pm

Re: query with user input

Postby Sliderule » Sat Mar 02, 2013 9:25 pm

Please try the various Select statements.

Code: Select all   Expand viewCollapse view
-- Command below will ONLY find the value in "field1" if it ENDS in the value of :input
SELECT "field1", "field2" FROM "mytable" WHERE "field1" LIKE '%' || :input


-- Command below will ONLY find the value in "field1" if it STARTS with the value of :input
SELECT "field1", "field2" FROM "mytable" WHERE "field1" LIKE :input || '%'


-- Command below will ONLY find the value in "field1" if it STARTS, ENDS, or MIDDLE in the value of :input
SELECT "field1", "field2" FROM "mytable" WHERE "field1" LIKE '%' || :input || '%'


Explanation:
  1. When using a LIKE clause, the wild-card character follows SQL standards, and, it is % . Now, depending whether you want to 'find' the values, when it is AT THE START, AT THE END, or, ANYWHERE in the value, depends how it ( the wild-card ) is placed. :super:


  2. Also, it is important to note, that, this will ONLY work, when the Query is NOT run in directly mode. When a Query is run in direct mode, the database FRONT-END ( OpenOffice / LibreOffice Base ) does not do anything, except, send the query, exactly as written DIRECTLY to the database engine ( database BACK-END ). But, you want Base to first PROCESS the Query, and, PROMPT the user for input, therefore, the Query MUST be run with the 'default' Base Parser, so, Base will open the prompt for user input, and, modify the written query to include the characters added by the user. :super:


  3. Alternatively, if you do NOT want to 'include' the wild-card value ( '%' ) in the Query, the END-USER can include it, as appropriate, in the value entered, depending if the user wanted to find it at the START, MIDDLE, or END of the string. For example, an end-user input of: eris% would find the value, only if it STARTED with that input in the database table. ;)


  4. Also, as an FYI ( For Your Informtion ), IF, you have defined your 'text' field with field type . . . VARCHAR . . . the CASE ( UPPER / Mixed / lower ) entered by the user, must match EXACTLY as it is stored in the database table. For example, 'eristo', is NOT the same as 'Erist' , NOR, 'ERISo' .

    BUT, I always DEFINE my 'text' fields as, VARCHAR_IGNORECASE , so the HSQL database engine will return a TRUE, regardless of CASE. :bravo:

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Last edited by Sliderule on Sat Mar 02, 2013 9:50 pm, edited 1 time in total.
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: query with user input

Postby Villeroy » Sat Mar 02, 2013 9:47 pm

Oh no. I did not notice the LIKE operator.
So we answered the direct SQL question and the pattern matching with LIKE while my answer about the form-subform binding remains unclear.
Search this forum for "power filtering".
This is an example file of mine: http://user.services.openoffice.org/en/ ... hp?id=6442 (Berlin Streets).
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.x
User avatar
Villeroy
Volunteer
 
Posts: 26874
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: query with user input

Postby eristo » Sat Mar 02, 2013 10:07 pm

@Sliderule
thank you for telling me how complex LIKE clause is, I will stick with = (equal) until everything goes smooth and then change back to LIKE.
how do run with the default Base Parser? how do I enable it? where is this option?
if you mean disabling the "Run SQL command directly" button, it prompts "Syntax error in SQL expression".
openoffice 3.3 on windows 7 home
eristo
 
Posts: 8
Joined: Sat Mar 02, 2013 4:55 pm

Re: query with user input

Postby Sliderule » Sat Mar 02, 2013 10:34 pm

eristo wrote:how do run with the default Base Parser? how do I enable it? where is this option?

To make sure the Query is executed, so, the OpenOffice / LibreOffice Base Parser is FIRST EXECUTED, EITHER

  1. On the Query Toolbar, Do NOT Press the BUTTON, SQL with a Green check mark ( Run SQL command directly )

  2. From the Menu: Edit -> Run SQL command directly this should NOT have a check mark.
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: Query with user input

Postby eristo » Sat Mar 02, 2013 11:53 pm

something really weird happened

initially my statement was
Code: Select all   Expand viewCollapse view
SELECT "field1", "field2" FROM "mytable" WHERE "field1" LIKE :input

and I couldn't save it nor run it, it would always say "Syntax error in SQL expression".

then I change it to:
Code: Select all   Expand viewCollapse view
SELECT "field1", "field2" FROM "mytable" WHERE UPPER("field1") LIKE UPPER(:input) || '%'

and it worked without problems

I wanted to understand where I was wrong, so I deleted || '%' :
Code: Select all   Expand viewCollapse view
SELECT "field1", "field2" FROM "mytable" WHERE UPPER("field1") LIKE UPPER(:input)

it still works

so I delete UPPER() as well :
Code: Select all   Expand viewCollapse view
SELECT "field1", "field2" FROM "mytable" WHERE "field1" LIKE :input

and it still works

it's the same statement as before, I don't know why it says syntax error this morning but it's ok now. is it a bug? I am sure the spelling is the same
openoffice 3.3 on windows 7 home
eristo
 
Posts: 8
Joined: Sat Mar 02, 2013 4:55 pm

Re: Query with user input

Postby Sliderule » Sun Mar 03, 2013 12:09 am

I cannot guess why have experienced what you are saying, without having a copy of your database. The only thing I can just mention, is, what comes after the colon ( : ) must be ONE WORD ( no spaces ), and, only contain the characters a to z, A to Z, numbers 0 to 9, and, underscore character. For example, :input is OK, and, :Please_Enter_Search_String is OK ( no spaces ) . . . BUT . . . :Please Enter Search String is NOT OK, since it is contains a space ( not one word ). Likewise, :input_% is NOT OK ( contains special character % :( ).

I hope this helps, please be sure to let me / us know.

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Query with user input

Postby eristo » Mon Mar 04, 2013 3:13 pm

@Sliderule

I attached the database (before it suddenly start working), hope you can find the problem.
I don't see error in syntax but the query refuse to run

note: the query is saved with "run SQL directly" on because it won't let me save otherwise.

forgot to say thank you for attaching an example file, it was really helpful.
Attachments
db.odb
(3.58 KiB) Downloaded 315 times
openoffice 3.3 on windows 7 home
eristo
 
Posts: 8
Joined: Sat Mar 02, 2013 4:55 pm

Re: [Solved] Query with user input

Postby Sliderule » Mon Mar 04, 2013 3:57 pm

The reason for the error was because you were using a database RESERVERD word ( user . . . but . . . input is NOT a RESERVED database word ) for the prompt name, so, the database engine ( HSQL ) indicated the error.

I am attaching your database, with two Queries, modifying them slightly so it is NOT using a database ( HSQL ) reserved word, and it works exactly as expected.

Just as an FYI ( For Your Information ), OpenOffice / LibreOffice Base ( database FRONT-END ) will present the parameters to the user in ALPHABETICAL ORDER. Since, I want to be in control of the order the user should enter the input(s) . . . logical order . . . I always MAKE the prompts ALPHABETICAL by adding a number to the prompt, so it will come back in the sequence I want, and, the prompt will therefore, NEVER contains a database reserved word. :super:

For example:

Code: Select all   Expand viewCollapse view
Select
   *
From "MY_TABLE"
Where "MY_TABLE"."LAST_NAME" Like '%' || :Enter_01_Last_Name || '%'
  and "MY_TABLE"."FIRST_NAME" Like '%' || :Enter_02_First_Name || '%'
  and "MY_TABLE"."MY_DATE" Between :Enter_03_From_Date and :Enter_04_To_Date
Order By "MY_TABLE"."LAST_NAME",
         "MY_TABLE"."FIRST_NAME"


This way, the prompts will be presented to the user in the sequence ( order ) I want because it is ALPHABETICAL based on the number that is a part of the prompt.

db.odb
(3.53 KiB) Downloaded 353 times


My only comment is, this is the first time I have seen that happen, and, I can only add: "Fascinating!" :crazy: 8-) :bravo:

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Query with user input

Postby iqullc » Sun Jan 05, 2014 11:54 pm

Hello Sliderule,

Following the thread on the topic Query with user input...how would I apply this for a form?

BTW you provided some very useful Base information.

Thanks in advance,
Stephanie/iqullc
signature is Windows7 AOO 4.0.0
Windows7 AOO 4.0.0
iqullc
 
Posts: 58
Joined: Thu Mar 28, 2013 5:16 pm

Re: [Solved] Query with user input

Postby Sliderule » Mon Jan 06, 2014 12:16 am

If you do a search, in this forum, in the Form sub-section . . . for the words: parameter and query . . . as below:

https://forum.openoffice.org/en/forum/search.php?keywords=parameter+form&terms=all&author=&fid%5B%5D=39&sc=1&sf=all&sr=posts&sk=t&sd=d&st=0&ch=300&t=0&submit=Search

you can look at some of the results. :)

Since, I do not know what you tried, and, what happened when you tried it . . . I cannot help further . . . except to add, a Form can be based ( pun intended ) on a Query, and, if the Query included the Primary Key(s) . . . it can also be added to ( INSERT ), changed ( UPDATE ), or, removed ( DELETE ).

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Query with user input

Postby iqullc » Thu Jan 09, 2014 11:28 pm

Thanks Sliderule...I have not tried to use the query in a form yet. Following what you provided in the thread and the sample db you sent, just wondered if possible to do the same in a form for a more aesthetic look.

Stephanie
Windows7 AOO 4.0.0
iqullc
 
Posts: 58
Joined: Thu Mar 28, 2013 5:16 pm

Re: [Solved] Query with user input

Postby arfgh » Tue May 05, 2015 11:28 am

exist a way to predefine the variable instead of user input ? no basic.
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] Query with user input

Postby Sliderule » Tue May 05, 2015 3:36 pm

arfgh wrote:exist a way to predefine the variable instead of user input ? no basic.

yes

you may use a table, and that other table is included in the query ( from clause ), that contains one record, and, that one record is you what you are calling . . . "predefine the variable instead of user input".

thank-you for double posting :( . . . https://forum.openoffice.org/en/forum/v ... 13&t=76985
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Query with user input

Postby arfgh » Tue May 05, 2015 4:25 pm

i didnt undertand you, please, can you show it with simple example ?

i am asking about to set variables to be used on the same query, example 'var a = 5+5', and then i use the var 'a' on other parts of the query.
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] Query with user input

Postby Sliderule » Tue May 05, 2015 4:53 pm

arfgh wrote:i didnt undertand you, please, can you show it with simple example ?

i am asking about to set variables to be used on the same query, example 'var a = 5+5', and then i use the var 'a' on other parts of the query.

Code: Select all   Expand viewCollapse view
CREATE CACHED TABLE "MY_VARIABLE" ("MY_VALUE_ID" VARCHAR_IGNORECASE(5) DEFAULT '' NOT NULL PRIMARY KEY, "MY_INTEGER" INTEGER DEFAULT 0 NOT NULL)


Code: Select all   Expand viewCollapse view
INSERT INTO "MY_VARIABLE" VALUES('a',10)


Code: Select all   Expand viewCollapse view
Select
   "MY_TABLE".*,
   "MY_TABLE"."MY_VALUE" * "MY_VARIABLE"."MY_INTEGER" as "MY_CALCULATED_VALUE"
From "MY_TABLE",
     "MY_VARIABLE"
Where "MY_VARIABLE"."MY_VALUE_ID" = 'a'
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Query with user input

Postby arfgh » Tue May 05, 2015 5:26 pm

all that in the same single query command ? i dont think
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] Query with user input

Postby Sliderule » Tue May 05, 2015 5:30 pm

arfgh wrote:all that in the same single query command ? i dont think

Since, you "dont think" . . . I do not need to proceed further.
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Query with user input

Postby arfgh » Tue May 05, 2015 9:30 pm

i have read out there information that i dont understand very well related to set variables within the sql query.
I see tht you create another table in the db to store there values, so that way it is different from my asking.
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] Query with user input

Postby arfgh » Wed May 06, 2015 1:11 pm

anybody knows if it is possible to do it ?
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests