Change column data type

Discuss the database features

Change column data type

Postby capemayal1 » Sat Jan 09, 2021 2:03 am

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
Last edited by RusselB on Sat Jan 09, 2021 5:28 am, edited 1 time in total.
Reason: Excessive caps removed
capemayal1
 
Posts: 86
Joined: Fri Dec 31, 2010 5:16 pm

Re: Change column data type

Postby UnklDonald418 » Sat Jan 09, 2021 5:41 am

According to the HyperSQL User Guide "HyperSQL's default character set is Unicode"
so VARCHAR and NVARCHAR are synonymous
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.8 & LibreOffice 6.4.7.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1398
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Change column data type

Postby capemayal1 » Sat Jan 09, 2021 2:51 pm

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.
capemayal1
 
Posts: 86
Joined: Fri Dec 31, 2010 5:16 pm

Re: Change column data type

Postby Villeroy » Sat Jan 09, 2021 3:36 pm

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"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29916
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Change column data type

Postby capemayal1 » Sat Jan 09, 2021 4:53 pm

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.
capemayal1
 
Posts: 86
Joined: Fri Dec 31, 2010 5:16 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 5 guests