Disabling Datawindow Edit

if you use :-

dw_1.enabled = False //the whole datawindow object will be disabled including scrollbars

instead of that use below :-

dw_1.Object.DataWindow.ReadOnly = "yes"

//the scroll bar will still work and you can scroll up and down the datawindow

to set enable again :_

dw_1.Object.DataWindow.ReadOnly = "no"

Checking SQL statement Status in Powerbuilder

Assume that your SQL in Powerbuilder script as below :-

//DECLARE sqlca is your transaction...
transaction SQLCA

SELECT * FROM MYTABLE A WHERE A.MYCOL = '01234' using SQLCA;

if SQLCA.sqlcode = 100 then
    messagabox('Error', 'Record Not Found',StopSign!)
    return
end if

/*
SQLCA.sqlcode = 100 //result not found
SQLCA.sqlcode = 0 //result found or SQL return no error
SQLCA.sqlcode = -1 //returns error in SELECT or INSERT or UPDATE
*/

First Character of Word UpperCase in SQL

SELECT INITCAP(A.NAME) FROM BIODATA A;

//data example : HARIMADA SABALKUNAN
//SQL output example : Harimada Sabalkunan

Getting Column Data Length with SQL in Oracle

SELECT * FROM RESULTS A
WHERE LENGTH(A.MATRIXNO) = 8;


//Above SQL will select data from table RESULTS that contains the length of MATRIXNO field equals 8 (eight)

//oracle 9G.

Display as Picture property in Column Datawindow

//1. your datawindow SQL like below, where the picture name is 00001.jpg, 00002.jpg and so on...

SELECT a.staffno, a.name, ('images\staff\'||a.staffno||'.jpg') as gambar
FROM staff a

//2. the pictures located in subdirectory images\staff\

//3. At column property, check Display as picture

//4. Output of datawindow will be like below...

Using OLE Blob in Powerbuilder 10 using Oracle 10g

In datawindow, you might want to have OLE column that display such as picture of staff department.
These are the step to do it :-

1. When building datawindow select the ID number (in my case staff number) without selecting the blob / longraw (in Oracle 10g) column.
2. Make sure the table have primary key (staff id), and update properties for datawindow as below :-

3. Add an OLE column from menu Insert->Control->OLE Database Blob.
4. As below the properties for OLE column (select blob column from the same table)

5. Key Clause: [primary key for blob table = :primary key for update in datawindow] 
6. Client Name Expression : used when opening paintbrush application to update to which staff number such as below (after running your application)

7. Paintbrush appears when double-click on OLEblob column

8. You can paste a picture or select paste from file...
9. Update back to datawindow by clicking menu in paint such below, then exit paint.

10. When return to datawindow, you have blob picture in you application!
11. Remember to save you datawindow by using dw_1.update() then commit using sqlca;
or the picture will not be updated into your database.
12. This feature doesnt not supported by Appeon till now (version 6.2)

Black Cat Relaxing

This black cat was found resting at my mother's house. Black is beauty, you're really black uh..

Crosstab and having SQL that display star rating

//lets say we have this SQL in crosstab datawindow in Powerbuilder (i'm using 10.2.x)

select a.KOD, d.PIHAK , c.JUMBINTANG, c.MULA, c.AKHIR, a.KOD_JENIS, e.SINGKATN, to_char(b.TKH_SEMASA, 'yyyy') as compute_0008_year
from main a, semasa b, star c, pihak d, kdfaklt e
where a.KOD = b.KOD (+)
and b.SEMASA is not null
and a.PIHAK = d.PIHAK (+)
and a.PTJ = e.KDFKLT
having  (count(to_char(b.TKH_SEMASA, 'yyyy')) between c.mula and c.AKHIR)
group by a.KOD, d.PIHAK , c.JUMBINTANG, c.MULA, c.AKHIR, a.KOD_JENIS, e.SINGKATN, to_char(b.TKH_SEMASA, 'yyyy')
order by to_char(b.TKH_SEMASA, 'yyyy'), a.KOD_PEKA

//your crosstab definition is like this

//you have datawindow output like this

//but you want an output like below?

//in a dialogbox format property of the displayed number in datawindow code this:-

Powerbuilder Openwithparm

 string ls_stafno
//Open a response window w_find passing a string
ls_stafno = '007'
openwithparm(w_find, ls_stafno, parent)

//other example passing a structure
struc_staff  lstrc_staff
lstrc_staff.stafno = '007'
lstrc_staff.name = 'James Bond'
openwithparm(w_find, lstrc_staff)

Connecting Powerbuilder to Oracle 10g

sqlca.DBMS = "O10 Oracle10g (10.1.0)"
sqlca.LogPass = "tiger"
sqlca.ServerName = "server"
sqlca.LogId = "scott"
sqlca.AutoCommit = False
sqlca.DBParm = "CommitOnDisconnect='No',Async=1,PBCatalogOwner='scott'"

connect using sqlca;
dw_1.settransobject(sqlca)

More CASE and DECODE in oracle SQL

SELECT b.TARIKH,
b.KOD_PROJEK,
a.NAMA_PROJEK,
b.KOD_VOT, 
b.DASAR,
b.KET,
(select d.NAMA_TRANS from sbp_trans d where d.JENIS = b.JENIS_TRANS) as trans,
b.LO as rujukan,
(case when b.jenis_trans in (3,4) then b.amaun
else null
end) as debit,
DECODE(b.JENIS_TRANS, 5, b.AMAUN, null) as kredit,
(case when b.jenis_trans not in (1,2,6) then null
else b.AMAUN
end) as amt_col8, 
b.JENIS_TRANS,
(select c.AMAUN from sbp_budget c where c.KOD_VOT = b.kod_vot and c.DASAR = b.dasar and c.PTJ = a.PTJ and c.SPTJ = a.SPTJ
and c.thn_blj = :as_thnblj and c.JENIS = 1) as peruntukan,
b.KOD_SODO
FROM PROJEK a, BELANJA b
WHERE b.KOD_VOT like :as_vot
AND b.DASAR like :as_dasar
AND a.KOD_PROJEK = b.KOD_PROJEK
AND substr(a.KOD_PROJEK, 1,4) = :as_thnblj
AND a.PTJ = :as_ptj
AND a.SPTJ like :as_sptj