Forum > FPC development

Arrays in SQLQuery

(1/1)

edgarrod71:
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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---JOB_CODEJOB_GRADEJOB_COUNTRYLANGUAGE_REQ_1LANGUAGE_REQ_2LANGUAGE_REQ_3LANGUAGE_REQ_4LANGUAGE_REQ_5I can select and I can post using this on the UpdateSQL,

--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:
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:

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

--- End quote ---

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


--- Quote from: rvk on November 03, 2020, 11:42:57 am ---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.

--- End quote ---

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

Navigation

[0] Message Index

Go to full version