Array as binary data into database-table

Hi,

an array  (x: array[0..100, 0..3] of word) shall be part of every record in a table of a databese (access, MSSQL-Server, MySQL). Obviously I cannot define each element as an indepandant field in the table.
It should be possible to write the whole array as a binary stream (into a memo- or blob-field) into the table. But how to do this ?

Thank you,
K.-P. Becker
KPBeckerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
BlackTigerXConnect With a Mentor Commented:
I knew I was missing something... the Seek

here's code to write:

var
  aStream:TMemoryStream;
  arr: array[0..100, 0..3] of word;
  X, Y:Integer;
begin
  ADOQuery1.SQL.Text:='insert into Table1 '+
    '(Field1, Field2, dataField) '+
    'values (:Field1, :Field2, :dataField)';

  for X:=0 to 100 do
    for Y:=0 to 3 do
      arr[X, Y]:=X;

  ADOQuery1.Parameters.ParamByName('Field1').Value:='value1';
  ADOQuery1.Parameters.ParamByName('Field2').Value:='value2';
  aStream:=TMemoryStream.Create;
  aStream.Write(arr[0, 0], 101*4*SizeOf(Word));
  aStream.Seek(0, soFromBeginning);
  ADOQuery1.Parameters.ParamByName('dataField').LoadFromStream(aStream, ftBlob);
  aStream.Free;
  ADOQuery1.ExecSQL;
end;

and code to read:

var
  aStream:TMemoryStream;
  arr: array[0..100, 0..3] of word;
  X, Y:Integer;
  str:string;
begin
  ADOQuery1.SQL.Text:='select top 1 Field1, Field2, datafield from Table1 '+
    'where ID>4 order by ID desc';
  ADOQuery1.Open;

  for X:=0 to 100 do
    for Y:=0 to 3 do
      arr[X, Y]:=0;

  aStream:=TMemoryStream.Create;
  TBlobField(ADOQuery1.FieldByName('dataField')).SaveToStream(aStream);
  aStream.Seek(0, soFromBeginning);
  aStream.ReadBuffer(arr[0, 0], 101*4*SizeOf(Word));
  aStream.Free;

  str:='';
  for X:=0 to 100 do
    for Y:=0 to 3 do
      str:=str+IntToStr(arr[X, Y])+',';
  ShowMessage(str)
end;
0
 
BlackTigerXCommented:
something like:

var
  aStream:TMemoryStream;
  x: array[0..100, 0..3] of word;
  P:Pointer;
begin
  ADOQuery1.SQL.Text:='insert into Table1 '+
    '(Field1, Field2, dataField) '+
    'values (:Field1, :Field2, :dataField)';

  ADOQuery1.Parameters.ParamByName('Field1').Value:='value1';
  ADOQuery1.Parameters.ParamByName('Field2').Value:='value2';
  aStream:=TMemoryStream.Create;
  P:=@x;
  aStream.WriteBuffer(P, 101*4*2);
  ADOQuery1.Parameters.ParamByName('dataField').LoadFromStream(aStream, ftBlob);
  aStream.Free;
  ADOQuery1.ExecSQL;
end;
0
 
BlackTigerXCommented:
the dataField is defined as a BlobField (OLE Object in ACCESS)
0
 
KPBeckerAuthor Commented:
Thanks to BlackTigerX !

KPB
0
All Courses

From novice to tech pro — start learning today.