reading / writing binary data to db

I'm trying to read a byte array of an image datatype from sql server, and then to put this in another field in the database. I get a byte array, but somehow the image doesn't get into the db well with the sql parameters. Does anyone have an idea how to tackle this problem?

Thanks a lot, Hugohttp://www.dotnetspider.com/Technology/QA/ViewQuestion.aspx?QuestionId=117

I think that link above has the answers to your question. Make sure you're using Image type, and passing in the byte array, and that you don't have anything blocking your connection (firewall or switch that's set to cap communication sizes).

Otherwise, I don't know what to tell ya. Need to see code.|||Thanks for the link, it helps me somehow although not enough, however...

Basically, what I'm trying to do is to "pump" binary data from one field in one table to another field in another table (both are of the image data type, length 16).

What I have now, is a byte array read from the original field, and set it as a value of an SqlParameter to insert it into the new field. This is the code fragment:

Dim SQLSel As String = "SELECT imgImage FROM tblProducts WHERE autoProductID=" & CategoryID
Dim reader As SqlClient.SqlDataReader
reader = _data.sqlRetrieveDataReader(SQLSel) 'retrieve a data reader via DAL
reader.Read()
'set the binary data of the original image in the byte array
Dim b() As Byte = CType(reader.GetValue(0), Byte())
Dim stream As System.IO.MemoryStream = New System.IO.MemoryStream(b, True)
stream.Write(b, 0, b.Length)
'insert the byte array into the new parameter
sqlComInsert.Parameters.Item("@.imgImage").Value = b

No errors are trapped when this code is executed. However, only red crossed (i.e. bad images) are displayed in the browser. The following code is used to display images from the database (which has proven to work on the original images/binary data):

 Dim ProductID As String = Request.QueryString("ProductID")
 Dim conn As New SqlClient.SqlConnection(CType(configurationAppSettings.GetValue("sqlConn.ConnectionString", GetType(System.String)), String))
 Dim SQLSel As String = "SELECT imgImage FROM tblProducts WHERE autoProductID=" & CategoryID
 Dim com As New SqlClient.SqlCommand(SQLSel, conn)
 conn.Open()
 Try
 Response.ContentType = "image/gif"
 Response.BinaryWrite(com.ExecuteScalar)
 Response.End()
 Finally
 conn.Close()
 End Try

I really have no clue what causes the movement of the images/binary data via asp.net to fail... if anyone knows how to tackle this problem, please help. Thanks a lot in advance, Hugo

readinf blob data in sql trigger

hi,
i am using sql sever 2005 , i want to read binary data and data of
computed coloumn using trigger.
my table is below:
table column
--
1) document data -- type (image)
2) userId -- type(varchar computed column)
i have to write delete trigger which can get image data and userid in
to some variable or cursor.
please can anyone help me in this regard.
sathya narayanan.v
narayanan@.gsdindia.comThe text, ntext, and image values in the inserted and deleted tables cannot
be accessed within a trigger. This is by design.
Anith|||Hi
If you are using SQL Server 2005, replace the image datatype with
varbinary(max) as the image datatype will be removed in future releases of
SQL Server.
Post SQL Server 2005 CTP/Beta questions to:
http://communities.microsoft.com/ne...p=sqlserver2005
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"sathya" wrote:

> hi,
> i am using sql sever 2005 , i want to read binary data and data of
> computed coloumn using trigger.
> my table is below:
> table column
> --
> 1) document data -- type (image)
> 2) userId -- type(varchar computed column)
>
> i have to write delete trigger which can get image data and userid in
> to some variable or cursor.
> please can anyone help me in this regard.
>
> sathya narayanan.v
> narayanan@.gsdindia.com
>|||Do you want to manipulate this data in the trigger or would you like the
trigger to return it? Since the latter is impossible, please elaborate on
what you actually want to do.
The sole purpose of the image datataype is to enable storage of large binary
data in SQL. Manipulating it is IMHO a matter for the application layer,
rather than the data/storage layer. But please, prove me wrong.
ML|||Well, not quite. You can access them in an INSTEAD OF trigger with the
compatibility level set to 80 (or higher - someday).
ML|||hi,
thank you for reply..
actually i want to create trigger on delete event which gets image and
computed coloumn data from one table to another table. Is it possible
to get image data (blob ) and computed data to read from sql in delete
trigger event.
i have posted my databse coloumns below:
Id Uniqueidentifier
SiteId Uniqueidentifier
OldDirName varchar(256)
OldLeafName varchar(128)
WebId Uniqueidentifier
DeletedListId Uniqueidentifier
DeletedDocLibRowId int
Type tinyint
Size int
MetaInfoSize int
Version int
UIVersion int
Dirty bit
CacheParseId Uniqueidentifier
DocFlags tinyint
ThicketFlag bit
CharSet int
TimeCreated datetime
TimeLastModified datetime
NextToLastTimeModified datetime
MetaInfoTimeLastModified datetime
TimeLastWritten datetime
SetupPath nvarchar(255)
CheckoutUserId int
CheckoutDate datetime
CheckoutExpires datetime
CheckoutSize int
VersionCreatedSinceSTCheckout bit
LTCheckoutUserId int
VirusVendorID int
VirusStatus int
VirusInfo nvarchar(255)
MetaInfo binary(8000)
Content binary(8000)
CheckoutContent binary(8000)
Extension varchar(128)
I have to read all coloumn values when in trigger for deleted row and
keep data in another coloumn.
I faced problem while reading image and computed column data.
please help me.
sathya narayanan v
narayanan@.gsdindia.com|||hi,
please tell me how you can do this...
sathya narayanan|||>> i have to write delete trigger which can get image data and userid in to
You will have to elaborate on what you are trying to do. As I said, you
cannot use image data within a trigger. You cannot declare a variable of
image type either.
If you are stuck with this table, you might have to use a different
approach, perhaps using a stored procedure or temp table with column of
image type etc.
Anith|||You should be using something like this in your trigger:
insert destination_table
(
..
)
select ...
from inserted
for the insert/update trigger
and
insert destination_table
(
..
)
select ...
from deleted
for the delete trigger.
You cannot store image values in triggers, since text, ntext and image
datatypes cannot be used for variables.
And if you had used them, such a trigger wouldn't have supported multiple
inserts/updates/deletes.
MLsql

