Extending SQLCA

• Download Source - 36.84 KB

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.