Updating My Website ...

I’m doing some work on my website at the moment, there is too many words and not enough clarity. I need to get the point across about what I do on the homepage, no fuss, no hassle. I AM STEVE. THIS IS MY COMPANY. THIS IS WHAT WE DO. Marketing is definitely a talent - I was at the back of the queue when when god gave out that particular ability I think.

Hence my purchase of the ebook ‘MicroISV Sites that Sell’ by Bob Walsh of 47Hats.com.

What an excellent read ! I’m definitely going to be taking some of his ideas on board. $20 well spent I think :-)

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ScreenAudit for Mac OSX

A trial version of ScreenAudit for Mac OSX is now available to download from my website:

http://www.artenscience.co.uk/artenscience/ScreenAudit.html

ScreenAudit is a Mac OSX application that performs snapshots of your screen at timed intervals.  If you have multiple monitors it will take snapshots of them all.  The interval between snapshots, in seconds, the name of the destination folder (created within the ScreenAudit folder), and the quality of the resulting jpg can be set from within the Preferences menu.

ScreenAudit is not designed for covert ‘spying’ on a computer.  ScreenAudit has it’s own window which shows a countdown until the next timed snapshot.  The window flips from right to left as the snapshot is taken and the ScreenAudit icon in the dock displays the ‘Note’ graphic to let you know a snapshot has been taken.  If you do not wish to view the ScreenAudit window it can of course be minimised.

ScreenAudit is useful for a number of purposes.  Primarily it allows you to keep a record of your activities as you use your computer.  If you bill for time then ScreenAudit can be used to remind you what you were doing and when.

If you forget to save a document then ScreenAudit might come to your rescue by at least showing you the screen with the text displayed.  You will have to type it again but at least you won’t have to do it from memory.

If you are concerned that someone is using your computer in your absence then ScreenAudit will help you to see what was done.  Yes they could quit ScreenAudit or even delete the snapshot files but then at least you would be aware that someone has perhaps been doing something they shouldn’t !
If you manage a department you may wish to install ScreenAudit on your colleagues computers to keep a record of work done ?

By default ScreenAudit takes a snapshot every 5 minutes.  Bear in mind the disk space used if you set this interval to several seconds ! Although the files generated by ScreenAudit are not large, it is nonetheless advisable to backup and archive your images folder daily or at least weekly.  Obviously the larger your monitor and the more monitors you have attached, the more disk space is going to be used.



www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

Interesting Recommendation !

I had a recommendation come through from a client recently. I couldn’t use it for obvious reasons. I’ve shown it below with name and project detail deleted to protect the guilty :-)

I have had the misfortune of working with Steve on a significant [Deleted] solution. Steve showed a consistency and quality which is unusual with many IT Contractors, and I have worked with a significant number of them to base this opinion on.

It was a misfortune because the rest of our IT Department had to step up to the mark, otherwise he would have made us look like amateurs.

I would have no hesitation in recommending him to anyone.

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

pcInfo: Freeware

pcInfo is a simple utility that displays IP Address, Subnet Mask and the MAC Address of your primary network adaptor. That is all it does.



It can be useful when running several Virtual Machines or Servers, and you need to see the network information at a glance. Putting a shortcut to the application in the startup directory on a Windows PC will ensure the application is visible whenever the computer is started. There are also ways to enable the automatic running of applications using Mac OSX and Linux to achieve the same effect.

pcInfo remembers it's position on the screen when the application is closed by writing to the Preferences.txt file. This file is stored in the same directory as the application itself.

pcInfo can be downloaded for Mac OSX, Windows and Linux from here:

www.artenscience.co.uk/artenscience/FreeSoftware.html

If you think of any ideas for improvements or additional features then I would appreciate hearing from you.

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSPEAK: Windows Version

ArtenSPEAK has been updated with a couple of bug fixes and the Windows version is now available for download. ArtenSPEAK can be downloaded from here:

www.artenscience.co.uk/artenscience/FreeSoftware.html

