Class DB_m
Private conn
private error_string
Public Sub Class_Initialize()
init()
End Sub
public sub init()
set conn = server.createObject("ADODB.Connection")
conn.open "Provider=SQLOLEDB.1;Data Source=[DB server];" & _
"User ID=[account];Password=[password];Initial Catalog=[DB name];Net=dbmssocn"
end sub
Public Sub Class_Terminate()
conn.close()
End Sub
Public Function useSql(sql)
dim rsTemp
Set rsTemp = Server.CreateObject("ADODB.Recordset")
rsTemp.Open sql, conn, 3, 1
if err.number = 0 then
Set useSql = rsTemp
else
error_string = "無法取得資料"
set useSql = null
end if
End Function
Public Function useSqlUpdate(sql)
dim rsTemp
Set rsTemp = Server.CreateObject("ADODB.Recordset")
rsTemp.Open sql, conn, 1, 2
if err.number = 0 then
Set useSqlUpdate = rsTemp
else
error_string = "無法更新資料"
set useSqlUpdate = null
end if
End Function
Public sub executeSql(sql)
conn.execute sql
if err.number <> 0 then
error_string = "無法執行動作"
end if
End sub
'data is a dictionary object
Public Function insert(data, table)
data.add "created_time", now()
sql = "insert into " & table & " ("
keys = data.keys()
sep = ""
For Each k In keys
sql = sql & sep & k
sep = ", "
next
sql = sql & ") values ("
sep = ""
For Each k In keys
value = data.item(k)
if check_int(value) = false then
value = "N'" & format_sql_input(value) & "'"
end if
sql = sql & sep & value
sep = ", "
next
sql = sql & ")"
sql = "SET NOCOUNT ON;" & sql & ";Select @@Identity as insert_id"
set rs = conn.execute(sql)
insert = rs("insert_id")
End Function
public sub update(id_field, id, data, table)
data.Add "updated_time", now()
sql = "update " & table & " set "
keys = data.keys()
sep = ""
For Each k In keys
value = data.item(k)
if check_int(value) = false then
value = "N'" & format_sql_input(value) & "'"
end if
sql = sql & sep & k & " = " & value
sep = ", "
next
if check_int(id) = false then
id = "N'" & format_sql_input(id) & "'"
end if
sql = sql & " where " & id_field & " = " & id
conn.execute(sql)
end sub
public function in_array(v, arr)
dim result, i
result = -1
i = 0
if not isNull(arr) and uBound(arr) > -1 then
for i = 0 to uBound(arr)
if cStr(arr(i)) = cStr(v) then
result = i
exit for
end if
next
end if
in_array = result
end function
public function check_int(n)
dim digits_str(9), result, i, t
result = true
if isNull(n) or len(n) = 0 then
result = false
else
for i = 0 to uBound(digits_str)
digits_str(i) = cStr(i)
next
for i = 1 to len(n)
t = cstr(n)
if in_array(mid(t, i, 1), digits_str) = -1 then
result = false
exit for
end if
next
end if
check_int = result
end function
public function format_sql_input(input)
dim replace_list(11), replace_list_to(11), result, temp_str, replace_index, i
result = input
replace_list(0) = "'" : replace_list(1) = """"
replace_list(2) = "<" : replace_list(3) = ">"
replace_list(4) = chr(12) & chr(15) : replace_list(5) = chr(15) & chr(12)
replace_list(6) = chr(10) & chr(13) : replace_list(7) = chr(13) & chr(10)
replace_list(8) = chr(10) : replace_list(9) = chr(12)
replace_list(10) = chr(13) : replace_list(11) = chr(15)
replace_list_to(0) = "'" : replace_list_to(1) = """
replace_list_to(2) = "<" : replace_list_to(3) = ">"
replace_list_to(4) = "<br/>" : replace_list_to(5) = "<br/>"
replace_list_to(6) = "<br/>" : replace_list_to(7) = "<br/>"
replace_list_to(8) = "" : replace_list_to(9) = ""
replace_list_to(10) = "" : replace_list_to(11) = ""
for i = 0 to Ubound(replace_list)
if inStr(result, replace_list(i)) > 0 then
result = replace(result, replace_list(i), replace_list_to(i))
end if
next
format_sql_input = result
end function
function get_max_log_id()
dim sql, result
sql = "select max(id) as m from dbo.TWExtendLog"
'response.write sql
'response.End()
set result = use_sql_pcc(sql)
if err.number = 0 then
get_max_log_id = cLng(result("m")) + 1
else
error_string = "無法取得紀錄編號"
get_max_log_id = 0
end if
end function
end class