Read large binary data from Sql Server 2005

Hi

I've followed a tutorial on how to write and read varbinary(max) data to and from a database. But when i try to read the data i get the error that the data would be truncated, but only when the varbinary(max) is greater then 8kB. I've used a system stored procedure (sp_tableoption) to set the table that holds the data to store data outside rows. To select the data i'm using a stored procedure:

SELECT imageData , MIMETypeFROMPicturesWHERE(imageTitle = @.imageTitle)

And then using an .aspx page to Response.Write the data:

Using conn As New sql.SqlConnection
conn.ConnectionString = ConfigurationManager.ConnectionStrings("myConnectionString").ToString

Dim getLogoCommand As New sql.SqlCommand
getLogoCommand.CommandType = Data.CommandType.StoredProcedure
getLogoCommand.CommandText = "GetPicture"
getLogoCommand.Connection = conn

Dim imageTitleParameter As New sql.SqlParameter("@.imageTitle", Data.SqlDbType.NVarChar, 200)
imageTitleParameter.Value = Request("imageTitle")
imageTitleParameter.Direction = Data.ParameterDirection.Input

getLogoCommand.Parameters.Add(imageTitleParameter)

conn.Open()

Using logoReader As sql.SqlDataReader = getLogoCommand.ExecuteReader
logoReader.Read()
If logoReader.HasRows = True Then
Response.Clear()
Response.ContentType = logoReader("MIMEtype").ToString()
Response.BinaryWrite(logoReader("imageData"))
End If

End Using

conn.Close()

End Using

Can anyone please help me with this?!

Even though you are using SQL Server 2005... the "compatibility mode" is set to "80" (meaning 8.0... meaning SQL Server 2000).

So, until you run "EXEC sp_dbcmptlevel 90"... your MAX will == 8000 :) (you'll only have to run this once btw)

|||

Thanks for your reply! I can see <binary data> when I'm just watching the table in SQL Server Studio Manager but still not through a stored procedure. I, however, can get the data with:

SELECT * FROM Pictures WHERE (imageTitle = @.imagetitle ) as a "text" command on the .aspx page.

So like that i'm able to actually show anything bigger then 8 kB on the page, but now I can't do anything with the stored procedure like, when there is no data for that imageTitle I can't make it return another value.

When I use

---

conn.Open()

Using pictureReader As sql.SqlDataReader = getLogoCommand.ExecuteReader
pictureReader.Read()
If pictureReader.HasRows = True Then
Response.Clear()
Response.ContentType = pictureReader("MIMEtype").ToString()
Response.BinaryWrite(pictureReader("imageData"))
End If

End Using

---

The pictureReader.HasRows = True even when there is no binary data. Is there anyway to get the page not to do anything when there is no data in a row?

Thanks

Read Image Data From SQL Server

Here is my task I am storing pdf's in sql server. I would like to retrieve the binary data from sql server and write the pdf content into an existing aspx page to the appropriate pageview section. What is the best way to handle this. The code works below but it loads a new browser with the content. I need it to appear in it's tabbed section in the original aspx file. Any assistance you can give me would be greatly appreciated.

Thanks Jerry

oSQLConn.Open()

Dim myreaderAs SqlDataReader

myreader = myCommand.ExecuteReader

Response.Expires = 0

Response.Buffer =True

Response.Clear()

DoWhile (myreader.Read())

Response.ContentType = ("application/pdf")

Response.BinaryWrite(myreader.Item("img_content"))

Loop

Hi,

From your description, it seems that you are going to retrieve the PDF data in tabbed section of the current aspx file, right?

Well, in your scenario, I noticed that you are going to reset the content type to "application/pdf", but the aspx page's content type is "text/html", they cannot be displayed at the same page.

I guess you have comment the "Response.contenttype.." line, right? And you are using Resposne. Binary Write to write the specified information to the current HTTP output. I suggest that you can have a try to wrap all these codes into a user control, use this control in your tabbed section of your current aspx page and try to see if it can work.

Thanks.

|||

Thanks for your response. Maybe this description will help more. I havea tab control on an aspx form. Each tab needs to display the picture, pdf etc etc associated with that particular tab for a specific user. The binary data is stored in a sql server 2005 image field. What would be the best way to achieve the desired result. What would be a good way to handle this? I should also add there may be more then one pdf/image for a particular section.

Thanks

Jerry

|||

Hi,

Well, you can add a Image control in your Tab control container, and create a page which works for displaying the image data from database.

The code snippet on that page to show the image data: (ShowImg.aspx)

// myRead is a DataReader object.
Byte[] Buffer = (Byte[])myRead[0];

//Output
this.Response.Clear();
this.Response.BinaryWrite(Buffer);
this.Response.End();

And then, assign the ImageUrl property of the Image control.

this.Image1.ImageUrl = "showimg.aspx"

Of course, if you want to show different pictures based on the parameters like following:

this.Image1.ImageUrl = "showimg.aspx?imageid=123"

Then, you can receive the parameter on ShowImg.aspx, make a query with that parameter against the database and return the corresponding image data.

Thanks.