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.

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

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

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))

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

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

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

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

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 )

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

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[])

   
   
   
   

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

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.

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

When performing checks in business logic to see if a record is used as part of a foreign key in a child table people often opt for the Select Count(*) type SQL call. This is a clean way to check for dependant rows but not necessarily the fastest. The Count(*) function call may result in a table scan. A better way to check for this is to make use of a handy Oracle feature called ROWNUM. Using this feature we can select a single row matching our parent key and then check the number of rows returned. This will be quicker than the Count(*) method. For example:
SQL Using Count(*)

SELECT Count(*) INTO :ll_Count
      FROM ORDER
      WHERE PROD_ID = :ls_CheckProd
      USING SQLCA;
IF ll_Count > 0 THEN // Cannot delete product

SQL Using ROWNUM

SELECT ORDER_ID INTO :ll_OrderID
      FROM ORDER
      WHERE PROD_ID = :ls_CheckProd
         AND ROWNUM < 2        USING SQLCA;     
IF SQLCA.SQLNRows <> 0 THEN // cannot delete product

A common problem when setting up validation logic in ItemChanged is that two message get
displayed. First, the intended message in ItemChanged and then a default PowerBuilder message:
"Item '[value]' does not pass validation test". One simple solution to avoid this problem is to
have descendents set a flag (ib_SuppressMsg) in the ItemChanged event, when they've already
displayed an error message.
In ItemChanged event:

IF ... error situation ... THEN
  ... display message ...
  li_Return = 1 // reject value
END IF

// Bottom of ItemChanged script ...
// set flag so ItemError know to suppress its default message
IF li_Return = 1 THEN
  ib_SuppressMsg = True
END IF

In ItemError, check this flag and, if set, return 1 so the default ItemError message is suppressed:

IF ib_SuppressMsg THEN
  li_Return = 1
  ib_SuppressMsg = False // reset flag for next time
ELSE
  ... error not caused by ItemChanged
END IF

A common requirement for applications is to capture certain keystrokes and perform special
actions when the user presses these keys. To capture keystrokes on your DataWindow, do the
following:

1. Declare an event on your DataWindow (possibly your ancestor DW, as is done here) and assign that event the pbm_dwnkey event id. This causes all keystrokes to be processed through the event.
2. Code that event to handle the special keystrokes in the desired manner. Ideally, common keystrokes (like Enter, Tab) will be captured at the ancestor level and a standard event will be called. A sample event that catches certain keystrokes is shown below.

In this sample event, the following keystrokes can be captured:

• tabs: to do so, a descendant DW sets the ib_CaptureTab instance variable to True. The
  ue_Tab or ue_BackTab event are then invoked when Tab/BackTab is pressed to allow
  the descendant to code appropriate logic.
• enter keys: to do so, a descendant DW sets the ib_CaptureEnter instance variable to True.
  The ue_Enter event is then called when Enter is pressed to allow the descendant to code
  appropriate logic.

sample ue_key event code:

// if descendent DW wants tab ... capture it
IF ib_CaptureTab AND KeyDown(KeyTab!) THEN
  // determine whether it is a tab or back-tab
  IF NOT KeyDown(KeyShift!) THEN
    This.POST ue_Tab()
  ELSE
    This.POST ue_BackTab()
  END IF
  Return 1 // have key not otherwise be ignored by PB

// if descendent DW wants enter keys ... capture it
ELSEIF ib_CaptureEnter AND KeyDown(KeyEnter!) THEN
  This.POST ue_Enter()
  Return 1 // have key not otherwise be ignored by PB

// otherwise send the key to the window-level for standard processing
ELSE
  IF IsValid(iw_ParentWin) THEN
    iw_ParentWin.TriggerEvent(Key!)
  ELSE
    // how did we get a key event for invalid parent?
    f_SignalError(0/gi_zero,"Invalid Parent Window in Key Event")
  END IF
END IF

Note: a handle to the parent window (iw_ParentWin) is obtained in the Constructor event. PFC's
of_GetParentWindow function is a good method to generically get a handle to the parent
window.

You can centralized your windows with the following code and put it on open event:

LONG   ll_X, ll_Y, ll_XCtr, ll_YCtr
WINDOW      lWin

