Retrieve an environment variable

Retrieve an environment variable

ContextKeyword lcxk_base
string ls_Path
string ls_values[]

this.GetContextService("Keyword", lcxk_base)
lcxk_base.GetContextKeywords("path", ls_values)
IF Upperbound(ls_values) > 0 THEN
   ls_Path = ls_values[1]
ELSE
   ls_Path = "*UNDEFINED*"
END IF

Common XP environment variables:

ALLUSERSPROFILE      location of the All Users Profile.
APPDATA              location where applications store data by default.
CD                   current directory string.
CLIENTNAME           client's NETBIOS name when connected to terminal server session.
CMDCMDLINE           command line used to start the current cmd.exe.
CMDEXTVERSION        version number of the current Command Processor Extensions.
CommonProgramFiles   path to the Common Files folder.
COMPUTERNAME         name of the computer.
COMSPEC              path to the command shell executable.
DATE                 current date.
ERRORLEVEL           error code of the most recently used command.
HOMEDRIVE            drive letter is connected to the user's home directory.
HOMEPATH             full path of the user's home directory.
HOMESHARE            network path to the user's shared home directory.
LOGONSEVER           name of the domain controller that validated the current logon session.
NUMBER_OF_PROCESSORS   number of processors installed on the computer.
OS                   name of the operating system.
                     (Windows XP and Windows 2000 list the operating system as Windows_NT.)
Path                 search path for executable files.
PATHEXT              file extensions that the operating system considers to be executable.
PROCESSOR_ARCHITECTURE   processor's chip architecture.
PROCESSOR_IDENTFIER  description of the processor.
PROCESSOR_LEVEL      model number of the computer's processor.
PROCESSOR_REVISION   revision number of the processor.
ProgramFiles         path to the Program Files folder.
PROMPT               command-prompt settings for the current interpreter.
RANDOM               random decimal number between 0 and 32767.
SESSIONNAME          connection and session names when connected to terminal server session.
SYSTEMDRIVE          drive containing the Windows root directory.
SYSTEMROOT           location of the Windows root directory.
TEMP and TMP         default temporary directories for applications that are available to
                     users who are currently logged on.
TIME                 current time.
USERDOMAIN           name of the domain that contains the user's account.
USERNAME             name of the user currently logged on.
USERPROFILE          location of the profile for the current user.
WINDIR               location of the OS directory.

Read More

Get a list of printers installed

In this example, we populate a listbox with the printers name

/* Get Printer List */
string printers[]
int rtn, i, nbPrinters
rtn = RegistryKeys &
   ("HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Print\Printers", &
      printers)
nbPrinters = UpperBound(printers)
FOR i = 1 TO nbPrinters
    lb_1.addItem(printers[i])
NEXT

Read More

List available ODBC datasources

You can list available ODBC datasources from within PowerBuilder. You need to declare the following external functions :

FUNCTION integer SQLAllocEnv(ref long henv) LIBRARY "odbc32.dll"
FUNCTION integer SQLFreeEnv(long henv) LIBRARY "odbc32.dll"
FUNCTION integer SQLDataSources &
(long henv, integer idirection, ref string szdsn, int idsnmax, &
ref integer idsn, ref string szdesc, integer idescmax, ref integer idesc) &
library "odbc32.dll"

The following snippet will initialize a DropDownListbox with DataSources defined on the current workstation.

long ll_henv
string ls_dsn, ls_desc
integer li_direction, li_dsnmax, li_dsnlen, li_descmax, li_desclen, li_rc
integer li_length = 255

ls_dsn = Space(li_length)
li_dsnmax = li_length
ls_desc = Space(li_length)
li_descmax = li_length

IF SQLAllocEnv(ll_henv) = -1 THEN
    MessageBox("SQLAllocEnv", "FAILURE")
ELSE
    li_direction = 1
    DO WHILE SQLDataSources &
        (ll_henv, li_direction, ls_dsn, li_dsnmax, li_dsnlen, &
         ls_desc, li_descmax, li_desclen) = 0
         ddlb_1.AddItem(ls_dsn + "  [" + ls_desc + "]")
    LOOP
    SQLFreeEnv(ll_henv)
