PowerBuilder Tips

Auto Instanciate (attribute of non visual objects)

-------------------------------------------------------------------------
The first time you use the object PB automatically instanciates it for you and destroys it when it goes out of scope. 

Here are a few things to remember about autoinstanciate: 

- Assignment into an auto-instanciate variiable must be for an object of the identical type. - Assignment into a non-auto-instanciate ancestor (or ANY) causes a duplicate object to be create. 
- Auto instanciate is set at development ttime and cannot be altered at runtime. 
- Auto instanciate comparison does a membeer be member comparison not a handle compare. 
- Auto instanciate does a deep copy not a  reference copy as a non auto instanciate would do. 
-------------------------------------------------------------------------

Better Reports

-------------------------------------------------------------------------
When you are using the datawindow for printed reports, you are best to change the Unit of measurement from PowerBuilder Units which is should be used for screen datawindows, to 1/1000 of an inch. To change the unit of measurement just double click on any empty space on the datawindow. 

This aids in designing your reports as you have a better idea of where to layout objects on the report to take advantage of the space. Use the ruler on the screen which corresponds to a real ruler. 

It also solves a problem of drawing lines vertically down the page. If you draw lines down the page with PBU's you get annoying gaps between the detail bands. Using 1/1000 of an inch solves this problem. 
-------------------------------------------------------------------------

First Row On Page

-------------------------------------------------------------------------
If you want to show the currently viewed rows in your datawindow then you can achieve this by using the describe command in a computed field. 

Here is a code extract from PBDelta That shows the row count and the row at the top of the page. You could use similar code to show the last row on the page. 

String( Long( describe( "datawindow.firstrowonpage" )), "000" ) + ":" + String( rowcount(), "000" ) 
-------------------------------------------------------------------------

Initialize Todays Date

-------------------------------------------------------------------------
Using the Column Specifications dialog is a good way to setup default data for your columns. It would be great if you could enter simple expressions but hey I can hope! Anyway a neat little trick is that for date columns that you want to initialise to todays date you can include the word Today. 

That is not Today() but Today. I only found this by mistake, one day I left off the parens by mistake! 
-------------------------------------------------------------------------

Passing a Structure to a DLL

-------------------------------------------------------------------------
This tip was submitted by Edward Hoo. 

Passing a structure or string pointer to a DLL's function is pretty strait forward, just declare it with the ref keyword, but... 

1) How can you pass a pointer inside another structure? 

or... 

2) How can you initialize the long paramenter to a predefined SDK msg to a pointer to a string or structure? 

Well... remember the old 'C' strcpy function. Yeap! that little sucker that returns a pointer to the target string????? It's ALIVE! ...and it's called lstrcpy and it lives on kernel32.dll!!!! 

Function pointer

create a user object and declare the following local external function 

> function long lstrcpy(ref string lpString1,ref string lpString2) library "kernel32.dll" 

declare the following user object function 


 > long of_StringPointer(ref string ps_string)
 > {
 > string foo
 >
 > foo = ps_string
 >
 > return lstrcpy(foo, ps_string)
 > }

Structure pointer

create a user object and declare the following local external function 

 > function long lstrcpy(ref MyOversizedStructure lpMyOversizedStructure,ref string lpString2) library "kernel32.dll"



declare the following user object function 


 > long of_MyStructurePointer(ref MyOversizedStructure ps_structure)
 > {
 > string foo
 >
 > foo = ""
 >
 > return lstrcpy(ps_structure, ps_string)
 > }

Now you are going to tell me that the "return lstrcpy(ps_structure, ps_string)" overwrites the first byte of ps_structure. Well... why dou you think we named your structure as MyOversizedStructure? Cuz it has an extra field at the beginning which is a long... Now your MyOversizedStructure looks like this: 

 > typedef MyOversizedStructure
 > {
 > long il_i_will_be_overwitten
 > [datatype] [yours structure 1st field]
 > [datatype] [yours structure 2nd field]
 > [datatype] [yours structure 3rd field]
 > ...
 > }

now the function looks like this... 

 > long of_MyStructurePointer(ref MyOversizedStructure ps_structure)
 > {
 > string foo
 >
 > foo = ""
 >
 > return lstrcpy(ps_structure, ps_string) + 4
 > }

and the '+4' takes care of the offset of the il_i_will_be_overwitten dummy field 

Remember that the address is only good as far as you do not assign values to fields within the structure i.e 

 > pointer_before = of_MyStructurePointer(my_structure)
 > my_structure.my_field = some_value
 > pointer_after = of_MyStructurePointer(my_structure)