lWin = aw_Window.ParentWindow();

ll_XCtr = lWin.X + ( lWin.Width  / 2 );
ll_YCtr = lWin.Y + ( lWin.Height / 2 );

ll_X = ll_XCtr - ( aw_Window.Width  / 2 );
ll_Y = ll_YCtr - ( aw_Window.Height / 2 );

IF ll_X < 0 THEN ll_X = 0;
IF ll_Y < 0 THEN ll_Y = 0;

RETURN aw_Window.Move( ll_X, ll_Y )

For MDI child window you can use the following code:

LONG   ll_X, ll_Y, ll_XCtr, ll_YCtr
WINDOW      lWin

lWin = aw_Window.ParentWindow();

IF ( ( ( lWin.WindowType = MDI! ) OR ( lWin.WindowType = MDIHelp! ) ) AND  &
  ( aw_Window.WindowType = Child! ) ) THEN
 ll_XCtr = lWin.WorkSpaceWidth() / 2;
 ll_YCtr = lWin.WorkSpaceHeight() / 2;

 IF lWin.WindowType = MDIHelp! THEN
  ll_YCtr = ll_YCtr - 40;
 END IF

 ll_X = ll_XCtr - ( aw_Window.Width  / 2 );
 ll_Y = ll_YCtr - ( aw_Window.Height / 2 ) - 200;

 IF ll_X < 0 THEN ll_X = 0;
 IF ll_Y < 0 THEN ll_Y = 0;
ELSE
 ll_X = aw_Window.X;
 ll_Y = aw_Window.Y;
END IF

RETURN aw_Window.Move( ll_X, ll_Y )

When building list windows for users you often want to allow them to specify search criteria. If the search criteria are simple and only have a single field or all fields must be entered than a simple WHERE clause with retrieval arguments will do the job.

For very complicated arguments with multiple selections you will have to resort to dynamically altering the SQL behind the datawindow. But you may not realize that if you do not require multiple selections for a single field then with some clever SQL coding you can avoid time consuming dynamic SQL.

For example if we were building a selection window for employees, you may want to allow the user to search based on employee number, employee name, Address or any combination of the three. We can achieve this by declaring three retrieval arguments of the correct datatypes. In your retrieve script on the window you would have designed the arguments input criteria using a datawindow! so you could select the empty field is null option of the edit control. If you have not and shame on you then if the field is empty you will need to manually set it to null.
Then in the SQL you would code for the NULL values:

SELECT emp_no, emp_name, emp_addr1, emp_telno
 FROM employee
 WHERE ( emp_no = :al_emp_no OR :al_emp_no IS NULL )
   AND ( emp_name = :as_emp_name OR :as_emp_name IS NULL )
   AND ( emp_addr1 = :as_emp_addr OR :as_emp_addr1 IS NULL )

You would also want to concatenate '%' on to the end of the strings to allow for pattern matching and convert both sides of the comparison to Lower case to make it more user friendly, you would convert the retrieval argument to lower case once in Powerscript and pass it to the datawindow:

