PowerBuilder Tips & Tricks
Advanced PowerBuilder Tips & Tricks Programming, that I copied from books, website, blog, literature, and other resources.

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

 

Sorting Datawindow

Posted In: . By popo

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

 

Hexadecimal to Decimal

Posted In: . By popo

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

 

Julian Date

Posted In: . By popo

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

 

Hiding Desktop and Taskbar

Posted In: . By popo

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.

 

Center a Response Window

Posted In: . By popo

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 )

 

If Statements Using SQL

Posted In: . By popo

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"
    Custom Class PowerBuilder 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:
    Create UserObject Function - isRunning
    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
//...

 

C/C++ Datatype Conversion

Posted In: . By popo

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 DatatypeC/C++ Datatype
BlobPBBlob
Booleanint
Characterchar
DatePBDate
DateTimePBDateTime
DecimalPBDecimal
Doubledouble
Integerint
Realfloat
StringPBString
TimePBTime
UnSignedIntegerunsigned int

C/C++ DatatypePowerBuilder Datatype
BOOLBoolean
WORDUnSignedInteger
DWORDUnSignedLong
HANDLEUnSignedLong
HWNDUnSignedLong
LPSTRString Ref
LPBYTEString Ref
LPINTLong Ref
charBlob {1}
intInteger
unsigned intUnsignedInt
longLong
ULONG/unsigned longUnsignedLong
doubleDouble
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 )

 

Get Volume Information

Posted In: . By popo

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:\

 

Mapping a Network Drive

Posted In: . By popo

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

 

Getting Active Directory

Posted In: . By popo

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

 

Getting Computer Name

Posted In: . By popo

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

 

Sending Key Press

Posted In: , . By popo

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

By popo
 

Privacy Policy

By popo

Privacy Policy
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.