Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • Last Modified:

Insert and Retrieving blob entries using ado and oracle

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
0
vbguy
Asked:
vbguy
  • 7
  • 5
1 Solution
 
mcixCommented:
Without knowing the full extent of what you are attempting to accomplish, it is difficult to advise you as to whether this would be a prudent approach or not.

I can tell you that if you need to store large amounts of text data, this is a good way to do it.  Unless you need to expose the Text to some other process, such as full-text retrieval.

Basically, you use the GetChunk and AppendChunk methods of the ADO RecordSet Object.

This is an example of how to read and write to BLOB fields using VB and ADO.  

Dim Cn As New ADODB.Connection
Dim rsRead As New ADODB.Recordset
Dim rsWrite As New ADODB.Recordset
Dim strChunk As String
Dim Offset As Long
Dim Totalsize As Long
Dim ChunkSize As Long

Cn.Open "Connection String"

rsRead.CursorType = adOpenStatic
rsRead.Open "SELECT Blob_Info FROM Blob_Database", Cn

rsWrite.CursorType = adOpenKeyset
rsWrite.LockType = adLockBatchOptimistic
rsWrite.Open "SELECT * FROM Write_Blob_To", Cn

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

Do WHILE Offset < Totalsize
    strChunk = rsRead("Blob_Info").GetChunk(ChunkSize)
    Offset = Offset + ChunkSize
    rsWrite("New_Blob_Info").AppendChunk strChunk
Loop
rsWrite.UpdateBatch
rsWrite.Close
rsRead.Close

End Sub


0
 
vbguyAuthor 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

0
 
mcixCommented:
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!


0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
vbguyAuthor 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
0
 
mcixCommented:
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...

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

Put this Before the rsWrite.Update

rsWrite("startpubdate") = SomeStartDate
rsWrite("endpubdate ") = SomeEndDate
0
 
vbguyAuthor 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  
   
 
0
 
mcixCommented:
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?


0
 
vbguyAuthor 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

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


0
 
vbguyAuthor Commented:
Thanks much for your help!!
0
 
mcixCommented:
Glad to help...

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

marko_justus@hotmail.com
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now