ArtenSPEAK is a simple application that speaks text. You may drag or paste text into the ArtenSPEAK text area and when you depress the loudspeaker button the text will be read to you. Ideal for use with webpages, email, or any text document.

Below are a couple of screenshots, the top one is from Windows XP and the bottom one from Windows Vista.





www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSPEAK Freeware

I'm on holiday and it's raining, almost constantly. So I sat down and created ArtenSPEAK. ArtenSPEAK is a simple utilty that speaks text. I have used it to read webpages and emails. Just drag or paste text into ArtenSPEAK and click the 'loudspeaker' icon, sit back and listen :-)

ArtenSPEAK is available for download here.

The window size, position and transparency can be set and is remembered by the application.

This version is for MacOSX only, i'll upload the Windows version when I get home next week.

Enjoy :-)

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 16: More SQL Comments

ArtenSUITE
A Development Journal
Part 16: More SQL Comments

In an earlier article I wrote about how I prefix every SQL statement with the Module, User ID and Date/Time. I do this by calling a function which sets these values and prefixes them as a comment to the beginning of the SQL statement.

The reason I do this is that I can view this information from within the application (by logging to a text file and then using a View SQL Window), but also this information is seen when looking at the statements in the database. Unfortunately from Oracle 10g these SQL comments are removed and not visible from within the database.

The way around this is to use the Hint syntax /*+ ... */. As you are not using valid hints they are ignored by Oracle, but not stripped from the SQL statement so are visible within the database. An example is shown below:

/*+ ArtenBASE / SJC / 1909208-11:00 */ select count(1) from bas_con_main

UPDATE: It turns out that using the above syntax causes each statement to be viewed as 'different' by the Oracle optimiser. Therefore any benefits of bind variables and caching are lost. This method can only be recommended for use during pre-production / development.

It seems there isn't a way (or at least, I haven't yet found one) in 10g to tag or comment the SQL statement in such a way that the comment is accessible from the database, as opposed to just within your application. Hence any logging of SQL statements with their associated comments must use the /* ... */ tags and be done on the client side.

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 15: Multi Language Support

ArtenSUITE
A Development Journal
Part 15: Multi Language Support

One of the original design ideas for ArtenSUITE was to develop the application in such a way that conversion to a different (non English) language was straightforward. As it was I got carried away with other (more exciting) things and that idea fell by the wayside. Realising the error of my ways, I started to rectify that today.

The first thing I did was create two modules, mStrings, for storing the constants that will contain all the text that the user will see in message and dialog boxes. The second module mUIText, for storing the text elements for the UI controls, ie: Captions, Names etc. These two modules are stored within a Localisation folder inside my Project.

The constants are named in the following way:

kError = "An Error Occurred ..."
kDBConnectOK = "You Successfully Connected to the Database ..."
etc.

Real examples can be seen in the following screenshot taken from my project:



I then went through my application replacing the text with the name of the appropriate constant. Interestingly enough if you are fairly consistent with your messages you will be surprised how many times you use the same type of message, meaning that the amount of constants required is probably smaller than you imagine.

It's important to keep the formatting and spacing within your code and not move that to constants. For example if your code says something like:

Message = "An Error Has Occurred: " + "Error Number: " + intErrNum

You would make the text into constants but leave the spacing in the Message string. It would look like this:

Message = mStrings.kErrorOccurred + " " +mStrings.kErrorNum + " " + intErrNum

The main reason for this is if you decide to implement another language and send an export of your modules to be translated there is a good chance the spaces will get 'lost' along the way.

So assuming all your text that will be seen by the user is contained inside a couple of modules all you need to do to implement another language is export these modules, get them translated, then re-import them and assign a language to them. Bingo! Multiple Language Support with the added bonus that it makes your code slicker and more maintainable.

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 14: Network Messaging

ArtenSUITE
A Development Journal
Part 14: Network Messaging

