Board index » delphi » Updating BLOB fields via SQL

Updating BLOB fields via SQL

My application generates SQL statements and submits the query to the
server.
I also would like to store the generated query (including start and end
time) for monitoring.

My problem is that I have no clue how to get a TStringList into a blob or
memo field using normal SQL.
Although sometimes it is suggested to be possible as Mr.Borland writes:
Q: Can BLObs be used as input parameters to queries and stored procedures?
A: Yes. Before BDE 4.0, no BLOb could be used as an input parameter.

Is it me or is there very little information available about BLOBs and
STREAMs ?

The best I can come up with is assigning an SQL parameter via the
statement:
params[?].AsString  := copy(objSelection.FStatsQuery.Text,1,255);

but this gives me only the first 255 characters of the TStringList.
All my other attempts have failed.

Does anybody have an idea ?

Thanks,
- Peter -

 

Re:Updating BLOB fields via SQL


Quote
Peter Hartman wrote:

> My application generates SQL statements and submits the query to the
> server.
> I also would like to store the generated query (including start and end
> time) for monitoring.

> My problem is that I have no clue how to get a TStringList into a blob or
> memo field using normal SQL.
> Although sometimes it is suggested to be possible as Mr.Borland writes:
> Q: Can BLObs be used as input parameters to queries and stored procedures?
> A: Yes. Before BDE 4.0, no BLOb could be used as an input parameter.

> Is it me or is there very little information available about BLOBs and
> STREAMs ?

> The best I can come up with is assigning an SQL parameter via the
> statement:
> params[?].AsString  := copy(objSelection.FStatsQuery.Text,1,255);

> but this gives me only the first 255 characters of the TStringList.
> All my other attempts have failed.

> Does anybody have an idea ?

> Thanks,
> - Peter -

Have you tried this:

procedure UpdateBlob(strs: TStringList);
var q: TQuery;
begin
  q := TQuery.Create(Self);
  q.DataBaseName := 'YourAlis';

  q.sql.Add('update YourTable Set AField = ' + strs.text);
  q.sql.Add('where AUniqueField = ' IntToStr(SomeUniqueValue));
  q.ExecSQL;
  q.Free;
end

--
J?rgen Aase    
Schleppegrellsgt 2B
0556 OSLO
22 71 44 28

Re:Updating BLOB fields via SQL


Quote
J?rgen Aase wrote:

> Peter Hartman wrote:

> > My application generates SQL statements and submits the query to the
> > server.
> > I also would like to store the generated query (including start and end
> > time) for monitoring.

> > My problem is that I have no clue how to get a TStringList into a blob or
> > memo field using normal SQL.
> > Although sometimes it is suggested to be possible as Mr.Borland writes:
> > Q: Can BLObs be used as input parameters to queries and stored procedures?

BLOBS are not standard SQL so, there is no legal statement to get a blob
int a db (correct me if I'm wrong).

You need a 'direct channel' to the corresponding cell. On delphi you get
such a 'channel' with live queries.

Query.Add('select BLOB_FIELD, ... from TABLEX where  ...');
Query.RequestLive := True;
Query.Open;
Query.First;
Query.Edit // Append, Insert..
Query.FieldByName('BLOB_FIELD').Assign(AStringList);
//or Query.FieldByName('BLOB_FIELD').Assign(Memo.Lines);
Query.Post;
Query.CLose;

got it!

Marco

Other Threads