Insert BLOB value.

  • Thread starter Thread starter Marius Gheorghe
  • Start date Start date
M

Marius Gheorghe

Is it possible to insert a BLOB value into a field using only SQL ? I
mean without an IDbParameter.
 
Marius Gheorghe said:
Is it possible to insert a BLOB value into a field using only SQL ? I
mean without an IDbParameter.
Click to expand...

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.
 
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.
 
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.
 
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() + ")";
Click to expand...
Also try with
cmd.CommandText = "insert into categories(picture) values('" +
sb.ToString() + "')"; for text.
Click to expand...
to know if there is a way to do this WITHOUT using
 
Marius Gheorghe said:
Thomas the sample you linked is using SqlParameters and i have asked
specifically for SQL only solution. Thanks for the link anyway.
Click to expand...

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".
Click to expand...

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.
 
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
 
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!
***************************
 
Back
Top