I’ve built simple network messaging into the ArtenSUITE framework. Using UDP (a connectionless transport) I am able to detect and communicate with all other copies of my software running on the local network. This offers me several advantages.

Licensing
Using this facility I am able to detect how many copies of the software are running concurrently, and compare that to the license owned by the customer. This is a fairly foolproof method of ensuring that license limits are not breached and the customer is then likely to purchase additional licenses if they run into this restriction often.

Messaging
The user can use this facility to send messages directly to another user, or group of users. A sort of simple ‘Group Chat’. My software can also use this mechanism to sent messages to both itself and also other copies of itself running elsewhere on the network. Some Interesting possibilities here.

Commands
Messages are send with an identifier, currently set to 0 this is interpreted as a simple message, and is display as such. I can however send messages with a different identifier which can be processed as a command on the remote machine. For example it could be used to force the remote software to perform a particular action, such as quit :-) This again offers interesting possibilities.


I’m not sure yet how to present this messaging interface to the user, currently I am using the screen shown below:



Any messages received by the application will select the Incoming Tab and display the latest message on the screen. There is no annoying ‘popup’ dialog, although if the speech interface is enabled in the preferences, the message will be announced, if not then a simple beep will be heard by the user.

I haven’t yet created any code to resolve the IP address, I am considering writing the IP Address to the user record at logon and then displaying the user name as logged into the system, rather than resolve the IP Address to the computer name.

There’s lots I can do with this, but the framework is in place :-)

"Never fear the want of business. A man who qualifies himself well for his calling, never fails of employment." - Thomas Jefferson

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 13: Splash Screens and Transparency

ArtenSUITE
A Development Journal
Part 13: Splash Screens and Transparency

I’ve spent a few hours working on the splash screen for my application. What I’ve done now is create an irregularly shaped window, based on my company logo. This can be seen in the screenshot below:



As the image for the splash screen is read from the disk and then rendered in real time, if there is any sort of error it instead opens the original splash screen, kind of a ‘Plan B’ :-)



I’ve also been working with transparency. People tend to have strong views on whether transparent windows are a good thing or a bad thing, whatever, I like to give people choice. Therefore each window within ArtenSUITE can have its own level of transparency set at runtime. This transparency level is then written to the local cache database along with the window size and position, and is then used next time the window is opened. This seems a good solution to me, allowing more windows to be open and viewable by varying the window transparency. The transparency level is set using an screen slider situated at the bottom of each window. An example on MacOSX shown below and below that is an example from Windows Vista:






"See things as they are and write about them. Don’t waste your creative energy trying to make things up. Even if you are writing fiction, write the things you see and know." - Real Live Preacher

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 12: Window Size and Positions

ArtenSUITE
A Development Journal
Part 12: Window Size and Positions

One of the things that really annoys me with a lot of software is that the window size and positions reset to the developers defaults every time the program is run. I have 2 Monitors, a 30’‘ and a 20’‘ and I like to use that space and work with a particular layout. ArtenSUITE has been designed from Day 1 to allow the storing of window size and position, for all windows, stored locally for each user.

For programs I write that use only one window I write and read the window size from a structured text file. For ArtenSUITE however which will have many windows it was necessary to use a different approach.

Each time the program is started it looks for a REALSQL datafile in the program directory. If it doesn’t find one it creates one and within the database it creates the table and index necessary to store the window positions.

REALSQL is a version of SQLite that comes with REALbasic Professional and is ideal for use as a local data store, or cache. Note: REALSQL can also be upgraded to a full blown multi-user database (REAL SQL Server)if an appropriate license is obtained, and the the code you have used to access the local version can be used on the full version without alteration.

The Code to create the Local Database, and also create the Schema and Index is as follows:

Creating the Local Database, Window Size Schema and Index
Dim f as FolderItem
f = GetFolderItem(mCodeBaseGV.pLocalCacheFileName)