END IF

Read More

How to get the current DBMS, Database or user through ODBC

These useful informations can be retrieved via direct calls to the ODBC API. This way we don't need DBMS-specific SELECT statement.
External Function Declaration :

FUNCTION integer SQLGetInfo  &
  (long hconn, integer infotype, ref string infotypeptr, &
   integer bufferlength, ref integer bufferlengthptr) &
  LIBRARY "odbc32.dll"

PowerScript:

string ls_dbms, ls_database, ls_user
integer li_length
CONSTANT integer SQL_DBMS_NAME = 17
CONSTANT integer SQL_DATABASE_NAME = 16
CONSTANT integer SQL_USER_NAME = 47
long   ll_dbhandle

ls_dbms = space(256)
ls_database = space(256)
ls_user = space(256)
ll_dbhandle = SQLCA.DbHandle()
SQLGetInfo(ll_dbhandle, SQL_DBMS_NAME, ls_dbms, 255, li_length)
SQLGetInfo(ll_dbhandle, SQL_DATABASE_NAME, ls_database, 255, li_length)
SQLGetInfo(ll_dbhandle, SQL_USER_NAME, ls_user, 255, li_length)

MessageBox("Current DBMS" , trim(ls_dbms))
MessageBox("Current DATABASE" , trim(ls_database))
MessageBox("Current USER" , trim(ls_user))

Read More

Sorting Datawindow

Here's a script to sort datawindow rows when the column header is clicked as in windows explorer.
Requirements : Column header should be same as column name and ended with '_t'. For Example, Column Name : 'emp_id'
Column Header : 'emp_id_t'
Condition : Only one column can sort at a time.

String ls_old_sort, ls_column
Char lc_sort
/* Check whether the user clicks on the column header */
IF Right(dwo.Name,2) = '_t' THEN
    ls_column = LEFT(dwo.Name, LEN(String(dwo.Name)) - 2)
    /* Get old sort, if any. */
    ls_old_sort = dw_1.Describe("Datawindow.Table.sort")
    /* Check whether previously sorted column and currently clicked column are same or not. If     both are same then check for the sort order of previously sorted column (A - Asc, D - Des) and     change it. If both are not same then simply sort it by Ascending order. */
    IF ls_column = LEFT(ls_old_sort, LEN(ls_old_sort) - 2) THEN
         lc_sort = RIGHT(ls_old_sort, 1)
        IF lc_sort = 'A' THEN
             lc_sort = 'D'
        ELSE
             lc_sort = 'A'
        END IF
        dw_1.SetSort(ls_column+" "+lc_sort)
    ELSE
        dw_1.SetSort(ls_column+" A")
    END IF
    dw_1.Sort()
END IF

Read More

Hexadecimal to Decimal

Here's function to convert Hexadecimal number to Decimal number

/**********************************************/
/*  public long uf_Hex2Dec ( string as_Hex )  */
/**********************************************/

CONSTANT STRING ls_HexSet = "0123456789ABCDEF"

STRING  ls_Hex, ls_Bit
LONG    ll_Div, ll_RetVal = 0
INTEGER li_C, li_Len, li_Pos
BOOLEAN lb_Error = FALSE

ls_Hex = Upper( as_Hex )

IF NOT IsNull( ls_Hex ) AND ls_Hex <> "" THEN
   
    li_Len = Len( ls_Hex )
   
    FOR li_C = 1 TO li_Len
        ls_Bit = Mid( ls_Hex, li_C, 1 )
        li_Pos = Pos( ls_HexSet, ls_Bit )
       
        IF li_Pos = 0 THEN
            lb_Error = TRUE
        ELSE
            ll_RetVal += ( ( li_Pos - 1 ) * ( 16 ^ ( li_Len - li_C ) ) )
        END IF
    NEXT
    IF lb_Error THEN ll_RetVal = 0
END IF

RETURN ll_RetVal

Read More

Julian Date

Here's a function to calculate Julian Day number for the specified day, month, year. If the year is B.C. it must be negative.

