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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
ITugayCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.