freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

RE: Image fields? How it works for me...

From: Franck Martin <Franck AT sopac.org>
To: 'TDS Development Group' <freetds AT franklin.oit.unc.edu>
Cc: "'gonzalze AT mail.slh.wisc.edu'" <gonzalze AT mail.slh.wisc.edu>
Subject: RE: Image fields? How it works for me...
Date: Fri, 18 Jan 2002 11:59:37 +1200

Here is the way image fields work for me....

BTW the message from Freddy Vuto about binary fields let me think that there
is a bug in all handling of binary data with freetds....

The first part is to have an image field in MSSQL7.0 (table Snapshot, field
Snap, record key Snap_ID), use access to create a form to this field. Then
place the mouse on the ms access field, right click and insert object,
select package, then inside the package manager, insert file and select a
jpeg file. Close the package. Now you have stored a package inside the image
field of MSSQL7.0

The first part is to write a SQL procedure to extract the data and return it
as chunck of data:

---------------------
CREATE PROCEDURE Snap_Image2vchar
@Snap_ID int
AS

DECLARE @Count int
DECLARE @Start int
DECLARE @NextLen int

SELECT @Count = datalength(Snap) FROM SnapShot WHERE
Snap_ID=@Snap_ID

SELECT @NextLen = 4096
SELECT @Start =1
CREATE TABLE #T (Snap text)
WHILE (@Count > 0)
BEGIN
if (select @Count) < @NextLen
begin
select @NextLen = @Count
end

insert into #T select

convert(text(4096),convert(varchar(4096),substring(Snap,@Start,@NextLen)))
as Snap from SnapShot where Snap_ID = @Snap_ID

select @Count = @Count - @NextLen
select @Start = @Start + @NextLen
END
SELECT * FROM #T
DROP TABLE #T
--------------------------------

Then here is the code from php that will get this data and display it...
Just save the code as image.php with nothing else (no <head> <body> or
other...)

(I use <% %> tags for php code)

--------------------------------

<%
Header("Content-type: image/jpeg");

$conn = sybase_pconnect("SERVER","login","password");
$ok=sybase_select_db("DB",$conn);
if (@$snapid==0)
{
$snapid=1;
}

$result = sybase_query("Snap_Image2vchar ".$snapid);
$nbrows=sybase_num_rows($result);

if ($nbrows==0)
{
$im=ImageCreate(270,270);
$white=ImageColorAllocate($im,255,255,255);
$black=ImageColorAllocate($im,0,0,0);
ImageString($im,3,3,3,"No Image",$black);
ImageJPEG($im);
ImageDestroy($im);
}

for ($i = 0 ; $i < $nbrows; $i++)
{
sybase_data_seek($result,$i);
$par = sybase_fetch_object($result);
if ($i==0)
{
// Needs to remove the OLE header if any...
$pos=strpos($par->Snap,"JFIF");
echo (substr($par->Snap,$pos-6));
} else
{
echo ($par->Snap);
}
}

%>

------------------------------------

And it works cf:

http://www.sopac.org/Data/Aerial/Snaps.html?setid=228

I have tried to save the jpeg image without the package directly inside the
image field but I never got a successful result. Other format like MS
PhotoEditor are not ok, because the image is converted to BMP inside a MS
Photoeditor container...

As usual, I would greatly appreciate if a developper look into this code and
why it seems that freetds+php is not able to handle images better.

Franck Martin
Network and Database Development Officer
SOPAC South Pacific Applied Geoscience Commission
Fiji
E-mail: franck AT sopac.org <mailto:franck AT sopac.org>
Web site: http://www.sopac.org/
<http://www.sopac.org/> Support FMaps: http://fmaps.sourceforge.net/
<http://fmaps.sourceforge.net/>

This e-mail is intended for its addresses only. Do not forward this e-mail
without approval. The views expressed in this e-mail may not be necessarily
the views of SOPAC.

RE: Image fields? How it works for me..., Franck Martin, 01/17/2002