/*  public long uf_JulianDate ( integer ai_Day, integer ai_Month, integer ai_Year )   */
DOUBLE  lr_ycorr
LONG    ll_Day, ll_Month, ll_Year, ll_ca, ll_cb, ll_RetVal = 0

ll_Day = Long( ai_Day );  ll_Month = Long( ai_Month );  ll_Year = Long( ai_Year );

IF ll_Day > 0 OR ll_Month > 0 OR ll_Year > 0 THEN
    IF ll_Year > 0 THEN
        lr_ycorr = 0.0
    ELSE
        lr_ycorr = 0.75
    END IF
    
    if ll_Month <= 2 THEN
        ll_Year  = Long( ll_Year  -  1 )
        ll_Month = Long( ll_Month + 12 )
    END IF
   
    ll_cb = 0
   
    IF ( ( Double( ll_Year ) * Double( 10000.0 ) + Double( ll_Month ) * Double( 100.0 ) + &
         Double( ll_Day ) ) >= Double( 15821015.0 ) ) THEN
      ll_ca = Long( ll_Year / 100 )
        ll_cb = Long( 2 - ll_ca ) + Long( ll_ca / 4 )
    END IF
   
    ll_RetVal = Long( Double( 365.25  ) * Double( ll_Year ) - lr_ycorr ) +  &
                    Long( Double( 30.6001 ) * Double( ll_Month + 1 ) ) +  &
                    Long( Double( ll_Day  ) + Double( 1720994.5 ) + Double( ll_cb ) )
END IF

RETURN ll_RetVal

Read More

Hiding Application on Windows Taskbar

With this simple tips you can hide the application on Windows Taskbar even if its running
1. Declare Win32 API module on Declare -> Local External Functions:

FUNCTION boolean SetWindowLongA (ulong hWnd, int nIndex, long newValue) Library "user32.dll"

2. Add below codes on Open event on main window

IF NOT SetWindowLongA(Handle(This), -20,128) THEN
   messageBox ('Error','Cannot Hide!')
END IF

In order the code to take effect you should compile your application into EXE first

Read More

Hiding Desktop and Taskbar

With this tips you can easily hide the desktop and taskbar from the user

1. Declare win32 API module on Declare -> Local External FUnctions

Function Long FindWindowEx (Long hwnd1, Long hwnd2, String lpsz1, String lpsz2) Library "user32" Alias For "FindWindowExA"
Function Long ShowWindow (Long hwnd, Long nCmdShow) Library "user32" Alias For "ShowWindow"

2. Declare constants below on Declare -> Instance Variables

Constant Long SW_HIDE = 0
Constant Long SW_NORMAL = 1
Constant Long SW_SHOWMINIMIZED = 2
Constant Long SW_SHOWMAXIMIZED = 3
Constant Long SW_SHOWNOACTIVATE = 4
Constant Long SW_SHOW = 5
Constant Long SW_MINIMIZE = 6
Constant Long SW_SHOWMINNOACTIVE = 7
Constant Long SW_SHOWNA = 8
Constant Long SW_RESTORE = 9
Constant Long SW_SHOWDEFAULT = 10

3. Type the example codes below to hide the desktop and taskbar

String ls_ShellViewWnd = "Progman"
String ls_ShellTaskBarWnd = "Shell_TrayWnd"
String ls_Null
Long ll_HTaskBar, ll_HDeskTop

setNull (ls_Null)
//Hide TaskBar
ll_HTaskBar = FindWindowEx ( 0, 0, ls_ShellTaskBarWnd, ls_Null)
ShowWindow ( ll_HTaskBar, SW_HIDE )
//Hide Desktop
ll_HDeskTop = FindWindowEx ( 0, 0, ls_ShellViewWnd, ls_Null )
ShowWindow ( ll_HDeskTop, SW_HIDE )

To show up the desktop and taskbar again, use codes below

//Show Task Bar
ll_HTaskBar = FindWindowEx ( 0, 0, ls_ShellTaskBarWnd, ls_Null)
ShowWindow ( ll_HTaskBar, SW_SHOW )
//Show Desktop
ll_HDeskTop = FindWindowEx ( 0, 0, ls_ShellViewWnd, ls_Null )
ShowWindow ( ll_HDeskTop, SW_SHOW )

