[Solved] LibreOffice doesn't accept ARRAY data type

[BSP]

Dear all,

I downloaded Verbos which seems to be an ARRAY data typed DB.
I copied the only table to a split database and succeeded to replace spaces through comma's.
Now,. the challenge is, to change the column data definitions to the ARRAY data type.
It seems to be impossible to make changes to the old table (definition), so I made a similar table from scratch using the first three columns (to keep it simple).
The third column being of the ARRAY Type.

I created the copy-table as follows):

Drop table "French3" if exists;
Create table "French3"
(
"Verbe" varchar (40),
"Description" varchar (40),
"Indicative" varchar(255) ARRAY [6]
);

Now,.. looking at the results,.. libre office has turned the ARRAY data type into a BLOB data type ?

Also I couln't insert the original data into this third column of the new table..

Thank you for having a look at this,

Kind regards,

BS

[Villeroy]

You are right. Base is just a tiny but powerful addition to an office suite. It is not a database development suite. You have to split the 6 or 7 elements of "Indicative" varchar(255) ARRAY [6] into separate values or use a related table instead of the array.
What I don't understand is: Verbos is based on HSQL and its latest version is of 2007. At that time HSQL did not support any array types.
verbsDB.properties:

Code: Select all

#HSQL Database Engine 1.8.0.7

[BSP]

Dear Villeroy,..

may be the ARRAY's didn't exist within HSQL 2007, however,.. the table "FRENCH" shows multiple values in one column,.. like an array ?

Anothet question,.. if arrays are currently part of HSQL,.. why can't I make a table based on this data type ?

Regards,,
BS

[Villeroy]

OpenOffice is a dead project. Since 2010 nobody has developed anything new for the Base component. The HSQL 1.8 shipped with OpenOffice is the same as the one in Verbos.
LibreOffice is the vibrant successor to OpenOffice but they made a mistake. Instead of adjusting Base to HSQL2, they tried to make Firebird the new default engine for LibreOffice Base. Wasted years of development power and the HSQL that is shipped with LibreOffice is still the same version 1.8.
It is fairly easy to use Base with a stand-alone HSQL2, with MySQL, MariaDB, PostgreSQL and many others. HSQL2 supports arrays.

[BSP]

Villeroy, thank you for paying attention.

* I'm using Libre Office.
* I was using HSQLDB version 2.5.1 (and upgraded to version 2.6 today).

Speculating on good luck, I also tried to set up the table French3 through the HyperSQL Database manager Swing), which is part of the HSQL package.
Obviously, this application accepts the ARRAY Type:

Drop table "French3" if exists;
Create table "French3" (
"Verbe" varchar (40),
"Description" varchar (40),
"Indicative" varchar (255) ARRAY [1]
);

However,.. it does'nt accept insertion of array data:

INSERT INTO "French3"
VALUES
(
'abaisser',
'To depress, lower',
ARRAY['abaisse','abaisses','abaisse','abaissons','abaissez','abaissent']
)

SQL Error: data exception: array data, right truncation / Error Code: -3491 / State: 2202F

I regret this since the Verbos DB seems to match the Array data type (6 conjugations per verb) in a nice way.

Profesionally I'm trying to automate my workflow (I'm a finance broker) and hoped that arrays could be a nice format to contain interest rates as a ledger.

I'd welcome anyone who knows how to crack this mystery: where/ how may the array format be tested ?

Thank you and kind regards,

BSP

[Villeroy]

Verbos and HSQL1.8 do not support arrays. Base does not care of arrays. Either you split the array in single values, so Base can display them at least or you abstain from arrays and use a many-to-many relation instead of arrays.

[Sliderule]

I suggest you try the following to create your table, since you want to create an ARRAY as part of your table "French3".

Code: Select all

CREATE CACHED TABLE "French3" (
   "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL PRIMARY KEY,
   "Verbe" varchar (40),
   "Description" varchar (40),
   "Indicative" varchar (255) ARRAY [6]
);

Add a record . . . use the following SQL, and, NOTE that "ID" is NOT a part of the INSERT statement:

Code: Select all

INSERT INTO "French3" ("Verbe", "Description", "Indicative")
VALUES
(
   'abaisser',
   'To depress, lower',
   ARRAY['abaisse','abaisses','abaisse','abaissons','abaissez','abaissent']
);

To see the contents of your table . . . use the following SQL - NOTE: Since LibreOffice / OpenOffice Base Parser will NOT accept ARRAY . . . must run with Run SQL Command Directly :

