[DUG] Images and MS SQL Server

Dave O'Brien dave at ICCS.CO.NZ
Tue Aug 19 11:00:36 NZST 2008 

I have just created a new prog, using TADOConnection, TADOTable,
TDataSource, TDBImage, TDBNavigator. The TDBImage is empty. Here is the
code I am using to save the image:

Image is a TImage, ID is correctly defined as the other fields are
correctly saved.

    datamod.ADOU.SQL.Text := 'Update StockCats' ;
    datamod.ADOU.SQL.Add('Set Category = '+Fix(edtCategory.Text)+',') ;
    datamod.ADOU.SQL.Add('Description = '+Fix(moDescr.Text)+',') ;
    datamod.ADOU.SQL.Add('Image = :Image1') ;
    datamod.ADOU.SQL.Add('Where ID = '+InttoStr(ID)) ;
    ms := TMemoryStream.Create ;
    try
      Image.Picture.Bitmap.SaveToStream(ms) ;
      ms.seek(0,0) ;
      datamod.ADOU.Parameters.ParamByName('Image1').DataType := ftBlob ;
      datamod.ADOU.Parameters.ParamByName('Image1').LoadFromStream(ms,
ftBlob) ;
      datamod.ADOU.ExecSQL ;
    finally
      ms.Free ;
    end ;

It actually appears to be something to do with the image being a jpeg.
If I use a bitmap image, the TDBImage shows the bitmap correctly. The
following code to retrieve the image (and everything else I've tried)
comes up blank:

  datamod.ADOQ.SQL.Text := 'Select * from StockCats where ID =
'+InttoStr(ID) ;
  datamod.ADOQ.Open ;
  try
    if not datamod.ADOQ.EOF then
    begin
      ID := datamod.ADOQ.FieldByName('ID').AsInteger ;
      edtCategory.Text :=
Trim(datamod.ADOQ.FieldByName('Category').AsString) ;
      moDescr.Text :=
Trim(datamod.ADOQ.FieldByName('Description').AsString) ;
      ms := TMemoryStream.Create ;
      try
        st :=
datamod.adoq.CreateBlobStream(datamod.ADOQ.FieldByName('Image'), bmRead)
;
        try
          ms.CopyFrom(st, 0) ;
          Image.Picture.Bitmap.LoadFromStream(ms);
        finally
          st.Free ;
        end ;
      finally
        ms.Free ;
      end ;
    end ;
  finally
    datamod.ADOQ.Close ;
  end ;

-----Original Message-----
From: delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz]
On Behalf Of Neven MacEwan
Sent: Tuesday, 19 August 2008 10:18 a.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] Images and MS SQL Server

Dave

Im getting a little confused, have you or have you not managed to write 
an image to your DB?
You could confirm this with a simple sql query or crystal reports

If you have you should be able to open the table in the ide and bind a 
TDBImage to it

