DB2 Write Image To Blob Field
Dec 29, 2006
I need some help in SSIS Package I am trying to write a byte array to an image (blob) in DB2 destination. I am getting SQL code -290 Invalid Description, if i set the output column to a byte stream. If I set the output column to an Image data type then I get a different error the package will not at that time even process it errors out right away. At least using a byte stream datatype it errors out when it is about to write to the olebd destination. Anybody have success using BLOB fields in SSIS package data flow? Thanks for any help.
View 5 Replies
ADVERTISEMENT
Jun 10, 2004
I'm trying to retrieve an image that I have stored in a SQL Server db, and display it in an .aspx page. It's supposed to retrieve just one image, according to a unique key that's passed. However, when I view the page, nothing appears... just the Internet Explorer missing image placeholder.
I've tried code from several diff tutorials, and can't seem to get it to work.
I'm aware that some people think it's not a good idea to store images in a db, but I have to in this case, since the requirements for this project are that the images are stored in the database. I already know how to store references to file paths of images located in a server folder. I've just never done it this way... storing the whole images in the database. The table has 3 columns, one for the id, "FileType" stores the image type, and the "ImageBinary" is the image itself.
If anyone can shine some light on why my code doesn't display anything, I'd greatly appreciated your help.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Response.Write("Page Loaded") 'This is just for testing to see if the page loads at all
Dim myCommand As New SqlCommand("Select * from UserImages WHERE id=1234", myConnection)
Try
myConnection.Open()
Dim DR As SqlDataReader
DR = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
If DR.Read() Then
Response.ContentType = DR.Item("FileType")
Response.BinaryWrite(DR("ImageBinary"))
Else
Response.Write("no image found")
End If
myConnection.Close()
Response.Write("<br>Image successfully retrieved!")
Catch SQLexc As SqlException
Response.Write("Read Failed : " & SQLexc.ToString())
End Try
End Sub
View 2 Replies
View Related
Oct 9, 2007
Hi,
I have a problem with writing BLOB data into type of 'image' field.
If the data size > ~280 MByte (< 2 GB) the operation failed.
It is appear on SQL Server 2000 and 2005 (MSDE and express also).
Detailes :
column type : image
Server : SQL Server 2005, version - 9.0.3042
technology : OLEDB, from C++ -> CDynamicAccessor
protocol for communication : TCP/IP
Size of the DB : ~3 MByte.
As I see the provider read partially (~267 943 936 byte if all is 280 MB) of data from my ISequentialStream.
Error of OLEDB:
DB_E_ERRORSOCCURED : 0x80040e21
[DBNETLIB][ConnectionWrite(send()).]
General network error.
SQL Server error log entries :
10/05/2007 16:52:53,Server,Unknown,A fatal error occurred while reading the
input stream from the network. The session will be terminated.
10/05/2007 16:52:53,Server,Unknown,Error: 4014<c/> Severity: 20<c/> State: 1.
Entries of SQL profiler :
Error: 4002, Severity: 16, State: 4
The incoming tabular data stream (TDS) protocol stream is incorrect. The stream ended unexpectedly.
I used to set up the bug with contents of a ~350 MB .zip or .mdf file. Everyting is ok when the data <= ~250 MB.
I had ran MS Network Monitor on the server side and not found partially transmitted frames (but I use it on first time).
We try it on another machines, on another LAN with other BLOB (and the bug arised).
I had try 'named pipes' protocol for communication (and the bug arised).
I will be glad if somebody maintain that how can I found the origin of the bug (tools, log options, protokol settings etc.) or
I can present my simple test program (VC 2005 project) and its database (MS SQL 2005).
Best Regards,
Imre
View 4 Replies
View Related
Jan 13, 2008
Hi, All
Can you add a group of images into the aspnet_profile table as a serialized binary blob?
If so how do you retrieve the image from the blob?
View 1 Replies
View Related
Oct 7, 2006
All,
I work with Microsoft SQL Server 2005 on windows XP professional.
I'd like to create stored procdure to add image to my database (jpg file).
I managed to do it using VARCHAR variable in stored procedure
and then using EXEC, but it don't work directly.
My Table definiton:
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Login] [char](10),
[Password] [char](20),
[Avatar] [image] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
My working solution using stored procedure:
ALTER PROCEDURE [dbo].[AddUser]
@Login AS VARCHAR(255),
@Password AS VARCHAR(255),
@AvatarFileLocation AS VARCHAR(255),
@UserId AS INT OUTPUT
AS
BEGIN
SET @Query = 'INSERT INTO USERS ' + CHAR(13)
+ 'SELECT '''+ @Login + ''' AS Login, ' + CHAR(13)
+ '''' + @Password + ''' AS Password,' + CHAR(13)
+ '(SELECT * FROM OPENROWSET(BULK ''' + @AvatarFileLocation + ''', SINGLE_BLOB) AS OBRAZEK)'
EXECUTE (@Query)
SET @UserID = @@IDENTITY
END
I'd like to use statement in the stored procdure:
ALTER PROCEDURE [dbo].[AddUser]
@Login AS VARCHAR(255),
@Password AS VARCHAR(255),
@AvatarFileLocation AS VARCHAR(255),
@UserId AS INT OUTPUT
AS
BEGIN
DECLARE
@Query AS VARCHAR(MAX)
SET @AvatarFileLocation = 'C:hitman1.jpg'
INSERT INTO USERS
SELECT @Login AS Login,
@Password AS Password,
(SELECT * FROM OPENROWSET(BULK @AvatarFileLocation, SINGLE_BLOB) AS OBRAZEK)
SET @UserID = @@IDENTITY
END
It generates error:
Incorrect syntax near '@AvatarFileLocation'.
My question is:
Why it does not work and how to write the stored procedure code to run this code without errors.
Thanks for any reply
View 7 Replies
View Related
Apr 5, 2008
Hi! I tried to save some image data, but it get truncated at 8000 (the table column is defined as Image). I then wrote a converter to try ntext-datatype instead, but it gets truncated at 4000.
Error message:
System.Data.SqlServerCe: @3 : String truncation: max=4000, len=4168
The code uses only ADO entity framework for database access. Is there a way to store binary data larger than 8000 bytes? I am running SQL Compact 3.5 sp 1 BETA.
Henning
View 7 Replies
View Related
Jul 12, 2007
hi all,
i have created a table with image field in it. Now i just want to store a jpeg file in it but not finding any way to do so.
how can i store any image ? what are the steps???????
thanx in advance
View 5 Replies
View Related
Feb 5, 2007
What is BLOB field in MS SQL? How to use insert and access from this field?
View 2 Replies
View Related
Feb 19, 2004
In SQLServer database i have stored a blob using updateblob function,
type of the table column is image.
When i use Selectblob statement to get the blob into a blob variable, i am getting only 32 KB of data. But with ASA i am getting the complete data..
How to get Complete data in SQL Server?
regds.,
ranga
View 1 Replies
View Related
May 27, 2008
Hi All,
i have a table in MS Access with CandidateId and Image column. Image column is in OLE object format. i need to move this to SQL server 2005 with CandidateId column with integer and candidate Image column to Image datatype.
its very udgent, i need any tool to move this to SQL server 2005 or i need a code to move this table from MS Access to SQL server 2005 in C#.
please do the needfull ASAP. waiting for your reply
with regards
View 1 Replies
View Related
Jan 15, 2004
I am trying to read an Informix 9.3 blob field into a TSQL (SQL Server Stored Procedure) and the data type conversion is not supported thru the MS ODBC Driver installed with Win2000/SQL2000. Does anyone know of another method to retrieve this data, or of a vendor that provides an ODBC driver that supports the Informix blob data type. Thanks!
View 1 Replies
View Related
May 6, 2004
I'm using the code below to send files that are in a blob file in my database to the browser client. The code sends the file in chunks in order to increase performance. The file I'm using to test with is 7MB. It works great on Windows XP with any browser. It takes virtually the same amount of time compared to downloading the file directly from the webserver. However, Windows 2000 and Mac OS X both take about 4x the amount of time it takes to download the file on XP machines. Why the performance difference? Is there anything I can do to fix this? I tried downloading the file directly from the webserver instead of getting it out of the database and it takes the same amount of time on all 3 OS. I had the same problem on Windows XP when I wasn't sending the file in chunks, but after using the code below, it started working for XP only.
Dim bufferSize As Integer = 24000
Dim outbyte(bufferSize - 1) As Byte
Dim retval As Long
Dim startIndex As Long = 0
Dim sql As String = "SELECT ..."
Dim cmd As New SqlCommand(sql, conn)
conn.open()
Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
If dr.Read() Then
' Reset the starting byte for a new BLOB.
startIndex = 0
' Read bytes into outbyte() and retain the number of bytes returned.
retval = dr.GetBytes(DocCol, startIndex, outbyte, 0, bufferSize)
Current.Response.Clear()
Current.Response.Buffer = True
Current.Response.ContentType = "application/octet-stream"
Current.Response.AddHeader("Content-Disposition", "attachment; filename=" & myfile" & "." & myextension)
Do While retval = bufferSize
Current.Response.BinaryWrite(outbyte)
Current.Response.Flush()
' Reposition the start index to the end of the last buffer and fill the buffer.
startIndex += bufferSize
retval = dr.GetBytes(DocCol, startIndex, outbyte, 0, bufferSize)
Loop
'Write the remainder of the last chunk
Dim remaining(retval) As Byte
Array.Copy(outbyte, 0, remaining, 0, retval)
Current.Response.BinaryWrite(remaining)
Current.Response.Flush()
Current.Response.Close()
End If
dr.Close()
conn.Close()
View 1 Replies
View Related
Oct 19, 2006
I was wondering if it is possible to read image data from a database in .net compact framework. Since cf does not have image.fromstream(memstream) to work with, I don't know how else to read the image from the database and then place it into a picturebox.
Here is the code I have been trying out:
Dim Img As Image
'
Dim conn As New SqlCeConnection("Data Source = My Documents est2.sdf")
conn.Open()
Dim sql As String = "SELECT * FROM Dater"
Dim cmd As New SqlCeCommand(sql, conn)
Dim reader As SqlCeDataReader = _
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
While reader.Read()
TextBox1.Text = reader.Item("name")
Dim b(reader.GetBytes(1, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
reader.GetBytes(1, 0, b, 0, b.Length)
Dim ms As New System.IO.MemoryStream(b)
Dim bmp As New Bitmap(ms) <-Error: Value does not fall within expected range
Img = bmp
End While
PictureBox2.Image = Img
I get an error ,Value does not fall within expected range.
Does this mean the image was not save correctly in the database?
Thanks for any help
View 1 Replies
View Related
Aug 24, 2006
Hi,
I have a concern about adding a new field to a table with image field - which is huge.
Will there be a problem with some databases, where they have a hard time locating data correctly after such a large field?
Previously this happened to me, and what was advised to put all the big fields at the end of the table.
Thanks.
View 1 Replies
View Related
Oct 11, 2006
DTS is not properly exporting a database between the servers when a table is containing a image fieldReason it gives as ::Error::Transfer Status: Transferring Data: (Table '[dbo].[Here comes the table name used ]') Data truncation occurred in table [dbo].[Here comes the table name used ], column 1. any workaround or solutions would be more appericated
View 11 Replies
View Related
Jul 20, 2005
Hi,Tasks in a MS Project SQL Server database have an image field calledTASK_RTF_NOTES. It contains text in RTF format. We would like to convertthis within a standard SQL statement into plain text.I suspect this is not possible without some sort of bolt-on, or at the veryleast using T-SQL in some way.Any ideas?Thanks,Gareth
View 4 Replies
View Related
Oct 10, 2006
Hi , i have a trigger that start on a record insert . . .This take de ID of the inserted record and start to fill other table in the database.In this triggher i have to update an image field of a table , getting the value from another table .I've casted this value in varbinary , but var binary can take only 8000 byte , and this fileis bigger than 8000 byte . . .How can i proced to solve the problem ???Thanks
View 3 Replies
View Related
Jan 31, 2005
There must be a better way to write this where clause. Can somebody help
if @id is null
begin
(bla bla bla) where parentid is null
end
else
begin
(bla bla bla) where parentid = @id
end
View 9 Replies
View Related
Mar 22, 2008
hello
my friends
i want write Select without write filed name
example
mytable(id,name1,family)
orginal selected is
select id,name1 from mytable
but i want write:
select field[0],field[1] from my table
that field[0] point to id and field[1] point to name1
thanx
M.O.H.I.N
View 3 Replies
View Related
Apr 19, 2004
Hi,
I am storing my upoaded images in sql server. However, if the user does not upload an image, a broken link is shown in the page.
All that shows up in the DB field is <Binary>.
Can we set this field to a default value?
Is it possible to view this binary data.
I would like a default blank gif to be returned if no image is uploaded.
Regards,
JB
View 3 Replies
View Related
Aug 3, 2004
Hi
I have a SQL 2000 table in which pictures are stored as an Image column. I want to display then onto a c# aspx webpage without storing them to disk.
What is the best way to read and display the pictures?
In classic ASP I used to do this:
Response.ContentType = "image/jpeg"
Response.BinaryWrite rs.fields("ThisImage")
but I can't get this to work in c#.
View 2 Replies
View Related
Feb 25, 2005
Hi,
I was wondering if someone can help me with is problem.
I have uploaded word docs to the db which is fine. The problem is viewing. I can view then as word documents but the boss does not want the files opened in word.
Is it possable to retrieve the file from from the db and put into say a textbox or lable.
I can see the letter P using this code
Dim Doc() As Byte = New Byte(Convert.ToInt32(0)) {}
Dim bytesReceived As Long = DBContent.GetBytes(0, 0, Doc, 0, Doc.Length)
Dim encoding As ASCIIEncoding = New ASCIIEncoding
lblTest.Text = encoding.GetString(Doc, 0, Convert.ToInt32(bytesReceived)).ToString
Can any help
Thanks
View 4 Replies
View Related
May 15, 2002
Hi,
does anyone know a way, using native SQL, to store a result of a query in an image field of a certain table.
The case is we have a selfmade replication to communicate with several SQL servers in stores, this replication is over a telephone line. So we collect all the data using SQL statements and store them in a separate table as an image field. This is done know through a Delphi application that streams the resultset to a image field.
Thanks in advance,
View 1 Replies
View Related
Feb 6, 2001
I try with textcopy but I did'n do nothing. Please help me
View 1 Replies
View Related
Nov 21, 2006
I am hoping someone will be able to help me with this.
i have a table of accountholders with the following fields
account_no
account_name
image_path
the image_path field has a defaultvalue images/***.jpg
I am looking for a query code to update the value in the image_path field by replacing the *** value with the value of the
account_no field
View 1 Replies
View Related
Jan 16, 2004
I would like to retrieve and view .jpg pictures that have been stored in a Data Type "image", Length "16", SQL2000 field.
Any ideas on "How to......."
Thanks alot and enjoy
HandyMac
View 3 Replies
View Related
Jul 23, 2005
I am using an image datatype to store multiple filetypes includeing plaintext data. it works except for pdf Data. When I retrieve the the filecouldn't be opened in acrobat reader and the file was 3KB bigger than theoriginal.Any suggestions would be helpful.
View 3 Replies
View Related
Jul 23, 2005
If the data type of field is "varchar",we can use "like" to query if it hassome substring.Such as "where custom.valuevariant like '%Verizon%' ", it will query out allrecords that contains string "Verizon".But how to do when data type of field custom.valuevariant is "image"?Thanks
View 2 Replies
View Related
Jul 20, 2005
I am creating a document management systems using asp. I have beenresearching the different ways of handling the documents such as using thefile system and storing the path in the db, and actually storing thedocument in the db. I like the idea of storing it in the database muchbetter because I can allow users to manage documents themselves (I alreadyhave the code in place to do it if I decide), having a central system withthe ability to add my own document properties by adding fields to the table,security, and backups. I have found that most think it is better to storethe path due to performance issues and the rate the db can grow. I havelooked at our current system in access and we have a total of 4400 documents(of which probably 25% are in the database but don't actually exist anymorein the file system, one hangup about the file system) since 1988. Thiscomes to about 300 documents added each year. The other thing is the issuewith the size of the db. I don't see a whole lot of difference with thisissue because it is going to take up space in your file system too, althoughthe file system may be more efficient at storing them. I would say that 95%of our docs are under 1 mb in size and done in ms word.The last thing is using full-text search capabilities in SQL Server. I needto be able to search the contents of the field.Is there other issues around storing documents in the db to consider besidesthe above?
View 1 Replies
View Related
Jul 20, 2005
I have a very large array of floating point numbers which I am tryingtostore in an image type field. I am using the dataset class todirectly enter the data but am having trouble trying to get this typeinto the image field. I guess the question is how do I get a largefloating point array into an image (byte)field of a database using thedataset class for new row entry.Thanks
View 1 Replies
View Related
Apr 4, 2006
In my simple table in MSDE 2000A, I have an image field. I have enabled 'text in row' using the stored procedure 'sp_tableoption'.
How do I get jpeg images in the table, and how do I read them?
View 3 Replies
View Related
Apr 9, 2008
I have .wav file data stored in an Image field in sql server 2005. Do you know of any sample code to play the sound in a windows forms app (C#)? (This post is just a lame effort to avoid about week of experiments.)
View 1 Replies
View Related
Apr 28, 2006
Hello,While working through my encryption questions from preivous posts, I amfinding that I may have to resort to use triggers to do the encryption(not that this is the only way, but might be the best way for mycircumstances).I would like to create a trigger that will encrypt the field before thewrite is committed.I've found serveral posts about triggers, but nothing that fits what Iwish to do.Upon an insert or update I want to modify the ssn field with this:cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), cast(SSN asnvarchar(11))) as nvarchar(40))so, ssn '123456789' in SSN would become <something encrypted> in SSNThis is the trigger I have so far, but it is generating an error:CREATE TRIGGER F_Student_SSN.eSSNON F_STUDENT_SSNINSERT, UPDATEAS SELECT cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), cast(SSN asnvarchar(11))) as nvarchar(40))TIARob
View 3 Replies
View Related
