We are going to create a bare-bones email object. Of course you can expand that in the future if you like, but for now, let's just create the functionality that we need.

I start by creating a custom class. In case you aren't familiar with the process look at Figure 1.

Let's start with an instance variable.

N_cst_mailer Instance Variables
private mailMessage io_mail_message // The message to send

I made the instance variables private so let's write a couple of functions to populate our mail Message. We'll start with the TO. Note that the io_mail_message contains an array of objects called recipient. One of the properties of that object is recipientType. We are looking for the recipientType of mailTo! There are several other types that don't concern us.

N_cst_mailer.of_to
// DESCRIPTION
// Returns the current TO address
int li_count, li_max
li_max = upperBound(io_mail_message.recipient)
for li_count = 1 to li_max
// We are looking for the TO address
if io_mail_message.recipient[li_count].recipientType = mailTo! then
return io_mail_message.recipient[1].name // We are done,let's just return
end if
next
return ""

Now let's do the SET function

N_cs_mailer.of_to
// n_cst_mailer.of_to
// ARGUMENTS string as_to
// RETURN VALUE              string
// DESCRIPTION

//

// Sets the mailTo item and returns the old one
// 2/4/2011 Rik Brooks
string ls_retVal
int li_count, li_max
ls_retVal = of_to()      // return the old value

li_max = upperBound(io_mail_message.recipient)
for li_count = 1 to li_max
if io_mail_message.recipient[li_count].recipientType = mailTo! Then
// Overwrite the existing mailto.
io_mail_message.recipient[li_count].name = as_to
return ls_retVal
end if
next
// We didn't find a mailTo. Give it one now
li_max ++
io_mail_message.recipient[li_max].recipientType = mailTo!
io_mail_message.recipient[li_max].name = as_to
return ls_retVal

We need a Get and Set for the subject as well.

N_cst_mailer.of_subject
// DESCRIPTION
// Returns the subject of the message
return io_mail_message.subject

N_cst_mailer.of_subject
// ARGUMENTS string as_subject
// RETURN VALUE  string
// DESCRIPTION
// Sets the subject of the message and returns the old subject
string ls_retVal
ls_retVal = of_subject()
io_mail_message.subject = as_subject
return ls_retVal

Now we just need a send function for our mailer object, then we are ready to test it. This object can be expanded to be a full-featured mail object so let's not use the of_send. Let's use a special name for a specialized function. I'm calling mine of_sqlca_error_send since I'm sending an sqlca error.

N_cst_mailer.of_sqlca_error_send
// DESCRIPTION
// Sends the message and returns a status
string ls_retVal, ls_body
mailReturnCode lo_return
ls_retVal = "Success"

lo_return = io_mail_session.mailLogon(mailNewSession!)
IF lo_return = mailReturnSuccess! THEN
if of_to( ) <> "" then

ls_body = "Database Error detected at " + &
string(datetime(today(), now()), "MM/DD/YYYY HH:MM")

ls_body += "~n"
ls_body += "~n SQLDBCODE: " + string(sqlca.sqldbcode)
ls_body += "~n User: " + sqlca.logid
ls_body += "~n~n" + sqlca.sqlerrtext
io_mail_message.notetext = ls_body
lo_return = io_mail_session.mailsend(io_mail_message)
choose case lo_return
case mailReturnFailure!
ls_retVal = "Failure"
case mailReturnInsufficientMemory!
ls_retVal = "Out of memory"
case mailReturnDiskFull!
ls_retVal = "Out of disk space"
case mailReturnTooManySessions!
ls_retVal = "Too many mail sessions"
case mailReturnTooManyFiles!
ls_retVal = "Too many files open"
end choose
end if
else
ls_retVal = "Failure to log in to mail server"
END IF
return ls_retVal

Now our object will work and I'm tempted to end it right here but right here I have an opportunity to show you polymorphism at work in an uncontrived way.

Just a few words first. We are about to implement optional parameters in PowerBuilder. We are going to implement something that looks like this:

Of_sent(To {, message {, subject } } )

What this means is that you must at least pass a TO to the function, but you can also pass a message or a message and a subject. It will default the last two. Look at the help for the messagebox function to see a good example of this.

This will require that you write three functions. I always start with the function that has all the arguments passed.