dim strCreateWinDatabase as String = "CREATE TABLE csh_win_main (win_name VARCHAR(20) NOT NULL, win_height INTEGER NOT NULL, win_width INTEGER NOT NULL, win_left INTEGER NOT NULL, win_top INTEGER NOT NULL)"
dim strCreateWinIndex as String = "CREATE INDEX win_name ON csh_win_main (win_name)"

if f.exists Then
__'Do Nothing
Else
____'Create Local Database Cache
____dim dbRBLocal as REALSQLDatabase
____dim folTarget as FolderItem
____dim booResult as Boolean

____dbRBLocal = New REALSQLDatabase
____folTarget = GetFolderItem(mCodeBaseGV.pLocalCacheFileName)

____dbRBLocal.DatabaseFile = folTarget
____booResult = dbRBLocal.CreateDatabaseFile

____if booResult = True then
______'Create Schema
______dbRBLocal.SQLExecute(strCreateWinDatabase)
______dbRBLocal.SQLExecute(strCreateWinIndex)
______dbRBLocal.Close
____else
______mCodeBaseStd.sDisplayAndLogErrorMessage(1003,"Local Cache Database NOT Created ..." + EndOfLine + dbRBLocal.ErrorMessage,"Local Cache Database NOT Created ... " + dbRBLocal.ErrorMessage)
____end if
End if

Setting and Getting the Window Size
Each window in ArtenSUITE has two methods, sGetWindowPosition and sSet WindowPosition. The Get is called by the window Open event and the Set is called by the window Close event.

The method sSetWindowPosition accepts the following as parameters:

strWindowName
intHeight
intWidth
intLeft
intTop

The method looks for a record in the local database that matches strWindowName, if a record is found it is updated using the parameters intHeight, intWidth, intLeft and intTop. If a record doesn’t exist for that window then one is created using the parameters as input.

The method sGetWindowPosition accepts the following as parameters:

strWindowName

It then returns the window size and position as integers. If a particular window does not allow resizing, only moving, then the parameters for Height and Width are ignored and only the parameters for Top and Left are used.

"Politeness and consideration for others is like investing pennies and getting dollars back." - Thomas Sowell

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 11: Sending / Logging SQL

ArtenSUITE
A Development Journal
Part 11: Sending / Logging SQL

Like in the previous article about Error and Message Handling, getting a mechanism in place to make it easy and simple to code SQL against the database is well worth the initial effort. Mixing SQL statements in with lines of program logic is messy, difficult to read, difficult to refactor and inelegant. I have in the past often done it that way, so I know :-)

For ArtenSUITE I have taken a much more structured approach.

When creating a SQL Query there are two things you need to do as a developer when working with this method. First you call the method that will contain your query, in this example the query is a function signified by the method starting with an ‘f’. This function will return a boolean. The objective of this SQL Query is to establish if a company record exists in the database. The function call is shown below, highlighted in the program code:

'Check if Company Record Exists, if Not - Probably a New Install
dim booCompanyRecordExists as Boolean = mCodeBaseDB.fSQLChkCompanyRecordExists
if booCompanyRecordExists = True then
__'Allow Logon to Database
__Dim wILogon as wLogon
__wILogon=New wLogon
else
__ ...

The second part of your job as as a developer is to write the function. I am using a seperate function or method for almost every SQL Query. It’s surprising how many times we reuse the same SQL, and as I said earlier I don’t like mixing the SQL code with the rest of the business code. (I will soon be adding the passing of parameters to certain SQL methods to cater for similar, but not the same queries) The function fSQLChkCompanyRecordExists is shown below:

dim rec as JavaRecordSetMBS = new JavaRecordSetMBS
dim strQuery as String = mCodeBaseStd.fCreateSQLComment + "SELECT count(*) FROM bas_com_main"
mCodeBaseSTD.sOutputSQLToFile(strQuery)

rec =pDBOracle.SelectSQL(strQuery,TRUE)
if rec<>Nil then 'Query is Valid
__dim intCount as Integer
__while rec.NextRecord
____intCount= val(rec.getString("count(*)"))
__wend
__if intCount > 0 then
____Return True
__else
____Return False
__end if
else 'Query is Invalid
__mCodebaseErr.sInvalidQuery(strQuery)
end if

