Datasource Programming: Insecure API and Alternative Futures

Batch Updates

see Batch Updates

Note : This page using the below APIs will not be supported in a future release

Unsafe API(s):

FieldFormula().addOwnerRestriction(String restriction)

FieldFormula().addAssignedRestriction(String restriction)

FieldFormula().setAssignedRestriction(String restriction)

Alternative future API(s):

For restriction, provide Sql object instead of Sql string

FieldFormula().addOwnerRestriction(Sql restriction)

FieldFormula().addAssignedRestriction(Sql restriction)

FieldFormula().setAssignedRestriction(Sql restriction)

Examples

new FieldOperation()

.setOwner("wr")

.setAssigned("wrtr")

.addOwnerRestriction(new Sql("NOT EXISTS (SELECT wr_id FROM wrcf WHERE wr.wr_id = wrcf.wr_id)"));

.calculate("wr.cost_est_labor", "SUM", "wrtr.cost_estimated");

new FieldOperation()

.setOwner("fl")

.setAssigned("rm")

.setStandard("rmcat")

.setAssignedRestriction(new Sql("rmcat.occupiable = ?", 1));

.calculate("fl.area_ocup", "SUM", "rm.area");

See additional help topics:

Custom SQL Queries

see Custom SQL Queries

This page using the below APIs will not be supported in a future release.

Unsafe API(s):

DataSource().addQuery(String sql)

DataSource.addQuery(String sql, String dialect)

Alternative future API(s):

DataSource().addQuery(SQL query)

DataSource().addQuery(SQL query, String dialect)

Example

ds.addQuery(new Sql("SELECT status, COUNT(*) AS total_requests FROM wr GROUP BY status"));

See additional help topics:

Database-Independent SQL AND The formatSQL Functions

see Database-Independent SQL and The formatSQL Functions

Unsafe API(s): All the formatSQL functions (e.g. formatSqlDaysBetween ) will not be supported in a future release.

Alternative future API(s): you can use the SQL binding expression (e.g. sql.daysBetween ) instead.

see, Binding Sources: SQL (with database examples)

Restrictions and Binding Expressions

see Restrictions and Binding Expressions

Unsafe API(s):

Restrictions.sql(String sql)

Alternative future API(s): you can use Sql object instead String

Restrictions.sql(Sql sql)

Example

ds.addRestriction(Restrictions.sql(new Sql("status IN ('Created', 'Requested')")));

With binding expressions

ds.addRestriction(Restrictions.eq("em", "email", "${user.email}"));

ds.addRestriction(Restrictions.sql("status IN (${sql.getBindVariable('statuses')})"));

ds.addQuery(new Sql("SELECT COUNT(*) AS late_projects FROM project " + "WHERE date_est_completion < ${sql.currentDate}", DataSource.DIALECT_GENERIC));

Note : Expressions like ${user.name} and ${sql.currentDate} will use bind variables. so, you will not wrap them in quotes.

See additional help topics:

Parameters Object (Binding Expressions )

see Parameters Object

Unsafe API(s): Do not use DataSource.DATA_TYPE_VERBATIM parameter type and the parameters micros like ${parameters['subquery']} in DataSource for sub query.

Alternative future API(s): you can use new Sql class(s) like Sql.Builder to add subquery and/restrictions instead.

Sql sql = new Sql.Builder("select ")

.field("wr_id")

.append(" from")

.append(WR_TABLE)

.append(" WHERE")

.append("wr.status IN ('I','HA','HP','HL')")

.append(" AND")

.append(" EXISTS ")

.append("( ")

.append(“ SELECT 1 FROM wrcf WHERE wrcf.status = 'Active' AND wrcf.wr_id = wr.wr_id AND (wrcf.cf_id IN (select cf.cf_id from cf where cf.email=").literal(userEmail).append(" )").append(" )")

.append(" )")

.toSql();

See additional help topics:

Executing SQL Update

Unsafe API(s):

SqlUtils.executeUpdate(String tableName, String sql)

Alternative future API(s):

SqlUtils.executeUpdate(String tableName, Sql sql)

Example

SqlUtils.executeUpdate("pmpsum", new Sql("DELETE FROM pmpsum"));

Custom Dimensions, VPA Restrictions and Custom Data Access

see Custom Dimensions and VPA Restrictions and Custom Data Access

Unsafe API(s):

DataSource().addQuery(String sql)

Alternative future API(s): provide Sql Object instead of Sql String for sql.

DataSource().addQuery(SQL query)

Porting Basic Script To Java

see Porting Basic Script To Java

Unsafe API(s):

SqlUtils.executeUpdate(String tableName, String sql)

Alternative future API(s): provide Sql Object instead of Sql String

SqlUtils.executeUpdate(String tableName, Sql sql)

See additional help topics:

Binding Expressions: Data Binding

see Data Binding Overview , Server-Side Bindings

Do not use the parameters micros like ${parameters['name']} in DataSource.

Replace ${parameters['name']} with ${sql.getBindVariable('name')} in the SQL query.

See additional help topics: