Search code examples
oracleconverterspowerbuilder

How to convert Powerbuilder Query to Oracle

I work on a company that has a program coded in power builder. I don't know at all about this one. Sadly my senior leaving, but I want to convert the program to Oracle. So I need to regenerate it. First of all, is there a good documentation about this "convert thing"?

retrieve = "PBSELECT( VERSION(400) TABLE(NAME=~" saldo_stok ~ " )  TABLE(NAME=~" rekening ~ " ) COLUMN(NAME=~" saldo_stok.urutan ~ ") 
COLUMN(NAME=~" saldo_stok.gudang ~ ") COLUMN(NAME=~" rekening.nama_jenisjurnal ~ ") COLUMN(NAME=~" saldo_stok.kode_rekening ~ ") 
COLUMN(NAME=~" saldo_stok.nama_barang ~ ") COLUMN(NAME=~" saldo_stok.sat_hitung2 ~ ") COLUMN(NAME=~" saldo_stok.saldo_awal ~ ")
COMPUTE(NAME=~" ~~~ "saldo_stok~~~".~~~ "SALDO_AWAL~~~" *~~~ "saldo_stok~~~".~~~ "HARGA~~~" AS SALDO_AWAL_NILAI ~ ") 
COLUMN(NAME=~" saldo_stok.mutasi_debet ~ ") COMPUTE(NAME=~" ~~~ "saldo_stok~~~".~~~ "MUTASI_DEBET~~~" *~~~ "saldo_stok~~~".~~~ "HARGA~~~" AS MUTASI_DEBET_NILAI ~ ") COLUMN(NAME=~" saldo_stok.mutasi_keluar ~ ") COMPUTE(NAME=~" ~~~ "saldo_stok~~~".~~~ "MUTASI_KELUAR~~~" *~~~ "saldo_stok~~~".~~~ "HARGA~~~" AS MUTASI_KELUAR_NILAI ~ ") COLUMN(NAME=~" saldo_stok.saldo_akhir ~ ") COMPUTE(NAME=~" ~~~ "saldo_stok~~~".~~~ "SALDO_AKHIR~~~" *~~~ "saldo_stok~~~".~~~ "HARGA~~~" AS SALDO_AKHIR_NILAI ~ ") COLUMN(NAME=~" saldo_stok.harga ~ ") COLUMN(NAME=~" saldo_stok.tgl_terima ~ ") COLUMN(NAME=~" saldo_stok.refference ~ ") COLUMN(NAME=~" saldo_stok.kode_barang ~ ")    JOIN (LEFT=~" saldo_stok.kode_rekening ~ "    OP =~" =~ "RIGHT=~" rekening.nomer_rekjurnal ~ "    OUTER1 =~" saldo_stok.kode_rekening ~ " )WHERE(    EXP1 =~" (
    ( ~~~ "saldo_stok~~~".~~~ "NAMA_BARANG~~~" ~ "   OP =~" LIKE ~ "    EXP2 =~" : brg ) ~ "    LOGIC =~" 
    AND ~ " ) WHERE(    EXP1 =~" ( ~~~ "saldo_stok~~~".~~~ "USER_ID~~~" ~ "   OP =~" =~ "    EXP2 =~" : usr ) ~ "    LOGIC =~" 
    AND ~ " ) WHERE(    EXP1 =~" ( ~~~ "saldo_stok~~~".~~~ "SALDO_AWAL~~~" ~ "   OP =~" <>~ "    EXP2 =~" 0 ) 
) ~ " ) ) ARG(NAME = ~" brg ~ " TYPE = string)  ARG(NAME = ~" usr ~ " TYPE = number)  ARG(NAME = ~" hak ~ " TYPE = string) " arguments = ( ( "brg", string ), ( "usr", NUMBER ), ( "hak", string ) ) sort = "saldo_rek A nama_barang A saldo_stok_urutan A sat_hitung2 A " 
) GROUP (
    LEVEL = 1 header.height = 84 trailer.height = 0 BY = ( "saldo_rek" ) header.color = "536870912" header.transparency = "0" header.gradient.color = "8421504" header.gradient.transparency = "0" header.gradient.angle = "0" header.brushmode = "0" header.gradient.repetition.MODE = "0" header.gradient.repetition.count = "0" header.gradient.repetition.length = "100" header.gradient.focus = "0" header.gradient.scale = "100" header.gradient.spread = "100" trailer.color = "536870912" trailer.transparency = "0" trailer.gradient.color = "8421504" trailer.gradient.transparency = "0" trailer.gradient.angle = "0" trailer.brushmode = "0" trailer.gradient.repetition.MODE = "0" trailer.gradient.repetition.count = "0" trailer.gradient.repetition.length = "100" trailer.gradient.focus = "0" trailer.gradient.scale = "100" trailer.gradient.spread = "100" 
    ) GROUP (
LEVEL = 2 header.height = 4 trailer.height = 4 BY = ( "nama_barang", "harga", "sat_hitung2" ) header.color = "536870912" header.transparency = "0" header.gradient.color = "8421504" header.gradient.transparency = "0" header.gradient.angle = "0" header.brushmode = "0" header.gradient.repetition.MODE = "0" header.gradient.repetition.count = "0" header.gradient.repetition.length = "100" header.gradient.focus = "0" header.gradient.scale = "100" header.gradient.spread = "100" trailer.color = "536870912" trailer.transparency = "0" trailer.gradient.color = "8421504" trailer.gradient.transparency = "0" trailer.gradient.angle = "0" trailer.brushmode = "0" trailer.gradient.repetition.MODE = "0" trailer.gradient.repetition.count = "0" trailer.gradient.repetition.length = "100" trailer.gradient.focus = "0" trailer.gradient.scale = "100" trailer.gradient.spread = "100" 
)
Solution

  • What you’re looking at is not something the majority of PowerBuilder developers would normally look at. What you’re showing us is:

    • an export (not normally used to look at code)
    • a portion of a DataWindow
    • a DW’s query stored in graphical format

    Graphical format is… well, first off, it’s meant to be presented in the PowerBuilder IDE in (spoiler alert) a graphical presentation. Not only that, but it was meant to be stored in a somewhat neutral syntax so that it could be converted to SQL in slightly different ways, depending on the database driver used. Any efforts I’ve seen to convert graphical syntax to SQL overlooked the driver factor, so I’ve never seen one that was universal. (Plus, given that storing in graphical produced a slight performance impact when PB had to convert to SQL at run time, I’ve never found it to be very popular, so there wasn’t much demand for a conversion utility.)

    I’d strongly recommend ditching the exports and looking at the queries in the PowerBuilder IDE. You’ll see it in normal SQL format.