( Lower( emp_name ) LIKE :as_emp_name...

In this tips, I would like to discuss how to make an application run only once at a time. When user try to run your application, first try to see if it is already running. If it is already running bring it to top and return. There are some sleek SDK functions you can make use of to implement this. The following are the steps to create this feature:

1. Create a "Custom Class UserObject"
   
2. Set the AutoInstantiate to TRUE
3. Declare the Win32 API modul at the UserObject on Declare -> Local External Functions
   

   Function Long GetLastError () Library 'kernel32.dll'
   Function ULong CreateMutex (ULong lpsa, Boolean fInitialOwner, String lpszMutexName) Library 'kernel32.dll' Alias for CreateMutexA

4. Create a UserObject Function like this:
   

   String ls_name
   If Handle(GetApplication()) > 0 Then
   ls_name = GetApplication().AppName + Char(0)
   CreateMutex(0, True, ls_name)
   If GetLastError() = 183 Then Return True
   End If
   Return False

5. Save the UserObject as uo_mutex (for example)

Below is how to use the object. Put this code at the beginning of open event from your Application object:

uo_mutex lou_mutex
If lou_mutex.uf_isrunning() Then
  MessageBox ('Warning', 'Application is already running', StopSign!)
  HALT CLOSE
End If
//...
// Your next line code
//...

If you want to use the Win32 API within PowerBuilder, you have to know the prototype modul. The following list is not an exhaustive list of all C and C++ to PowerBuilder Datatype but most of the common Datatype that I've used over the years of interfacing PowerBuilder and C/C++ programs. There are two lists, the first is PowerBuilder to C++ Datatype, useful for the C Proxy Generator. Then there is a list of C/C++ to PowerBuilder Datatype useful for people writing DLL's and the like:

PowerBuilder Datatype	C/C++ Datatype
Blob	PBBlob
Boolean	int
Character	char
Date	PBDate
DateTime	PBDateTime
Decimal	PBDecimal
Double	double
Integer	int
Real	float
String	PBString
Time	PBTime
UnSignedInteger	unsigned int

C/C++ Datatype	PowerBuilder Datatype
BOOL	Boolean
WORD	UnSignedInteger
DWORD	UnSignedLong
HANDLE	UnSignedLong
HWND	UnSignedLong
LPSTR	String Ref
LPBYTE	String Ref
LPINT	Long Ref
char	Blob {1}
int	Integer
unsigned int	UnsignedInt
long	Long
ULONG/unsigned long	UnsignedLong
double	Double
char *	String Ref

Having problems with OCX's which work fine in development and EXE on your machine but when you ship the EXE to a users machine and install your program the OCX does not work?

The problem is that the way OCX's are designed to work is that every OCX should attempt on its own to register with the system during its constructor event. This is done by the container calling a function that is in EVERY OCX called DLLRegisterServer.

The problem is that PowerBuilder does not call this function. Even if you run the REGSRV utility, the OCX will fail to register itself. To correct this problem, in object where you are using the OCX you need to goto the constructor event and call the OCX DLLRegesterServer function.

Declare a local external function in the container object

Function long DllRegisterServer() Library "ocxname.OCX"

In the constructor event

LONG ll_RC
ll_RC = DllRegisterServer()

Using the Get Volume API tip previously posted will return the names of the drives connected to your machine. Unfortunately for network drives this returns the name of the physical drive not the name of the share you have mapped to. If you have many shares mapped to a single physical network drive you will not be able to distinguish between the drives.

To improve this situation you can use an API call to return the name of the network share. This is more meaningful and easier for the user. The API call you will need to use is WNetGetConnectionA. To use the API call declare the local external function as follows:

function ulong WNetGetConnectionA( string szLocalPath, &
   ref string szNameBuffer,  ref ulong lpBufferSize) Library "mpr.dll"

Then in your script you can call the API as follows:

Ulong lul_Max
Long ll_RC
String ls_Drive, ls_Volume

// hard coded for the f: drive
ls_Drive = 'f:'
lul_Max = 2000

// pre allocate string to stop GPF
ls_Volume = Space( 2000 )

// call the API function, the share name
// will be in the ls_Volume variable
ll_RC = WNetGetConnectionA( ls_Drive, ls_Volume, lul_Max )
ls_Volume = Trim( ls_Volume )

In order to show a list of Drives We needed to get the name of the Drive. The following API call gets the volume name as well as the serial number.

Create an NVO add the following local external function:

function long GetVolumeInformationA( ref string ls_RootPath, &
 ref string ls_VolName, long ll_VolLen, ref string ls_volserial, &
 long ll_maxcomplen, long ll_systemflags, &
 ref string ls_SystemName,&
 long ll_SystemLen ) Library 'kernel32'

Then add a function called GetVolumeName which accepts a string and returns a string and add the following code:

// Call the API function to get the volume label from a drive letter
Long ll_Max, ll_Flags, ll_Ret, ll_FileSys
Long ll_Vol
String ls_Vol
String ls_Drv, ls_FileSys, ls_Flags, ls_Serial


ls_Drv = as_Vol
ls_Vol = Space(32)
ls_FileSys = Space(32)
ls_Serial = Space(32)
ll_Vol = Len( ls_Vol )
ll_FileSys = Len( ls_Filesys )

ll_Ret = GetVolumeInformationA( ls_Drv, ls_Vol, ll_Vol, &
  ls_Serial, ll_Max, ll_Flags, ls_FileSys, ll_FileSys )

IF (ll_Ret = 0) THEN
  ls_Vol = ''
ELSE
  ls_Vol = Trim( ls_Vol )
END IF

RETURN (ls_Vol)

Call the function passing the drive you want the volume name for. For example C:\

This tip show how to use the Window API for mapping a network drive from PowerBuilder.
Function Declaration:

FUNCTION ulong WNetUseConnectionA (ulong hwndOwner, &
   REF s_netresource lpNetResource, string lpPassword,
   string lpUsername, ulong dwFlags, REF string lpAccessName, &
   REF ulong lpBufferSize, REF ulong lpResult) library "mpr.dll"

Structure Definition:

$PBExportHeader$s_netresource.srs
global type s_netresource from structure
 unsignedlong  dwScope
 unsignedlong  dwType
 unsignedlong  dwDisplayType
 unsignedlong  dwUsage
 string  lpLocalName
 string  lpRemoteName
 string  lpComment
 string  lpProvider
end type

Mapping Code:

CONSTANT ulong NO_ERROR = 0
CONSTANT ulong CONNECT_REDIRECT = 128
CONSTANT ulong RESOURCETYPE_DISK = 1

s_netresource lstr_netresource

String   ls_null
String   ls_buffer
String   ls_MappedDrive

uLong    ll_bufferlen
uLong    ll_null
uLong    ll_ErrInfo
uLong    ll_success

SetNull(ll_null)
SetNull(ls_null)

ls_buffer = Space(32)
ll_bufferlen = Len(ls_buffer)

lstr_netresource.dwType = RESOURCETYPE_DISK
lstr_netresource.lpLocalName = ls_null
lstr_netresource.lpRemoteName = "UNC resource name here"
lstr_netresource.lpProvider = ls_null

ll_ErrInfo = WNetUseConnectionA(ll_null, lstr_netresource, &
   'password', 'username', &
   CONNECT_REDIRECT, ls_buffer, ll_bufferlen, ll_success)

IF ll_ErrInfo = NO_ERROR THEN
   MessageBox("Drive Mapped", "Drive Letter is " + ls_buffer)
   Return 1
ELSE
   MessageBox("Mapping Falied", "Error is " + String(ll_ErrInfo))
   Return -1
END IF

With this simple trick you can get the current active directory. Declare the Win32 API modul on Declare -> Local External Functions

Function boolean GetCurrentDirectoryA (long nBufferLength, ref string lpBuffer) Library "kernel32.dll"

Write this code:

string ls_temp
ulong lul_value
boolean lb_rc

lul_value = 255
ls_temp = space(255)
lb_rc = GetCurrentDirectoryA (lul_value, ls_temp)
If lb_rc Then MessageBox('Current Directory', ls_temp, information!)

You can get the computer name from within the application. Declare the Win32 API modul on Declare -> Local External Functions

Function boolean GetComputerNameA (ref string lpBuffer, ref ulong nSize) Library "kernel32.dll"

Now write this code:

string ls_temp
ulong lul_value
boolean lb_rc

lul_value = 255
ls_temp = space(255)
lb_rc = GetComputerNameA (ls_temp, lul_value)
messageBox ('Computer Name', ls_temp, information!)

With this tips, you can make a control likely pressing (a) key(s). First declare the Win32 API modul on Declare -> Local External Functions

Subroutine keybd_event( int bVk, int bScan, int dwFlags, int dwExtraInfo) Library "user32.dll"

Next is an example to simulate pressing "A" key

integer li_vkey
li_vkey = 65 // Character A
sle_1.setfocus() // the desired control to view
keybd_event( li_vkey, 1, 0, 0)

Another example to simulate "Backspace" key

integer li_vkey
integer li_pos
li_pos = len(sle_1.Text) + 1
sle_1.selectText(li_pos, 0)// Cursor position on last text
li_vkey = asc ("~b") // backspace
keybd_event( li_vkey, 1, 0, 0)

When using several types of printer for a special purpose, sometime we have to send hexadecimal character to control the printer and to by pass the printer driver.
The example is shown below

int li_job
li_job = PrintOpen()
// Print Mode
PrintText ( li_job, "~h1B~h21~001", 0, 0)
// Spacing
PrintText ( li_job, "~h1B~h33~001", 0, 0)
PrintClose ( li_job )

From PowerBuilder application, we can call (open) the default internet browser, as well as setting the default home page for the browser

Inet linet_base
GetContextService("Internet", linet_base)
linet_base.HyperlinkToURL("http://...")
If IsValid(linet_base) Then Destroy linet_base

By getting the date format from the Windows registry, we can use it for various need when you build a PowerBuilder application

RegistryGet("HKEY_CURRENT_USER\Control Panel\International","sShortDate", ls_shortdate
messageBox ("ShortDate", ls_shortdate)

Sometime when a PowerBuilder application is called by other program or a shell script, programmer want a return code after exiting the PowerBuilder application, just to get the status of the application called.

To give a return code, simply set the Message.LongParm property on the close event of the Application object with the desirable value.

   If ib_endingOK then
       Message.LongParm = 1
   Else
       Message.LongParm = 0
   End If

Sitemap :

• If Statements Using SQL
• Run an Application Only Once
• C/C++ Datatype Conversion
• Registering OCX Components in an Exe
• Get The Name of a Network Drive
  
• Mapping a Network Drive
• Getting Active Directory
• Getting Active Directory
• Getting Computer Name
• Sending Key Press
• Sending Hexadecimal Character to Printer
• Calling an Internet Browser from Application
• Getting Date Format from Windows Registry
• Return Code on Application Exit

Friend's Blog:

• Oracle Tips & Tricks
• Misterpopo Blog
• Indonesian Career Information Directory
• Avatar Fansite
• Info Karir

If you require any more information or have any questions about our privacy policy, please feel free to contact us by email at sprpopo@yahoo.com.

At power-builder.blogspot.com, the privacy of our visitors is of extreme importance to us. This privacy policy document outlines the types of personal information is received and collected by power-builder.blogspot.com and how it is used.

Log Files
Like many other Web sites, power-builder.blogspot.com makes use of log files. The information inside the log files includes internet protocol ( IP ) addresses, type of browser, Internet Service Provider ( ISP ), date/time stamp, referring/exit pages, and number of clicks to analyze trends, administer the site, track user’s movement around the site, and gather demographic information. IP addresses, and other such information are not linked to any information that is personally identifiable.

Cookies and Web Beacons
power-builder.blogspot.com does use cookies to store information about visitors preferences, record user-specific information on which pages the user access or visit, customize Web page content based on visitors browser type or other information that the visitor sends via their browser.

Some of our advertising partners may use cookies and web beacons on our site. Our advertising partners include Google Adsense, .

PowerBuilder Tips & Tricks utilizes certain services from Google for ad serving and web traffic analysis. Please review Google's privacy policy for more information on how Google AdSense and Google Analytics stores your personal information. These Google servies may place and read cookies on your browser, or use web beacons to collect information, in the course of ads being served on the PowerBuilder Tips & Tricks website. Additionally, Google uses the DART cookie to enables it to serve ads to based on your visits to PowerBuilder Tips & Tricks and other sites on the Internet. You may opt out of the use of the DART cookie by visiting the Google ad and content network privacy policy.

These third-party ad servers or ad networks use technology to the advertisements and links that appear on power-builder.blogspot.com send directly to your browsers. They automatically receive your IP address when this occurs. Other technologies ( such as cookies, JavaScript, or Web Beacons ) may also be used by the third-party ad networks to measure the effectiveness of their advertisements and / or to personalize the advertising content that you see.

power-builder.blogspot.com has no access to or control over these cookies that are used by third-party advertisers.

You should consult the respective privacy policies of these third-party ad servers for more detailed information on their practices as well as for instructions about how to opt-out of certain practices. power-builder.blogspot.com's privacy policy does not apply to, and we cannot control the activities of, such other advertisers or web sites.

If you wish to disable cookies, you may do so through your individual browser options. More detailed information about cookie management with specific web browsers can be found at the browsers' respective websites.