Basically you can cut and paste your SQL function, rename it, modify slightly it and reuse for the next query. I could have done this differently and written a single method to handle every query and used the SQL string as a parameter. I’ve done that before and this time I decided to do it this way, more code but easier to read and debug without the amount of parameters that are needed to suit all circumstances.

There are a couple of useful things about this function, the first is on line 2 where mCodeBaseStd.fCreateSQLComment is called. This function returns a string which is appended to the beginning of the SQL Query as a comment. The contents of the string are: Module - UserID - DateTime. As this is appended to the beginning of the SQL Query it is available to view and track from within the database, extremely useful when troubleshooting.

Combined with the second interesting feature it makes even more sense. Line 3 is a procedures that outputs every SQL Query to a structured file on the users local computer. It provides a complete session history. When the program is relaunched the file is deleted and a new Session History file is started. Before deleting the file it is copied and stored as ‘Previous_’. Again, very useful when troubleshooting.

I also give the user the ability to view the contents of their own SQL Session History from within the application, using the SQLView window, shown below:



"You should not live one way in private, another in public." - Publilius Syrus

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 10: Errors and Message Handling

ArtenSUITE
A Development Journal
Part 10: Errors and Message Handling

Errors and Message Handling is an important part of your application. I believe it is necessary to create a framework or standard way of dealing with and displaying and logging errors and messages within your application.

General Messages

I have four kinds of messages that I can display to the user, these are INFORMATION, WARNING, STOP and ERROR. Each error message is handled by a method, the explanation for the message is sent as a parameter to the message method. This is shown below via code and then message image:

mCodeBaseStd.sDisplayInformationMessage("You Are Entering a Secure Module, All Actions Will be Logged ...")



The method mCodeBaseStd.sDisplayInformationMessage looks like this:

dim popup as New MessageDialog
dim result as MessageDialogButton
popup.icon = MessageDialog.GraphicNote
popup.Message = mProgramInfo.pProgramName + ": " + "INFORMATION:"
popup.Explanation = strMessage
result = popup.ShowModal

A icon appropriate to an information message is used and the module name is shown before the word ‘INFORMATION’. All Information messages will be displayed identically, the same goes for the other types of message. This gives a clean, consistent look to your application.


Error Messages

Error messages are handled differently. They are displayed to the user in the same way but there is more to them.

1. All My Error Messages have a Message Number


2. Error Message Numbers between 1000 and 2000 are classed as Priority 1, and this is shown to the user


3. Any Error Description or Number retrieved from the system or database is passed to my Error Display Method


4. An appropriate Icon is used to depict an error


5. All Errors are written out to a text file with date and time and module information


6. All Errors are handled by their own unique method, within the module mCodeBaseErr

When the error occurs the error method is called as such:

mCodebaseErr.sCannotConnectToDB("Could Not Connect to Oracle, The Database is Unreachable ... / Error Code: "+str(pDBOracle.Errorcode))



mCodebaseErr.sCannotConnectToDB looks like this:

dim intErrorNumber as Integer = 1001
dim strErrorMessage as String = "Cannot Connect to Database:"
dim strErrorMessageDisplay as String = strErrorMessage + EndOfLine + strMessage
dim strErrorMessageLog as String = strErrorMessage + " " + strMessage
mCodeBaseStd.sDisplayAndLogErrorMessage(intErrorNumber,strErrorMessageDisplay,strErrorMessageLog)

and mCodeBaseStd.sDisplayAndLogErrorMessage looks like this:

