Fields seem not to be ok when you use arrays in firebird database using a TSQLQuery component.
For instance:
I use IBConnection, SQLTransaction, and Firebird DatabaseName=localhost:employee, in SQL property of TSQLQuery I put:
SELECT a.JOB_CODE, a.JOB_GRADE, a.JOB_COUNTRY,
a.language_req[1],
a.language_req[2],
a.language_req[3],
a.language_req[4],
a.language_req[5]
FROM JOB a
WHERE a.language_req IS NOT NULL
When I try to get the fields, I get this:
JOB_CODE
JOB_GRADE
JOB_COUNTRY
LANGUAGE_REQ
LANGUAGE_REQ_1
LANGUAGE_REQ_2
LANGUAGE_REQ_3
LANGUAGE_REQ_4
according with the FB and Interbase documentation on arrays we could conclude that both a.language_req and a.language_req_1 are different, because a.language_req is an array type field and pascal interprets a.language_req_1 as a string field, because on the definition is a varchar type field, so these fields ought to be:
JOB_CODE
JOB_GRADE
JOB_COUNTRY
LANGUAGE_REQ_1
LANGUAGE_REQ_2
LANGUAGE_REQ_3
LANGUAGE_REQ_4
LANGUAGE_REQ_5
I can select and I can post using this on the UpdateSQL,
UPDATE JOB a
SET
a.LANGUAGE_REQ[1] = :LANGUAGE_REQ,
a.LANGUAGE_REQ[2] = :LANGUAGE_REQ_1,
a.LANGUAGE_REQ[3] = :LANGUAGE_REQ_2,
a.LANGUAGE_REQ[4] = :LANGUAGE_REQ_3,
a.LANGUAGE_REQ[5] = :LANGUAGE_REQ_4
WHERE
a.JOB_CODE = :JOB_CODE AND
a.JOB_GRADE = :JOB_GRADE AND
a.JOB_COUNTRY = :JOB_COUNTRY
but when I try to Update with SQLQuery1.ApplyUpdates it gives me this error:
IBConnection1:PrepareStatement:
-Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 3, column 19
-[
address 101FB3EF
I tried also to update using in UpdateSQL:
UPDATE JOB a
SET
a.language_req = :lang1
WHERE
a.JOB_CODE = :JOB_CODE AND
a.JOB_GRADE = :JOB_GRADE AND
a.JOB_COUNTRY = :JOB_COUNTRY
an array in Pascal like this:
var
arr: array [1..5] of string[15];
.
.
.
procedure TForm1.SQLQuery1BeforePost(DataSet: TDataSet);
begin // I put a param in SQLQuery1
SQLQuery1.Params[0].Value:= @arr;
end;
and of course it didn't work either.
I would like to work with arrays in Firebird, inserting, updating, what components I should use to work with? How could I fix the components involved?