Question on inserting varbinary to SQL table via Powershell

Frank

Hi,

I apologize if this is the wrong discussion group but its a question of how
to insert a varbinary (word doc) into SQL. I actually got this to work but
need to understand why it works one way and not the other. Here is the
procedure which works:
==============================================
[byte[]]$file = Get-Content d:\test.docx -Encoding byte
$DB = "server=sqlserver01;integrated security=sspi;database=test"

$conn = new-object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $DB
$conn.Open()
$cmd = new-object System.Data.SqlClient.SqlCommand
$cmd.Connection=$conn
$cmd.CommandText="insert into binarytable (binary,datetime,type,service)
values(@FileData,'$datetime','$type','$service');"
$cmd.Parameters.Add("@FileData", $file)
$rdr = $cmd.ExecuteReader() | out-null 2>&1
====================================

What doesn't work is using an insert statement and use the $file variable I
set earlier, ie:

$cmd.CommandText="insert into binarytable (binary,datetime,type,service)
values('$file','$datetime','$type','$service');"

The "$cmd.Parameters.Add("@FileData", $file)" line must do some sort of
conversion which enables this but I would like to understand this a little
better.

Can anyone help?

Thanks in advance,

Leo Tohill

Hello Frank,

$cmd.CommandText="insert into binarytable (binary,datetime,type,service)
values(@FileData,'$datetime','$type','$service');"

builds a string literal as the insert statement. Binary values simply
cannot be represented in a string literal. Using a parameter, as you have
done, is the most common approach to inserting binary data. Another
approach is hex-encoding the data. If you hex-encoded the data with the
right escape characters for sql server, then you could place it as a literal
into the insert, but
why bother?

I'm puzzled why you use ExecuteReader() instead of ExecuteNonQuery().

"Frank" wrote:

> Hi,
>
> I apologize if this is the wrong discussion group but its a question of how
> to insert a varbinary (word doc) into SQL. I actually got this to work but
> need to understand why it works one way and not the other. Here is the
> procedure which works:
> ==============================================
> [byte[]]$file = Get-Content d:\test.docx -Encoding byte
> $DB = "server=sqlserver01;integrated security=sspi;database=test"
>
> $conn = new-object System.Data.SqlClient.SqlConnection
> $conn.ConnectionString = $DB
> $conn.Open()
> $cmd = new-object System.Data.SqlClient.SqlCommand
> $cmd.Connection=$conn
> $cmd.CommandText="insert into binarytable (binary,datetime,type,service)
> values(@FileData,'$datetime','$type','$service');"
> $cmd.Parameters.Add("@FileData", $file)
> $rdr = $cmd.ExecuteReader() | out-null 2>&1
> ====================================
>
> What doesn't work is using an insert statement and use the $file variable I
> set earlier, ie:
>
> $cmd.CommandText="insert into binarytable (binary,datetime,type,service)
> values('$file','$datetime','$type','$service');"
>
> The "$cmd.Parameters.Add("@FileData", $file)" line must do some sort of
> conversion which enables this but I would like to understand this a little
> better.
>
> Can anyone help?
>
> Thanks in advance,
>
>
>
>
>

Frank

I didn't know there was ExecuteNonQuery(). What is the difference?

Thanks!



"Leo Tohill" wrote:

