CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Intermediate:
Binary Data Perl into SQL Binary Field (SQL 2005)

 



cccict
New User

Dec 10, 2015, 9:48 AM

Post #1 of 9 (2795 views)
Binary Data Perl into SQL Binary Field (SQL 2005) Can't Post

Hi,

We want to save image data into a SQL binary field. So far we have been completely unsuccessful. We are running perl under windows. Some guidance would be really appreciated as we have tried several methods (scroll down) all without success.

Someone must have achieved this before but we can't find anything on-line that can help us out. Any help/guidance really appreciated.

PERL BITS
------------
use DBI;
#image data is stored in $CGI{'imageone'}->{'Contents'}
#can write the data to a binary file and it is OK.
$data = "$CGI{'imageone'}->{'Contents'}";

SQL STATEMENT EXAMPLE
-------------------------------
$sql = "UPDATE web_news SET wbn_image1= CONVERT(varbinary(max),'$data') WHERE wbn_ref='49';";
$sth = $dbh2->prepare($sql);
$sth->execute or die "SQL Error: $DBI::errstr\n";


Method 1
-----------
We tried to assign the data field $CGI{'imageone'}->{'Contents'} into the SQL field (both directly and indirectly by assigning into a variable first).

Because the data contains all types of characters obviously that is not feasible and the SQL statement fails.

Method 2
-----------
We tried to assign the data field $CGI{'imageone'}->{'Contents'} into the SQL field using a SQL CONVERT statement (both directly and indirectly by assigning into a variable first).

Because the data contains all types of characters obviously that is not feasible and the SQL statement fails.

Method 3
-----------
We successfully wrote the data into a temporary file and tried to load the data into SQL using SQL BLOB code (i.e. .... FROM OPENROWSET (BULK 'c:\images\tempfile.jpg', SINGLE_BLOB) ) .

This should have worked! All permissions on the file were equivalent to rwx for everyone and the file absolutely exists. The error came back that the file could not be found! SQL just won't cooperate or Perl DBI not set up for this.

Method 4
-----------
Here we tried to be very clever. We thought that we convert all the data to hex and store the hex, it should work as there would be no characters that SQL will dislike. This way we could bring the data back and split into array of two characters and convert back.

The conversion routine as below...

$img = "$CGI{'imageone'}->{'Contents'}";
$leni = length($img);
for (my $i=0; $i <= $leni; $i++)
{
$chr= substr($img,$i,1);
$asciiChar = ord($chr);
$hexChr = sprintf("%x",$asciiChar);
$hexChr = sprintf("%2s", $hexChr);
$converted = "$converted$hexChr";
}

Because it is image data, the length with a test was over 5,000,000 characters. It takes ages to run and kills the server. No good for a web application!

Method 5
-----------
Same as method 4 but tried reading data from temp file. Same problem. It takes ages to run and kills the server. No good for a web application!


Method 6
-----------
Help!!! We have given up and are now storing the temp file on the server itself and referencing the filename/path in SQL. This is the cheat we have used but is there another way??


Laurent_R
Veteran / Moderator

Dec 10, 2015, 11:26 PM

Post #2 of 9 (2772 views)
Re: [cccict] Binary Data Perl into SQL Binary Field (SQL 2005) [In reply to] Can't Post

Hum, I can't really help on what you are trying to do, I just don't know how to do it, especially on an unnamed database.

However, my two cents: I usually don't want to store large binary object into a database if I can avoid it, this is most of the time inefficient and wasteful. To me, it is better to devote another file system to your binary objects, to store images as independent files into it and to store in the database the path to such image files.


BillKSmith
Veteran

Dec 11, 2015, 7:51 AM

Post #3 of 9 (2763 views)
Re: [cccict] Binary Data Perl into SQL Binary Field (SQL 2005) [In reply to] Can't Post

Laurent's suggestion sounds very much like your method 6. It sounds reasonable to me also. Do you have a good reason for not using it?

Your method 4 avoids the problem of storing binary data. If it is acceptable except for speed, there are variations you can try. The perl functions pack and unpack should be much faster than your code. I suspect that there are other character encodings of binary data. Perl almost certainly has modules to support them. Sorry, I really do not know what to look for.
Good Luck,
Bill


FishMonger
Veteran / Moderator

Dec 11, 2015, 8:07 AM

Post #4 of 9 (2760 views)
Re: [cccict] Binary Data Perl into SQL Binary Field (SQL 2005) [In reply to] Can't Post

Where is the image data coming from?

How are you assigning the data to $CGI{'imageone'}->{'Contents'}?

Please post a short but complete script that demonstrates the problem so that I can test/troubleshoot it.

In "Method 3" you mention using a BLOB field in the db. Are inserting into a BLOB field for all methods? If not, you should be.


(This post was edited by FishMonger on Dec 11, 2015, 8:14 AM)


cccict
New User

Dec 11, 2015, 9:09 AM

Post #5 of 9 (2751 views)
Re: [FishMonger] Binary Data Perl into SQL Binary Field (SQL 2005) [In reply to] Can't Post

Hi

Firstly, thank you everyone for your help. We have decided to go with option 6 and recommendations from colleagues here as there was no 'real' reason not to do it that way. Really appreciated.

