SQL Tutorials and examples

SQL Tutorial -Powerbuilder SQL implementation

One of the great things about Powerbuilder is the implementation of SQL (Structured Query Language). The Powerbuilder Datawindow generates SQL to retrieve data from the database. These SQL examples assume you already have some basic Powerbuilder / SQL experience. If not, you may want to use a resource like our Powerbuilder OOP ebook to get up to speed. These examples are implemented using Powerbuilder 10 and ASA (Adaptive Server Anywhere) V9.

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

Example - Dynamic SQL format 4

This format is used when the parameters and result set are not known at design-time.

This format is dealt with in more detail in our Powerbuilder ebook.

Additional examples will be added here on a regular basis, so check back often.

Back Next