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