N_cst_mailer.of_send(to, message, subject)
// ARGUMENTS
//          string as_to - recipient address
//          string as_message - the body
//          string as_subject - the subject
// RETURN VALUE
// A status
// DESCRIPTION
// Sends the message and returns a status
// 2/5/2011 Rik Brooks
string ls_retVal, ls_body
mailReturnCode lo_return
ls_retVal = "Success"
mailSession lo_mail_session

lo_mail_session = create mailSession
lo_return = lo_mail_session.mailLogon(mailNewSession!)
IF lo_return = mailReturnSuccess! THEN

// Set the values in the message object
of_to(as_to)
of_subject( as_subject)
io_mail_message.notetext = as_message

lo_return = lo_mail_session.mailsend(io_mail_message)
choose case lo_return
case mailReturnFailure!
ls_retVal = "Failure"
case mailReturnInsufficientMemory!
ls_retVal = "Out of memory"
case mailReturnDiskFull!
ls_retVal = "Out of disk space"
case mailReturnTooManySessions!
ls_retVal = "Too many mail sessions"
case mailReturnTooManyFiles!
ls_retVal = "Too many files open"
end choose
else
ls_retVal = "Failure to log in to mail server"
END IF
return ls_retVal

Now I default the last argument, in this case the subject line. Notice that I only have to write one line. I call the function that I just wrote but for the last parameter I send a default. If you really wanted to get fancy you could put these defaults into an ini file or your database.

N_cst_mailer.of_send(as_to, as_message)
// ARGUMENTS
//          string as_to                              The recipient
//          string as_body             The body of the message
// RETURN VALUE              string a status
// DESCRIPTION
// The send message with a defaulted subject line
// 2/5/2011 Rik Brooks
return of_send(as_to, as_body, "(No Subject)")

Finally the simplest form of the function:

N_cst_mailer.of_send(to)
// ARGUMENTS
//          as_to
// RETURN VALUE
//          A status
// DESCRIPTION
// Sends an email message
// 2/5/2011 Rik Brooks
return of_send(as_to, "No body sent", "(No Subject)")

Okay, I've made my point. You can create your own function with optional parameters and you do it using polymorphism. Now let's finish out the subject of this article - your custom sqlca.

Create a new object. This one needs to be a standard class (see Figure 2).

This selection will give you a dialog asking which standard class you want. In this case it's a transaction as shown in Figure 3.

Now we will have a painter with a transaction in it. I saved mine as n_cst_sqlca. There are only two small steps to making this work. First we need to put some code in our dbError event. We want to send an email to ourselves whenever there is a database error not caught by our DataWindow. Here's the code:

N_cst_sqlca.dberror
// DESCRIPTION
// Sends an email when an error happens

n_cst_mailer lo_mailer
lo_mailer = create n_cst_mailer
lo_mailer.of_to( "your_address@aol.com")
lo_mailer.of_subject( "test")
messagebox("", lo_mailer.of_sqlca_error_send( ))

Now we use the new object. First we have to tell PowerBuilder that sqlca is no longer the traditional one but it's our new one. Go to your application object and look at the properties. You will see a button that says Additional Properties. When you click on it you get Figure 4.

Now SQLCA is no longer a transaction; it's an n_cst_sqlca that derives or inherits from transaction (see Figure 4).

The last part is to test our sqlca. Create a window and put a button in it. In the clicked event of the button add the following code:

W_main.cb_1.clicked()
// Profile EAS Demo DB V115
SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = False
SQLCA.DBParm = "ConnectString='DSN=EAS Demo DB V115;UID=dba;PWD=sql'"
connect using sqlca ;

string temp

SELECT "bonus"."no_column" 
INTO :temp 
FROM "bonus"  ;

There is no column named "no_column" in the bonus table. This will throw an error. Now when you run it this will automatically send an email telling you when you got an error and what it was.

Database Error detected at 02/05/2011 05:38

SQLDBCODE: -143
User:

SQLSTATE = S0002
[Sybase][ODBC Driver][SQL Anywhere]Column ‘no_column' not found

This will happen automatically every time there is a database error. There is no coding required on your part and your user doesn't have to do anything. This is what we used to call "elegant programming" and now you know how to do it.

Source Code

• custom_transaction.pbl
• custom_transaction.pbt
• custom_transaction.usr.opt
• custom_transactions.pbw
• tools.pbl