Pseudo-Objects in Active Server Pages

<%
' db.inc -- Database functions and procedures.

' DB_NullVal() is equivalent to the Oracle nvl() function. It returns the 
' given value if the given variable is null. Otherwise, it returns the 
' variable.
'
function DB_NullVal(var, val)
   if IsNull(var) then
      DB_NullVal = val
   else
      DB_NullVal = var
   end if
end function

' DB_NextVal() returns the next values of the given sequence.
'
function DB_NextVal(connString, sequenceName)
   dim conn
   dim rs
   dim nextVal

   nextVal = 0

   set conn = Server.CreateObject("ADODB.Connection")
   conn.Open connString
   set rs = conn.Execute("select " & sequenceName & ".NextVal from dual")
   nextVal = rs("NextVal")
   rs.Close

   DB_NextVal = nextVal
end function

' DB_Query() runs a query and returns the resulting recordset.
'
function DB_Query(connString, fields, from, where, orderBy)
   dim recordset
   dim sql

   sql = "select " & fields & " from " & from

   if where <> "" then
      sql = sql & " where " & where
   end if

   if orderBy <> "" then
      sql = sql & " order by " & orderBy
   end if

   ' debug
   ' Response.Write "
" & sql & "
"

   set recordset = Server.CreateObject("ADODB.Recordset")
   recordset.Open sql, connString

   set DB_Query = recordset
end function

' DB_Insert() executes an SQL command to insert records. If the insert
' statement completes successfully, a commit is executed.
'
function DB_Insert(connString, table, fields, values)
   dim conn
   dim rs
   dim sql
   dim recordsAffected

   DB_Insert = 0

   set conn = Server.CreateObject("ADODB.Connection")
   conn.Open connString

   sql = "insert into " & table & " (" & fields & ") values(" & values & ")"
   
   conn.Execute sql, recordsAffected

   DB_Insert = recordsAffected

   if recordsAffected > 0 then
      ' Uncomment for Oracle, if autocommit is not on.
      ' conn.Execute "commit", recordsAffected
   end if

   conn.Close
end function

' DB_Update() executes SQL to perform a database update.  If the update
' statement completes successfully, a commit is executed.
'
function DB_Update(connString, tables, setStatements, where)
   dim conn
   dim sql
   dim recordsAffected

   sql = "update " & tables & " set " & setStatements

   if where <> "" then
      sql = sql & " where " & where
   end if
   
   ' debug
   ' Response.Write "

	

		
				
Email Article
		
				
Print Article
			
				
	

		
sql = " & sql & "
"

   set conn = Server.CreateObject("ADODB.Connection")
   conn.Open connString
   conn.Execute sql, recordsAffected

   DB_Update = recordsAffected

   if recordsAffected > 0 then
      ' Uncomment for Oracle, it autocommit is not on.
      ' conn.Execute "commit", recordsAffected
   end if

   conn.Close
end function

' DB_Delete() executes an SQL command to delete records.  If the delete
' statement completes successfully, a commit is executed.
'
function DB_Delete(connString, from, where)
   dim conn
   dim sql
   dim recordsAffected

   sql = "delete from " & from

   if where <> "" then
      sql = sql & " where " & where
   end if

   set conn = Server.CreateObject("ADODB.Connection")
   conn.Open connString
   conn.Execute sql, recordsAffected

   DB_Delete = recordsAffected

   if recordsAffected > 0 then
      ' Uncomment for Oracle, if autocommit is not on.
      ' conn.Execute "commit", recordsAffected
   end if

   conn.Close
end function

' DB_BeginTransaction() begins a transaction. (SQL Server)
'
function DB_BeginTransaction(connString)
   dim conn

   set conn = Server.CreateObject("ADODB.Connection")
   conn.Open connString
   conn.BeginTrans
   
   set DB_BeginTransaction = conn
end function
%>

Page 11 of 11



This article was originally published on October 14, 1999