Non-cursor statements

Requirements

  1. For SQL Server database, PowerServer cannot recognize SQL statements with double quotation marks if the SET QUOTED_IDENTIFIER is OFF.

  2. For SQL Server and ASE database with JDBC driver, use "SET NOCOUNT ON" before the calls to SQL statements when you create a trigger object. Otherwise, update to the database on mobile may fail with an error "Failed to update database due to java.sql.SQLException...cannot insert the value NULL.".

  3. In SQL statements, if table names and column names are in double quotation mark, string constant should be quoted as single quotation marks.

Supported

Appeon currently supports six types of non-cursor SQL statements:

Table 176. 

DELETE

INSERT

SELECT

SELECTBLOB

UPDATE

UPDATEBLOB


The following table provides more information for supported non-cursor SQL statements:

Table 177. 

Type

Supported

Details

SELECT

Retrieval list (SELECT clause)

The retrieval list can be column names, IDs, or aliases. The column names can be expressed as characters (for example, "empid") or as dot notation (for example, emp.empid).

Example:

select s_emplid, 
s_emplname //Column names
select e.emp_lname //Table 
aliases
select s_emplid + s_emplname as 
emplinfo //Column (expression) 
aliases

The retrieval list can be expressions that are functions, sub queries, arithmetic operators or any combination of columns, constants, and expressions.

Example:

select s_emplid + s_emplname as 
emplinfo //Concatenation of 
strings
select substring(s_emplid + 
s_emplname,1,3) as 
emplinfo,getdate() //Function 
expression
select f_salary / 12 as 
f_persalary //Operation 
expression

The retrieval list can be an asterisk that represents all columns in one table.

Example:

select * //Asterisk symbol representing all columns 

Mobile Enhancement: It is supported to use variable in the Select statement. For example, the following syntax is supported on mobile:

string ls_colname="emp_name"
string ls_value
select :ls_colname 
into:ls_value from t_employee 
where id = 1; 

FROM clause

The FROM clause can contain a single table (view) or multiple tables (views). The table can be expressed using table name or table alias.

Example:

from Employee, viewbonus
from Employee a, viewbonus b

The tables can be locked or unlocked.

WHERE clause

The WHERE clause can contain any of the following:

  • Retrieval parameters

  • Standard comparison operators (=, >, <, <>, >=, <=)

  • Standard logical operators (NOT, AND, OR)

  • Special operators (UNION, BETWEEN, IN, LIKE, IS NULL)

  • Join conditions

  • Special characters ('&', '~', '[]', '!~~!', '```', '!~@~~~~!', etc.)

HAVING clause

Supported

GROUP BY clause

Supported

COMPUTED clause

Supported

FOR UPDATE clause

Supported

Variables list (INTO statement)

The Variables list can be variable(s) or reference(s) to a control property.

Example:

into: ls_emplid
into: sle_1.Text

Example

SELECT f1, f2, ..., fn 
into :v1, :v2, ..., :vn
FROM table
WHERE w1 = :p1 and w2 
= :p2.prop and/or ... and/or wn = :pn

SELECTBLOB

SELECTBLOB INTO clause

Supported

On mobile, if the result set of SELECTBLOB contains several rows, the first row is returned and the return value of SQLNRows is 1 for every supported DBMS. This is different from PowerBuilder.

SelectBlob statement may return "" (empty string) on mobile when it returns NULL in PowerBuilder. The difference is caused by the JDBC driver.

FROM clause

Supported

WHERE clause

Supported

Example

Blob Emp_id_pic
SELECTBLOB Emp_pic
INTO:Emp_id_pic
FROM Employee
WHERE Employee.Emp_Num = 100
USING Emp_tran;
p_1.SetPicture(Emp_id_pic)

INSERT

INSERT INTO clause

The INSERT INTO clause can be either table (view) name(s) or a column list.

VALUES clause

The VALUES clause can contain any of the following:

  • All supported data types in allowed scope

  • Space, special characters ('<>', '!', '@', '#', '$', '%', '^', '&', '*')

  • Initial value, a single record, multiple records (up to 500)

Validation

The validation will be automatically done by the system.

Example

INSERT INTO table
VALUES("v1", 12.3, :p1, :p2, ..., :pn)

UPDATE

SET clause

The SET clause can update a single record or multiple records (up to 200). It can contain special characters including '<>', '!', '@', '#', '$', '%', '^', '&', '*'.

Example

UPDATE table
SET f1 = :p1, f2 = :p2
WHERE w1 = :p3 and/or ... wn 
= :pn

UPDATEBLOB

WHERE clause

Supported

If data is updated by UPDATEBLOB statements without a WHERE clause, on mobile all the data will be updated to the database, and the return value of SQLNRows is equal to the number of rows that you have updated.

On mobile, the BlobVariable could be NULL.

Example

UPDATEBLOB Employee SET emp_pic = :Emp_id_pic
WHERE Emp_num = 100
USING Emp_tran ;

DELETE

DELETE FROM clause

The FROM clause can contain a single table (view) or multiple tables (views). The table can be expressed using a table name or table alias.

The DELETE statement can delete a single record or multiple records.

WHERE clause

The WHERE clause can contain any of the following:

  • Retrieval parameters

  • Standard comparison operators (=, >, <, <>, >=, <=)

  • Standard logical operators (NOT, AND, OR)

  • Special operators (UNION, BETWEEN, IN, LIKE, IS NULL)

  • Join conditions

  • Special characters ('&', '~', '[]', '!~~!', '```', '!~@~~~~!', etc.)

Example

DELETE
FROM table
WHERE f1 = '1' and f2 = :p1 ... 
fn = :pn

Unsupported

  • Updateblob statements cannot update multiple blob columns at one time.

  • It is unsupported to parenthesize SELECT statements when using EXCEPT ALL or INTERSECT in SQL statements for DB2 database.

    For example, the following script is unsupported:

    (SELECT DEP_ID FROM PUB_T_DEPARTMENT) EXCEPT ALL (SELECT DEP_ID FROM PUB_T_EMPLOYEE)
    (SELECT DEP_ID FROM PUB_T_DEPARTMENT) INTERSECT (SELECT DEP_ID FROM PUB_T_EMPLOYEE) //Unsupported
    

    The following script is supported:

    SELECT DEP_ID FROM PUB_T_DEPARTMENT EXCEPT ALL SELECT DEP_ID FROM PUB_T_EMPLOYEE
    SELECT DEP_ID FROM PUB_T_DEPARTMENT INTERSECT SELECT DEP_ID FROM PUB_T_EMPLOYEE //Supported
    
  • If the database is SAP ASE or SQL Server, SQL statements can contain column name(s) enclosed in double quotes (same as in PowerBuilder). If the database is not SAP ASE or SQL Server, SQL statements cannot contain column name(s) enclosed in double quotes (same as in PowerBuilder).

  • Using a COMPUTE BY clause in the SELECT statement is unsupported if an application uses a JDBC driver.