Run time variables in queries?

[RhinoCan]

I'm new to Base but very familiar with other databases, especially DB2 and MySQL.

I'm trying to determine how I can replace the hard-coded dates in a query with variables that I supply at run-time. Essentially, I have this:

Code: Select all

where order_date between '2015-12-13' and '2015-12-19'

and want to supply the values of the two dates at run-time.

How can I do that in Base?

[Sliderule]

Code: Select all

where order_date between :Enter_From_Date and :Enter_To_Date

The Base Parser, will replace, what starts with a colon as a prompt. NOTE: after the colon, the text prompt must be entered withOUT a space, and, they are displayed in alphabetical order.

You can read about it, in the Base help file, topic: parameters;queries (Base) towards the bottom.

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]

Named parameters with leading : and no spaces:

Code: Select all

where "order_date" between :From_Date and :Until_Date

In forms you can substitute subform parameters by parent form values. In all other cases Base will show a dialog.

[RhinoCan]

Thank you both for your replies. The technique certainly looks simple enough but it's not working for me.

I decided to start with a single parameter and adjusted my WHERE clause to say:

Code: Select all

 WHERE "Order_Date" BETWEEN :Week_Begin_Date AND '2015-12-19'

When I run this, I do not get prompted for a value for Week_Begin_Date and the query just returns an empty result. What am I doing wrong (or what additional step do I need to take)?

[Sliderule]

While I cannot answer your question / comment definitively with knowing for certain which database back-end you are using ( therefore the DATE syntax required ) . . . and . . . therefore database syntax needed, I did mention in my post above . . . the Base Parser . . . that is . . . OpenOffice / LibreOffice Base will parse and modify the Query ( Select statement as written ) . . . and you want / require this for a Parameter Query, since it will then prompt you for the user input . . . when you do NOT run the Query in Direct Mode ( Direct Mode no changes to the query, just take it and send it 'directly' as written to the database back-end ). Since you want it to prompt you for input . . . you canNOT run the query in direct mode.

Put another way, for your query, EITHER

1. From the Menu: Edit -> Run SQL command directly must be UNCHECKED
2. From the toolbar, the SQL icon is UNCHECKED

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[RhinoCan]

Thank you, Sliderule. My query prompts for the variables now. I checked the menu and Edit->Run SQL command was checked so I unchecked it. Then I pressed the Run SQL icon in the toolbar and the query prompted me for values for the variables. After I supplied values (between apostrophes, e.g. '2015-12-13') the query executed and returned the correct answer. (The apostrophes got turned into # signs along the way so that is apparently what it prefers.)

As for your first paragraph, I truly don't know which database back-end I am using or how to determine that. Can you fill me in on that, just in case I have to ask another question and that information is pivotal to the answer? Also, I'm not sure what "Direct Mode" is and what the alternative to "Direct Mode" is: "Indirect Mode"? What are the purpose of each of the modes, just so that I know how to use each appropriately?

One other followup question. In an ideal world, it would be great if I could choose the week_start_date from a date chooser instead of typing it in. Is that possible? If so, how would I do it? (Since the week_end_date is always 6 days after the week_start_date, I'm going to replace that variable with a calculation that says week_start_date plus 6 days. I haven't actually tried that so I'm not sure if I'm allowed to do that or exactly what the syntax will be but now that I have an SQL reference, I should be able to figure it out, assuming you can use a variable/parameter in a calculation/function. If I can use a date chooser initialized to the current date, then I only have to click on the Sunday or the preceding week, then the correct rows will be chosen for the result.)

[Sliderule]

OK, glad you got it to work, fantastic.

I will attempt to answer your questions, but, just as an FYI ( For Your Information ) . . . it is usually best, when asking questions, that are not related ( database pun intended ) to the original post . . . to ask with a new forum post. The reason, so, others, when searching the forum, typically by forum subject . . . they will be able to find answers easier.

I truly don't know which database back-end I am using or how to determine that. Can you fill me in on that, just in case I have to ask another question and that information is pivotal to the answer?

After you start your database file ( *.odb ) . . . the 'connection' to the database back-end is displayed on the status bar at the bottom. Since OpenOffice / LibreOffice Base is only a database front-end ( what the user sees ) the 'work', that is, the database work, can be connected to a number of different databases. For example, not listed in any order, HSQL, H2, MySQL, Microsoft Access, PostgreSQL, IBM DB2, Firebird, etc.

If, on the Status Bar, it says something like: Embedded database and HSQL database engine . . . that means . . . you are using HSQL Version 1.8.0.10 . . . and . . . that is a very OLD version of HSQL that does NOT include date arithmetic.

Also, I'm not sure what "Direct Mode" is and what the alternative to "Direct Mode" is: "Indirect Mode"? What are the purpose of each of the modes, just so that I know how to use each appropriately?

As I explained in my post above . . . when you run a Query, you have the option to run the Query exactly as YOU wrote it, and, pass the Select statement directly to the database back-end. BUT, if you use ( allow ) the OpenOffice / LibreOffice Parser to first read and modify the Query, it will do it. Modification might include 'formatting' of dates, for example, if it 'assumes' you are entering a date, in, DD/MM/YY format, or, MM/DD/YY, or, DD.MM.YY, or, DD-MM-YY, etc . . . the Parser will 'convert' that to YYYY-MM-DD format. The Parser is used within the GUI ( Create Query in Design View... or Use Wizard to Create Query ) to create a Query. Also, the Parser, does NOT support some operations that the database back-end might. For example, a UNION, or, UNION ALL statement. BUT, you can use the GUI to 'start' a Query, edit it with the UNION ( etc ) and run it in "direct mode" . . . and . . . it will work.

One more additional note about the Base Parser, or, better put, an advantage to NOT using the Base Parser . . . assuming you do NOT need to prompt the user for input . . . when you SAVE a query, that is saved in "Direct Mode" ( sans the Base Parser ), the Query ( select statement ) is saved, INCLUDING, carriage returns, indents ( multiple spaces ), and comments you can make ( starting with two dashes ) . . . so . . . the Query is easier to read / debug.

One other followup question. In an ideal world, it would be great if I could choose the week_start_date from a date chooser instead of typing it in. Is that possible?

That might be done using a Form . . . perhaps other will help you here with that, or, search in the Form section, or, post that question to the Form section.

Also, see my comment above about performing date arithmetic with HSQL 1.8.0.10 . . . BUT . . . if you upgrade the database to HSQL 2.3.3 ( the current version of HSQL as of the date I am writing this ) . . . function . . . DATEADD is available, that will return a date, in X ( in your case 6 ) days, in the future, or, earlier. For example, using the example you gave above:

Code: Select all

WHERE "Order_Date" BETWEEN :Week_Begin_Date AND DATEADD('day', 6, :Week_Begin_Date)

I hope this helps. Best of continued success.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.