Want to write a rock-solid, dependable application, but in record time? Code efficiency is the key! This article provides useful tools and ideas to speed up your database interactions in ASP, as well as a best-practice you just might be convinced to adapt for your database-driven applications.
Contributed by Justin Cook Rating: / 42 December 29, 2003
Let’s face it; we live in a fast-paced society! Demands are placed upon us to meet deadlines, and a good expectation to maintain is that those deadlines will seem stressfully unrealistic. When tasked with creating a web-based application, we have to factor in time for the requirements-gathering phase, design phase, development phase, debugging phase, and finally the tedious documentation phase. (No, we can’t include the stress-induced coma phase, sorry)
One of the best ways to speed up the development phase – well, besides just typing faster – is to simplify common procedures into reusable functions. This article will examine ways to do this with some common database interactions.
Many development suites (i.e. Dreamweaver) provide inbuilt mechanisms for handling some pf these common procedures. But if you’re at all like me, you prefer total control over your coding. If you’re not like me, you need to watch the Matrix, and ponder deeply its significance regarding your consent to allowing programs control over your work. Ok ok, there’s nothing wrong with your preference, but at least continue reading to see if the efficiencies described appeal to you.
I will cover two common procedures, and one perhaps not as common, but that I highly recommend. These are: executing SQL statements, retrieving a count of specific items within a table, and auto-incrementing. For all three we will examine a simple, reusable tool to do the job right, and fast. The most you can expect from using these tools is the burden of that deadline being lightened; the least you can expect is that they’ll make your life a little simpler.
Already you’re thinking that it is very simple to execute a SQL statement. It’s really only 6 lines of code, providing you have a connection string and SQL statement:
dim objConn
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open( your connection string )
objConn.Execute( SQL statement )
objConn.Close()
set objConn = Nothing
Could it really be any simpler? Well, of course, or else I wouldn’t be writing this article! When you think about it, each time you need to write 6 more lines of code. This little block of code also gives you approximately 150 more opportunities to mistype a character, resulting in time lost due to debugging. And trust me, by the 50th time you write objConn.Close() in your application, you will inexplicably begin to despise each o, b, j, etc. that you have to type to perform the same simple procedure as the other 49 times.
So, here’s the tool to turn those 6 lines of monotony into 1 line of excitement. Well, maybe you won’t be that excited by it, but nonetheless, here it is:
'================================
Sub commitToDB( strSQL2, strDB )
'================================
If strSQL2 = "" Then Call errorMessage( "Cannot commit empty string!" )
If strDB = "" Then strDB = Application("MySQL_inventory")
‘=== in case you call several DBs from your application
‘===Application("MySQL_inventory") is the default
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open( strDB )
objConn.Execute( strSQL2 )
objConn.Close()
Set objConn = Nothing
End Sub
Explanation
This subprocedure takes a SQL statement, and executes it on the default connection unless another is specified. The call errorMessage() is explained in the article “Easy Error Management”, basically just a way to explain in plain English that there’s a problem. Other than that, nothing miraculous is accomplished here, except for the fact that now when you wish to execute a statement, it only requires that you type the following:
Call commitToDB( strSQL, “” )
Wasn’t that easy? Honestly, it brings me a strange sense of satisfaction to type in this one line of code and move on with my life!
Quite often, I find it necessary to determine a number if items in a table. This happens when I want to make sure something exists (the count is greater than 0). Trying to perform work on a non-existent result will give me an error, so I must first make sure I have something to work with! Or perhaps I know something exists, and I just need the total. For instance, I know I have lots of green socks in my inventory; I need to know many, and perhaps whether or not I should therefore be ordering more green socks.
Here’s a simple way to do this:
Dim intCount, strSQL
strSQL = "SELECT COUNT( * )
FROM socks WHERE color = ‘green’"
set objRS = server.CreateObject("ADODB.RecordSet")
objRs.Open strSQL2, strConnect
intCount = objRS( 0 )
objRS.Close()
Set objRS = Nothing
If intCount < 100 Then
Response.Write( “You’d better order more green socks!” )
Else
Response.Write( “You have lots of green socks” )
End If
Now, we’re in the same position as before. This is not the most difficult thing to manage, but suppose you have to do it for green, red, orange, yellow, and puce socks all individually? Now it becomes repetitious. So, here we go:
'===================================== function getCount( strTable, strCriteria, altString ) '===================================== dim strSQL2, objRS strSQL2 = "" if trim( altString ) = "" then if strTable = "" then call errorMessage( "Invalid table!" ) strSQL2 = "SELECT COUNT( * ) FROM " & strTable if strCriteria <> "" then strSQL2 = strSQL2 & " WHERE " & strCriteria else strSQL2 = altString end if 'debug( strSQL2 ) set objRS = server.CreateObject("ADODB.RecordSet") objRs.Open strSQL2, Application("MySQL_inventory") getCount = objRS( 0 ) objRS.Close() Set objRS = Nothing strSQL2 = null end function
Now watch how this simplifies our life:
If getCount( “socks”, “color = ‘green’”, “” ) < 100
Then Response.Write( “Almost out of green socks!” )
End If
Here’s another example:
If getCount( “users”, “email = ‘me@here.com’”, “” ) = 0
Then strErrorMessage = “Sorry, that email address
does not exist in our records.”
End If
There, we’ve done it again. Six lines of code reduced to one function call!
But you ask: “What if I am a good RDBA and have normalized my tables to the 3rd normal form? ‘strTable’ is insufficient when I’m performing JOINs and the like!”
This is very true, and that’s where the altString parameter comes in. We could create our SQL statement separately, with all the fancy JOINs we need. Something like this:
strSQL = “SELECT COUNT( s.id ) FROM socks s “ & _
“INNER JOIN colors c ON c.id = s.color_id “ & _
“WHERE c.description = ‘” & strColor & “’”
if getCount( “”, “”, strSQL ) < 100
Then Response.Write( “Almost out of green socks!” )
This little function has simplified my life to the extent that I now only have to spend three minutes of my day coding, and the rest of if writing for ASP Free! A slight exaggeration, but it’s very useful, I kid you not.
Now it’s worth noting that in our first function, commitToDB(), one of the parameters (strDB) allowed us to perform the procedure using the default connection, or specify our own. It would be simple to add this feature to the getCount() function: just add the extra parameter, and the default setting within the function.There are two reasons why it’s not already there:
I copied the function from an application where I’m only referencing one database. I had no need for the optional parameter, so I took it out to save myself writing the extra ‘, “” ’ every time.
So that I could highlight the simplicity of modifying these tools to fit your needs. Aren’t I nice?
Necessity was very much the mother is this little invention. My conundrum was this: the application I was designing wrote the same data to two databases; one on our intranet and the other on the World Wide Web. I was using auto-increment to set my unique identifier for each record. But what if one of the writes failed to either of the databases? Would that not mess up the index of IDs? Would the subsequently written records be inconsistently identified?
I had neither the time nor the desire to reference the records by anything other than their unique identifiers (that’s what they’re there for after all!). So I decided to forego the whole auto-increment feature, and write my own IDs. So now I just had to work out some efficient way of getting/setting the highest ‘id’ within a table. Here’s the function I came up with:
All this little function accomplishes is a retrieval of the highest primary key within a table. The primary key (pk) defaults to ‘id’, the column name used by probably 99% of all DBA’s.You’ll notice that I hotwired the getCount() function to retrieve the MAX id for us, which it does quite happily. Maybe you’re now thinking that I should change the name of getCount() to reflect the fact that it can get other things besides just counts. Well, I suppose the only reason that I haven’t, is because getCount() is easier to remember and faster to type then getCountOrPossiblySomeOtherRequiredValue().
Anyhow, we can easily use this maxID() to set our unique identifiers like this:
There we have it, we can our own unique ID, and easily keep it consistent across countless tables and databases. I would also recommend the use of foreign key constraints to ensure referential integrity, but that’s beyond this article.
It was interesting to discover after creating this technique that I had inadvertently complied with a ‘best-practice’ of the DBA world. It turns out that many people are highly critical of using auto-increment, no matter what the application/database requirements. In fact, you could read up on this raging debate if you like.
The other reason I highly recommend this method, is that down the road you may wish to upgrade/switch your database engine. It will be much easier to do when we don’t have to worry about the new system using auto-increment in exactly the same manner. That’s because we never used it, our SQL statements never depended on it, for all we care the database could suffer severe frostbite and have to undergo amputation of it’s auto-increment appendage. We simply refuse to have our great applications brought to their knees by a simple ID.
The above concepts are meant not only to improve efficiency, but also to drive home the point that if there is a slightly faster way to do things, do it. At first look, it’s only a difference of 5 lines of code. But after typing it 100 times in 6 different applications… well, you do the math.You will be able to reuse these tools, as well as develop your own. You will improve your efficiency because of it. You might meet that deadline after all…
What is ADO?