dim popup as New MessageDialog
dim result as MessageDialogButton
popup.icon = MessageDialog.GraphicCaution
popup.Message = mProgramInfo.pProgramName + ": " + "ERROR DETECTED:"
if intErrorNumber >=1000 and intErrorNumber <=1999 then
__popup.Explanation = "Number: " + str(intErrorNumber) +" (Priority 1 Error: Contact Support)" + EndOfLine+ strErrorMessageDisplay
else
__popup.Explanation = "Number: " + str(intErrorNumber) + EndOfLine+ strErrorMessageDisplay
end if
result = popup.ShowModal
mCodebaseStd.sOutputErrorToFile(intErrorNumber,strErrorMessageLog)

with the final line of the method calling another method which outputs the error to the logfile. This method is shown below:

Dim f as FolderItem
Dim t as TextOutputStream
dim strDate as String = mCodeBaseDB.fSQLFetchSystemDateTime
'Open File Stream
f = GetFolderItem(mCodeBaseGV.pErrorLogFileName)
t = f.AppendToTextFile
'Write to the File
t.WriteLine "<ErrorDate>" + strDate + "</ErrorDate>"
t.WriteLine "<ErrorNumber>" + str(intErrorNumber) + "</ErrorNumber>"
t.WriteLine "<ErrorMessage>" + strErrorMessage + "</ErrorMessage>"
'Close the File
t.Close

It may seem that my procedure for dealing with errors is complicated. In use it is actually quite simple. I pay a lot of respect to errors and so each one is given their own number and method within mCodeBaseErr, this is the method that is called when the error occurs. The call to output the error to the screen and subsequently the log file is handled automatically, so once I had setup this structure it has been easy for me to build solid handling of errors into my application.

"Anything that has real and lasting value is always a gift from within." - Franz Kafka

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 9: Startup Sequence

ArtenSUITE
A Development Journal
Part 9: Startup Sequence

I’ve spent a few hours last night and this morning working on refining the startup and database logon procedure for ArtenSUITE. There are several complications and several paths with different outcomes and it is easy to end up with a convoluted, difficult to follow startup procedure. At the moment I am fairly happy with the startup procedure which works like this:

The app.Open Event is automatically called when the application launches. Within this method I have called a startup method which does the following by calling individual methods from within either the mCodeBaseSTD or mCodeBaseDB modules:

Displays a Splash Screen
Registers my MBS Plugins
Sets a property to show which platform the application is currently running on
Sets properties with the pathname to the Preferences file, the Errorlog file and the Sqlsession file
Sets properties containing network information, IP Address, Subnet, MAC Address Etc.
Checks for the existence of the Preferences file, Sets a Boolean property accordingly
if PrefsFileExists = TRUE
__Attempt to connect to the database
__if ConnectedProperty = True
____Enable the Continue Button on the Splash Screen

__if ConnectedProperty = False
____Display an Error Message



____Display the Preferences Window for Input
____Close Application

if PrefsFileExists = FALSE
__Display the Preferences Window for Input
__Close Application

The Splash screen, early version shown below, is displayed and when the ‘Continue’ button is depressed an event is triggered by the control which performs the following actions:



Check if a Default Company Record exists in the Database
If CompanyRecordExists,
__Close the Splash Window and Open the Logon Window for ID and Password Input



If CompanyRecordNOTExists then assume a new company needs setting up and open New Company Input Window
__Close Application

This sequence of events may well be refactored in the future but for now they logical, simple and work well :-)

"Even the most dangerous look less threatening when they are dead." - Laura Moncur

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 8: NLS ...

ArtenSUITE
A Development Journal
Part 8: NLS ...

After installing Oracle it’s important to setup the variables NLS_LANG and NLS_DATE_FORMAT. These variables determine the default settings that Oracle uses for Date formatting, Currency formatting, Sort Orders etc. More information is available here.

If Oracle is installed on a Windows server the NLS_LANG can be as an environment variable using the System Control Panel. If you are in the UK the settings you will want are as follows:

Key: NLS_LANG
Value: ENGLISH_UNITED KINGDOM.WE8ISO8859P1

NLS_DATE_FORMAT has to be set on a session by session basis, so you can either put it in the startup procedure for your application as such:

alter session set NLS_DATE_FORMAT = ‘dd/mm/yyyy’;

or you can implement it as a database trigger:

CREATE OR REPLACE TRIGGER "DATA_LOGON_TRIGGER"
after logon
ON DATABASE
begin
execute immediate
'alter session set nls_date_format = ''ddmmyyyy'' ';
end;
/
ALTER TRIGGER "DATA_LOGON_TRIGGER" ENABLE
/

"The man who has nothing to boast of but his illustrious ancestry is like the potato - the best part under ground." - Thomas Overbury

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 7: Triggers on Delete

ArtenSUITE
A Development Journal
Part 7: Triggers on Delete

For ArtenSUITE I intend to make far greater use of the capabilities of the database than I ever have previously. In the past I have often ignored Triggers and Database Constraints in favour of keeping the rules within my applications. This has been relatively successful, especially when developing software that is going to be used within a controlled environment such as a single corporation.

Software that intends to be unleashed to the masses however needs more control putting into the database where it can stop the Smart Alec with the password and a SQL tool from deleting or modifying data directly, completely bypassing the controls within the application.

So this evening I have been putting some Triggers into my database.

Against each Database Table is a Column called LOCKED. So for example the Main User Table, BAS_USR_MAIN would have a column called USR_LOCKED. If the contents of the LOCKED column equal 1, then the record cannot be deleted. Ever. Under any circumstances. Mostly. This is useful for stopping the Master User ( the Main Admin User that is setup automatically when the system is first used) from deleting his own entry and therefore locking himself out of the system. There are lots of circumstances, in all kinds of tables, where having the LOCKED column be beneficial.

I therefore needed a Trigger to execute BEFORE the row was to be deleted and stop the delete happening if the LOCKED flag was set. This is very easy to accomplish in Oracle. The SQL I used is shown below:



What is more useful about this Trigger is that I have used the RAISE_APPLICATION_ERROR process to pass a user defined error number and message back to the application that attempted to perform the delete. The user defined error numbers can be between -20000 and -20999, the other numbers are reserved by Oracle for their own purposes. My application can check for this error code when a delete is attempted and then display a nicely formatted error message that explains precisely why the record could not be deleted. The message description I have specified in the script is the one that will be seen when an attempt to delete is made from outside the control of my application.

An example of this can be seen in the screenshot below. I logged onto SQLPlus from my Mac and attempted to delete the contents of a table (one record of which) is protected by the Trigger shown earlier. The screenshot shows the results of the attempted delete.



I only have one record in this table at the moment but if there was more than one record the SQL command I issued would have deleted all of the other rows successfully except for the one marked as LOCKED. This is down to my use of the ‘FOR EACH ROW’ statement in the Trigger.

"Self-development is a higher duty than self-sacrifice." - Elizabeth Cady Stanton

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

MonkeyBread Software Plugin Help System

After evaluating the REALbasic Plugins from MonkeyBread Software for the last couple of weeks I have gone ahead and ordered the complete bundle. At around 200 Euros it’s good value for money, providing over 20000 extra functions for use within your RB programs. I’m especially impressed with the Java and CGS Transition functions, they work very well and are simple to incorporate into your own applications.

With so many functions available and over 7000 pages of documentation (that’s not a mis-type, there really are over 7000 pages) it can be daunting finding the information you require. Fortunately MBS provide a Documentation Search program. The program works but is not at all polished. It is provided as Open Source so I decided to make some modifications to make it slicker and more useful.

The modifications I have made are as follows:

Quit application when window closed
Named the application
Listbox modified to use small font
Window is now resizable
Window position and size now saved
Recent Searches List Added, Click to Select
Logo Added
Incorporated CGS Transition Effects on Search and Close

The executable file can be downloaded from here:

Mac Download
Windows Download

The source code is available to anyone who is interested, just drop me an email.

"Most people rust out due to lack of challenge. Few people rust out due to overuse." - Unknown

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business