Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Reading a BLOB to a text stream from Oracle using ASP.net

Posted on 2006-05-08
12
Medium Priority
?
393 Views
Last Modified: 2008-01-09
Hi I have a database table called email_template that has these following columns email_template_id, email_body, email_subject.  Email_body is a BLOB column.

I have this code in my data access layer to retrieve the email template:

    Public Overrides Function getEmailTemplate(ByVal templateID As Integer) As DataTable
        sql = "select email_body, email_subject from obi.email_template where email_template_id=" & templateID
        Return dao.getDataTable(Me.ConnectionString, sql)
    End Function

Which is called by my business logic layer here:

      Public Shared Function getEmailTemplate(ByVal templateID As Enums.EmailTemplates) As DataTable
            Dim sql As New SQL
            Dim dt As DataTable = sql.getEmailTemplate(templateID)
            Dim body As String
            Dim fileStream As Stream = dt.Rows(0).Item(0)
            Dim filesize As Integer = fileStream.Length
            Dim document(filesize) As Byte
            fileStream.Read(document, 0, filesize)


      End Function

What I am trying to do is pass the body and subject back to my UI layer but I want the body to be a string rather than a BLOB.  I think I'm close but I'm not sure about the following things:
1. How to do the conversion
2. How to drop the string back into the datatable and return it to the UI layer of the app

Any comments gratefully received
0
Comment
Question by:TristanWebb
  • 6
  • 5
12 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 16629776
-----------------------------------------

        Dim da As New OleDbDataAdapter("SELECT imData FROM tblIMAGES where ImId=3", sConnect)
        Dim ds As New DataSet

        da.Fill(ds)

        Dim dr As DataRow

        dr = ds.Tables(0).Rows(0)



        Dim b() As Byte = dr.ItemArray(0)

       dim s as string = system.text.encoding.default.getstring(b)

       

0
 
LVL 18

Accepted Solution

by:
deighton earned 2000 total points
ID: 16629806
so you probably need something like


Dim filesize As Integer = fileStream.Length
Dim document(filesize) As Byte
fileStream.Read(document, 0, filesize)


dim s as string = system.text.encoding.default.getstring(document)




0
 

Author Comment

by:TristanWebb
ID: 16630161
Yes, but how do I get the database value for email_body to be read into the stream?

I'm currently referencing it like this: dt.Rows(0).Item(0)
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.

 
LVL 18

Expert Comment

by:deighton
ID: 16630310
since you're already getting the value into the byte array 'document'

does the following work for you?


Dim sql As New SQL
          Dim dt As DataTable = sql.getEmailTemplate(templateID)
          Dim body As String
          Dim fileStream As Stream = dt.Rows(0).Item(0)
          Dim filesize As Integer = fileStream.Length
          Dim document(filesize) As Byte
          fileStream.Read(document, 0, filesize)

          dim s as string = system.text.encoding.default.getstring(document)
0
 

Author Comment

by:TristanWebb
ID: 16630430
No it falls over here:

Specified cast is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Specified cast is not valid.

Source Error:


Line 100:            Dim dt As DataTable = sql.getEmailTemplate(templateID)
Line 101:            Dim body As String
Line 102:            Dim fileStream As Stream = dt.Rows(0).Item(0)
Line 103:            Dim filesize As Integer = fileStream.Length
Line 104:            Dim document(filesize) As Byte
 

Source File: F:\OBI\Components\BusinessLogicLayer\EmailLog.vb    Line: 102
0
 

Author Comment

by:TristanWebb
ID: 16630541
The blob cell ids empty for this id, should this matter?
0
 
LVL 18

Expert Comment

by:deighton
ID: 16630727
>>>The blob cell ids empty for this id, should this matter?

'Not sure, have you got any sign of their being BLOB data?

'try this anyway, based on something that reads SQL server 2000 images



          Dim dt As DataTable = sql.getEmailTemplate(templateID)

        Dim dr As DataRow

        dr = dt .Rows(0)

 Dim mem As New System.IO.MemoryStream

        Dim b() As Byte = dr.ItemArray(0)

        mem.Write(b, 0, b.Length)


          Dim body As String = system.text.encoding.default.getstring(b)
 
0
 

Author Comment

by:TristanWebb
ID: 16630867
Nope, this came up.

Specified cast is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Specified cast is not valid.

Source Error:


Line 114:            Dim mem As New System.IO.MemoryStream
Line 115:
Line 116:            Dim b() As Byte = dr.ItemArray(0)
Line 117:
Line 118:            mem.Write(b, 0, b.Length)
 

Source File: F:\OBI\Components\BusinessLogicLayer\EmailLog.vb    Line: 116
0
 

Author Comment

by:TristanWebb
ID: 16631807
There is now data in all my BLOB fields
0
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16636961
Okay quick thought before I look into this more

Change
Dim b() As Byte = dr.ItemArray(0)

to be

Dim b() As Byte = CType(dr.ItemArray(0), Byte)
0
 
LVL 18

Expert Comment

by:deighton
ID: 16637554
what happens if you do a quick watch on your BLOB field, in what sort of format is the data presented?
0
 

Author Comment

by:TristanWebb
ID: 16637570
This is actually all sorted now.  I made a bit of a schoolboy error by converting the byte array and declaring a variable to hold it and then later on in my code setting the same variable to reference the value I pulled from the database.

I'll get my coat...

I'll split the points based proportionally on number of posts.  Deighton wins 5-1 (approx. 425-75)
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses
Course of the Month15 days, 21 hours left to enroll

580 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