Solved

Insert and Retrieving blob entries using ado and oracle

Posted on 1998-08-31
12
555 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
ID: 1432157
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
ID: 1432158
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
ID: 1432159
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:vbguy
ID: 1432160
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
ID: 1432161
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
ID: 1432162
To insert the new values for Start and EndDate Pub Date

Put this Before the rsWrite.Update

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

Author Comment

by:vbguy
ID: 1432163
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
ID: 1432164
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
ID: 1432165
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
ID: 1432166
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
ID: 1432167
Thanks much for your help!!
0
 
LVL 4

Expert Comment

by:mcix
ID: 1432168
Glad to help...

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

marko_justus@hotmail.com
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

785 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