> Hello Frank,
>
> $cmd.CommandText="insert into binarytable (binary,datetime,type,service)
> values(@FileData,'$datetime','$type','$service');"
>
> builds a string literal as the insert statement. Binary values simply
> cannot be represented in a string literal. Using a parameter, as you have
> done, is the most common approach to inserting binary data. Another
> approach is hex-encoding the data. If you hex-encoded the data with the
> right escape characters for sql server, then you could place it as a literal
> into the insert, but
> why bother?
>
> I'm puzzled why you use ExecuteReader() instead of ExecuteNonQuery().
>
> "Frank" wrote:
>
> > Hi,
> >
> > I apologize if this is the wrong discussion group but its a question of how
> > to insert a varbinary (word doc) into SQL. I actually got this to work but
> > need to understand why it works one way and not the other. Here is the
> > procedure which works:
> > ==============================================
> > [byte[]]$file = Get-Content d:\test.docx -Encoding byte
> > $DB = "server=sqlserver01;integrated security=sspi;database=test"
> >
> > $conn = new-object System.Data.SqlClient.SqlConnection
> > $conn.ConnectionString = $DB
> > $conn.Open()
> > $cmd = new-object System.Data.SqlClient.SqlCommand
> > $cmd.Connection=$conn
> > $cmd.CommandText="insert into binarytable (binary,datetime,type,service)
> > values(@FileData,'$datetime','$type','$service');"
> > $cmd.Parameters.Add("@FileData", $file)
> > $rdr = $cmd.ExecuteReader() | out-null 2>&1
> > ====================================
> >
> > What doesn't work is using an insert statement and use the $file variable I
> > set earlier, ie:
> >
> > $cmd.CommandText="insert into binarytable (binary,datetime,type,service)
> > values('$file','$datetime','$type','$service');"
> >
> > The "$cmd.Parameters.Add("@FileData", $file)" line must do some sort of
> > conversion which enables this but I would like to understand this a little
> > better.
> >
> > Can anyone help?
> >
> > Thanks in advance,
> >
> >
> >
> >
> >

Leo Tohill

ExecuteReader() is intended to execute a Select statement that returns a
result set (rows of data). The reader object returned by ExecuteReader() is
used to iterate through the result set. ExecuteNonQuery() is used to execute
statements that don't return data, such as Insert, Update, Delete, as well as
Create, Alter, Drop, etc.

(See also ExecuteScalar() for statements that return a single value.)



"Frank" wrote:

> I didn't know there was ExecuteNonQuery(). What is the difference?
>
> Thanks!
>
>
>
> "Leo Tohill" wrote:
>
> > Hello Frank,
> >
> > $cmd.CommandText="insert into binarytable (binary,datetime,type,service)
> > values(@FileData,'$datetime','$type','$service');"
> >
> > builds a string literal as the insert statement. Binary values simply
> > cannot be represented in a string literal. Using a parameter, as you have
> > done, is the most common approach to inserting binary data. Another
> > approach is hex-encoding the data. If you hex-encoded the data with the
> > right escape characters for sql server, then you could place it as a literal
> > into the insert, but
> > why bother?
> >
> > I'm puzzled why you use ExecuteReader() instead of ExecuteNonQuery().
> >
> > "Frank" wrote:
> >
> > > Hi,
> > >
> > > I apologize if this is the wrong discussion group but its a question of how
> > > to insert a varbinary (word doc) into SQL. I actually got this to work but
> > > need to understand why it works one way and not the other. Here is the
> > > procedure which works:
> > > ==============================================
> > > [byte[]]$file = Get-Content d:\test.docx -Encoding byte
> > > $DB = "server=sqlserver01;integrated security=sspi;database=test"
> > >
> > > $conn = new-object System.Data.SqlClient.SqlConnection
> > > $conn.ConnectionString = $DB
> > > $conn.Open()
> > > $cmd = new-object System.Data.SqlClient.SqlCommand
> > > $cmd.Connection=$conn
> > > $cmd.CommandText="insert into binarytable (binary,datetime,type,service)
> > > values(@FileData,'$datetime','$type','$service');"
> > > $cmd.Parameters.Add("@FileData", $file)
> > > $rdr = $cmd.ExecuteReader() | out-null 2>&1
> > > ====================================
> > >
> > > What doesn't work is using an insert statement and use the $file variable I
> > > set earlier, ie:
> > >
> > > $cmd.CommandText="insert into binarytable (binary,datetime,type,service)
> > > values('$file','$datetime','$type','$service');"
> > >
> > > The "$cmd.Parameters.Add("@FileData", $file)" line must do some sort of
> > > conversion which enables this but I would like to understand this a little
> > > better.
> > >
> > > Can anyone help?
> > >
> > > Thanks in advance,
> > >
> > >
> > >
> > >
> > >