We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Insert and Retrieving blob entries using ado and oracle

vbguy
vbguy asked
on
Medium Priority
635 Views
Last Modified: 2012-06-27
Hi,
I created a news system that stores stories in a database and creates an html page based on a template system.  
Currently the articles are stored in a varchar2 fields which is limited to 2000 chars. I want to break this barrier, and I was told that blobs were the way to go.
Can someone tell me if this is the right way and and how to go about storing and rerieving the article text in the oracle database.


thanks,
arthur
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
mcix,

basically what I am trying to do is a select * from articletable,
and then format the data I get with html.
I am not sure if blob is the right way to go.

However,  when I do a select from the table I want to store the blob in  a variable.

looking at your code, it seems that I would have to do something like this:

dim storearticle

Do WHILE Offset < Totalsize
    strChunk = rsRead("Blob_Info").GetChunk(ChunkSize)
    Offset = Offset + ChunkSize
    storearticle = storearticle +  strChunk
Loop



Does this look right to you???


TIA
--Arthur

Commented:
Arthur,

That is correct...

Just make sure you set

ChunkSize = 1000                             ' This size is up to you
Totalsize = rsRead("Blob_Info").ActualSize   ' Most do this to get the size of what you want to read

I'm not sure how good the Oracle support is for doing this sort of thing... It may work great or it may blow up!


Author

Commented:
mcix,

when I do an insert to a blob, how do I set a text field to the blob.  The story for the articles will be entered in  a text field that will have unlimited characters. Also how will I insert the rest of the values for that table.  For example, I want to put a startpub date and endpubdate when I enter the blob.

Is that a seperate insert?


I get the following error with the code below:



rsWrite("artcle").AppendChunk (Text1.Text)

Either BOF or EOF is True, or the current record has been deleted; the operation requested by the application requires a current record.



Here is a table i created for testing purposes.
SQL> desc jjcdt.ennblob
 Name                            Null?    Type
 ------------------------------- -------- ----
 ARTCLNO                         NOT NULL NUMBER(3)
 ARTCLE                                   LONG RAW



Here is the vb code:
    Set Cn = New ADODB.Connection
   
       With Cn
         .ConnectionString = Conn
         .CursorLocation = adUseClient
         .Open
       End With

'Cn.Open "Connection String"
'sql = "insert
rsRead.CursorType = adOpenStatic
'rsRead.Open "SELECT Blob_Info FROM Blob_Database", Cn
rsRead.Open "SELECT artcle FROM ennblob", Cn


rsWrite.CursorType = adOpenKeyset
rsWrite.LockType = adLockBatchOptimistic

rsWrite.Open "SELECT * FROM ennblob", Cn


rsWrite("artcle").AppendChunk (Text1.Text)

ChunkSize = 1000
Totalsize = rsRead("artcle").ActualSize

Do While Offset < Totalsize
    strChunk = rsRead("Blob_Info").GetChunk(ChunkSize)
    Offset = Offset + ChunkSize
    storearticle = storearticle +   strChunk
Loop




'rsWrite.UpdateBatch
'rsWrite.Close

MsgBox storearticle
rsRead.Close



thanks

Commented:
The problem is that you do not have a record in your table:
This is a quick and dirty way to do that:

rsWrite.AddNew
Offset = 0 ' Reset offset.
ChunkSize = 1000
TextBoxSize = Len(Text1.Text)

Do While Offset < TextBoxSize
   strChunk = Left(Right(Text1.Text, TextBoxSize - Offset), ChunkSize)
   rsWrite("artcle").AppendChunk strChunk
   Offset = Offset + ChunkSize
Loop
rsWrite.Update

You should note that the standard TextBox Control has a limit of 32K or something like that.  You may want to consider using an RTF (Rich Text Box) - Control if the Text you want to put in this field is larger than 32K...

Commented:
To insert the new values for Start and EndDate Pub Date

Put this Before the rsWrite.Update

