Solved

Insert and Retrieving blob entries using ado and oracle

Posted on 1998-08-31
12
549 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
0
Comment
Question by:vbguy
  • 7
  • 5
12 Comments
 
LVL 4

Accepted Solution

by:
mcix earned 200 total points
Comment Utility
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
 

Author Comment

by:vbguy
Comment Utility
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
 
LVL 4

Expert Comment

by:mcix
Comment Utility
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
 

Author Comment

by:vbguy
Comment Utility
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
 
LVL 4

Expert Comment

by:mcix
Comment Utility
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
 
LVL 4

Expert Comment

by:mcix
Comment Utility
To insert the new values for Start and EndDate Pub Date

Put this Before the rsWrite.Update

rsWrite("startpubdate") = SomeStartDate
rsWrite("endpubdate ") = SomeEndDate
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:vbguy
Comment Utility
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
 
LVL 4

Expert Comment

by:mcix
Comment Utility
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
 

Author Comment

by:vbguy
Comment Utility
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
 
LVL 4

Expert Comment

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


0
 

Author Comment

by:vbguy
Comment Utility
Thanks much for your help!!
0
 
LVL 4

Expert Comment

by:mcix
Comment Utility
Glad to help...

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

marko_justus@hotmail.com
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now