Insert BLOB value.

[Marius Gheorghe]

Is it possible to insert a BLOB value into a field using only SQL ? I
mean without an IDbParameter.

 

[Chad Z. Hower aka Kudzu]

Is it possible to insert a BLOB value into a field using only SQL ? I
mean without an IDbParameter.

Some databases allow you to specify text for insertion into blobs, but some restrict it depending on
the blob type. So in short - it depends on your database and if you want to insert text, or more than text.

 

[Marius Gheorghe]

Thomas the sample you linked is using SqlParameters and i have asked
specifically for SQL only solution. Thanks for the link anyway.

Chad...let's take SqlServer and a "Image" field. If i tried to insert
the text representation (with '') of the byte array which represents the
image i get a "Operand type clash: text is incompatible with image". If
i try without the text representation (without '') i get a "The number
that starts with [part of byte arry] is too long".

In this case it seems that even if the field's type is Image it's
treating him as Binary (which has the max length set to 8000).

Other ideea ?

Thanks.

 

[Thomas Wenning]

Is it possible to insert a BLOB value into a field using only SQL ? I
mean without an IDbParameter.

Hi,

read this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;321900&Product=vsnet

Thomas

 

[Rogas69]

Hi Marius,
How do you do that? Can you show an example code?
Peter

 

[Marius Gheorghe]

Hi Rogas,

Here is the sample code :


try
{
SqlConnection con = new SqlConnection();

con.ConnectionString = @"Data
Source=mariusg\marius;Initial Catalog=test;User Id=sa;Password=1234";


SqlCommand cmd = new SqlCommand();

byte[] scc =
System.IO.File.ReadAllBytes(@"C:\untitled.bmp");

StringBuilder sb = new StringBuilder();


for (int i = 0; i < scc.Length; i++)
{
sb.Append(scc.ToString());
}

cmd.CommandText = "insert into categories(picture)
values(" + sb.ToString() + ")";

cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();

}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

Also try with
cmd.CommandText = "insert into categories(picture) values('" +
sb.ToString() + "')"; for text.

The type of the "picture" field is Image. The DB is SqlServer 2000.
Running with .NET 2.0

I just want to know if there is a way to do this WITHOUT using
IDbParameters. The "no IDbParameter" is very important to me.

Thank you very much for your help.

 

[Rogas69]

well,
so you can use WRITETEXT function probably, i would place the code in stored
procedure
and pass the text representing image to that stored procedure, and (but i
don't know) would have probably to convert the text to binary datatype
inside the procedure if you use image datatype for data.
there are several examples in BOL for manipulation of text/image datafields.

anyway, you should be aware that this approach is susceptible to errors,
like you can have single or double quotes in your string representing image
and your query will fail then, unless you escape them somehow.

Peter

cmd.CommandText = "insert into categories(picture)
values(" + sb.ToString() + ")";

Also try with
cmd.CommandText = "insert into categories(picture) values('" +
sb.ToString() + "')"; for text.

to know if there is a way to do this WITHOUT using

 

[Guest]

Hi Marius,

Following URL may be helpful:

http://support.microsoft.com/default.aspx?scid=kb;en-us;326502

Elton Wang
(e-mail address removed)

 

[Chad Z. Hower aka Kudzu]

Thomas the sample you linked is using SqlParameters and i have asked
specifically for SQL only solution. Thanks for the link anyway.

With all the messages in the group - readers tend to skim messages and assume content. We've all
done it from time to time.

Chad...let's take SqlServer and a "Image" field. If i tried to insert
the text representation (with '') of the byte array which represents the
image i get a "Operand type clash: text is incompatible with image". If
i try without the text representation (without '') i get a "The number
that starts with [part of byte arry] is too long".

Im not sure if/how it can be done in SQL server. I do use SQL server, but lately I mostly work with
Firebird. Firebird has a subtype on blobs - if its text I can just treat it like a varchar. If its not text,
Firebird has cast functions that I can use. SQL server might have something similar, or you might have
to write a SP and call the SP.

 

[Chad Z. Hower aka Kudzu]

Following URL may be helpful:

http://support.microsoft.com/default.aspx?scid=kb;en-us;326502

Unless I've missed something - this does exactly what he doesnt want to do, use parameters.

 

[Guest]

Although it actually uses parameter (SqlCommandBuilder uses parameter to pass
value), it does not directly use parameter. You just need to assign binary
data to datatable, then call SqlDataAdapter.Update() to save BLOB data to DB.


HTH

Elton

 

[Guest]

It is possible to snort coca cola, but I would not advise it either.

It really depends on the BLOB type whether you can send in the bytes as
text. In certain types of BLOB fields, you can certain insert as text, but in
most cases the answer is no.

Overall, parameters save you from a lot of potential dangers, including
basic SQL injection protection.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************