rsWrite("startpubdate") = SomeStartDate
rsWrite("endpubdate ") = SomeEndDate

Author

Commented:
hi,
I tried the following and I get no errors, but I do get any inserts either.   I thought it was the commit, but when I try to do a connection.commit I get the error "no transaction active"

Take a look at the code below:

 Dim cnn1 As ADODB.Connection
    Dim rstPubInfo As ADODB.Recordset
    Dim strCnn As String
    Dim strPubID As String
    Dim strPRInfo As String
    Dim lngOffset As Long
    Dim lngLogoSize As Long
    Dim varLogo As Variant
    Dim varChunk As Variant
    Dim rsWrite As New ADODB.Recordset
    Dim rsRead As New ADODB.Recordset
    Const conChunkSize = 100
   
   
    ' Open a connection.
    Set cnn1 = New ADODB.Connection
    strCnn = "UID=jjcdt;PWD=tdcjj;DRIVER={Microsoft ODBC for Oracle};" _
            & "SERVER=TEST;"
    cnn1.Open strCnn
   
       
    rsWrite.CursorType = adOpenKeyset
   rsWrite.LockType = adLockBatchOptimistic
   rsWrite.Open "SELECT * FROM jjcdt.ennblob", cnn1
    cnn1.CommitTrans

   
   rsWrite.AddNew
   Offset = 0 ' Reset offset.
   ChunkSize = 1000
   TextBoxSize = Len(Text1.Text)
   
   rsWrite("artclno") = "2"

   Do While Offset < TextBoxSize
     strChunk = Left(Right(Text1.Text, TextBoxSize - Offset), ChunkSize)
     rsWrite("article").AppendChunk strChunk
     Offset = Offset + ChunkSize
   Loop
   
rsWrite.AddNew
   rsWrite.Update
'   cnn1.CommitTrans
   rsWrite.Close
   
   
   
 thanks much,
arthur  
   
 

Commented:
You need to have the statement

cnn1.BeginTrans

Before you attempt the AddNew and the statement

cnn1.CommitTrans

After the Update...

You should note that if the update fails, it will trip the error handler.  At which point you should have the statement

cnn1.Rollback

depending on the error the you receive.

Is it starting to come together for you now?


Author

Commented:
hi,

I got it to work, but for some reason it turns out that
    Totalsize = rstEmployees("article").ActualSize

does not work for me.

It gives me a number of 2 instead of 3000.

have you had any problems with this?



Dim conn As New ADODB.Connection
conn.Open "DSN=test;UID=jjcdt", "jjcdt", "tdcjj"

   Dim rstEmployees As ADODB.Recordset
    Dim strCnn As String
    Dim strID As String
    Dim strFirstName As String
    Dim strLastName As String
    Dim storearticle As String
    Dim booRecordAdded As Boolean   ' Open a connection.
    Dim artclnum As String
    Dim getnum As String
    Set rstEmployees = New ADODB.Recordset
    rstEmployees.CursorType = adOpenKeyset
    rstEmployees.LockType = adLockOptimistic
    getnum = Trim(InputBox("Enter article ID:"))
    rstEmployees.Open "SELECT * from ennblob where artclno = ' " & getnum & "'", conn
           
    artclnum = rstEmployees("artclno")
       
    ChunkSize = 1000
    Totalsize = rstEmployees("article").ActualSize

    Do While Offset < 3000
        strchunk = rstEmployees("article").GetChunk(ChunkSize)
        MsgBox strchunk
        Offset = Offset + ChunkSize
        storearticle = storearticle + CStr(strchunk)
    Loop

rstEmployees.Close

MsgBox artclnum & " is articlenumber"
Text1.Text = storearticle

Commented:
I have not had that problem with SQL Server, like I said maybe Oracle is a little different with the GetChunk Methods...


Author

Commented:
Thanks much for your help!!

Commented:
Glad to help...

If you have more problems/questions, feel free to E-mail me @

marko_justus@hotmail.com
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.