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 the Web 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 222. 

DELETE

INSERT

SELECT

SELECTBLOB

UPDATE

UPDATEBLOB


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

Table 223. 

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.

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

string
ls_colname="emp_name"
string ls_value
select :ls_t_employee where id = 1;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

SELECT​BLOB

SELECT​BLOB INTO clause

Supported

On the Web, 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 the Web 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 

UPDATE​BLOB

WHERE clause

Supported

If data is updated by UPDATEBLOB statements without a WHERE clause, on the Web 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 the Web, 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.

    Unsupported example:

    (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
    

    Supported example:

    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.