Solved

converting BLOB to String

Posted on 2004-08-24
13
7,017 Views
Last Modified: 2012-05-05
hey guys, I need a select statement that convert a blob, and bring me back a string

i know for sure, everytime, that the blob is just plain old text. Is there anyway to do that?
0
Comment
Question by:lmpsteelwire
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 1

Expert Comment

by:JanMah
ID: 11886693
Hi

Do you want to convert BLOB to string in oracle itself or in a programming language (VB ..)?
0
 
LVL 2

Author Comment

by:lmpsteelwire
ID: 11886763
Either oracle or VB.net....but if vb.net, im using oracleclient dll
0
 
LVL 1

Expert Comment

by:JanMah
ID: 11886811
OK

Here is one of the VB solutions to convert BLOB to string.

After getting the BLOB from the oracle

Dim sAns As String
Dim iPos As String
   
sAns = StrConv(GetAttachment, vbUnicode) ' GetAttachment is BLOB
iPos = InStr(sAns, Chr(0))
If iPos > 0 Then sAns = Left(sAns, iPos - 1)
   
GetAttachment = sAns

Hope this helps.

JanMah
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 2

Author Comment

by:lmpsteelwire
ID: 11886846
im using vb.net, not vb
0
 
LVL 1

Expert Comment

by:JanMah
ID: 11886871
well i am sure you can change it to .Net, you can't expect all as piece of cake, huh?

JanMah
0
 
LVL 2

Author Comment

by:lmpsteelwire
ID: 11886884
I could convert it...

but I dont know how to bring a blob in from the database to my vb/vb.net
0
 
LVL 1

Expert Comment

by:JanMah
ID: 11886966
OK.. here we go

The SQL will be

Select * from TableName where ID = <ID>

Nothing special here.

After getting it in recordset, use

Dim sAns As String
Dim iPos As String
Dim bGetAttachment as Byte()

bGetAttachment = rs("Attachment").GetChunk(rs("Attachment").ActualSize)

   
sAns = StrConv(bGetAttachment, vbUnicode) ' GetAttachment is BLOB
iPos = InStr(sAns, Chr(0))
If iPos > 0 Then sAns = Left(sAns, iPos - 1)
   
GetAttachment = sAns


janMah
0
 
LVL 2

Author Comment

by:lmpsteelwire
ID: 11887023
StrConv wont take a byte array.
0
 
LVL 1

Expert Comment

by:JanMah
ID: 11887045
well, it will take. Have you checked it?
0
 
LVL 2

Author Comment

by:lmpsteelwire
ID: 11887051
nevermind. I used another question on EE to answer that last part...thanks man.


is inserting a string back into oracle as a blob just the same?
0
 
LVL 1

Expert Comment

by:JanMah
ID: 11887075
yes, if you want to just strore the string just because, it is more than 4000 char long, then I would suggest you to go for clob.

If you want to strore a file (eg. excel/image) to keep the format, then go for blob.

JanMah
0
 
LVL 2

Author Comment

by:lmpsteelwire
ID: 11887176
how exactly can i insert it back into the database? I cant insert a byte()

is there another method i have to do?
0
 
LVL 1

Accepted Solution

by:
JanMah earned 500 total points
ID: 11887305
This is one of the methods:

Pass the blob data as an variant to a procedure.
Here it will be a_aryAttachment

Dim OraSession As OracleInProcServer.OraSessionClass
Dim OraDB As OraDatabase
Dim OraField As OraField
Dim OraSound As OraBlob
Dim OraDyn As OraDynaset
Dim chunksize As Long
Dim conString As String
Dim tempconString() As String
Dim tempStr() As String
Dim server As String
Dim userid As String
Dim password As String
Dim nDsnCounter As Integer
Dim TmpChunkSize As Long



Set OraSession = New OracleInProcServer.OraSessionClass
    Set OraDB = OraSession.OpenDatabase(server, userid & "/" & password, 0&)

    'Opening the Dynaset.
    Set OraDyn = OraDB.CreateDynaset(SQL, ORADB_ENLIST_IN_MTS)
    Set OraSound = OraDyn.Fields("BLOB").Value
    'if the blob field is null for the first time before writing
    ' then an Empty keyword is written into the field.
    If OraSound.IsNull Then
        Set OraField = OraDyn.Fields("BLOB")
        OraDyn.Edit
        OraField.Value = Empty
        OraDyn.Update
    End If


    Set OraSound = OraDyn.Fields("BLOB").Value

    'to write the blob data in a single shot.
    chunksize = UBound(a_aryAttachment) + 1
    'to get the previous size of the Blob field.
    TmpChunkSize = OraSound.Size
    OraSound.offset = 1
    OraSound.PollingAmount = a_lSize
    OraDyn.Edit
   
    OraSound.Write a_aryAttachment, chunksize, ORALOB_ONE_PIECE
    'if the previous size is greater than the present size then Blob field
    'is trimmed to avoid the Unnecessary data.
    If chunksize < TmpChunkSize Then OraSound.Trim chunksize
    OraDyn.Update

    OraDyn.Close
    OraDB.Close

    Set OraSession = Nothing

Hope you are clear. All the best.

JanMah
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

710 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