SQL Tutorial -Powerbuilder SQL implementation

Salah satu hal besar mengenai pelaksanaan PowerBuilder adalah SQL (Structured Query Language). Datawindow menghasilkan yang PowerBuilder SQL untuk mengambil data dari database. Contoh SQL ini menganggap anda telah memiliki pengalaman dasar PowerBuilder / SQL .

Using SQL in the Powerbuilder IDE

Here is an example of a datawindow SQL select statement.

Example - SQL Select

The select statement is at the heart of SQL DML (Data Manipulatuion Language)

 
SELECT "fdlvdet"."season",
     "fdlvdet"."pool_code",
     "fdlvdet"."dist_code",
     "fdlvdet"."memnum",
     "fdlvdet"."mar_code",
     "fdlvdet"."ctn_code",
     "fdlvdet"."fclass",
     "fdlvdet"."fcount",
     "fdlvdet"."num_cartons",
     "fdlvdet"."wt_kg"
FROM "fdlvdet"
WHERE ( "fdlvdet"."season" = :arg_seas ) AND
     ( "fdlvdet"."pool_code" = :arg_pool )

The above example is a straight forward select statement using two retrieval arguments.

SQL Tutorial - Create Tables

Example - SQL DDL

DDL (data definition language) is used to create and modify table structure and data. For example the create table statement is used to create a new database table.

Consider the following SQL script which creates a customer financial table.

CREATE TABLE "dba"."cusfin"
("cuscode" char(6) NOT NULL DEFAULT NULL,
"currbal" numeric(12,2) DEFAULT NULL,
"days_0" numeric(12,2) DEFAULT NULL,
"days_30" numeric(12,2) DEFAULT NULL,
"days_60" numeric(12,2) DEFAULT NULL,
"days_90" numeric(12,2) DEFAULT NULL,
"days_120" numeric(12,2) DEFAULT NULL,
"credit_limit" numeric(12,2) DEFAULT NULL,
"credit_stop" char(1) DEFAULT NULL ,
"status" char(1) NOT NULL,
PRIMARY KEY ("cuscode")) ;

You can run script like this in the Interactive SQL window within the Powerbuilder IDE database painter.

---

SQL Tutorial - Embedded Static SQL

You can place SQL code within your Powerscript code.

Example - Commit and Rollback statements

if dw_1.update()=1 then
commit using sqlca;
dw_1.reset()
dw_1.insertrow(0)
else
rollback using sqlca;
end if

SQL Tutorial - Dynamic SQL

SQL can be executed at run-time (dynamically). There are four formats

Format 1 is appropriate for DDL statements, such as create, drop, insert, grant

Example - Consider the following SQL script - Dynamic SQL Format 1:

---

string ls_isql

ls_isql="CREATE TABLE dba.cusfin " + &
("cuscode char(6) NOT NULL DEFAULT NULL, " + &
"currbal numeric(12,2) DEFAULT NULL, " + &
"days_0 numeric(12,2) DEFAULT NULL, " + &
"days_30 numeric(12,2) DEFAULT NULL, " + &
"days_60 numeric(12,2) DEFAULT NULL, " + &
"days_90 numeric(12,2) DEFAULT NULL, " + &
"days_120 numeric(12,2) DEFAULT NULL, " + &
"credit_limit numeric(12,2) DEFAULT NULL, " + &
"credit_stop char(1) DEFAULT NULL , " + &
"status char(1) NOT NULL, " + &
"PRIMARY KEY (cuscode)) ; "

Execute immediate :ls_isql using sqlca;

---

Example : - Dynamic SQL format 2

This format is used when a known input parameter needs to be used. For example:

 delete from cusfin where cuscode=ls_code

Here is a sample SQL scipt

string ls_code
ls_code=sle_1.text
prepare SQLSA FROM "delete from cusfin where cuscode=?" using sqlca;
execute SQLSA using :ls_code;

SQLSA is a private Powerbuilder datatype called DynamicStagingArea. It is used to store information about the SQL statement.

---

Example - Dynamic SQL format 3

This format is used when there is a result set and a known number of input parameters. For example:

 
select * from cusfin where credit_stop=ls_code

Here is a sample SQL scipt

string ls_result,ls_code,sqlstmt
ls_code=sle_1.text

declare fin_curs dynamic cursor for sqlsa;
sqlstmt="select * from cusfin where credit_stop=?"
prepare SQLSA FROM :sqlstmt;
open dynamic fin_curs using :ls_code;
fetch fin_curs inyo :ls_result;
lb_names.additem(ls_result)
do while SQLCA.SQLCode =0
fetch fin_curs into :ls_result;
lb_names.additem(ls_result)
loop

Perintah ini menggunakan PowerBuilder 10.
Resource : http://www.rds.co.za/pbsql.htm