Supported PB Features for PowerServer Web
Embedded SQL

DELETE

INSERT

SELECT

SELECTBLOB

UPDATE

UPDATEBLOB

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:

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:

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:

Example

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