Recent

Author Topic: Arrays in SQLQuery  (Read 1859 times)

edgarrod71

  • Jr. Member
  • **
  • Posts: 68
Arrays in SQLQuery
« on: November 02, 2020, 11:55:15 pm »
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][+][-]
  1. SELECT a.JOB_CODE, a.JOB_GRADE, a.JOB_COUNTRY,
  2.     a.language_req[1],
  3.     a.language_req[2],
  4.     a.language_req[3],
  5.     a.language_req[4],
  6.     a.language_req[5]
  7. FROM JOB a
  8. WHERE a.language_req IS NOT NULL
  9.  
When I try to get the fields, I get this:
Code: Text  [Select][+][-]
  1. JOB_CODE
  2. JOB_GRADE
  3. JOB_COUNTRY
  4. LANGUAGE_REQ
  5. LANGUAGE_REQ_1
  6. LANGUAGE_REQ_2
  7. LANGUAGE_REQ_3
  8. 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:
Code: Text  [Select][+][-]
  1. JOB_CODE
  2. JOB_GRADE
  3. JOB_COUNTRY
  4. LANGUAGE_REQ_1
  5. LANGUAGE_REQ_2
  6. LANGUAGE_REQ_3
  7. LANGUAGE_REQ_4
  8. LANGUAGE_REQ_5
I can select and I can post using this on the UpdateSQL,
Code: SQL  [Select][+][-]
  1. UPDATE JOB a
  2. SET
  3.     a.LANGUAGE_REQ[1] = :LANGUAGE_REQ,
  4.     a.LANGUAGE_REQ[2] = :LANGUAGE_REQ_1,
  5.     a.LANGUAGE_REQ[3] = :LANGUAGE_REQ_2,
  6.     a.LANGUAGE_REQ[4] = :LANGUAGE_REQ_3,
  7.     a.LANGUAGE_REQ[5] = :LANGUAGE_REQ_4
  8. WHERE
  9.     a.JOB_CODE = :JOB_CODE AND
  10.     a.JOB_GRADE = :JOB_GRADE AND
  11.     a.JOB_COUNTRY = :JOB_COUNTRY
but when I try to Update with SQLQuery1.ApplyUpdates it gives me this error:
Code: Diff  [Select][+][-]
  1. IBConnection1:PrepareStatement:
  2. -Dynamic SQL Error
  3. -SQL error code = -104
  4. -Token unknown - line 3, column 19
  5. -[
  6. address 101FB3EF
I tried also to update using in UpdateSQL:
Code: SQL  [Select][+][-]
  1. UPDATE JOB a
  2. SET
  3.   a.language_req = :lang1
  4. WHERE
  5.     a.JOB_CODE = :JOB_CODE AND
  6.     a.JOB_GRADE = :JOB_GRADE AND
  7.     a.JOB_COUNTRY = :JOB_COUNTRY
  8.  
an array in Pascal like this:
Code: Pascal  [Select][+][-]
  1. var
  2.   arr: array [1..5] of string[15];
  3.   .
  4.   .
  5.   .
  6. procedure TForm1.SQLQuery1BeforePost(DataSet: TDataSet);
  7. begin  // I put a param in SQLQuery1
  8.   SQLQuery1.Params[0].Value:= @arr;
  9. end;
  10.  

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

  • Hero Member
  • *****
  • Posts: 6110
Re: Arrays in SQLQuery
« Reply #1 on: November 03, 2020, 11:42:57 am »
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

  • Hero Member
  • *****
  • Posts: 1052
Re: Arrays in SQLQuery
« Reply #2 on: November 03, 2020, 05:17:34 pm »
I'm not sure SQLdb (or even IBX) supports ARRAYs.

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

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.

Can't really argue with that... unless you're doing warehousing where they provided a de-normalized view for quicker access.
Preview Lazarus 3.99 documentation at: https://dsiders.gitlab.io/lazdocsnext

 

TinyPortal © 2005-2018