Solved

.CreateParameter   recieving a CLOB

Posted on 2006-10-30
18
796 Views
Last Modified: 2008-01-09
I am recieving a CLOB from an oracle query.
How should my .CreateParameter method's parameters look?

This is what I have , but the app seems to be bombing out

.Parameters.Append .CreateParameter("highlight", adVarChar, adParamInput, 57478)
0
Comment
Question by:bertino12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
  • +2
18 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 17835286
I believe that an adVarChar needs a length parameter and that a text value needs delimiters:

.Parameters.Append .CreateParameter("highlight", adVarChar, adParamInput, 5, "57478")

FtB
0
 

Author Comment

by:bertino12
ID: 17835673
I dont think this way works at all for a CLOB. Im trying to find a way to make it work.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 17835701
Well, I don't know about CLOBS, but if you are using an adVarChar, .CreateParameter will require all of those parameters.

A few things to check:

http://forums.oracle.com/forums/thread.jspa?messageID=1116750
http://forums.oracle.com/forums/thread.jspa?messageID=1101332


FtB
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:bertino12
ID: 17837201
I saw those urls early this morning. Unfortunately, they dont help :(
0
 

Expert Comment

by:soconne
ID: 17837209
This guy bertino12 is kind of a hot head.
0
 

Author Comment

by:bertino12
ID: 17837235
?
0
 

Expert Comment

by:soconne
ID: 17837257
Hey man, chill out.  No need to start sending me hateful private messages.  Please respect the rest of us in this forum.
0
 

Expert Comment

by:soconne
ID: 17837316
sorry mr han fwitz, I so wery sorwy.  no happen again evwer
0
 

Author Comment

by:bertino12
ID: 17841465
Still Havent figured it out im calling a stored procedure last and thats whats blowing up my code. The first 3 createParameters have been tested and work.
The 4th CreateParameter is recieving a CLOB and thats what is messing me up.

    sql = "SELECT doc_id, display_name, " &_
           "to_char((SELECT MAX(process_end_time) FROM CONVERT_LOG l WHERE l.doc_id=d.doc_id), 'MM/DD/YYYY HH:Mi:SS PM')  publish_date" &_
        "Markup_First_Hit(d.doc_id, 'dog', '<b><font style=""background:#FFFF99"">', '</B></font>') highlight " &_
        "FROM DOCUMENTS d WHERE published=1"

      Set oCmd = Server.CreateObject("ADODB.COMMAND")
            With oCmd
            .ActiveConnection = conn
            .CommandType = adCmdText
            .CommandText = sql
            .Parameters.Append .CreateParameter("doc_id", adInteger)
            .Parameters.Append .CreateParameter("display_name", adVarChar)
            .Parameters.Append .CreateParameter("publish_date", adVarChar)
            .Parameters.Append .CreateParameter("highlight")
      end with
      
      Set rst = oCmd.Execute
      Set oCmd = Nothing
0
 

Author Comment

by:bertino12
ID: 17843282
I figured it out. I didnt even need a createParameter.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 17854326
I am okay with the refund as long as bertino posts his final solution so that others might reference it.

FtB
0
 

Author Comment

by:bertino12
ID: 17854659
I didnt change anything, but took off the createParameter. The select statement contained a stored procedure, but wasnt a stand alone stored procedure so there was no need for createParameter functions.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17854666
Just post your solution, as your explanation makes no sense.
0
 

Author Comment

by:bertino12
ID: 17857416
How does removing the createParameter not make sense?

   sql = "SELECT doc_id, display_name, " &_
          "to_char((SELECT MAX(process_end_time) FROM CONVERT_LOG l WHERE l.doc_id=d.doc_id), 'MM/DD/YYYY HH:Mi:SS PM')  publish_date" &_
       "Markup_First_Hit(d.doc_id, 'dog', '<b><font style=""background:#FFFF99"">', '</B></font>') highlight " &_
       "FROM DOCUMENTS d WHERE published=1"

     Set oCmd = Server.CreateObject("ADODB.COMMAND")
          With oCmd
          .ActiveConnection = conn
          .CommandType = adCmdText
          .CommandText = sql
     end with
     
     Set rst = oCmd.Execute
     Set oCmd = Nothing
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17861076
>>How does removing the createParameter not make sense?<<
I was not referring to that, but rather this:
"The select statement contained a stored procedure, but wasnt a stand alone stored procedure so there was no need for createParameter functions."

A select statement cannot contain a stored procedure,, perhaps you meant the reverse: the stored procedure contained a select statement.

I have no idea what you meant by "stand alone stored procedure", perhaps you mean/meant one that does not have parameters.

But the bottom line is that your code does not contain any stored procedures.  Period.  Further you do not even need a Command object for such a trivial exercise.

Glad you got it figured out.
0
 
LVL 1

Accepted Solution

by:
kodiakbear earned 0 total points
ID: 17877202
Closed, 125 points refunded.
kb
Experts Exchange Moderator
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question