The existing windows normally had your typical ‘search’ datawindows which defaulted to the ‘default’ site assigned to the user and then they could select a location from a drop down datawindow.

Example ‘post open’ event on window code:

datawindowchild ldwc

long ll_rows

dw_search.getchild('loc_code', ldwc)
ldwc.settransobject(SQLCA)
ll_rows = ldwc.retrieve(gs_user_id)
dw_search.object.loc_code[1] = gs_default_site

Example ‘itemchanged’ event on dw_search code

this.getChild("site_to", ldwc)
ldwc.setTransObject(SQLCA)
If ldwc.Retrieve(long(data)) = 0 then
  ldwc.insertrow(0)
End if

So each time the user changed the location value another retrieve was sent to the database.

I needed to add a site column to the search datawindow which would also be a dddw.  This meant that even more calls to the database would occur as the user changed the value in the new column.

My approach involved creating an nvo with two datastores, one for the sites and the other for the locations.  Each were populated when the window was opened (populated based on the user’s security settings).  Then when I needed to set up the drop down datawindow columns I used the following.

// all sites and locations for this user
ls_sites = invo_multisite.ids_site.object.datawindow.data
ls_locs = invo_multsite.ids_loc.object.datawindow.data

dw_search.getchild('site_code', ldwc)
ldwc.importstring(ls_sites)
dw_search.object.site_code[1] = is_site_code
ll_find = ldwc.Find('site_code = ' + string(is_site_code), 1, ldwc.RowCount() + 1)

dw_search.getchild('loc_code', ldwc)
ldwc.importstring(ls_locs)