'pointer_before' might be different from 'pointer_after' 
-------------------------------------------------------------------------

Executing DOS Commands

-------------------------------------------------------------------------
To execute DOS commands you can write the commands out using FileWrite() to a DOS .BAT file. Then just run the batch file. If you want to stop the DOS window from opening create a PIF file or shortcut which points to a .BAT file then Run that. 

It is useful to wait for the DOS commands to finish so I usually do a short Yield();SetPointer( HourGlass! ) loop before using the results of the commands. 

Great for creating directories etc. 
-------------------------------------------------------------------------

Moving a Window Without a Titlebar

-------------------------------------------------------------------------
In order to move any window with a mouse it must have a titlebar. Child, popup and response windows can have no title bar but what do you do if you want to simulate a title bar and allow the user to move the window. 

In the mouse down event of the window (pbm_lbuttondown) send the window the following message: 

send( handle( this ), 274, 61458, 0 )
-------------------------------------------------------------------------

Stopping Minimize/Maximize

-------------------------------------------------------------------------
A very common request is to disable the minimize and maximize buttons on an mdi sheet. This tips comes with a disclaimer: you are taking away from the 'purpose' of MDI sheets. 

Here's the code: 

Map this to the userevent: PBM_SYSCOMMAND 

IF message.wordparm = 61488 THEN // Maximize Button
	message.processed = TRUE
	message.returnvalue = 1
END IF
IF message.wordparm = 61472 THEN // Minimize button
	message.processed = TRUE
	message.returnvalue = 1
END IF
IF message.wordparm = 61536 THEN // Close the Window
	message.processed = TRUE
	message.returnvalue = 1
END IF
-------------------------------------------------------------------------

Freeing an Unbound Array

-------------------------------------------------------------------------
When using unbound arrays for more than one purpose in a script or when you have instance, shared or global arrays you may want to clear out the current contents. 

There are two ways to do this. One is to declare two arrays of the same type, do not use one of the arrays. Whenever you need to clear out the first array just assign it to the second unused array and PowerBuilder will free all the memory. 

String ls_Array[], ls_Empty

ls_Array[1] = "Ken"
ls_Array[2] = "Howe"

...
...

ls_Array = ls_Empty // Clear out array

Or you could just redeclare the array using the same name. 

ls_Array[]

ls_Array[1] = "Ken"
ls_Array[2] = "Howe"

...
...

String ls_Array // Clear out array
-------------------------------------------------------------------------

Playing a Sound

-------------------------------------------------------------------------
Adding Sound to your application can be a great way of whiling away a few extra days at the end of a project thats ahead of schedule :) 

Adding sound to a PowerBuilder application is as easy as adding an external function declaration. One such function call to play a wav file is: 

FUNCTION INT SndPlaySound( '*.wav fn(STRING)', modeofplay(INT)) library "mmsystem.dll"

Under Win32 you will need to use a different set of DLL calls: 

Function boolean sndPlaySoundA( string SoundName, uint Flags ) Library "WINMM.DLL"
Function uint waveOutGetNumDevs() Library "WINMM.DLL"

You can play a sound with the following code: 

uint lui_NumDe vs

lui_NumDevs = WaveOutGetNumDevs()
IF lui_NewDevs > 0 THEN
    sndPlaySound( "filename.wav", modeofplay(INT) )
END IF

where: modeofplay
0 - play synchronously
1 - play asynchronously
2 - don't use default sound if u can't find file
3 - 1&2
8 - loop the sound until next sndPlaysound
10- don't stop any correctly playing sound

Return Values:
0 error(usually file not found)
1 success

This previous method is fine but under Windows NT and Windows 95 you can also play sounds using OLE: 

ole_snd.InsertFile("snd.wav")
ole_snd.Activate(InPlace!)

This tip extension was submitted by Anatoly Kuznetsov 
-------------------------------------------------------------------------

Get User Lan ID

-------------------------------------------------------------------------
You can obtain the users network login id for most popular network clients with a call to the windows API call WNetGetUserName. This call works for Netware, Windows for Workgroups, Windows NT, Windows 95 and LanManager. For 32 bit applications you will need to make a different call to the API function GetUserNameA(). 

For 16-bit apps
 //Declare an external function as:
 function int WNetGetUser( ref string userid, ref uint len ) library "user.exe"

In PowerScript
 string login_name
 uint   lui_len
 int    li_rc
 string ls_temp

 lui_len = 255
 ls_temp = space( 255 )
 li_rc = WNetGetUser( ls_temp, lui_len )
 login_name = Trim( ls_temp )