Read More

Keeping Column into Array

With this simple tips you can keep all the column name on DataWindow into an array

int colNum, numCols
string colName[]

numCols = Integer(dw_control.Describe("DataWindow.Column.Count"))

FOR colNum = 1 TO numCols //Get Column Name with describe
    colName[colNum] = dw_control.Describe("#"+String(colNum) +".name")
NEXT

Read More

Calling Oracle Stored Procs/Functions from PB

Whenever you want to make a call to an Oracle stored procedure or stored function, a good approach is to first declare it as an external function and then invoke it based on that declaration.

A. Declaring an Oracle Stored Procedure so that PowerBuilder Knows About it
This function/procedure declaration is done in the transaction user object. First create transaction object. Select File > New then choose Standard Class under Object Tab. Then select Transaction Type on Select Standard Class Type. Once inside the transaction user object, choose "Declare-Local External Functions" and follow the syntax below.

1. Stored Procedure (no package)
   The declaration syntax for a stored procedure (on its own, outside package) is:
   
   

       SUBROUTINE SubRtnName(args) RPCFUNC

   
   
   In example, the declaration passes a string by value (i.e. IN) and a string by reference (i.e. IN OUT or OUT).
   
   

       SUBROUTINE CalcAmount(string LS_In1, ref string LS_Out2) RPCFUNC

   
   Notes:
   
   • if the procedure is not in a package and does not take any array parameters, then you can click the procedures button to paste in the procedure declaration directly from the database.
   • an optional alias clause can be added to allow PowerBuilder to use a different function name from Oracle (see alias format used with package declarations).
2. Procedure inside an Oracle package
   The declaration syntax for a stored procedure inside a package is:
   
   

       SUBROUTINE SubRtnName(args) RPCFUNC ALIAS FOR "PackageName.ProcName"

   
   
   In example, the declaration passes a string by value (i.e. IN) and a string array by reference
   (i.e. IN OUT or OUT).
   
   

       SUBROUTINE CalcPenaltyAmt(string LS_In1, ref string LS_Out2[]) RPCFUNC ALIAS FOR "Penalty.P_Calc_Amount"

3. Stored Function (no package)
   The declaration syntax for a stored function (on its own, outside package) is:
   
   

       FUNCTION ReturnType FcnName(args) RPCFUNC

   
   
   In example, the declaration passes a string by value (i.e. IN) and a string array by reference (i.e. IN OUT or OUT) and it returns a long.
   
   

       FUNCTION long CalcAmount(string LS_In1, ref string LS_Out2[]) RPCFUNC

   
   
   Note: the same notes given for stored procedure declarations apply to stored functions.
4. Function inside an Oracle package
   Function inside an Oracle package
   
   The declaration syntax for a stored function inside a package is:
   
   

       FUNCTION ReturnType FcnName(args) RPCFUNC ALIAS FOR "PackageName.FunctionName"

   
   
   In example, the declaration passes a string by value (i.e. IN) and a string array by reference (i.e. IN OUT or OUT) and returns a long.
   
   

       FUNCTION long CalcPenaltyAmt(string LS_In1, ref string LS_Out2[])) RPCFUNC ALIAS FOR "Penalty.f_Calc_Amount"

   
   

B. Invoking an Oracle Stored Procedure/Function
This is the invocation syntax for a stored procedure/function that has been declared in the transaction object is shown below.
Notes on Variables passed by Reference:

• Dynamically-sized output variables (i.e. strings and arrays) must be preallocated up to the size needed. When using this invocation method, PowerBuilder does not dynamically allocate the space needed for them.
• Array Size Limitation: number of array elements times maximum element size cannot exceed 32K.

1. Invoking a Stored Procedure
   The invocation syntax for a stored procedure is:
   
   

       TransactionObjectName.ProcName(args)

   
   
   Sample invocation:
   

   
      string in_parm1
      string out_parm2
   
      in_parm1 = "input value"
      out_parm2 = space(50) // preallocating space for string
   
      SQLCA.CalcAmount(in_parm1, out_parm2)

