Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
12
Erster Beitrag:
vor 2 Jahren, 7 Monaten
Letzter Beitrag:
vor 2 Jahren, 7 Monaten
Beteiligte Autoren:
Yogi Yang, DarrenF, Arie, Allard

[WX17] - How to set null parameters in an Query

Startbeitrag von Yogi Yang am 04.11.2015 05:00

I have a query in which there are multiple parameters.

Depending on user selection I want to set some parameters with a value and some without any value so that they do not have any effect on the output of the query.

Here is the query:

SELECT
student_master.sm_id AS sm_id,
student_master.sm_student_name AS sm_student_name,
student_master.sm_gr_no AS sm_gr_no,
enrollment.enrollment_roll_no AS enrollment_roll_no,
student_master.sm_gender AS sm_gender,
student_master.sm_performance_benchmark AS sm_performance_benchmark
FROM
enrollment,
student_master
WHERE
enrollment.enrollment_sm_id = student_master.sm_id
AND
(
enrollment.enrollment_ay_id = {ParamAcademicYear}
AND enrollment.enrollment_std_id = {ParamStdID}
AND enrollment.enrollment_div_id = {ParamDivID}
)

Here for example if a user selects the Standard then I just want to set the {ParamStdID} to a value and make others null.

How to achieve this?

If I try to set the other parameters to null it get error at run time.

TIA

Yogi Yang

Antworten:

Yogi,
then just don't fill in those parameters.
Suppose your query is named Query1 and you records with stdid=3 then do this:
Query1.ParamAcademicYear = 2015
Query1.ParamStdID = 3
HExecuteQuery(Query1)

If you just need DivID
Query1.ParamAcademicYear = 2015
Query1.ParamDivID = 77
HExecuteQuery(Query1)

If you need both
Query1.ParamAcademicYear = 2015
Query1.ParamStdID = 3
Query1.ParamDivID = 77
HExecuteQuery(Query1)

von Arie - am 04.11.2015 07:53
...further to Arie's response, if you need to re-execute the query, I tend to issue a HFreeQuery to free up system resources but the additional effect of doing this is that the parameters are reset to NULLs as well.

Sometimes depending on requirements, I might also set the paramter = Null in my code.

HTH

von DarrenF - am 04.11.2015 10:15
Arie and Danner,

Thanks for your inputs but I have to use the same query again and again with different parameters.

Let me explain this a bit more about the UI that I have built:
I have a window in which the user has to select the criteria according to which they want to see the data and when they click on a button another window is opened with the list of data.

Now the user can close the list window and again change the criteria and click on the button to get the list as per different criteria.

Actually this should work but unfortunately this seems to work for the first time and after that the result has unexpected data in the list.

TIA

Yogi Yang

von Yogi Yang - am 05.11.2015 04:49
Hi yogi,

This is I guess normal behavior. You cannot use a querie over and over again. You can but moast likely get unexpected results!.

For the same reson you should never make one query and use it at different parts in your application. As a roule I use one query only ones !.

Iam not an expert. But I took this advice form a verry experianced programmer and have not had unexpected results since.
Maybe arie can expalin why this is?

Ok for what you want I would temperary save the first query in a file. On that you can do filtering or even run a new query.

regards
Allard
Regards
Allard

von Allard - am 05.11.2015 07:00
Yogi,
there are several ways to present the query data, can you give us more details?

In my apps I use queries this way almost everywhere. So it can work as expected.

Do you mean you execute the query in one window and display the result in another.Maybe it has something to do with the "independnat hyperfile context" of your window? Afaik query are global so that should work.

Or do you pass the parameters to the window and run it there. Both would work, but the latter is more common practise if you ask me.

How do you display the results? In a table "bound" to the query? Or do you fill the table by programming.
I use the latter. I never use tables bound to datafiles or queries. Just for one reason: to keep things under control myself.
Back in WD14 and earlier I used TableAddLine "for each" record.

Later I started filling an array of structures with the query data (FileToMemory) and add computed values and so on on the fly. And bound the table to this array. Works much faster! And I am still in control of the data presented.

von Arie - am 05.11.2015 08:08
Yogi,

I have a pretty complex query and a set of fields and drop-downs where the user selects and enters various values and here's how I prepare the query and the parameters:


// Reset the query
HFreeQuery(QRY_ProductsAndCategoriesAllForMaint)

