By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,537 Members | 1,743 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,537 IT Pros & Developers. It's quick & easy.

Command text was not set for the command object - error from dbms_lob.writeappend

P: 1
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]

Expand|Select|Wrap|Line Numbers
  1. ##  This is the code that uses the above functions
  2.  
  3. <%
  4. commentsLogger.write Request("add_action")
  5. dim  workingRS, value
  6. ''
  7. dim daSQL
  8. Set daSQL = new SQL
  9. daSQL.init()
  10. daSQL.dbConnect()
  11. ''
  12. Select Case Request("add_action")
  13. '' ## determine what to do, depending on which button the user has clicked
  14.  
  15. Case "PComments":
  16.  
  17.   If Request("db_command") = "insert" Then
  18.  
  19.     tempCom = Request("comments")
  20.  
  21.       tempComTooLong = False
  22.  
  23.  
  24.     commentsSQL.dbInsertBegin "P_Comments"
  25.     commentsSQL.addColumnValuePair "P_Comment_ID", "P_Comment_ID.NextVal"
  26.     commentsSQL.addColumnValuePair "P_ID", "" & Request("p_id") & ""
  27.     commentsSQL.addColumnValuePair "P_Comment_Date", "" & "To_Date('" & Date() & "','MM/DD/YYYY')"
  28.     commentsSQL.addColumnValuePair "P_Comment", "'" & tempCom & "'"
  29.     commentsSQL.addColumnValuePair "E_ID", "" & Session("id") & ""
  30.     commentsSQL.dbInsertEnd
  31.  
  32.  
  33.     Response.write "<center>"
  34.     Response.write "<span class=""saveConfirm"">Comment has been saved."
  35.     Response.write "</span></center><br><br>"
  36.     %>
  37.  
  38.   commentsSQL.dbUpdateBegin "P_Profiles"
  39.    commentsSQL.addColumnValuePair "GSP _Comments", "'" & tempCom & "'"
  40.    commentsSQL.dbUpdateEnd "P_ID=" & Request("p_id") & ""
  41.  
  42.  
  43. <%Case "ProcessComments":
  44.  
  45.   If Request("db_command") = "insert" Then
  46.     tempCom = ""
  47.     tempCom = Request("comments")
  48.     tempCom = "'" & tempCom & "'"
  49.  
  50. ''##  These commented out lines are how the code was originally, before attempting clob 
  51.  ''## commentsSQL.dbUpdateBegin "P_Profiles"
  52.  ''## commentsSQL.addColumnValuePair "GSP _Comments", "'" & tempCom & "'"
  53.   ''## commentsSQL.dbUpdateEnd "P_ID=" & Request("p_id") & ""
  54.  
  55.  
  56.     Set workingRS = daSQL.dbExecute("SELECT GSP_COMMENTS FROM P_PROFILES where P_ID = " & Request("p_id") & " FOR UPDATE OF GSP_COMMENTS")
  57.     value = workingRS("GSP_COMMENTS")
  58.  
  59.    ''## The following statement is what is causing the error.  I have tried it both with and
  60.    ''## without the daSQL.dbExecute
  61.  
  62.    daSQL.dbExecute("dbms_lob.writeappend value, length(tempCom), tempCom")
  63.  
  64.     workingRS.Close
  65.     Set workingRS = Nothing
  66.     Response.write "<center>"
  67.     Response.write "<span class=""saveConfirm"">Comment has been saved."
  68.     Response.write "</span></center><br><br>"
  69.     %>
  70.  
Feb 2 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.