One of the main problems I've always faced was writing code to manipulate
database entities using SQL. You can write it in the source itself and
concatenate large strings in order to issue the statement through ADO. This
works just fine so long as you never need to change you database.
However, I've worked on a number of projects that need to work on both
Oracle and SQLServer, or even DB2 on an MVS mainframe. In order to
accomodate these changes, I created a simple class that treats SQL like a
parameterized string. All the programmer needs to do is create either a
resource file for statically linked code, or a text file for dynamically
linked code. The class opens either the named resource or the text file and
reads its contents to create a list of tokens. These tokens can be assigned
values. The progam can then ask the object to retrieve the formatted SQL
statement and execute it. The text below represents a formatted SQL
template that the class can read and then format based on provided
parameters.
UPDATE MY_TABLE
SET
MY_FIELD = {MyField:C(11)},
MY_NUMBER = {MyNumber:N},
My_DATE={MyDate:D}
WHERE
MY_ID={MyID:N};
The source code would then read:
Dim metaData
Set metaData = Server.CreateObject("MetaSql.CSqlMetaData")
Call metaData.loadFile("myTestSql.sql")
metaData.param("MyField") = "This isn't a bad test of the class's automated
string formatting."
metaData.param("MyNumber") = 10
metaData.param("MyDate") = Now()
metaData.param("MyID") = Request("my_id")
Call adoConnection.Execute(metaData.sqlSource)
The preceding code would create the following SQL statement:
UPDATE MY_TABLE
SET
MY_FIELD = 'This isn''t',
MY_NUMBER = 10,
My_DATE='12/28/2000'
WHERE
MY_ID={MyID:N};
This doesn't look like much unless you need to convert the SQL to Access or
Oracle which both have their own ideas about formatting dates, etc. Even
more important, the code logic above doesn't need to change at all if the
SQL changes. You can replace the UPDATE statement with a stored procedure.
All the logic needs to know is that there is a named file that contains SQL
expecting a certain number of parameters. You could also use the following
logic for updating tables based on <form> input.
Dim metaData
Dim tokenList
Set metaData = Server.CreateObject("MetaSql.CSqlMetaData")
Call metaData.loadFile("myTestSql.sql")
tokenList = metaData.paramList
If IsArray(tokenList) Then
For i = LBound(tokenList) To UBound(tokenList) Step 1
metaData.param(tokenList(i)) = Request(tokenList(i))
Next
Call adoConnection.Execute(metaData.sqlSource)
End If
In the code mentioned above, all the developer needs to do is create a
syntactically correct SQL statement, and then create correspondingly named
input fields. The object even formats the data on the way in to help reduce
errors from user input. Obviously some forms will require additional type
checking, but the bulk of the work of creating the SQL statement and
extracting the supplied data has been done for you.
Download the supplementary zip file, which
contains the COM object, some sample code for your Web site, a
sample SQL template, and a readme.txt file explaining how it all fits together.
Submitted by Robert J. Morris
