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 "
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