SQL & how different databases implement it

Some vaguely structured thoughts on the differences that you see between SQL as implemented by one DBMS and that of another.

14 June 2006


Example ADODB for SELECT with variables

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


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

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.



September 2005   October 2005   December 2005   January 2006   March 2006   May 2006   June 2006   September 2006   May 2007   June 2007   November 2007   December 2007   March 2008   April 2008   December 2008   January 2011  

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]