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