// Decide if we search on the code or the other filter fields
IF WEDIT_CodeSearch = "" THEN
// Set up query params
IF WEDIT_TitleSearch "" THEN
QRY_ProductsAndCategoriesAllForMaint.SearchTitle = WEDIT_TitleSearch
END

IF WCBX_Categories 0 THEN
QRY_ProductsAndCategoriesAllForMaint.SearchCategory = WCBX_Categories..DisplayedValue
END

IF WCBX_ModelSearch 0 THEN
QRY_ProductsAndCategoriesAllForMaint.ProdModelID = WCBX_ModelSearch
END

// Set the active mode
// 1 = Active
// 2 = In-Active
// 3 = Both
SWITCH glocaliTempActiveMode
CASE 1: QRY_ProductsAndCategoriesAllForMaint.Active = 1
CASE 2: QRY_ProductsAndCategoriesAllForMaint.Active = 0
CASE 3: QRY_ProductsAndCategoriesAllForMaint.Active = Null
END
ELSE
// Use code search
QRY_ProductsAndCategoriesAllForMaint.SearchCode = WEDIT_CodeSearch

// Clear the contents of the CodeSearch field
WEDIT_CodeSearch = ""
END

// Execute the query
glocalbQryres = HExecuteQuery(QRY_ProductsAndCategoriesAllForMaint)


There was a learning curve in getting to this point, but it DOES work and it seems to be rock solid. The window where this query is used is the most heavily used window of my app.

von DarrenF - am 05.11.2015 08:33
Darren,

I think I have found my answer from your reply.

But just for clarifications. Will this code work?

IF AY > 0 THEN
QRY_Students.ParamAcademicYear = AY
ELSE
QRY_Students.ParamAcademicYear = Null
END //IF AY > 0 THEN

IF Std > 0 THEN
QRY_Students.ParamStdID = Std
ELSE
QRY_Students.ParamStdID = Null
END

IF Div > 0 THEN
QRY_Students.ParamDivID = Div
ELSE
QRY_Students.ParamDivID = Null
END


I am trying this code in WM17 but am not getting expected results.

TIA

Yogi Yang

von Yogi Yang - am 05.11.2015 14:35
Hey Yogi,

If you issue a HFreeQuery beforehand, you shouldn't need the "= Null" lines of code. HFreeQuery is the easiest/safest/quickest way (delete as applicable :xcool: ) to reset everything about a WX query.

For example, because I've issued the HFreeQuery(...), if WEDIT_TitleSearch = "" then I don't need to set it to NULL because HFreeQuery has set all the parameters to NULL:


IF WEDIT_TitleSearch "" THEN
QRY_ProductsAndCategoriesAllForMaint.SearchTitle = WEDIT_TitleSearch
END


So your code becomes:


HFreeQuery(QRY_Students)

IF AY > 0 THEN
QRY_Students.ParamAcademicYear = AY
END //IF AY > 0 THEN

IF Std > 0 THEN
QRY_Students.ParamStdID = Std
END

IF Div > 0 THEN
QRY_Students.ParamDivID = Div
END


...and revisiting my code, I probably don't need the CASE 3: that sets the Null value either :cool:

As for whether this will work on WM, I believe it will, as I'm using parameters in WM queries but then again, I'm using WM20 and can't comment on earlier versions as v20 is the first WM version I've used.

von DarrenF - am 05.11.2015 14:50
Arie,

I have two windows.

In one the user selects the criteria which are displayed as combo boxes. Here is the screen shot
[attachment 1760 2015-11-05_202025.jpg]

In the other window I am passing the values of combo boxes as parameters and show the required information in a Looper.


TIA

Yogi Yang

von Yogi Yang - am 05.11.2015 14:52
Darren,

I tried the the HFreeQuery route but in case of WM17 it gives error in the final Android at run time.

TIA

von Yogi Yang - am 05.11.2015 14:57
Looking at the Help, it was introduced at V16, but that was probably into WD?

If it's not avaiable in your version of WM, I would have thought you'd get a syntax error. Is your error message stating it's the HFreeQuery command causing the problem?

If so, I wonder if HCancelDeclaration might help?

http://help.windev.com/en-US/?3044174&name=hcanceldeclaration_function

von DarrenF - am 05.11.2015 15:05
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.