2. Invoking a Stored Function (shown using an array variable)
   The invocation syntax is:
   
   

       ReturnValue = TransactionObjectName.FcnName(args)

   
   
   Sample invocation:
   

   
      string in_parm1
      string out_parm2[5] // defining fixed sized array
      long ll_return
   
      in_parm1 = "input value"
   
      // preallocating space for 500 chars for whole string array.
      // Each element will effectively be 500 bytes by allocating
      // the first.
      out_parm2[1] = space(500)
   
      ll_Return = SQLCA.CalcAmount(in_parm1, out_parm2[])

   
   
   
   

Read More

Re-initializing an Unbounded Array

Suppose that you have been filling an array with values. Now you want to reinitialize the array to its original empty state. PowerBuilder doesn’t have an explicit function to help re-initialize the array, so you have to do it manually as described below.

Declare a dummy array of the same type. Never put any values into this array. When you want to re-initialize your "working array", assign it to the value of the empty array. This will clear out the "working" array and make the UpperBound function return the correct value (zero). See sample code below:

string ls_EmptyArray[]
string ls_NameArray[]

ls_NameArray[1] = "James"
... more work ...

// empty out Name Array
ls_NameArray = ls_EmptyArray

Read More

Tracing on Running Application

On PowerBuilder we can trace the running compiled application (*.exe) by running application with the /PBDEBUG option.

application_name.exe /PBDEBUG

After that, just run your application as usual. On the same directory with the compiled PowerBuilder application, you will find file with an extension *.dbg ( eg. application_name.dbg). Within this file you will find useful orderly manner information . This is just an alternative for debugging the program.

Read More

Using Windows Scripting Host

You can use Windows Scripting Host on PowerBuilder for many purpose. On sample code below, you can use it to get the network domain, user name and computer name. To do so, you need Windows Scripting Host Object Model component (wshom.ocx)

integer li_rc
OleObject ole_wsh

ole_wsh = CREATE OleObject
li_rc = ole_wsh.ConnectToNewObject ( "WScript.Network" )
IF li_rc = 0 THEN
     MessageBox ("Domain", string( ole_wsh.UserDomain ))
     MessageBox ("User", string( ole_wsh.UserName ))
     MessageBox ("Computer", string( ole_wsh.ComputerName ))
END IF

With WSH, you can execute other application from PowerBuilder application. On example below, Notepad will be called by clicking a button from PowerBuilder application

integer li_rc
OleObject ole_wsh

ole_wsh = CREATE OleObject
li_rc = ole_wsh.ConnectToNewObject ( "WScript.Shell" )
ole_wsh.Run ("Notepad")
ole_wsh.AppActivate("Untitled - Notepad")
Sleep (500)
ole_wsh.SendKeys (''Hello from PB")

Sleep is a function from Win32 API module with a declaration like this :

Subroutine Sleep (Long dwMilliseconds) Library "kernel32" Alias for "Sleep"

You also can run Calculator program and send some key stroke on it

integer li_rc
OleObject ole_wsh

ole_wsh = CREATE OleObject
 li_rc = ole_wsh.ConnectToNewObject ( "WScript.Shell" )
ole_wsh.Run("calc")
ole_wsh.AppActivate ("Calculator", 100)
Sleep (1000)
ole_wsh.SendKeys("1{+}")
Sleep (1000)
ole_wsh.SendKeys("2")
Sleep (1000)
ole_wsh.SendKeys("=")
Sleep (1000)
ole_wsh.SendKeys("*4")
Sleep (1000)
ole_wsh.SendKeys("=")
// 1+2 = ( 3, then) * 4 = 12

You also can run VBScript from PowerBuilder easily. To do that you need Microsoft Script Control component (msscript.ocx)

OleObject ole_wsh
Integer li_rc, i, j, k

ole_wsh = Create OleObject
li_rc = ole_wsh.ConnectToNewObject ("MSScriptControl.ScriptControl")
ole_wsh.language = "vbscript"
ole_wsh.addcode("function retfnc(s) retfnc = s end function")
ole_wsh.executestatement ('if 1 > 1 then msgbox retfnc("true") else msgbox retfnc("false") end if

Read More