The appropriate code is below so if it is solved, it may help someone in the future.

use File::Copy;
use DBI;
use CGI;
use Time::Local;

# START : Read in variables from the html page
read(STDIN,$buff,$ENV{'CONTENT_LENGTH'});
# END : Read in variables from the html page

# START : Process $buff in subroutine
# Decodes and splits up variables and stores them into
# $CGI.

&Parse_Multi;

# If code needed for subroutine Parse_Multi, please ask.
# Alternatively to test read in any image file as
# binary from file system.
# END : Process $buff in subroutine

# START : Reference processed data into variable.
# Not necessary but makes easier reading below
$filename = "$CGI{'imageone'}->{'filename'}";
$imgdata = "$CGI{'imageone'}->{'Contents'}";
# END : Reference processed data into variable.
# Not necessary but makes easier reading below

#START: SET UP DATABASE AND CONNECT
#Make sure your DB credentials match here
my $host2 = 'srv-db01\\orsdb';
my $database2 = 'srv_appl';
my $user2 = 'test';
my $auth2 = 'test';

my $dsn2 = "dbi:ODBC:Driver={SQL Server};Server=$host2;Database=$database2";
my $dbh2 = DBI->connect($dsn2, $user2, $auth2, { PrintError => 0 } );

# For info and test table srv_appl.web_news has
# two fields wbn_image1 as var binary max and
# wbn_ref as integer and one record with wbn_ref = 49

#END : SET UP DATABASE AND CONNECT

# START : Set up and run SQL
$sql = "UPDATE web_news SET wbn_image1= CONVERT(varbinary(max),'$imgdata') WHERE wbn_ref='49';";
$sth = $dbh2->prepare($sql);
$sth->execute or die "SQL Error: $DBI::errstr\n";
# END : Set up and run SQL


# In the SQL we simply wanted to store the
# data from $imgdata
# or $CGI{'imageone'}->{'Contents'} into binary SQL field

# As above we have decided not to do this now and using
# alternative where we only store the filename into the
# database table and save the file onto the file system
# For info. code to save file is listed below.

# START : Save the file contents as a file
# onto the file system
$ssh = "\\";
$vdir4="C:\\inetpub\\wwwroot\\storedimages\\";
$prcs = "$$";
$gti1=rindex($filename,"$ssh");
$gti2=length($filename);
$filename=substr($filename,$gti1+1,$gti2-$gti1);
$filename = "i$prcs\_$filename";
open(TMP, ">$vdir4$filename");
binmode(TMP);
print TMP $CGI{'imageone'}->{'Contents'};
close(TMP);
# END : Save the file contents as a file onto the file system

And lastly For info.. HTML code for picking up file below. og_news.cgi is the perl script above.

<form name="NewForm" action="og_news.cgi" method="POST" class="fixed" enctype="multipart/form-data" name="newsform" id="newsform">
<label for="image1">Image One:</label>

<input type="file" name="imageone" id="imageone" accept="image/*"></input>
<input type="submit" value="Submit">
</form>


(This post was edited by cccict on Dec 11, 2015, 9:22 AM)


FishMonger
Veteran / Moderator

Dec 11, 2015, 9:16 AM

Post #6 of 9 (2748 views)
Re: [cccict] Binary Data Perl into SQL Binary Field (SQL 2005) [In reply to] Can't Post

You should add these 2 pragmas.

Code
use strict; 
use warnings FATAL => 'all';

Then fix all of the problems in the code that they point out.


cccict
New User

Dec 11, 2015, 9:26 AM

Post #7 of 9 (2746 views)
Re: [FishMonger] Binary Data Perl into SQL Binary Field (SQL 2005) [In reply to] Can't Post

Hi

Thank you for that. I can do that but the code works perfectly apart from the original problem of getting the data into SQL binary field. In this case I don't believe strict, warnings are not going to resolve that part :).

(using alternative method now and will if time allows put the strict/warnings in the code).


FishMonger
Veteran / Moderator

Dec 11, 2015, 9:37 AM

Post #8 of 9 (2741 views)
Re: [cccict] Binary Data Perl into SQL Binary Field (SQL 2005) [In reply to] Can't Post

They won't fix your sql issue, but they are the first step in writing good quality code. They can point out mistakes/bugs in your code which could be difficult to track down. By leaving those pragma out, you're telling perl that you don't want to know about the mistakes you made.

Code that functions is not always good code. The code you posted is questionable and I suspect is just the tip of the iceberg.


(This post was edited by FishMonger on Dec 11, 2015, 9:41 AM)


Zhris
Enthusiast

Dec 16, 2015, 5:09 PM

Post #9 of 9 (2710 views)
Re: [FishMonger] Binary Data Perl into SQL Binary Field (SQL 2005) [In reply to] Can't Post


Quote
the SQL statement fails


Could you provide the specific error you are receiving.

Immediately I notice you are "binding" perl variables into sql statements yourself but not escaping them. This is nearly always a bad idea for numerous reasons. In this case, its highly likely the image binary data will contain single quotes, thus injecting everything else afterwards into your sql statement. Read into DBI placeholders and bind values.

Chris

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives