Blog posts tagged with 'SQL'

In the Rate Expression of an Option (or OptionExit) type record, put in a SQL statement (or call a stored procedure). It must return a table of Shipping Options - columns: Name VARCHAR, Description VARCHAR, Rate Decimal.

For example, if you can set up Warehouses as locations for local Pickup:

If you have a complex rate lookup, or if you just prefer to write your rate calculation as a SQL Server stored procedure, Shipping Director has the ability to do SQL queries and call stored procedures.

If an expression starts with one of these keywords, then the expression is evaluated as a SQL query:

SELECT

?SELECT"

EXECUTE"

?EXECUTE

Note thate there are two forms of SELECT and EXECUTE; one form is prefixed with a “?”. Without the leading “?” the query is treated as a standard SQL paramaterized query using “@”-prefixed parameters. For example:

select Zone from ShippingZone_Origin23235 where @p1 between PrefixFrom and PrefixTo; [ZipCode3]

This default behavior should be used when there is a concern for SQL injection. (It is also recommended to use ErrorExit to test for for such possibilities.)

With the leading “?” the query can contain zero-based indexed placeholders (format items to be used in String.Format). For example:

?select Zone from ShippingZone_Origin{0} where '{1}' between PrefixFrom and PrefixTo;[OriginZipCode],[ZipCode3]

This provides the ability to create truly dynamic queries. In the above example, the table name contains a format specifier.

The result of a SQL expression needs to be a single scalar value (i.e. a String, a Decimal, etc.). Typically the expression is assigned to a variable.

An example of using SQL queries would be to do a custom rate lookup from a set of Zone/Weight/Rate tables in the database. The first table would be used to lookup a Zone base on a shipping destination zip code, and the second table would use the looked up Zone and the total weight of items in the cart to lookup the rate.

ShippingZone_Origin23235

PrefixFrom

PrefixTo

Zone

120

126

4

127

127

3

128

147

4

148

163

3

164

165

4

166

172

3

ShippingZone_Rate

Weight

Zone2

Zone3

Zone4

Zone5

Zone6

Zone7

Zone8

Zone9

1

5.17

5.4

5.51

5.75

6.04

6.12

6.22

19.18

2

5.37

5.72

6.22

6.34

6.75

6.89

7.13

21.3

3

5.45

5.97

6.53

6.73

7.15

7.36

7.88

23.21

4

5.58

6.16

6.86

7.17

7.53

7.85

8.44

25.37

5

5.79

6.25

7.15

7.46

7.83

8.2

8.91

27.63

6

5.96

6.44

7.27

7.65

7.97

8.44

9.1

30.01

The first option is to use parameterized queries (“select” without the “?” prefix). In this example, a constant Origin Zip Code is provided, but this could instead be a calculation of a specific warehouse based on what is in the cart.

SQL queries using parameterized queries

Order

Type

Name

Expression

Rate

Expression

200

String

ZipCode3

ShippingAddress.ZipPostalCode.Substring(0,3)

300

String

Zone

select Zone from ShippingZone_Origin23235 where @p1 between PrefixFrom and PrefixTo; [ZipCode3]

Use a stored procedure to do the lookup (replace lines 300 and 350 above with just this line 300)

300

Decimal

ZoneRate

EXECUTE ShippingZone_LookupRate @p1, @p2; [ZipCode3], [$TotalWeight]

The second option is to use the “?” prefix so that the table name can be dynamically determined. In this example, a constant Origin Zip Code is provided, but this could instead be a caclulation of a specific warehous based on what is in the cart.

SQL queries using zero-based index placeholders

Order

Type

Name

Expression

Rate

Expression

100

String

OriginZipCode

"23235"

200

String

ZipCode3

ShippingAddress.ZipPostalCode.Substring(0,3)

300

String

Zone

?select Zone from ShippingZone_Origin{0} where '{1}' between PrefixFrom and PrefixTo;[OriginZipCode],[ZipCode3]