Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » Elevate Web Builder Technical Support » Support Forums » Elevate Web Builder Web Server and Modules » View Thread |
Messages 1 to 4 of 4 total |
Problem with "where col in ()" |
Sun, Oct 9 2016 1:16 PM | Permanent Link |
Jiri Dvorsky | Hi All,
I have this problem, in my dataset "Row Source" section I need do: SELECT * from TAB1 where (col1 in (val1, val2, val3 .....) ) col1 is indexed integer value and I need send values in parametr. So, I think this declaration should work: SELECT * from TAB1 where (col1 in ({list_values}) ) Bat when I test it, then result from select contains row only for val1, content after the first comma is ignored. When I test for example this: SELECT * from TAB1 where (col1 in ({list_values=3377,3376}) ) in "Preview" section I can see only row where col1=3377. SQL server is Firebird and when this select runs in ISQL, result is OK. Can anybody confirm if clausule "where column in ()" with param works well on EWB server ? Thanks, Jiří Dvorský |
Tue, Oct 11 2016 1:14 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Jiri,
<< I have this problem, in my dataset "Row Source" section I need do: >> Unfortunately, you have to specify the parameter names/values separately, and cannot just use a comma-delimited list of values as the value for one parameter. In other words, you have to use something like this: SELECT * FROM MyTable WHERE MyColumn IN ({Param1='Test'}, {Param2='Test'}) This has to do with how EWB parses the SQL and parameterizes it for SQL Server, MySQL, etc. It uses actual SQL parameters for the various parameters, and most database engines/servers do not support multiple values within a single parameter. Tim Young Elevate Software www.elevatesoft.com |
Tue, Oct 11 2016 2:30 PM | Permanent Link |
Jiri Dvorsky | Thank you for your answer, I understand. But in real-world applications, the clause "WHERE IN ()" is used to indexed selection 1-n values (the number of parameters is not known). I'm going to write user modules.
**************************************************************************************************************************** Tim Young [Elevate Software] wrote: Jiri, << I have this problem, in my dataset "Row Source" section I need do: >> Unfortunately, you have to specify the parameter names/values separately, and cannot just use a comma-delimited list of values as the value for one parameter. In other words, you have to use something like this: SELECT * FROM MyTable WHERE MyColumn IN ({Param1='Test'}, {Param2='Test'}) This has to do with how EWB parses the SQL and parameterizes it for SQL Server, MySQL, etc. It uses actual SQL parameters for the various parameters, and most database engines/servers do not support multiple values within a single parameter. Tim Young Elevate Software www.elevatesoft.com |
Mon, Oct 17 2016 1:17 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Jiri,
<< Thank you for your answer, I understand. But in real-world applications, the clause "WHERE IN ()" is used to indexed selection 1-n values (the number of parameters is not known). >> Yes, and this is a known issue with SQL parameters in just about every SQL database that supports parameters, including SQL Server. It's not specific to EWB's dataset access. The problem is that SQL with parameters is supposed to be compiled and the parameters bound at compilation time. If the number of parameters (and possibly their types) is dynamic, then you cannot reconcile this fact with the ahead-of-time compilation of parameterized SQL. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |