SQL & how different databases implement it

I wasted at least an hour trying to find a simple example of use of static SQL (i.e. using "bind variables" or "SQL parameters") with ADODB, just to remind myself after a long period without doing so.

You should remember that the traditional (legacy?!?) approach to writing SQL statement is as follows:

set objConn = server.CreateObject("ADODB.Connection")
objConn.Open ("Provider=OraOLEDB.Oracle;User ID=xxx;Password=yyy;Data Source=zzz")
set objRS = Server.CreateObject("ADODB.Recordset")
v_designid = "3" ' This could come from e.g. a querystring variable
v_status = "ordered" ' This could come from e.g. a querystring variable
sqlString = "SELECT * FROM calendar WHERE calendar_design_id = " & v_designid
sqlString = sqlString & " AND status = '" & v_status & "'"
objRS.Open sqlString, objConn

response.write(objRS(0).Value)

Having not found anything satisfactory on the Web that illustrates a good simple example of static SQL, I am forced to write my own for your amusement or help. Here is the code in ASP:

' Setup connection object once, preferably in an include file
set objConn = server.CreateObject("ADODB.Connection")
objConn.Open ("Provider=OraOLEDB.Oracle;User ID=xxx;Password=yyy;Data Source=zzz")

' Setup recordset object and command object
set objRS = Server.CreateObject("ADODB.Recordset")
set objCommand = Server.CreateObject("ADODB.Command")

' Associate the command object with the connection object
objCommand.ActiveConnection = objConn

' Final step before we are ready to move on the the actual SQL!
set objParam = objCommand.Parameters


' What has gone before should really be in an include file,
' because it will be the same for every page.
' From this point on, put the code in your normal ASP file
' Setup the SQL statement
sqlString = "SELECT delivery_vat "
sqlString = sqlString & " FROM sales_order "
sqlString = sqlString & " WHERE private_cust_id = ?"
sqlString = sqlString & " AND status = ?"
objCommand.CommandText = sqlString
objCommand.CommandType = adCmdText

' The question marks in the above statement are correct.
' They are the ADODB placeholders for variables
' The order in which you create and append the parameters is important
' The order needs to match the question marks in the SQL above
set objParam = Server.CreateObject("ADODB.Parameter")
objParam.Type = adVariant
objParam.Size = 20
objParam.Direction = adParamInput
objParam.Value = session("private_cust_id")
objCommand.Parameters.Append objParam
set objParam = nothing

set objParam = Server.CreateObject("ADODB.Parameter")
objParam.Type = adVariant
objParam.Size = 10
objParam.Direction = adParamInput
objParam.Value = v_status
objCommand.Parameters.Append objParam
set objParam = nothing

' Now execute the SQL
set objRS = objCommand.Execute

' And get setup ready for the next SQL statement in the same ASP file
set objCommand = nothing
set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConn

' Now we can use the contents of the recordset retrieved
if NOT objRS.EOF then
v_vat = objRS("delivery_vat").Value
...
...
etc

The second SQL statement in the code can be added in the same way as the first, starting with the line of code sqlString = ...

Remember that to use the variables such as adVariant, adParamInput, etc, place the following TWO lines of code at the top of your global.asa:

<!--METADATA TYPE="TypeLib" NAME="Microsoft ActiveX Data Objects 2.5 Library" UUID="{00000205-0000-0010-8000-00AA006D2EA4}" VERSION="2.5"-->
<!--METADATA TYPE="TypeLib" NAME="Microsoft ADO Ext. 2.5 for DDL and Security" UUID="{00000600-0000-0010-8000-00AA006D2EA4}" VERSION="2.5"-->

I hope you can follow the above, it really is that simple. Of course the two big benefits of the above approach to constructing and executing SQL statements are:

• increased performance (two users executing the same statement, even with different values in the parameters, enable the DBMS to use a "compiled" version of the statement)
• better security (no more SQL injection attacks where a stray apostrophe entered in a text field disrupts the normal flow of a SQL statement)

Feel free to write with any comments or questions.