Page 1 of 1

Change column data type

PostPosted: Sat Jan 09, 2021 2:03 am
by capemayal1
OS = Ubuntu 16.04
HSQLDB 2.4.1
LO Version: 5.1.6.2

I have a table (newones) with text fields. I am making parameter input query - LIKE '%' || :whoname || '%'
Column name is "jones"

1) when creating the table in design mode, I select data type NVARCHAR. When I save the table the column is changed to VARCHAR.
2) If I do sql tool ALTER TABLE "newones" ALTER COLUMN "jones" NVARCHAR(50); The length will change from 100 to 50 but, the type stays varchar.
3) I have tried every statement I can find without success.
4) The thing that puzzles me is why does the table when saved change the NVARCHAR to VARCHAR

Thanks in Advance.
Al

Re: Change column data type

PostPosted: Sat Jan 09, 2021 5:41 am
by UnklDonald418
According to the HyperSQL User Guide "HyperSQL's default character set is Unicode"
so VARCHAR and NVARCHAR are synonymous

Re: Change column data type

PostPosted: Sat Jan 09, 2021 2:51 pm
by capemayal1
Thanks UnkDonal418.
I've read that also. But, it just doesn't work. If I don't type upper case letters on a parameter query, the records will not be found. I set up a dummy DB and have the same results.

Re: Change column data type

PostPosted: Sat Jan 09, 2021 3:36 pm
by Villeroy
I just tried this with HSQL 2.5.1 and the old-style keyword VARCHAR_IGNORECASE:
Code: Select all   Expand viewCollapse view
ALTER TABLE "Articles" ALTER COLUMN "Name" VARCHAR_IGNORECASE

menu:Tools>Refresh Tables
Now the table designer GUI stills shows "Text [VARCHAR]" for this column but:
Code: Select all   Expand viewCollapse view
SELECT * FROM "Articles" WHERE "Name" = 'peanuts'

shows the record where the name is "Peanuts"

Re: Change column data type

PostPosted: Sat Jan 09, 2021 4:53 pm
by capemayal1
Thanks Villeroy, That does work. The only problem I do have when the form opens, the input query the iis run but the form stays in the background after the input box is clicked OK.