Solved

Insert and Retrieving blob entries using ado and oracle

Posted on 1998-08-31
12
558 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
Independent Software Vendors: 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: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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
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…

756 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