Extracting Image datatype content with SQL2005?
in [Using MS SQL Server]
|
From: Arne Vajhøj on 31 Jul 2010 21:21 On 31-07-2010 20:50, Joe Cool wrote: > On Jul 31, 8:41 pm, Arne Vajh�j<a...(a)vajhoej.dk> wrote: >> On 31-07-2010 20:39, Joe Cool wrote: >>> On Jul 31, 8:37 pm, Arne Vajh�j<a...(a)vajhoej.dk> wrote: >>>> On 31-07-2010 20:29, Joe Cool wrote: >> >>>>> On Jul 31, 8:02 pm, Arne Vajh�j<a...(a)vajhoej.dk> wrote: >>>>>> On 31-07-2010 19:36, Joe Cool wrote: >> >>>>>>> I am cross posting this request for help in both the C#,NET and the >>>>>>> SQLServer newsgroups in he hope that some reader of one these >>>>>>> newsgroups can offer some help. >> >>>>>>> I am well aware that the image datatype (as well as others) are being >>>>>>> phased out in some future version of SQL Server. For that reason, in >>>>>>> all of my personal C#.NET projects that store images use the >>>>>>> varbinary(max) datatype. >> >>>>>>> I am in the process of converting a VB.NET project that I use for >>>>>>> consulting work with a former (and hopefully soon to be repeat) >>>>>>> employer tp C#.NET. When originally designed, their software was based >>>>>>> on SQL7. Currently their software supports SQL2005. One key table in >>>>>>> their database is a table of images and (not sure if it still does) >>>>>>> but legacy databases utilize the image datatype to store images. With >>>>>>> SQL7 I used a SQL command line utility called TEXTCOPY to extract the >>>>>>> binary content of image columns to a JPG file. >> >>>>>>> This utility needs to be able to extract these images to external >>>>>>> files even for legacy databases that may still use the image datatype. >>>>>>> But I find that the technique I use to extract images from a >>>>>>> varbinary(max) column doesn't work for image datatypes. And I see that >>>>>>> SQL2005, while it still supports image datatype, the TEXTCOPY utility >>>>>>> is nowhere to be found. >> >>>>>>> Any ideas on how to extract image data types from a SQL2005 database >>>>>>> with C#.NET? >> >>>>>> Have you tried the obvious: SqlConnection, SqlCommand, SELECT, >>>>>> ExecuteReader, SqlDataReader og l�se kolonnen som byte[] ? >> >>>>> Here is what I am using that works with varbinary(max) datatype >>>>> columns. >> >>>>> SqlCommand cmd; >>>>> SqlDataReader rdr; >>>>> DataTable dataTable; >> >>>>> cmd = new SqlCommand("select imagecolumn, linkcolumn from images", >>>>> cn)' >>>>> rdr = cmd.ExecuteReader(); >>>>> dataTable.Load(rdr); >> >>>> Drop the data table and try: >> >>>> while(rdr.Read()) >>>> { >>>> byte[] image = (byte[])rdr[0]; >>>> string link = (string)rdr[1]; >>>> // process image and link >> >>>> } >> >>> Umm, so are you saying that while the datatable works with a >>> varbinary(max) datatype, for an image datatype I should drop the >>> datatable and just use a data reader? >> >> I don't know if the data table is the problem. >> >> I know that the reader will work. >> >> And according to your description, then you don't need >> the data table. >> >> So it is worth a try. > > The only reason I was trying to use a data table is because I am using > a support class library (that I also wrote) that returns data using > just a datatable. I suppose I can enhance it to also support returning > data with a data reader. What is the problem when you try getting the image with the current code? Arne
From: Joe Cool on 31 Jul 2010 21:41 On Jul 31, 9:21 pm, Arne Vajhøj <a...(a)vajhoej.dk> wrote: > On 31-07-2010 20:50, Joe Cool wrote: > > > > > > > On Jul 31, 8:41 pm, Arne Vajhøj<a...(a)vajhoej.dk> wrote: > >> On 31-07-2010 20:39, Joe Cool wrote: > >>> On Jul 31, 8:37 pm, Arne Vajhøj<a...(a)vajhoej.dk> wrote: > >>>> On 31-07-2010 20:29, Joe Cool wrote: > > >>>>> On Jul 31, 8:02 pm, Arne Vajhøj<a...(a)vajhoej.dk> wrote: > >>>>>> On 31-07-2010 19:36, Joe Cool wrote: > > >>>>>>> I am cross posting this request for help in both the C#,NET and the > >>>>>>> SQLServer newsgroups in he hope that some reader of one these > >>>>>>> newsgroups can offer some help. > > >>>>>>> I am well aware that the image datatype (as well as others) are being > >>>>>>> phased out in some future version of SQL Server. For that reason, in > >>>>>>> all of my personal C#.NET projects that store images use the > >>>>>>> varbinary(max) datatype. > > >>>>>>> I am in the process of converting a VB.NET project that I use for > >>>>>>> consulting work with a former (and hopefully soon to be repeat) > >>>>>>> employer tp C#.NET. When originally designed, their software was based > >>>>>>> on SQL7. Currently their software supports SQL2005. One key table in > >>>>>>> their database is a table of images and (not sure if it still does) > >>>>>>> but legacy databases utilize the image datatype to store images. With > >>>>>>> SQL7 I used a SQL command line utility called TEXTCOPY to extract the > >>>>>>> binary content of image columns to a JPG file. > > >>>>>>> This utility needs to be able to extract these images to external > >>>>>>> files even for legacy databases that may still use the image datatype. > >>>>>>> But I find that the technique I use to extract images from a > >>>>>>> varbinary(max) column doesn't work for image datatypes. And I see that > >>>>>>> SQL2005, while it still supports image datatype, the TEXTCOPY utility > >>>>>>> is nowhere to be found. > > >>>>>>> Any ideas on how to extract image data types from a SQL2005 database > >>>>>>> with C#.NET? > > >>>>>> Have you tried the obvious: SqlConnection, SqlCommand, SELECT, > >>>>>> ExecuteReader, SqlDataReader og læse kolonnen som byte[] ? > > >>>>> Here is what I am using that works with varbinary(max) datatype > >>>>> columns. > > >>>>> SqlCommand cmd; > >>>>> SqlDataReader rdr; > >>>>> DataTable dataTable; > > >>>>> cmd = new SqlCommand("select imagecolumn, linkcolumn from images", > >>>>> cn)' > >>>>> rdr = cmd.ExecuteReader(); > >>>>> dataTable.Load(rdr); > > >>>> Drop the data table and try: > > >>>> while(rdr.Read()) > >>>> { > >>>> byte[] image = (byte[])rdr[0]; > >>>> string link = (string)rdr[1]; > >>>> // process image and link > > >>>> } > > >>> Umm, so are you saying that while the datatable works with a > >>> varbinary(max) datatype, for an image datatype I should drop the > >>> datatable and just use a data reader? > > >> I don't know if the data table is the problem. > > >> I know that the reader will work. > > >> And according to your description, then you don't need > >> the data table. > > >> So it is worth a try. > > > The only reason I was trying to use a data table is because I am using > > a support class library (that I also wrote) that returns data using > > just a datatable. I suppose I can enhance it to also support returning > > data with a data reader. > > What is the problem when you try getting the image with the > current code? The extracted files are smaller than the JPGs extracted by the VB.NET version and are not valid JPG files (their content is not the same).
From: Arne Vajhøj on 31 Jul 2010 21:46 On 31-07-2010 21:41, Joe Cool wrote: > On Jul 31, 9:21 pm, Arne Vajh�j<a...(a)vajhoej.dk> wrote: >> On 31-07-2010 20:50, Joe Cool wrote: >>> On Jul 31, 8:41 pm, Arne Vajh�j<a...(a)vajhoej.dk> wrote: >>>> On 31-07-2010 20:39, Joe Cool wrote: >>>>> On Jul 31, 8:37 pm, Arne Vajh�j<a...(a)vajhoej.dk> wrote: >>>>>> On 31-07-2010 20:29, Joe Cool wrote: >> >>>>>>> On Jul 31, 8:02 pm, Arne Vajh�j<a...(a)vajhoej.dk> wrote: >>>>>>>> On 31-07-2010 19:36, Joe Cool wrote: >> >>>>>>>>> I am cross posting this request for help in both the C#,NET and the >>>>>>>>> SQLServer newsgroups in he hope that some reader of one these >>>>>>>>> newsgroups can offer some help. >> >>>>>>>>> I am well aware that the image datatype (as well as others) are being >>>>>>>>> phased out in some future version of SQL Server. For that reason, in >>>>>>>>> all of my personal C#.NET projects that store images use the >>>>>>>>> varbinary(max) datatype. >> >>>>>>>>> I am in the process of converting a VB.NET project that I use for >>>>>>>>> consulting work with a former (and hopefully soon to be repeat) >>>>>>>>> employer tp C#.NET. When originally designed, their software was based >>>>>>>>> on SQL7. Currently their software supports SQL2005. One key table in >>>>>>>>> their database is a table of images and (not sure if it still does) >>>>>>>>> but legacy databases utilize the image datatype to store images. With >>>>>>>>> SQL7 I used a SQL command line utility called TEXTCOPY to extract the >>>>>>>>> binary content of image columns to a JPG file. >> >>>>>>>>> This utility needs to be able to extract these images to external >>>>>>>>> files even for legacy databases that may still use the image datatype. >>>>>>>>> But I find that the technique I use to extract images from a >>>>>>>>> varbinary(max) column doesn't work for image datatypes. And I see that >>>>>>>>> SQL2005, while it still supports image datatype, the TEXTCOPY utility >>>>>>>>> is nowhere to be found. >> >>>>>>>>> Any ideas on how to extract image data types from a SQL2005 database >>>>>>>>> with C#.NET? >> >>>>>>>> Have you tried the obvious: SqlConnection, SqlCommand, SELECT, >>>>>>>> ExecuteReader, SqlDataReader og l�se kolonnen som byte[] ? >> >>>>>>> Here is what I am using that works with varbinary(max) datatype >>>>>>> columns. >> >>>>>>> SqlCommand cmd; >>>>>>> SqlDataReader rdr; >>>>>>> DataTable dataTable; >> >>>>>>> cmd = new SqlCommand("select imagecolumn, linkcolumn from images", >>>>>>> cn)' >>>>>>> rdr = cmd.ExecuteReader(); >>>>>>> dataTable.Load(rdr); >> >>>>>> Drop the data table and try: >> >>>>>> while(rdr.Read()) >>>>>> { >>>>>> byte[] image = (byte[])rdr[0]; >>>>>> string link = (string)rdr[1]; >>>>>> // process image and link >> >>>>>> } >> >>>>> Umm, so are you saying that while the datatable works with a >>>>> varbinary(max) datatype, for an image datatype I should drop the >>>>> datatable and just use a data reader? >> >>>> I don't know if the data table is the problem. >> >>>> I know that the reader will work. >> >>>> And according to your description, then you don't need >>>> the data table. >> >>>> So it is worth a try. >> >>> The only reason I was trying to use a data table is because I am using >>> a support class library (that I also wrote) that returns data using >>> just a datatable. I suppose I can enhance it to also support returning >>> data with a data reader. >> >> What is the problem when you try getting the image with the >> current code? > > The extracted files are smaller than the JPGs extracted by the VB.NET > version and are not valid JPG files (their content is not the same). Is the size in the DB correct? If yes - is the size in the data table correct? Arne
From: Joe Cool on 31 Jul 2010 21:52 On Jul 31, 9:46 pm, Arne Vajhøj <a...(a)vajhoej.dk> wrote: > On 31-07-2010 21:41, Joe Cool wrote: > > > > > > > On Jul 31, 9:21 pm, Arne Vajhøj<a...(a)vajhoej.dk> wrote: > >> On 31-07-2010 20:50, Joe Cool wrote: > >>> On Jul 31, 8:41 pm, Arne Vajhøj<a...(a)vajhoej.dk> wrote: > >>>> On 31-07-2010 20:39, Joe Cool wrote: > >>>>> On Jul 31, 8:37 pm, Arne Vajhøj<a...(a)vajhoej.dk> wrote: > >>>>>> On 31-07-2010 20:29, Joe Cool wrote: > > >>>>>>> On Jul 31, 8:02 pm, Arne Vajhøj<a...(a)vajhoej.dk> wrote: > >>>>>>>> On 31-07-2010 19:36, Joe Cool wrote: > > >>>>>>>>> I am cross posting this request for help in both the C#,NET and the > >>>>>>>>> SQLServer newsgroups in he hope that some reader of one these > >>>>>>>>> newsgroups can offer some help. > > >>>>>>>>> I am well aware that the image datatype (as well as others) are being > >>>>>>>>> phased out in some future version of SQL Server. For that reason, in > >>>>>>>>> all of my personal C#.NET projects that store images use the > >>>>>>>>> varbinary(max) datatype. > > >>>>>>>>> I am in the process of converting a VB.NET project that I use for > >>>>>>>>> consulting work with a former (and hopefully soon to be repeat) > >>>>>>>>> employer tp C#.NET. When originally designed, their software was based > >>>>>>>>> on SQL7. Currently their software supports SQL2005. One key table in > >>>>>>>>> their database is a table of images and (not sure if it still does) > >>>>>>>>> but legacy databases utilize the image datatype to store images.. With > >>>>>>>>> SQL7 I used a SQL command line utility called TEXTCOPY to extract the > >>>>>>>>> binary content of image columns to a JPG file. > > >>>>>>>>> This utility needs to be able to extract these images to external > >>>>>>>>> files even for legacy databases that may still use the image datatype. > >>>>>>>>> But I find that the technique I use to extract images from a > >>>>>>>>> varbinary(max) column doesn't work for image datatypes. And I see that > >>>>>>>>> SQL2005, while it still supports image datatype, the TEXTCOPY utility > >>>>>>>>> is nowhere to be found. > > >>>>>>>>> Any ideas on how to extract image data types from a SQL2005 database > >>>>>>>>> with C#.NET? > > >>>>>>>> Have you tried the obvious: SqlConnection, SqlCommand, SELECT, > >>>>>>>> ExecuteReader, SqlDataReader og læse kolonnen som byte[] ? > > >>>>>>> Here is what I am using that works with varbinary(max) datatype > >>>>>>> columns. > > >>>>>>> SqlCommand cmd; > >>>>>>> SqlDataReader rdr; > >>>>>>> DataTable dataTable; > > >>>>>>> cmd = new SqlCommand("select imagecolumn, linkcolumn from images", > >>>>>>> cn)' > >>>>>>> rdr = cmd.ExecuteReader(); > >>>>>>> dataTable.Load(rdr); > > >>>>>> Drop the data table and try: > > >>>>>> while(rdr.Read()) > >>>>>> { > >>>>>> byte[] image = (byte[])rdr[0]; > >>>>>> string link = (string)rdr[1]; > >>>>>> // process image and link > > >>>>>> } > > >>>>> Umm, so are you saying that while the datatable works with a > >>>>> varbinary(max) datatype, for an image datatype I should drop the > >>>>> datatable and just use a data reader? > > >>>> I don't know if the data table is the problem. > > >>>> I know that the reader will work. > > >>>> And according to your description, then you don't need > >>>> the data table. > > >>>> So it is worth a try. > > >>> The only reason I was trying to use a data table is because I am using > >>> a support class library (that I also wrote) that returns data using > >>> just a datatable. I suppose I can enhance it to also support returning > >>> data with a data reader. > > >> What is the problem when you try getting the image with the > >> current code? > > > The extracted files are smaller than the JPGs extracted by the VB.NET > > version and are not valid JPG files (their content is not the same). > > Is the size in the DB correct? > > If yes - is the size in the data table correct? In the FileStream object's Write mthod, I am using ((byte[])dataTable.Rows[i][0]).Length as the number of bytes to write. I assume thelength is the DB is correct since I am using the same DB to test the C#.NET program to test with that I originally used the VB.NET programs to originally extract the JPs with.
From: Arne Vajhøj on 31 Jul 2010 22:04 On 31-07-2010 21:52, Joe Cool wrote: > On Jul 31, 9:46 pm, Arne Vajh�j<a...(a)vajhoej.dk> wrote: >> On 31-07-2010 21:41, Joe Cool wrote: >> >> >> >> >> >>> On Jul 31, 9:21 pm, Arne Vajh�j<a...(a)vajhoej.dk> wrote: >>>> On 31-07-2010 20:50, Joe Cool wrote: >>>>> On Jul 31, 8:41 pm, Arne Vajh�j<a...(a)vajhoej.dk> wrote: >>>>>> On 31-07-2010 20:39, Joe Cool wrote: >>>>>>> On Jul 31, 8:37 pm, Arne Vajh�j<a...(a)vajhoej.dk> wrote: >>>>>>>> On 31-07-2010 20:29, Joe Cool wrote: >> >>>>>>>>> On Jul 31, 8:02 pm, Arne Vajh�j<a...(a)vajhoej.dk> wrote: >>>>>>>>>> On 31-07-2010 19:36, Joe Cool wrote: >> >>>>>>>>>>> I am cross posting this request for help in both the C#,NET and the >>>>>>>>>>> SQLServer newsgroups in he hope that some reader of one these >>>>>>>>>>> newsgroups can offer some help. >> >>>>>>>>>>> I am well aware that the image datatype (as well as others) are being >>>>>>>>>>> phased out in some future version of SQL Server. For that reason, in >>>>>>>>>>> all of my personal C#.NET projects that store images use the >>>>>>>>>>> varbinary(max) datatype. >> >>>>>>>>>>> I am in the process of converting a VB.NET project that I use for >>>>>>>>>>> consulting work with a former (and hopefully soon to be repeat) >>>>>>>>>>> employer tp C#.NET. When originally designed, their software was based >>>>>>>>>>> on SQL7. Currently their software supports SQL2005. One key table in >>>>>>>>>>> their database is a table of images and (not sure if it still does) >>>>>>>>>>> but legacy databases utilize the image datatype to store images. With >>>>>>>>>>> SQL7 I used a SQL command line utility called TEXTCOPY to extract the >>>>>>>>>>> binary content of image columns to a JPG file. >> >>>>>>>>>>> This utility needs to be able to extract these images to external >>>>>>>>>>> files even for legacy databases that may still use the image datatype. >>>>>>>>>>> But I find that the technique I use to extract images from a >>>>>>>>>>> varbinary(max) column doesn't work for image datatypes. And I see that >>>>>>>>>>> SQL2005, while it still supports image datatype, the TEXTCOPY utility >>>>>>>>>>> is nowhere to be found. >> >>>>>>>>>>> Any ideas on how to extract image data types from a SQL2005 database >>>>>>>>>>> with C#.NET? >> >>>>>>>>>> Have you tried the obvious: SqlConnection, SqlCommand, SELECT, >>>>>>>>>> ExecuteReader, SqlDataReader og l�se kolonnen som byte[] ? >> >>>>>>>>> Here is what I am using that works with varbinary(max) datatype >>>>>>>>> columns. >> >>>>>>>>> SqlCommand cmd; >>>>>>>>> SqlDataReader rdr; >>>>>>>>> DataTable dataTable; >> >>>>>>>>> cmd = new SqlCommand("select imagecolumn, linkcolumn from images", >>>>>>>>> cn)' >>>>>>>>> rdr = cmd.ExecuteReader(); >>>>>>>>> dataTable.Load(rdr); >> >>>>>>>> Drop the data table and try: >> >>>>>>>> while(rdr.Read()) >>>>>>>> { >>>>>>>> byte[] image = (byte[])rdr[0]; >>>>>>>> string link = (string)rdr[1]; >>>>>>>> // process image and link >> >>>>>>>> } >> >>>>>>> Umm, so are you saying that while the datatable works with a >>>>>>> varbinary(max) datatype, for an image datatype I should drop the >>>>>>> datatable and just use a data reader? >> >>>>>> I don't know if the data table is the problem. >> >>>>>> I know that the reader will work. >> >>>>>> And according to your description, then you don't need >>>>>> the data table. >> >>>>>> So it is worth a try. >> >>>>> The only reason I was trying to use a data table is because I am using >>>>> a support class library (that I also wrote) that returns data using >>>>> just a datatable. I suppose I can enhance it to also support returning >>>>> data with a data reader. >> >>>> What is the problem when you try getting the image with the >>>> current code? >> >>> The extracted files are smaller than the JPGs extracted by the VB.NET >>> version and are not valid JPG files (their content is not the same). >> >> Is the size in the DB correct? >> >> If yes - is the size in the data table correct? > > In the FileStream object's Write mthod, I am using > ((byte[])dataTable.Rows[i][0]).Length as the number of bytes to write. > > I assume thelength is the DB is correct since I am using the same DB > to test the C#.NET program to test with that I originally used the > VB.NET programs to originally extract the JPs with. How does the VB.NET and C# code differ? Arne
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: Simple (?) SQL problem Next: SQL server messed up after installing Foxpro 9 |