For 32-bit apps
 //Declare an external function as:
 Function boolean GetUserNameA( ref string userID, ref ulong len ) library "ADVAPI32.DLL"

In Powerscript
 string  login_name
 string  ls_temp 
 ulong   lul_value
 boolean lb_rc

 lul_value = 255
 ls_temp = Space( 255 )
 lb_rc = GetUserNameA( ls_temp, lul_value )
 login_name = Trim( ls_temp )
-------------------------------------------------------------------------

Window On Top

-------------------------------------------------------------------------
Sometimes when developing applications you want your window to stay on top of all other windows. This is useful in utilities such as PBBrowse where you want the user to always have access to your window. 

One way to achieve this is to declare an API call, but Powerbuilder has support for this built in. If at all possible it is best not to go directly to an API call as is makes your application platform specific. 

Anyway, there is a method of the window object, SetPosition() which takes an enumerated datatype of TopMost! or NoTopMost!. 

If you call the function with TopMost! then your window will always be on top of all other windows in the system. Calling the function with NoTopMost! will return the window back into the normal layering of windows. 
-------------------------------------------------------------------------

Getting The Windows Directory

-------------------------------------------------------------------------
When using .INI files it can be useful to know the location of the windows directory, esp. with all the flavours of windows and different setups. In PBBrowse I use this so I know where to create the PBBrowse.INI file. 

The external function declaration for PB goes like this: 

win16 FUNCTION INT GetWindowsDirectory (REF STRING ls_WindowsDirectory, INT cbSysPath) LIBRARY 'KERNEL.EXE'
win32 Function uint GetWindowsDirectoryA (ref string dirtext, uint textlen) library "KERNEL32.DLL"

Then in your code you can just say: 

String ls_WinPath

ls_WinPath = Space( 128 )
GetWindowsDirectory( ls_WinPath, 128 )

Note windows expects the memory to be allocated for the string where it will copy the windows directory to. We do that by making PowerBuilder create a blank string of 128 chars, the second parameter to the call tells window the maximum size of the buffer we have allocated for it to use. 
-------------------------------------------------------------------------

DDDW Tricks

-------------------------------------------------------------------------
This tip was subimmted by Qing Vincent Yin. 

Remember the feature of dddw where all the lookup values (in the child datawindow) are retrieved at the time the parent record is retrieved? Problem: why should the program retrieve a bunch of stand-by records (for dropdown purposes) while some users just want to view the "real, native" record itself, and never intended to click some of the dropdown arrows? 

To supress dddw.retrieve() at runtime is easy: just put a blank record in the datawindow painter at design time. The trick is to get the dddw to do the appropriate retrieve() at the moment when the user actually drops down the dddw (i.e., when he clicks the little dropdown arrow). 

Someone once proposed a solution of doing the dddw.retrieve() in the `clicked!' event of the parent datawindow. That's a correct idea, but the problem is that `clicked!' event is fired whether the user clicks exactly on the dropdown arrow or the text edit box portion of that column. If the clicked spot is the latter (i.e., not the little arrow beside it), then the program should not do dddw.retrieve(). But it turns out that there's no way (to the best of my knowledge) to distinguish the clicked spot between those two. That is, GetObjectAtPointer() and GetClickedColumn() don't tell you whether the arrow itself was clicked. 

After some hacking, I gathered that the correct event is pbm_dwndropdown. 

So the solution: 

Create a user event ue_dddw_dropdown (mapped to pbm_dwndropdown) in the datawindow with the following script: 

1)	DataWindowChild dwc

        dwGetChild(GetColumnName(), dwc)
        dwc.SetTransObject(SQLCA)
        dwc.retrieve()

That's the basic implementation. Several improvements are: 

2)	dwGetChild(GetColumnName(), dwc) 

        // Now, if dwc.rowcount()>1, then the dddw has once been dropped-down 
        // before. So don't bother to retrieve() again.  Remember that the dddw
        // initially has a single (blank) row.

        if dwc.rowcount() > 1 then return

        dwc.SetTransObject(SQLCA) 
        dwc.retrieve() 

     3)	...	// same as that in (1).
        dwc.retrieve(GetText())

        where the dddw's SQL is:
         select country_name from contry_lookup 
         where country_name like :retrieve_argument + "%"

That is, if the user types in "ca" and click the dropdown arrow, the the list of contry names dropped down will look like: 

		cambodia
		cameroon
		canada
		...	/* all contries that start with "ca" */

But countries like "france" and "russia" are not retrieved because they don't satisfy the LIKE clause in the SQL. Needless to say, that saves lots of runtime resources. 

