|
| [WX20] Need ideas for dynamic query generation |
| Iniciado por guest, 30,ago. 2015 16:41 - 15 respuestas |
| |
| | | |
|
| |
| Publicado el 30,agosto 2015 - 16:41 |
Hi,
in my project I have a monster query (created with the query editor)
Some of these parameters should be dynamic, thats means that they can be different in special scenarios.
For example: The number of a drawing in a architecture project has the following format "AAABBBCCC"
AAA= house number
BBB= company number
CCC=status number
In another project it has such a format "AABBBBCC" and so on. I save the drawing number schema in a table and build the query parameters to select the different elements of the drawing number at runtime.
My first idea was to use this query and run this without the dynamic part (HExecuteQuery).
The second query (based on the first query) is created with the dynamic parameters (HExecuteSQLQuery).
So can I use the query editor for 90 % of the parameters. Works so far.
The problem is: If the user needs only the dynamic parameters the first query returns the full table content (+100.000 records) and only the second query reduce it. The whole process is to slow.
Any ideas how to put that in one query without loosing the comfort of the query editor ?
My hope was, that Myquery..Sqltext give me the Sql text with the content of all the parameters at runtime but it doesn´t.
HExecuteSQLQuery doesn´t allow me to use parameters so I have replace all parameters with the correct notation and thats a lot ... |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,agosto 2015 - 17:24 |
Hello Michael
I do not understand what the problem is.
The query editor supports of course dynamic parameters in the conditions. Just select value= (or any other condition) "parameter" and enter a name for your parameter that you will fill at runtime...
But that is too simple an answer, so I probably didn't get the question at all.
Best regards |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,agosto 2015 - 17:36 |
Assuming that drawing number is a text/string field in of your tables, then using the query editor and creating a condition where item "matches" parameter. The result sql will be LIKE {param1} . You can programmatically create your parameter at runtime using "_" for wild card characters and format as required depending on what piece of the string you are going for..
Note that performance will be slower using strings than on numeric index fields. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,agosto 2015 - 17:43 |
Hi Fabrice,
the problem is, that I doesn´t know all parameters at design time.
Look at my example. My program is used in different architectual projects, each with special drawing number format.
In one project
MyQuery.ParaHOUSENUMBER is MID(Drawingnumber,1,4)
in another
MyQuery.ParaHOUSENUMBER is MID(Drawingnumber,1,3)
and so on. The length and format of the drawingnumber is completly different in every architectual project.
I can´t define this at design time. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,agosto 2015 - 17:47 |
Hi Blacky,
it doesn´t work. I don´t know these conditions at designt time. The user creates the format and lenght of the drawing number at runtime. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,agosto 2015 - 18:35 |
Hi Michalely..
I'm sorry to hear that. If a LIKE {param1} doesn't work for you where Param1 is generated at run time, then I don't know what would work. Note that when NULL is passed as a parameter value, the whole condition is omitted from the query at runtime. Not sure if that is what you mean by "you don't know all the parameters at run time" or if programmatically generating the param1 string for drawing number and inserting "_" wild cards where required is a problem.. I can see that if you were going for company number but didn't know house number, you wouldn't be able figure out if you need 3 or 4 "_" preceding. If that's the case, then maybe you need to look at the structure of the table and bust those guys out into separate fields so searching isn't so complex, if doable at all. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,agosto 2015 - 19:07 |
Hi Michalely..
One more thought, if you are using HFCS, you maybe able to programmatically generate separate query fields for house number, company number and status number using stored procedures. You would need a SP that can return house number, etc as a calculated fields, then set a condition on the calculated field. Still would be slow, but it should allow you to use the query editor as you want. Still think the right way to do this would be to bust those guys out into separate fields on their own if you can, then if needed deal with concatenation in the presentation layer.. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,agosto 2015 - 19:19 |
Hi,
of course your example should be work.
And of course I simplified my example. In fact I need for every part from the drawing number
like
In list
between para1 and para2 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,agosto 2015 - 19:26 |
...and house number and company number are examples. These are not existent in all projects.
my drawing number field is a string of 50 characters.
the structure, length, parts and meanings of every part is completly different in every project.
So I can´t put these information in seperate fields. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,agosto 2015 - 19:32 |
Hi Michael,
you could read all drawings in an array at program start. If the user searches for one parameter you search the array and copy the results in a second array. If the user enters a second search parameter then your programm has to search in the second array. Maybe a third and a fourth array are needed. So you have to execute the query only once (at program start) and this should be fast enough.
If a user adds new drawings then you add this entry to the array, if he deletes a drawing then you have to delete it from the array.
In a separete file you set a flag for the other users which is checked before each search. This flag is set when a drawing is added or deleted by another user.
Greetings
Markus |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,agosto 2015 - 20:40 |
Hi again
I still don't understand...
The fixed part (in the file) is a string... It's content depend on the user defined structure...
But the user defined part is ALSO the structure of the string you need to pass as a parameter...
So you build your value in a string, and you pass the string, the same way that you build it when before saving a record.
And if you need many different condition and just use a few, you set the other to null so that they are ignored.
I really do not understand the problem here.
Best regards |
| |
| |
| | | |
|
| | |
| |
| Publicado el 31,agosto 2015 - 08:46 |
Hi,
if I understand it correctly, you need to define the different parts of the drawingnummer of every project. Based on these definitions you can (may be) compose your search parameter for the query at runtime. But a "in the list of ..." condition will still be a problem. Therefore, you have to execute the query more than ones, I suppose. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 31,agosto 2015 - 09:35 |
Yes, you got it.
I have a table "drawingnumberschema", which has the following structure:
From Length Description
------------------------------------------------------------
1 3 House Number
4 3 Company Number
7 2 .........
"From" means the start point of the string "Drawingnumber".
Based on this table I need a dynamic query for my table "drawings". Every part of the drawingnumber has 3 Paramaters:
equal, In List, between
The user can select a catalog with all possible values (In list), a range (between) or he knows the value (equal).
My 2 query program works so far, but its not so fast as it could be. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 31,agosto 2015 - 11:02 |
Hi Michael,
It's not too hard to use hExecuteSQLquery and Stringbuild to generate tailor made queries.
First you can use the query editor to build the query.
Then copy the sql code to your code window and place it between square brackets like
sSQL=[
here goes your query code
]
Next you replace all parameters {xxx} with %1 etc., remember to place string parameters between single quotes.
Now you can add or remove blocks of sql code by replacing it by parameters for the stringbuild.
So you create a variable and assign a block of sql to it. You can use parameters in the block by using stringbuild again.
I use this technique all the time e.g. to give users flexible selections like the QBE in WDmap or HFSQL control centre.
It's a little more difficult to debug, but if you put a breakpoint on or right after the hEcecuteSQLquery, you can check the result of the query and the content of the sSQL variable.
Regards,
Piet |
| |
| |
| | | |
|
| | |
| |
| Publicado el 01,septiembre 2015 - 00:38 |
Maybe out on a limb here.. If you have a key field in the drawing file that is used to look up the schema, then this should work. If HFCS cache is a good size, it should perform ok..
Create a SP for each field element that you use in drawingnum. They would look something like this.. You may be able to normalize this by passing a field id or something.
PROCEDURE spGetHouseNum(sDrawingNum, sKey)
HouseNum is string = "Not found"
Hreadseekfirst(drawingschema, schemakey, sKey)
IF hfound() THEN
sHouseNum = Mid(sDrawingNum,drawingschema.From, drawingscheam.Length)
END
RESULT = sHouseNum
Then create a calculated field in your query for each field element and set to corresponding SP passing drawingnum and the key as parameters.
Then set a condition on each of the calculated fields...
This approach would only work if you can match a drawing file record to a schema record via a key that is stored in the drawing file... If that's the case, this should work..
Hreadseekfirst in the SPs will be expensive regarding performance, but it might surprize you how fast HFCS can do it since the reads are all local to HFCS. Maybe it would be smoken fast as a materialized view... |
| |
| |
| | | |
|
| | |
| |
| Publicado el 01,septiembre 2015 - 18:09 |
Hi Piet,
yes I know. But my Query has approx. 100 different parameters and I was hoping to avoid this ... |
| |
| |
| | | |
|
| | | | |
| | |
|