ldwc.setfilter("site_code = '" + is_site_code + "' AND status = 'Active'"

ldwc.filter()  // only show locations associated with the chosen site

You need to remember to turn off Autoretrieve on the DDDW columns you are using for this.  You also need to have the datawindow object used in the nvo datastores the same as that used in the other datawindow dddw columns.  If you need to account for user access being changed during the sesssion, set up a timer in the nvo and periodically refresh the datastores.

Reportevent External Function and Eventcreate command line command

You can use the command shell to do this too but this has the drawback of not being able to assign the source of the event.

First you need the following External Functions:

Function ulong RegisterEventSource ( &
	ulong lpUNCServerName, &
	string lpSourceName &
	) Library "advapi32.dll" Alias For "RegisterEventSourceW"

Function boolean ReportEvent ( &
	ulong hEventLog, &
	uint wType, &
	uint wCategory, &
	ulong dwEventID, &
	ulong lpUserSid, &
	uint wNumStrings, &
	ulong dwDataSize, &
	string lpStrings[], &
	ulong lpRawData &
	) Library "advapi32.dll" Alias For "ReportEventW"

Function boolean DeregisterEventSource ( &
	ref ulong hEventLog &
	) Library "advapi32.dll"

Then in PowerScript

integer li_messagelevel
string ls_errmsg[]
string ls_messagesource
ulong lul_eventsource
// the event message is here
ls_errmsg[1] = "Event Log Entry " + String(now(), 'hh:mm:ss') 
// setting the source of the message
ls_messagesource = this.classname()

lul_eventsource = RegisterEventSource(0, ls_messagesource)
IF IsNull(ls_eventsource) THEN ls_eventsource = -1
// set the level of the message
li_messagelevel = 4 // Information,  also 1 = Error, 2 = Warning

IF lul_eventsource > 0 THEN
	// write to the log
	ReportEvent(lul_eventsource, li_messagelevel, 0, 0, 0, &
        UpperBound(ls_errmsg), 0, ls_errmsg, 0)
	DeregisterEventSource(lul_eventsource)
END IF

Viewing the Log

Per the MSDN documentation, if the source name cannot be found the event logging service uses the Application log. Although events will be reported , the events will not include descriptions because there are no message and category message files for looking up descriptions related to the event identifiers.

Thanks to Roland Smith for most of the code for this functionality.

You are also able to use the ‘EVENTCREATE’ command line command.

From the Microsoft Technet page (found Here):

Enables an administrator to create a custom event in a specified event log.
Syntax
eventcreate [/s Computer [/u Domain\User [/p Password]] {[/l {APPLICATION|SYSTEM}]|[/so SrcName]} /t {ERROR|WARNING|INFORMATION|SUCCESSAUDIT|FAILUREAUDIT} /id EventID /d Description
Top of page 
Parameters
/s   Computer   : Specifies the name or IP address of a remote computer (do not use backslashes). The default is the local computer.
/u   Domain \ User   : Runs the command with the account permissions of the user specified by User or Domain\User. The default is the permissions of the current logged on user on the computer issuing the command.
/p   Password   : Specifies the password of the user account that is specified in the /u parameter.
/l { APPLICATION | SYSTEM } : Specifies the name of the event log where the event will be created. The valid log names are APPLICATION and SYSTEM.
/so   SrcName   : Specifies the source to use for the event. A valid source can be any string and should represent the application or component that is generating the event.
/t { ERROR | WARNING | INFORMATION | SUCCESSAUDIT | FAILUREAUDIT } : Specifies the type of event to create. The valid types are ERROR, WARNING, INFORMATION, SUCCESSAUDIT, and FAILUREAUDIT.
/id   EventID   : Specifies the event ID for the event. A valid ID is any number from 1 to 65535.
/d   Description   : Specifies the description to use for the newly created event.
/? : Displays help at the command prompt.

In PowerScript:

// command line event log
integer li_rtn
string ls_msg, ls_eventmsg
ls_msg = 'Command Line Message'

 ls_EventMsg = 'EVENTCREATE /T ERROR /ID 100 /SO "My Application" /l APPLICATION /D "' +ls_msg + '" '
 li_Rtn = Run (ls_EventMsg,  Minimized!)

 IF li_Rtn <> 1 THEN
	messagebox('event log', 'error loging msg')
 END IF

And the message in the Event Log viewer

To use this the user must be an administrator on the machine or you have to provide the id/password combination of one.

Thanks to Kyle Griffis for this tip.

I have updated the presentation files along with exports of the PB objects so that folks using versions prior to 12.5 can take a look at the code and the functionality it provides.  The new file is available here.

The examples presented cover some basic datawindow functionality techniques which can improve the ‘usability’ of an application.  Most of these can be considered ‘second nature’ in that many ‘modern’ applications do these sorts of things already.  Taking advantage of the power of the datawindow, these types of improvements can be made quickly and with very low risk to an already established user base.  What I mean here is since the ‘improvement object (IO)’ is inherited from a datawindow, all you need to do is to establish a reference between any datawindow already in your application and the IO, wire some events on the existing datawindow to corresponding events on the IO, and you have access to the new functionality.

Once you have the IO in place, it’s easy to add even more functionality to it and have those improvements flow into as many datawindows in your applications as you like.

In this version of the IO I have included ‘Type ahead’ functionality.  My earlier blog post on this is here.

Treeview like tooltips.  Blog post is here.

‘Mouseover’ effects.  Similar blog post is here.

And a ‘Universal Searcher’ tool which can be used not only on datawindows but also multi line edit, rich text edit, and treeview controls.

This is an extract from the session I presented at the SAP PowerBuilder Developers Conference 2012.

Now you might think that using the LibraryExport and LibraryImport methods will do the trick but you would be mistaken. A newly imported datawindow object keeps the same version as when it was exported.

To accomplish the task you need to use the datawindow CREATE method.

integer li_file, li
string ls_old_syntax, ls_importsyntax
// old datawindow syntax saved to a file
ls_old_syntax = 'C:\temp\d_oldstuff.txt'
// open and read the file
li_file = Fileopen(ls_old_syntax, TextMode!)
Filereadex(li_file, ls_importsyntax)
// remember to close the file
FileClose(li_file)
// check version of ls_importsyntax
IF POS(ls_importsyntax, 'release 12.5;') > 1 THEN
   // its already new, leave it alone		
ELSE
   // dw_new is object on the window
   dw_new.reset()
   dw_new.dataobject = ''
   // create the new datawindow object from the old syntax
   li = dw_new.create(ls_importsyntax, ls_err) 
   // now get the new syntax
   ls_new_syntax = dw_new.describe("DataWindow.Syntax")   
END IF

Now you can do whatever you want with the new syntax.

I was working on a generic data drill down control which has a filtering process to eliminate data based on a date argument. Since I also wanted this date value for other purposes (expressions) on the datawindow I decided to include it within the result set. There are a variety of ways to do the same thing but this is what I fixated on while figuring out the control’s functionality. Initially my SQL was something like this (MS SQL Server 2008):

...
,IsNull(part_name, 'No Part') AS part_name
,:adt_date AS svc_date
...

Everything is fine but during my testing I encountered the message: “Select Error: Column lists do not match.”

Hmmm…

Running a SQL trace gave this result for the query:

...
,IsNull(part_name, 'No Part') AS part_name
,NULL AS svc_date
...

So my date argument in this case was null. I could code in PowerBuilder to ensure the paramenter is always populated with a date but I preferred to following:

...
,IsNull(part_name, 'No Part') AS part_name
,IsNull(:adt_date, Getdate()) AS svc_date
...

What I initially coded was this:

ll_open_x = Long(adw.describe(as_colname + '.X') + &
 adw.describe(as_colname + '.height')) + 10

I run the window and my visual object is no where to be seen.

Looking in the debugger I see that ll_open_x is being set to 72270!

Fixed code is this:

ll_open_x = Long(adw.describe(as_colname + '.X')) + &
 Long(adw.describe(as_colname + '.height')) + 10

Since the Describe method always returns a string the values ’72’ and ‘270’ were simply being concatenated prior to being converted to a long.

So we look at the datawindow object on the application window they are having issues with. Must be something in an expression on the window which, when combined with some type of unusual data, results in a bad expression. The multiple messages comes from the window being refreshed on a periodic basis and, as long as the bad data exists, we get further messages.

Turns out there is nothing wrong with the datawindow or any of the expressions on it. The timer on the window was issuing a Find on the datawindow using data retrieved by some other datastore. Turns out a column used in the find was a descriptive column and the data contained a single quote. That caused the find string to be messed up resulting in the error.

Moral of the story is to check strings inserted into find expressions for quotes and insert a tilde to escape them (unless you don’t want to allow quotes in your strings in the first place).

One point of particular interest to me was the section on ‘inferring content from history’ and how at the very least a system should appear as the user left it on the previous session (and how often ‘this is not even bothered with’). How much could productivity be improved if business software simply ‘remembered’ window/widget placement and sizing from session to session?

Here is a link to my post on setting up a user ini service in PowerBuilder.

In C# (I’m using Visual Studio 2008) create a class as indicated.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Net.Mail;
using System.Net.Mime;

namespace icalEmail
{
    public class StoredProcedures
    {
        [SqlProcedure]
        public static void SendiCal(DateTime startDateTime, DateTime endDateTime, string emailSubject,
           string userEmailAddress, string cnAttendee, string methodType, DateTime createdOn, string notes, string UID, string location, string seqNo, string smtpserver, int smtpport)
        {
            // create the iCal string in the proper format
            string iCal = CreateiCalFormat(startDateTime, endDateTime, emailSubject, userEmailAddress,
            cnAttendee, methodType, createdOn, UID, notes, location, seqNo);
            // create the MIME protocol Content-Type 
            var calendarType = new ContentType("text/calendar;method=REQUEST; charset=UTF-8");
            calendarType.Parameters.Add("method", "REQUEST");

            AlternateView caledarView = AlternateView.CreateAlternateViewFromString(iCal, calendarType);
            caledarView.TransferEncoding = TransferEncoding.SevenBit;
            // connect to smtpserver
            var client = new SmtpClient(smtpserver, smtpport);
            // create the email
            var mailMesage = new MailMessage();
            mailMesage.From = new MailAddress("donotreply@email.com");
            mailMesage.To.Add(new MailAddress(userEmailAddress));
            mailMesage.Subject = emailSubject;
            mailMesage.Body = iCal;
            mailMesage.AlternateViews.Add(caledarView);
            client.Send(mailMesage);
        }

        private static string _dateFormat = "yyyyMMddTHHmmssZ";
        private static string CreateiCalFormat(DateTime startDateTime, DateTime endDateTime,
        string emailSubject, string userEmailAddress, string cnAttendee, string methodType, DateTime createdOn, string UID,
        string notes, string location, string seqNo)
        {
            string iCal =

            "BEGIN:VCALENDAR" +

            "\nPRODID:-//SampleApp//AoT//EN" +

            "\nVERSION:2.0" +
            /// REQUEST for add or update, CANCEL for cancellation
            "\nMETHOD:" + methodType +

            "\nBEGIN:VEVENT" +

            "\nORGANIZER:CN='Matt'" +

            //"\nORGANIZER:MAILTO:matt.balent@anvil-of-time.com" +

            "\nATTENDEE;CN='" + cnAttendee + "'" +

            ";ROLE=REQ-PARTICIPANT;RSVP=TRUE;CN=" + userEmailAddress + 

            "\nDTSTART:" + startDateTime.ToUniversalTime().ToString(_dateFormat) +

            "\nDTEND:" + endDateTime.ToUniversalTime().ToString(_dateFormat) +

            "\nSTATUS:CONFIRMED" +

            "\nTRANSP:OPAQUE" +
            /// 0 for add, 1 for update or cancellation
            "\nSEQUENCE:" + seqNo +

            "\nUID:" + Guid.NewGuid() +
            /// resend original UID for updates and cancellations
            //"\nUID:" + UID +

            "\nDTSTAMP:" + createdOn.ToUniversalTime().ToString(_dateFormat) +

            "\nLAST-MODIFIED:" + createdOn.ToUniversalTime().ToString(_dateFormat) +

            "\nLOCATION:" + location +

            "\nDESCRIPTION:" + notes +

            "\nSUMMARY:" + emailSubject +

            "\nPRIORITY:5" +

            "\nCLASS:PUBLIC" +

            "\nEND:VEVENT" +

            "\nEND:VCALENDAR";

            return iCal;
        }

    }
}

This would be compiled into a .dll which needs to be registered in SQL Server. This assembly is to be marked as UNSAFE so someone with sysadmin priviges needs to register it.

CREATE ASSEMBLY [eventEmail]
AUTHORIZATION [dbo]
FROM 'C:\temp\icalemail.dll'
WITH PERMISSION_SET = UNSAFE

There is an interesting discussion of ‘UNSAFE’ assemblies on Stackoverflow.

Now create the stored procedure which will call the assembly.

create procedure dbo.usp_mb_eventnotify(@startDateTime datetime
				, @endDateTime datetime
				, @emailSubject nvarchar(80)
				, @emailSummary nvarchar(1000)
				, @location nvarchar(255)
				, @attendeeName nvarchar(100)
				, @attendeeEmail nvarchar(255)
				, @organizerName nvarchar(100)
				, @organizerEmail nvarchar(255)
				, @UID nvarchar(255)
				, @status nvarchar(12)
				, @requestType nvarchar(12)
				, @smtpserver nvarchar (255)
				, @smtpport bigint)
as external name [iCalEmail].[iCalEmail.Storedprocedures].[SendiCal];

Examples of use:

Create the event

exec dbo.usp_mb_eventnotify  
'2012-08-08 13:00:00',
'2012-08-08 15:00:00',
'BigProj 2',
'New schedule of Event sent by Matt',
'CONF01',
'Balent, Matt',
'matt.balent@anvil-of-time.com',
'AoT',
'donotreply@email.com',
'AOT372711c',
'CONFIRMED',
'REQUEST',
'1234.na.corp.aot.com',
25

Update the event

exec dbo.usp_mb_eventnotify				
'2012-08-08 10:00:00',
'2012-08-08 12:00:00',
'BigProj 2',
'New schedule of Event sent by Matt',
'CONF01',
'Balent, Matt',
'matt.balent@anvil-of-time.com',
'AoT',
'donotreply@email.com',
'AOT372711c',
'CONFIRMED',
'REQUEST',
'1234.na.corp.aot.com',
25

Cancel the event

exec dbo.usp_mb_eventnotify 
'2012-08-08 10:00:00',
'2012-08-08 12:00:00',
'BigProj 2',
'New schedule of Event sent by Matt',
'CONF01',
'Balent, Matt',
'matt.balent@anvil-of-time.com',
'AoT',
'donotreply@email.com',
'AOT372711c',
'CANCELLED',
'CANCEL',
'1234.na.corp.aot.com',
25