Extending SQLCA
The goal is to replace SQLCA with something a little more robust
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.