Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » Elevate Web Builder Technical Support » Support Forums » Elevate Web Builder Public Beta Tests » View Thread |
Messages 1 to 10 of 10 total |
Using params in a SELECT |
Thu, Apr 16 2020 9:01 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | To use parameters in a SELECT in EWB2, you set up params such as:
SELECT ISOCode, UNCode, Country, Capital FROM Countries WHERE ISOCode={ISOCode='AU'} The Params are set by: aCountry.Params.Clear; aCountry.Params.Add('ISOCode=' + QuotedStr(fISOCode)); Database.LoadRows(aCountry); This no longer works in EWB3. Can someone please explain how queries work in EWB3? Many thanks. Richard. |
Fri, Apr 17 2020 1:27 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Richard,
<< Can someone please explain how queries work in EWB3? >> With EWB3, you need to use the :ParamName parameter syntax for any SQL, and an associated breaking change is that any string parameters used as parameters cannot contain single quotes around the parameter values. This is covered in the release notes, so be sure to read about the other breaking changes, also: https://www.elevatesoft.com/download?action=info&category=ewb&type=ewbbeta&majorversion=3&version=3.00 "URL string parameters to the database API must no longer contain single quotes around the value (CustomerID=Test Customer vs. CustomerID='Test Customer')." Tim Young Elevate Software www.elevatesoft.com |
Sun, Apr 19 2020 7:58 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Tim,
With EWB2, we could set up parameters for LIKE and BETWEEN conditions. For example, SELECT "Category", "Number", StockNumber, Qty, Berco FROM Stock S1 WHERE Category = {Category='TST'} AND Berco LIKE {Berco='%'} AND Qty BETWEEN {QtyMin=1} AND {QtyMax=10} This appears to be no longer possible with EWB3. Richard |
Tue, Apr 21 2020 1:41 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Richard,
<< This appears to be no longer possible with EWB3. >> No, it is definitely possible. You just need to use the :Param naming syntax instead of the EWB2-specific parameter naming syntax ({Param=Value}). Tim Young Elevate Software www.elevatesoft.com |
Wed, Apr 22 2020 1:29 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Tim,
Setting a parameter value to an EDB computed column results in the dataset not returning any rows. Richard |
Mon, Apr 27 2020 1:43 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Richard,
<< Setting a parameter value to an EDB computed column results in the dataset not returning any rows. >> What happens if you run the same query in the EDB Manager (including parameters) ? Tim Young Elevate Software www.elevatesoft.com |
Sun, May 10 2020 8:16 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Tim
<What happens if you run the same query in the EDB Manager (including parameters) ?>> This returns the correct results. ====== Below is an example of an SQL that has been defined in a dataset. SELECT Category, Number, StockNumber, 1 AS Cnt, Identity, ITR, Description, OEM, Qty, Berco, SellPrice, Location FROM Stock S1 WHERE Category = :Category AND Description LIKE :Description AND Location LIKE :Location AND Qty BETWEEN :QtyMin AND :QtyMax ORDER BY S1.Category, S1.Number In EWB2 and EWB3, users can enter selection criteria in a form. In EWB3, this generates the parameter list below which gives the correct results. Category=ADR;Description=%idler%;Location=%;QtyMin=0;QtyMax=99999; ======================= If I included the IDENTITY column in the SQL for EWB3, this results in no rows being found. IDENTITY is a computed column. I can run the query in EDB Manager, set the parameters and receive the correct results. SELECT Category, Number, StockNumber, 1 AS Cnt, Identity, ITR, Description, OEM, Qty, Berco, SellPrice, Location FROM Stock S1 WHERE Category = :Category AND Identity LIKE :Identity AND Description LIKE :Description AND Location LIKE :Location AND Qty BETWEEN :QtyMin AND :QtyMax ORDER BY S1.Category, S1.Number The parameter list is: Category=65T;Identity=%T750%;Description=%idler%;Location=%;QtyMin=0;QtyMax=99999; Richard |
Mon, May 11 2020 11:32 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Richard,
<< If I included the IDENTITY column in the SQL for EWB3, this results in no rows being found. IDENTITY is a computed column. I can run the query in EDB Manager, set the parameters and receive the correct results. >> Please email me the database catalog and relevant table files, and I'll check it out. Also, please make sure that you also include the exact SQL that you're using for the SELECT statement in the dataset. Tim Young Elevate Software www.elevatesoft.com |
Fri, May 15 2020 3:22 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Richard,
Okay, I looked at everything, and this isn't a bug. << If I included the IDENTITY column in the SQL for EWB3, this results in no rows being found. IDENTITY is a computed column. I can run the query in EDB Manager, set the parameters and receive the correct results. >> The problem here is that you defined the IDENTITY computed column as CHAR(20), which means that any un-used space in the parameter will, by default, be padded with spaces. This is because EDB will auto-define parameters based upon how they are used in the expression: if the parameter is being compared to a column, then EDB will use the column's data type, length, etc. to define the parameter. So, what happens is that: Identity LIKE :Identity becomes: 'Computed Value ' LIKE '% ' at runtime. The solution is to alter the table and change the data type to VARCHAR(20) instead of CHAR(20) and that will fix the issue. Typically, the CHAR data type is only used with single-character columns, because of this behavior. More information: https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=String_Types Tim Young Elevate Software www.elevatesoft.com |
Fri, May 15 2020 3:59 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | <<Okay, I looked at everything, and this isn't a bug.
The problem here is that you defined the IDENTITY computed column as CHAR(20), which means that any un-used space in the parameter will, by default, be padded with spaces. This is because EDB will auto-define parameters based upon how they are used in the expression: if the parameter is being compared to a column, then EDB will use the column's data type, length, etc. to define the parameter. So, what happens is that:>> Great - Thank you Tim. |
This web page was last updated on Wednesday, April 24, 2024 at 11:07 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |