[Solved] Query with user input

[eristo]

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

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.

[Villeroy]

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.

[Nocton]

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

SELECT "FirstName", "LastName", "JoinDate" FROM "MemberDetails" AS "MemberDetails" WHERE "JoinDate" >= :StartDate

Regards,
Nocton

[eristo]

@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

[Sliderule]

Please try the various Select statements.

Code: Select all

-- 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.
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.
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.

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.

[Villeroy]

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).

[eristo]

@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".

[Sliderule]

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.

[eristo]

something really weird happened

initially my statement was

Code: Select all

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

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

SELECT "field1", "field2" FROM "mytable" WHERE UPPER("field1") LIKE UPPER(:input)

it still works

so I delete UPPER() as well :

Code: Select all

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

[Sliderule]

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

[eristo]

@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.

[Sliderule]

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.

For example:

Code: Select all

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

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

Sliderule

[iqullc]

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

[Sliderule]

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

search.php?keywords=parameter+form&term ... mit=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

[iqullc]

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

[arfgh]

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

[Sliderule]

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 . . . viewtopic.php?f=13&t=76985

[arfgh]

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.

[Sliderule]

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

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

INSERT INTO "MY_VARIABLE" VALUES('a',10)

Code: Select all

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'

[arfgh]

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

[Sliderule]

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

Since, you "dont think" . . . I do not need to proceed further.

[arfgh]

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.

[arfgh]

anybody knows if it is possible to do it ?