I am rather new at this code and am attempting to modify existing code to use clob datatypes, which I have never used before. The database tables have been set up for clob data. When trying to use dbms_lob.writeappend, I am getting the following error..Command text was not set for the command object.
I've researched this in the net as much as I could, but I'm lost in what I am doing wrong. Can you help please?
Here is my code...
[FUNCTIONS]
<!-- #INCLUDE FILE="adovbs.inc" -->
<%
''################################################ #
''# Description: A class used to handle the data manipulation language
''# features of SQL. A Logger is also used for error handling.
''#
''################################################ #
Class SQL
''## The ADODB Connection
Dim connection
''## A result set
Dim resultSet
''## Column value pairs used for inserting and updating
Dim columnValuePairs
''## Name of table, used for checking for valid input on inserting and updating
Dim tableName
''## A logger forthe writedbSelect method
Dim SQLLogger
''## A variable used for looping
Dim I
''###############################################
''# Function: dbConnect
''# Description: Establish a connection to the database
''###############################################
Public Sub dbConnect()
'' ## Set connection = Server.CreateObject("ADODB.Connection")
'' ##connection.Open Application("dbName"), Application("dbLogin"), Application("dbPassword")
Set connection = Server.CreateObject("ADODB.Connection")
connection.Open "Provider=OraOLEDB.Oracle;User ID=" & Application("dbLogin") & ";Password=" & Application("dbPassword") & ";Data Source=" & Application("dbName") & ";"
End Sub
''###############################################
''# Function: dbClose
''# Description: Close the database connection.
''###############################################
Public Sub dbClose()
connection.Close
Set connection = Nothing
connection = ""
End Sub
''###############################################
''# Function addColumnValuePair
''# Description: Adds to the Dictionary Object columnValuePairs to be used
''# with inserting and updating. See dbUpdateBegin and dbInsertBegin for example
''# of use.
''# @param column The table column
''# @param value The value of the column
''###############################################
Public Sub addColumnValuePair(column, value)
''## Insert code to make sure types of columns match types of values (use tableName)
columnValuePairs.add column, value
End Sub
''###############################################
''# Function dbUpdateBegin
''# Description: Begin constructing an UPDATE on the database. All arguments are
''# required.
''# @param table The table to update
''#
''# Update Example:
''# Set sequel = New SQL
''# sequel.init
''# sequel.dbConnect
''# sequel.dbUpdateBegin "employees"
''# sequel.addColumnValuePair "FirstName", "John"
''# sequel.addColumnValuePair "LastName", "Doe"
''# sequel.addColumnValuePair "Company", "Company"
''# sequel.dbUpdateEnd "employeeId=1"
''# sequel.dbClose
''###############################################
Public Sub dbUpdateBegin(table)
tableName = table
columnValuePairs.removeAll
End Sub
''###############################################
''# Function dbUpdateEnd
''# Description: Finish constructing an UPDATE statementon the database
''# and execute it. The condition argument is required but an empty string may
''# be provided. See dbUpdateBegin for example of use.
''# @param condition What to specify in the WHERE clause
''###############################################
Public Sub dbUpdateEnd(condition)
Dim errDesc
sqlStatement = constructDbUpdate(condition)
On Error Resume Next
connection.Execute(sqlStatement)
If Err.Number<>0 Then
errDesc = Err.Description
On Error Goto 0
Err.Raise 7216, "", errDesc & "<br><br>" & sqlStatement
End If
End Sub
''###############################################
''# Function dbExecute
''# Description: Execute sqlStatement on the database
''# @param sqlStatement The sqlStatement statement
''# @return The result of the execution of the sqlStatement statement
''###############################################
Public Function dbExecute(sqlStatement)
Dim errDesc
If(InStr(UCase(sqlStatement),"SELECT") <> 0) Then
Set resultSet = server.createObject("adodb.recordSet")
resultSet.CursorLocation = adUseClient
On Error Resume Next
resultSet.open sqlStatement, connection, adOpenKeySet
If Err.Number<>0 Then
errDesc = Err.Description
On Error Goto 0
Err.Raise 7216, "", errDesc & "<br><br>" & sqlStatement
End If
Set dbExecute = resultSet
Else
On Error Resume Next
Set dbExecute = connection.Execute(sqlStatement)
If Err.Number<>0 Then
errDesc = Err.Description
On Error Goto 0
Err.Raise 7216, "", errDesc & "<br><br>" & sqlStatement
End If
End If
End Function
[/FUNCTIONS]
Code: ( text )
’’## This is the code that uses the above functions
<%
commentsLogger.write Request("add_action")
dim workingRS, value
''
dim daSQL
Set daSQL = new SQL
daSQL.init()
daSQL.dbConnect()
''
Select Case Request("add_action")
'' ## determine what to do, depending on which button the user has clicked
Case "PComments":
If Request("db_command") = "insert" Then
tempCom = Request("comments")
tempComTooLong = False
commentsSQL.dbInsertBegin "P_Comments"
commentsSQL.addColumnValuePair "P_Comment_ID", "P_Comment_ID.NextVal"
commentsSQL.addColumnValuePair "P_ID", "" & Request("p_id") & ""
commentsSQL.addColumnValuePair "P_Comment_Date", "" & "To_Date('" & Date() & "','MM/DD/YYYY')"
commentsSQL.addColumnValuePair "P_Comment", "'" & tempCom & "'"
commentsSQL.addColumnValuePair "E_ID", "" & Session("id") & ""
commentsSQL.dbInsertEnd
Response.write "<center>"
Response.write "<span class=""saveConfirm"">Comment has been saved."
Response.write "</span></center><br><br>"
%>
commentsSQL.dbUpdateBegin "P_Profiles"
commentsSQL.addColumnValuePair "GSP _Comments", "'" & tempCom & "'"
commentsSQL.dbUpdateEnd "P_ID=" & Request("p_id") & ""
<%Case "ProcessComments":
If Request("db_command") = "insert" Then
tempCom = ""
tempCom = Request("comments")
tempCom = "'" & tempCom & "'"
''## These commented out lines are how the code was originally, before attempting clob
''## commentsSQL.dbUpdateBegin "P_Profiles"
''## commentsSQL.addColumnValuePair "GSP _Comments", "'" & tempCom & "'"
''## commentsSQL.dbUpdateEnd "P_ID=" & Request("p_id") & ""
Set workingRS = daSQL.dbExecute("SELECT GSP_COMMENTS FROM P_PROFILES where P_ID = " & Request("p_id") & " FOR UPDATE OF GSP_COMMENTS")
value = workingRS("GSP_COMMENTS")
''## The following statement is what is causing the error. I have tried it both with and
''## without the daSQL.dbExecute
daSQL.dbExecute("dbms_lob.writeappend value, length(tempCom), tempCom")
workingRS.Close
Set workingRS = Nothing
Response.write "<center>"
Response.write "<span class=""saveConfirm"">Comment has been saved."
Response.write "</span></center><br><br>"
%>