4) Suppose the dddw is "employee_name", and a previous field is "department". There are lots of employees in the company. But after you typed in "Information Services" for the department, tab to employee_name field and click the dropdown arrow, you only want to see a list of I.S. employees. So: 

        ...   // same as that in (1)
        dwc.retrieve(GetItemString(GetRow(), "department"))

        where the dddw's SQL is:
          select employee_name from employee_table
          where department = :retrieve_argument 

Alright! Have fun and remember that pbm_dwndropdown is an undocumented event (and probably unsupported by PowerSoft.) 
-------------------------------------------------------------------------

Change the Graph Colours

-------------------------------------------------------------------------
If you want to change the colour of the bars of your graph at run time. On your datawindow or graph control, you have to create a user event, such as ue_graph_create which maps to the PowerBuilder event ID pbm_dwngraphcreate, this event gets fired when the graph is created. 

Inside this event, issue the function: 

this.SetSeriesStyle ( "gr_graph", "series", ForeGround!, Rgb (1, 1, 1 ))

Where gr_graph is the name of your graph, "series" is the name of the series you wish to change color and so forth, check out the help for this function to get more details. 
-------------------------------------------------------------------------

Validating Editable DDDW's

-------------------------------------------------------------------------
Editable DDDW's are great for users but bad for programmers. The users get greater flexibility because they can type in a code or type the first few letters of a description. Programmers get to write more code. 

When it comes to saving the data you will quite often want to validate the users input because unlike a non Editable DDDW the user may have entered data that is not in the list. It would be nice to handle this on the client so that we can display some nice message to the user and reduce the work of the backend. 

AFAIK there are three methods of checking the users data: 

- Perform a SQL select to make sure the daata is valid. Yuk! this is v.slow and should be avoided like the plague. 
- You can get the handle to the Child Dataawindow that is the Editable DDDW and then do a Find() against the DW to see if the row exists. This would be much faster than the first method. 
- Or you can use a feature of the DDDW thaat will automatically scroll to the row in the DDDW if the value typed exists. Then do a getchild on the Editable DDDW as before only instead of doing a find you can check the value of the Editable DDDW against the value of the current row in the Child DW. 

There not much time difference of the last two for a few rows but for large DDDW's the latter will be a bit quicker. Either way is much faster than perfroming a SELECT and also reduces the hits to the database. This would be a good function to add to your base class objects or DW service. 
-------------------------------------------------------------------------

Quickly Make a DW readonly

-------------------------------------------------------------------------
Sometime you want to use a Datawindow for editting and for display only purposes. You could achieve this in a number of ways: 

