Image field

Dear friends,
After inserting a word document in an image field, is it possible to
retrieve the same file and store it to some of the windows folder:
1. through SQL Server Stored Procedure?
2. though any other means like .Net code etc..
Thnaks in anticipation
Regards
Sathian> After inserting a word document in an image field, is it possible to
> retrieve the same file and store it to some of the windows folder:
> 1. through SQL Server Stored Procedure?
No idea, but would you want to? T-SQL was not designed for this.

> 2. though any other means like .Net code etc..
Absolutely. It's a piece of cake. Remember that an image field is nothing mo
re
than an array of bytes. All you do is creata a FileStream and set it's conte
nts
to that of the byte array from the image field.
Thomas|||Hello thomas,
Thank you very much.
Any sample piece of code wiich does so..?
I tried in net.. so far I wasn't sucessful..
Regards
Sathian
"Thomas" <thomas@.newsgroup.nospam> wrote in message
news:eoguWJXQFHA.2876@.TK2MSFTNGP09.phx.gbl...
> No idea, but would you want to? T-SQL was not designed for this.
>
> Absolutely. It's a piece of cake. Remember that an image field is nothing
more
> than an array of bytes. All you do is creata a FileStream and set it's
contents
> to that of the byte array from the image field.
>
> Thomas
>|||Although this is not the forum for this sort of question...
private const string connStr = ...
public void ReadFromDBToFile(string destFilePath)
{
byte[] data = null;
using (SqlConnection conn = new SqlConnection(connStr))
{
StringBuilder sb = new StringBuilder();
sb.Append("Select Content, DataLength(Content) As Len");
sb.AppendFormat("\nFrom dbo.TableName");
sb.AppendFormat("\nWhere PK = @.PK");
conn.Open();
using (SqlCommand cmd = new SqlCommand(sb.ToString(), conn))
{
cmd.Parameters.Add("@.PK", SqlDbType...);
cmd.Parameters[0].Value = <pk>
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int len = dr.GetInt32(1);
data = new byte[len];
dr.GetBytes(0, 0, data, 0, data.Length);
}
}
}
using (FileStream fs = new FileStream(destFilePath, FileMode.Create,
FileAccess.Write, FileShare.Write))
{
fs.Write(data, 0, data.Length);
}
}
public void WriteFromFileToDB(string sourceFilePath)
{
byte[] data;
using (FileStream fs = new FileStream(sourceFilePath, FileMode.Open,
FileAccess.Read, FileShare.Read))
{
data = new byte[fs.Length];
fs.Read(data, 0, (int)fs.Length);
}
using (SqlConnection conn = new SqlConnection(connStr))
{
StringBuilder sb = new StringBuilder();
sb.Append("Update dbo.TableName");
sb.AppendFormat("\nSet Content = @.Content");
sb.AppendFormat("\nWhere PK= @.PK");
conn.Open();
using (SqlCommand cmd = new SqlCommand(sb.ToString(), conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@.Content", SqlDbType.Image);
cmd.Parameters.Add("@.PK", SqlDbType...
cmd.Parameters[0].Value = data;
cmd.Parameters[1].Value = <pk>
cmd.ExecuteNonQuery();
}
}
}
I used a dynamic SQL statement, but you could just as easily use a stored pr
oc.
HTH
Thomas
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:d3nfqc$oem$1@.ns2.fe.internet.bosch.com...
> Hello thomas,
> Thank you very much.
> Any sample piece of code wiich does so..?
> I tried in net.. so far I wasn't sucessful..
> Regards
> Sathian
> "Thomas" <thomas@.newsgroup.nospam> wrote in message
> news:eoguWJXQFHA.2876@.TK2MSFTNGP09.phx.gbl...
> more
> contents
>

Image Data Type

Hello,
Does any one has any document or link about pros and cons of using Image
data type to store images? Specially performance point of view.
Thanks,
Mukesh
Well, it depends. A quick search on the web reveals this article:
http://www.extremeexperts.com/sql/FAQ/StoreImages.aspx
I hope this will get you started.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mukesh" <Mukesh@.discussions.microsoft.com> wrote in message
news:DBDBEF9F-4897-4043-8F2C-5E0BA9CCA541@.microsoft.com...
> Hello,
> Does any one has any document or link about pros and cons of using Image
> data type to store images? Specially performance point of view.
> Thanks,
> Mukesh
|||Thanks Wei,
This will help a lot...
Thanks,
Mukesh
"Wei Xiao [MSFT]" wrote:

> Well, it depends. A quick search on the web reveals this article:
> http://www.extremeexperts.com/sql/FAQ/StoreImages.aspx
> I hope this will get you started.
>
> --
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Mukesh" <Mukesh@.discussions.microsoft.com> wrote in message
> news:DBDBEF9F-4897-4043-8F2C-5E0BA9CCA541@.microsoft.com...
>
>

Image Data Type

Hello,
Does any one has any document or link about pros and cons of using Image
data type to store images? Specially performance point of view.
Thanks,
MukeshWell, it depends. A quick search on the web reveals this article:
http://www.extremeexperts.com/sql/FAQ/StoreImages.aspx
I hope this will get you started.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mukesh" <Mukesh@.discussions.microsoft.com> wrote in message
news:DBDBEF9F-4897-4043-8F2C-5E0BA9CCA541@.microsoft.com...
> Hello,
> Does any one has any document or link about pros and cons of using Image
> data type to store images? Specially performance point of view.
> Thanks,
> Mukesh|||Thanks Wei,
This will help a lot...
Thanks,
Mukesh
"Wei Xiao [MSFT]" wrote:

> Well, it depends. A quick search on the web reveals this article:
> http://www.extremeexperts.com/sql/FAQ/StoreImages.aspx
> I hope this will get you started.
>
> --
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Mukesh" <Mukesh@.discussions.microsoft.com> wrote in message
> news:DBDBEF9F-4897-4043-8F2C-5E0BA9CCA541@.microsoft.com...
>
>

I'll be posible process a bulk load for following xml document?

the xml doc is:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element name="entity" sql:relation="ENTITY">
<xs:complexType mixed="true">
<xs:sequence minOccurs="0" maxOccurs="unbounded">
<xs:element ref="entity"/>
</xs:sequence>
<xs:attribute name="name" use="required" sql:field="NAME"/>
<xs:attribute name="descripcion" use="required" sql:field="DESCRIPCION"/>
<xs:attribute name="address" use="required" sql:field="ADDRESS"/>
</xs:complexType>
</xs:element>
<xs:element name="entities" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="entity" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

the table is:

TABLE [dbo].[ENTITY](
[ENTITYNAME] [varchar](50) NULL,
[ENTITYDESCRIPCION] [varchar](200) NULL,
[ENTITYADRESS] [varchar](50) NULL,
) ON [PRIMARY]

GO

SOMEBODY COULD DO IT ?

Yes this shape would be possible to bulkload. However the column names you have specified with sql:field do not match the columns in the database, so if you are having trouble with this schema, that might be one possibility.

<xs:attribute name="name" use="required" sql:field="NAME"/>
<xs:attribute name="descripcion" use="required" sql:field="DESCRIPCION"/>
<xs:attribute name="address" use="required" sql:field="ADDRESS"/>

look like they should be:

<xs:attribute name="name" use="required" sql:field="ENTITYNAME"/>
<xs:attribute name="descripcion" use="required" sql:field="ENTITYDESCRIPCION"/>
<xs:attribute name="address" use="required" sql:field="ENTITYADRESS"/>

-Todd

I'll be posible process a bulk load for following xml document?

the xml doc is:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element name="entity" sql:relation="ENTITY">
<xs:complexType mixed="true">
<xs:sequence minOccurs="0" maxOccurs="unbounded">
<xs:element ref="entity"/>
</xs:sequence>
<xs:attribute name="name" use="required" sql:field="NAME"/>
<xs:attribute name="descripcion" use="required" sql:field="DESCRIPCION"/>
<xs:attribute name="address" use="required" sql:field="ADDRESS"/>
</xs:complexType>
</xs:element>
<xs:element name="entities" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="entity" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

the table is:

TABLE [dbo].[ENTITY](
[ENTITYNAME] [varchar](50) NULL,
[ENTITYDESCRIPCION] [varchar](200) NULL,
[ENTITYADRESS] [varchar](50) NULL,
) ON [PRIMARY]

GO

SOMEBODY COULD DO IT ?

Yes this shape would be possible to bulkload. However the column names you have specified with sql:field do not match the columns in the database, so if you are having trouble with this schema, that might be one possibility.

<xs:attribute name="name" use="required" sql:field="NAME"/>
<xs:attribute name="descripcion" use="required" sql:field="DESCRIPCION"/>
<xs:attribute name="address" use="required" sql:field="ADDRESS"/>

look like they should be:

<xs:attribute name="name" use="required" sql:field="ENTITYNAME"/>
<xs:attribute name="descripcion" use="required" sql:field="ENTITYDESCRIPCION"/>
<xs:attribute name="address" use="required" sql:field="ENTITYADRESS"/>

-Todd