Append data into Interbase Table

Dear Experts, please help !
Create Table Temperature ( Name Char(10), Temp_Array SmallInt  [24,366] )

This table is intended to hold 'hourly temperatures for twenty cities for a year', but I
couldn't  append data into Temp_Array column, please give me detail solution.

Thanks from apin.
pc_melsaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ITugayConnect With a Mentor Commented:
Hi pc_melsa!

It may be not best solution, but I do it like this:

let suppose we have type:
    TTemp_Array = array[1..24,1..366] of SmallInt;
and Interbase table:
    create table temperature (name varchar(10),temp_array blob)

At first, we need procedures to pack and unpack TTemp_Array to blob field. (Look at TBlobFiled.Value property).

// convert TTemp_Array to string
function TempArrayToStr(Buf : pointer) : string;
var I : integer;
begin
  result:='';
  for I:=0 to SizeOf(TTemp_Array)-1 do
  result:=result+PChar(Buf)[I];
end;

// convert string to TTemp_Array
procedure StrToTempArray(const S : string; Buf : pointer);
var I : integer;
begin
  for I:=0 to SizeOf(TTemp_Array)-1 do
  PChar(Buf)[I]:=S[I+1];
end;

Now, you can operate with TTemp_Array.

var A : TTemp_Array;
     Q : TQuery;
begin    
    Q:=TQuery.Create(nil);
    Q.DatabaseName:='YOUR_DB_NAME';
    Q.RequestLive:=true;
    Q.SQL.Add('select * from TEMPERATURE');
    Q.Open;
.....
    //do something with array
    A[3,25]:=36;
    A[4,25]:=37;
.....
    // insert into table  
    Q.Insert;
    Q.Fields[0].AsString:='ANY_NAME'; // assign name
    (Q.Fields[1] as TBlobField).Value:=TempArrayToStr(@A); // assign array
    Q.Post;
.....
    // retrieve data from table
    Q.Last;
    StrToTempArray((Q.Fields[1] as TBlobField).Value,@A);
    // array assigned from blob field
.....


That's all.
Need some explanation?

Best regard,
Igor.
0
 
ITugayCommented:
Hi pc_melsa.

Do you realy need to manipulate with array of SmallInt in Interbase procedures or triggers? If it's not, then more easy to declare blob field and keep there normal Delphi array.

When you declare field as array[xx,yy], you will need to do something like this:

EXEC SQL
BEGIN DECLARE SECTION;
BASED ON TEMPERATURE.TEMP_ARRAY Temp_Array;
EXEC SQL
END DECLARE SECTION;

....do somethig with array....

EXEC SQL
INSERT INTO TEMPERATURE (NAME,TEMP_ARRAY)
VALUES ("some_name", :Temp_Array);

EXEC SQL
COMMIT;
0
 
pc_melsaAuthor Commented:
Dear Mr. ITugay,
    Thanks for your sugestion, if the blob field can solve the same problem, please tell me how to do it, and please give me example how to append, update and retrieve the data.

Thanks from apin.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
kretzschmarCommented:
hmm pc_melsa,

the answer will cause,
that only the application will be able to edit/retrieve
the array-values

i would recommend to forget the array
and store the values in a record with a structure like

name
date
hour

with primary key over all three fields or adding a sequence-number as id like

id
name
date
hour

this will cause in one record
per name per day per hour and is much easier to handle and will save space by none leap-years
additional based on this structure are third-party products
(like Tquickreport, or TDBChart, or TDecisioncube) able to retrieve and calculate with this records

just a suggestion, igors will work also

meikl
0
 
pc_melsaAuthor Commented:
Dear Mr. Igors
    Thanks for your answers, may God Bless you.

Best Regards,
apin
0
 
pc_melsaAuthor Commented:
Dear Mr. Meikl,
   Thanks for your suggestion, may God Bless you.

Best Regards,
apin
0
All Courses

From novice to tech pro — start learning today.