Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 10 total
Thread Using params in a SELECT
Thu, Apr 16 2020 9:01 PMPermanent 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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 PMPermanent 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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 PMPermanent 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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 PMPermanent 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 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 PMPermanent 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.
Image