- Two copies of the data window one all prrotected and one editable :( 
- Run through all the columns and switch tthe tab order to zero. But if you want to switch between on and off then you need to hard coded all the tab orders, yuk! 
- You could run through all columns and swwitch the protect attribute on and off but this does not work if you are using protect attribute to perform edit processing. 
- You guessed it the last item is the bestt, there is an attribute of the datawindow called readonly. You can set this attribute to yes which will render the Datawindow readonly, changing it back to no restores its original state. 
-------------------------------------------------------------------------

Findout Datawindow style

-------------------------------------------------------------------------
When writing generic tools and utilities for DataWindows you can quite often want to know what design sytle the datawindow is. If you read the PB books or do a scan on the infobase this information is not easy to track down. 

The answer is in the processing attribute of the datawindow: 

Style of Datawindow   Processing Number

Crosstab 4
Freeform 0
Graph 3
Grid 1
Group 0
Label 2
N-UP 0
Tabular 0
Composite 5
-------------------------------------------------------------------------

Referencing Other Rows in a DW

-------------------------------------------------------------------------
When creating computed fields in the datawindow you can reference other fields on the current row for example: 

cf_profit

sale_price - cost

When coding complex computed fields it would be very useful to be able to reference other fields on rows other than the current row. This can be very useful in place of two aggregate function (something you cannot normally do in PB). To reference other rows you just treat the data as if it was one large array. You place a subscript at the end of the column name where the numeric reference is the offset from the current row. 

For example you may use the following code to display a bitmap on change of customer: 

if( first( customer for group 1 ) = customer, 0, 1 )

Could be replaced by: 

if( customer[ -1 ] = customer, 0, 1 )

Using this code we could then sum the computed column to get the total number of customers, something we could not do with the first for group code. 

This is not the only use for this trick but it is one I used recently. 
-------------------------------------------------------------------------

DataWindow Blinking Text

-------------------------------------------------------------------------
Blinking Text, whilst very irritating if used in large amounts can sometimes be very useful for drawing peoples attention to something. 

I was surfing the net and came across a page with too much blinking text which made me wonder if it was possible to do a similar thing in PowerBuilder. 

This is a very simple but very effective trick. You will need to set the timer inteval of the datawindow to something, I set it to 10. You will find the timer by double clicking on any blank part of the datawindow. 

Next place a text field on the datawindow (or any control) goto the attributes dialog. Then in the visible attribute place this code: 

if( mod( Integer( Mid( String( Now() ), 7, 2 ) ),2 ) =1,0,1)

It is also possible to make the text change colours by using similar code in the color attribute: 

if( mod( Integer( Mid( String( Now() ), 7, 2 ) ),2 ) =1,0,255)

Have a blinking time. 
-------------------------------------------------------------------------

Text at Any Angle

-------------------------------------------------------------------------
In the datawindow you have probably placed hundreds of horizontal statics text. But did you know that you could make your text vertical or at any angle you choose. 

New to PowerBuilder 4 was an attribute of the datawindow static text called font.escapement. This new attribute is the angle you want your text at display at. 

To display text vertically you would set the escapement attribute to 900 ( 10 * the angle ). You can achieve some really cool text effects with this attribute. 

You will not see the effect in the DW painter, but preview your DW and the text is displayed at the correct angle! 
-------------------------------------------------------------------------

DW Enter As Double Click

-------------------------------------------------------------------------
This Tip was sent in by Steve Gutterman sgutterman@dreamworks.com 

Sometimes it would be useful to have the Enter key work like a double-click. To achieve this follow these steps: 

1. create a user event to map to pbm_dwnprocessenter. 

2. in the new user event script,

   this.PostEvent( doubleclicked! )
   SetActionCode(1)

3. process the event "doubleclicked" as normal 
-------------------------------------------------------------------------

Make Dynamic Object be in EXE

-------------------------------------------------------------------------
When PowerBuilder creates your EXE is checks all objects to see if they have been referenced within the course of the application execution. If they are not it does not include it in the EXE. When you are trying to build a single EXE this can be a problem. 

You can force PB to include datawindows but dynamic userobjects and windows are a problem. But with a simple trick we can get PB to include whatever we want! Goto the application painter and declare an application function called af_dummy_exe() then declare local variables for each object you want to include. 

Example from PBDelta

This is an example of such a function I created for my new utility PBDelta.

u_compare_object luo_compare_object
u_compare_pbl luo_compare_pbl
u_compare_script luo_compare_script

u_diff_events luo_diff_events
u_diff_functions luo_diff_functions

u_wiz_type luo_wiz_type
u_wiz_title luo_wiz_title
u_wiz_new luo_wiz_new
u_wiz_old luo_wiz_old

w_pbdelta lwi_pbdelta
-------------------------------------------------------------------------

More Speed From Oracle 1

-------------------------------------------------------------------------
The are various step PowerBuilder goes through when you execute a piece of SQL. Two of the first steps it performs is to validate the SQL and check the results set still matches. 

For simple SQL these two steps can take up to 25% of the total execution time. 

New in Powerbuilder 4 is the extension of the Oracle DBParm to support more features. Once such feature is the SQLCache option. This feature caches information about the last X SQL calls and can save you the 25% described above. You will see the biggest speed improvement on simple single row result sets. 

To do this add (without quotes) "SQLCache=50" to your Oracle DBParm string.
-------------------------------------------------------------------------

How To Find Database Bottle Necks

-------------------------------------------------------------------------
Time and time again I here people saying that their powerbuidler application is really slow and blame powersoft for all their problems. Unless you have a really fat client most times the problem is not the Powerbuilder code but the SQL to the database. 

PowerBuilder has a really cool database debug and timing tool built right in. When ever you connect the database in the DBMS field append trace to the front of the DBMS name eg: 

SQLCA.DBMS = "Or7"

is replaced with

SQLCA.DBMS = "trace Or7"

PowerBuilder will create a dbtrace.log file in your windows directory. This file contains every piece of SQL executed by your program, complete with substituted variables, number of rows returned and the length of time it took to execute the SQL. 

Enjoy! 
-------------------------------------------------------------------------

Autostart a Painter

-------------------------------------------------------------------------
You can make Powerbuilder automatically open any of the powerbuilder painters when powerbuilder starts up. 

To do this, goto the properties for your PowerBuilder icon and add the following at the end of the command to start PowerBuilder. 

/p (paintername) 

For example to autoload the library painter 

/p library 
-------------------------------------------------------------------------