Neven
> That's exactly what I am doing to save the image, how do I retrieve
it?
> Every method I have tried comes up blank.
>
> -----Original Message-----
> From: delphi-bounces at delphi.org.nz
[mailto:delphi-bounces at delphi.org.nz]
> On Behalf Of Leigh Wanstead
> Sent: Monday, 18 August 2008 4:32 p.m.
> To: NZ Borland Developers Group - Delphi List
> Subject: Re: [DUG] Images and MS SQL Server
>
> IIRC, I will do this way.
>
> 	varStream just a memory stream, you might want to instante with
> a
> TFileStream
>
> 	ADOQueryTest.sql.text := 'UPDATE table SET FileCol =
> :FileColOBJECTS WHERE
> ID = :ID';
> 	...
>  
> ADOQueryTest.Parameters.ParamByName('FileColOBJECTS').LoadFromStream(v
> arStream, ftBlob);
>       ADOQueryTest.ExecSQL;
>
> Have a nice day
>
> Regards
> Leigh
> www.smootharm.com
>
>
> -----Original Message-----
> From: delphi-bounces at delphi.org.nz
> [mailto:delphi-bounces at delphi.org.nz]On Behalf Of Dave O'Brien
> Sent: Monday, 18 August 2008 4:17 p.m.
> To: NZ Borland Developers Group - Delphi List
> Subject: Re: [DUG] Images and MS SQL Server
>
>
> So did I, but I can't tell if it's not working because it's not saving
> or loading...
>
> The problem is FieldByName doesn't have any saveto... methods.
>
> I have tried to cast it as a Tblobfield
> (TBlobField(adoq.fieldbyname('Image')).Saveto...), but either this
> doesn't work or my save code doesn't work.
>
> -----Original Message-----
> From: delphi-bounces at delphi.org.nz
[mailto:delphi-bounces at delphi.org.nz]
> On Behalf Of Neven MacEwan
> Sent: Monday, 18 August 2008 4:06 p.m.
> To: NZ Borland Developers Group - Delphi List
> Subject: Re: [DUG] Images and MS SQL Server
>
> Dave
>
> I thought you had code to save it?
>
> To tretrieve it (rough I don't have )
>
> ADOQuery.SQL.Add('SELECT FileCol FROM Table WHERE ID = 1');
> ADOQuery.Open;
> ADOQuery.FieldByName('FileCol ').SaveToFile(FileName);
>
> FieldByName('FileCol ') should be a TBlobField
>
> Neven
>
>
>
>   
>> I've struck a complete blank on this.
>> Can anyone show me some code to save and load images from SQL Server
>> using TADOQuery?
>> The methods I've come up with don't appear to work at all...
>>
>> -----Original Message-----
>> From: delphi-bounces at delphi.org.nz
>>     
> [mailto:delphi-bounces at delphi.org.nz]
>   
>> On Behalf Of Neven MacEwan
>> Sent: Friday, 15 August 2008 4:02 p.m.
>> To: NZ Borland Developers Group - Delphi List
>> Subject: Re: [DUG] Images and MS SQL Server
>>
>> Dave
>>
>> Hasn't Tblobfield got the same streaming functions IIRC
>>
>>     
>>> I have worked out how to save an image into an Image field, but I'm
>>> having brain fade on how to retrieve the image... Using TADOQuery,
>>>
>>>       
>> D2005.
>>
>>     
>>> Save:
>>>
>>> ...
>>>
>>> datamod.ADOU.SQL.Add(':Image)') ;
>>>
>>> ms := TMemoryStream.Create ;
>>>
>>> try
>>>
>>> Image.Picture.Bitmap.SaveToStream(ms);
>>>
>>> ms.seek(0,0) ;
>>>
>>> datamod.ADOU.Parameters.ParamByName('Image').LoadFromStream(ms,
>>> ftGraphic);
>>>
>>> finally
>>>
>>> ms.Free ;
>>>
>>> end ;
>>>
>>> datamod.ADOU.ExecSQL ;
>>>
>>> Anyone (I suppose everyone has) got a way to get the image back?
>>>
>>> Cheers,
>>>
>>> Dave.
>>>
>>>
>>>
>>>       
>
------------------------------------------------------------------------
>   
>>> _______________________________________________
>>> NZ Borland Developers Group - Delphi mailing list
>>> Post: delphi at delphi.org.nz
>>> Admin: http://delphi.org.nz/mailman/listinfo/delphi
>>> Unsubscribe: send an email to delphi-request at delphi.org.nz with
>>>
>>>       
>> Subject: unsubscribe
>>
>> _______________________________________________
>> NZ Borland Developers Group - Delphi mailing list
>> Post: delphi at delphi.org.nz
>> Admin: http://delphi.org.nz/mailman/listinfo/delphi
>> Unsubscribe: send an email to delphi-request at delphi.org.nz with
>>     
> Subject:
>   
>> unsubscribe
>>
>> No virus found in this incoming message.
>> Checked by AVG - http://www.avg.com
>> Version: 8.0.138 / Virus Database: 270.6.4/1616 - Release Date:
>> 16/08/2008 5:12 p.m.
>>
>> _______________________________________________
>> NZ Borland Developers Group - Delphi mailing list
>> Post: delphi at delphi.org.nz
>> Admin: http://delphi.org.nz/mailman/listinfo/delphi
>> Unsubscribe: send an email to delphi-request at delphi.org.nz with
>>     
> Subject: unsubscribe
>   
>>
>>     
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz with
Subject:
> unsubscribe
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com
> Version: 8.0.138 / Virus Database: 270.6.4/1617 - Release Date:
> 17/08/2008 12:58 p.m.
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz with
Subject:
> unsubscribe
>
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz with
Subject:
> unsubscribe
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com 
> Version: 8.0.138 / Virus Database: 270.6.4/1617 - Release Date:
> 17/08/2008 12:58 p.m.
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz with
Subject: unsubscribe
>
>
>   

_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: delphi at delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
unsubscribe

No virus found in this incoming message.
Checked by AVG - http://www.avg.com 
Version: 8.0.138 / Virus Database: 270.6.5/1619 - Release Date:
18/08/2008 5:39 p.m.

More information about the Delphi mailing list