1. Code: Select all

   Select 
      "ID", 
      "Verbe", 
      "Description", 
      CONCAT_WS(', ',TRIM("Indicative"[1]), TRIM("Indicative"[2]), TRIM("Indicative"[3]), TRIM("Indicative"[4]), TRIM("Indicative"[5]), TRIM("Indicative"[6])) as "Indicative",
      "Indicative"[1] as "Indicative_01", 
      "Indicative"[2] as "Indicative_02", 
      "Indicative"[3] as "Indicative_03", 
      "Indicative"[4] as "Indicative_04", 
      "Indicative"[5] as "Indicative_05", 
      "Indicative"[6] as "Indicative_06"
   From "French3";
   

   OR

2. Code: Select all

   Select 
      "ID", 
      "Verbe", 
      "Description", 
      "Indicative"[1] as "Indicative_01", 
      "Indicative"[2] as "Indicative_02", 
      "Indicative"[3] as "Indicative_03", 
      "Indicative"[4] as "Indicative_04", 
      "Indicative"[5] as "Indicative_05", 
      "Indicative"[6] as "Indicative_06"
   From "French3"
   

Explanation:

1. In the original CREATE CACHED TABLE, you need a PRIMARY KEY . . . and . . . I am suggesting the use of "ID" as a GENERATED VALUE which must be NOT NULL.
2. The declaration of an ARRAY should either be as:
   1. ARRAY []
   2. ARRAY [6]
   3. It should NOT BE ARRAY [1] since you want to have 6 in the array

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[BSP]

PS
I succeeded to copy the first two columns from the original table "FRENCH" to the new cached table including the array [6] column "Indicative"

As I tried to copy the third column "INDICATIVE" to the new table I received a warning: cardinality violation.

I tried to copy the third column (originaly a space separated string containing 6 values) to the new array [6] column through the following statement:

Insert into "French3" ("Indicative")
values
(
select
"FRENCH"."VERB",
"French3"."Verbe",
regexp_substring_array ("FRENCH"."INDICATIVE", '\p{Alnum}*[^ ]' )
from "FRENCH", "French3"
where "French3"."Verbe"="FRENCH"."VERB"
)

The query by itself seems to work all right ?

What's going on ?

Kind regards,

BSP

[Sliderule]

Frankly, I am not sure I know what you are doing "Indicative" and "INDICATIVE", and, I suspect nor does the database backend ( HSQL 2.6.0 ).

The latest INSERT statement you wrote above, indicates you want to Add a new record, not to change the contents of an existing record.

Furthermore, the VALUES clause results in 3 columns whereas Insert into "French3" ("Indicative") one column only.

Bottom line, I do not understand what you want to do, nor, why, so perhaps someone else will be able to answer for you.

[BSP]

Dear Sliderule,..

I already copied col1 and col2 from original table ("FRENCH") to new table (as you recommended ("French3").
Now I am trying to copy the third column from original table ("FRENCH"."INDICATIVE") to new table ("French3"."Indicative"), using both first columns for "Where-IS" condition.

You are right,.. I don't want to add new records,.. they are there already (COL1 and Col2).
I juist want to copy values from third column (6 x space separated values) original table, to third column new table (array [6]).

If you like to, I can send over DB.

Thank you,

BSP

[Sliderule]

Since you want to "copy values" the required statement is UPDATE . . . and . . . that is something you will have to write.

[BSP]

Dear Sliderule and colleagues,

following your advice I elaborated the "UPDATE" instruction.

Working:
Update "French3"
Set "Indicative" =
ARRAY ['abaisse','abaisses','abaisse','abaissons','abaissez','abaissent']
Where "French3"."Verbe"= 'abaisser'

Not working (v.1): ( data exception: array data, right truncation)
Update "French3"
Set "Indicative" =
select
regexp_substring_array ("FRENCH"."INDICATIVE", '\p{Alnum}*[^ ]' )
from "FRENCH"
where "French3"."Verbe"="FRENCH"."VERB"

Not working (v.2): ( cardinality violation)
Update "French3"
Set "Indicative" =
select
regexp_substring_array ("FRENCH"."INDICATIVE", '\p{Alnum}*[^ ]' )
from "FRENCH","French3"
where "French3"."Verbe"="FRENCH"."VERB"

Not working (v.3): ( cardinality violation)
MERGE INTO "French3" USING "FRENCH"
ON "French3"."Verbe" = "FRENCH"."VERB"
WHEN MATCHED THEN
UPDATE SET
"French3"."Indicative" = regexp_substring_array ("FRENCH"."INDICATIVE", '\p{Alnum}*[^ ]' )

I'd happy to share this little DB.

Thank you all for helping me out and kind regards,

BSP

PS
I also posted this queston on the Source Forge forum (HSQLDB), since my question rather seems to be related to HSQLDB than Libre Office/ Open Office Base