Arrays in SQLQuery
lazarus / 64-FPC development

----------------------------------------------------------------------
edgarrod71 · 2020-11-02 22:55

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:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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 aWHERE a.language_req IS NOT NULL When I try
to get the fields, I get this:
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i JOB_CODEJOB
_GRADEJOB_COUNTRYLANGUAGE_REQLANGUAGE_REQ_1LANGUAGE_REQ_2LANGUAGE_REQ_
3LANGUAGE_REQ_4according 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:
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i JOB_CODEJOB
_GRADEJOB_COUNTRYLANGUAGE_REQ_1LANGUAGE_REQ_2LANGUAGE_REQ_3LANGUAGE_RE
Q_4LANGUAGE_REQ_5I can select and I can post using this on the
UpdateSQL,
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i UPDATE JOB
aSET    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_4WHERE    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:
Code: Diff  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
IBConnection1:PrepareStatement:-Dynamic SQL Error-SQL error code =
-104-Token unknown - line 3, column 19-[address 101FB3EFI tried also
to update using in UpdateSQL:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i UPDATE JOB
aSET  a.language_req = :lang1WHERE    a.JOB_CODE = :JOB_CODE AND   
a.JOB_GRADE = :JOB_GRADE AND    a.JOB_COUNTRY = :JOB_COUNTRY  an array
in Pascal like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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?

----------------------------------------------------------------------
rvk · 2020-11-03 10:42

I'm not sure SQLdb (or even IBX) supports ARRAYs.

But besides that... are you sure you want to use arrays in your DB-
design?
My advise... don't. You are better off normalizing your DB-design
correctly.

https://www.ibexpert.net/ibe/pmwiki.php?n=Doc.Array

What happens if language_req[3] needs to become empty. You can't set
it to 0 and you can't shift all the language after that to an index
lower. If you put the language->job connection in a separate table
(normalizing your tables) the have better control with this.

----------------------------------------------------------------------
dsiders · 2020-11-03 16:17

Quote from: rvk on November 03, 2020, 11:42:57 amI'm not sure SQLdb
(or even IBX) supports ARRAYs.

IBX does support arrays, includes a TIBArrayField type, and has demos
showing their usage.

Quote from: rvk on November 03, 2020, 11:42:57 amBut besides that...
are you sure you want to use arrays in your DB-design?
My advise... don't. You are better off normalizing your DB-design
correctly.

Can't really argue with that... unless you're doing warehousing where
they provided a de-normalized view for quicker access.