Supported PB Features for PowerServer Mobile
Embedded SQL

DELETE

INSERT

SELECT

SELECTBLOB

UPDATE

UPDATEBLOB

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:

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:

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:

Example

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