[Solved] Can't alter (reduce) text length in column

Discuss the database features

[Solved] Can't alter (reduce) text length in column

Postby AfTech54 » Sat Nov 23, 2019 11:26 am

Not sure why I can't alter text length from 50 to 15 in the ShortName column. I can extend the length, but never reduce it. Each time I try to reduce it I get a warning message. I extended to 100, then I got the message when trying to reduce it to 99.

Couldn't alter the column "ShortName". Should it rather be deleted and the new format attached.

If I copy the table the text length will be adjustable up and down in the new table.

Anyone has any idea what's wrong here?
Attachments
Can't alter text length_2.jpg
Last edited by AfTech54 on Mon Nov 25, 2019 3:27 pm, edited 2 times in total.
Ooo v4.1.2, Windows 10
AfTech54
 
Posts: 38
Joined: Tue Dec 31, 2013 10:08 am

Re: Can't alter(reduce) text length in column

Postby UnklDonald418 » Sun Nov 24, 2019 5:47 am

In my tests I get that error message when I try changing the Entry Required property for a VARCHAR() column to Yes when there are rows in my table that have NULL in that column.
Either change Entry Required to No or put something in the rows with NULL in that column.
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1338
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Can't alter(reduce) text length in column

Postby AfTech54 » Sun Nov 24, 2019 9:01 am

Thanks!
Sorry but I have no columns with null and I get the same message with "No". Two strange things; I can extend the length and I can alter the length in a table copy.
Ooo v4.1.2, Windows 10
AfTech54
 
Posts: 38
Joined: Tue Dec 31, 2013 10:08 am

Re: Can't alter (reduce) text length in column

Postby UnklDonald418 » Sun Nov 24, 2019 6:03 pm

When you attempt to change the length and get the Base error dialog, answer Yes to deleting the the old column and inserting a new one. Base should generate a new error dialog, but this one should display the ALTER TABLE statement that Base used when it attempted to change the table.
Copy that statement and execute it at Tools>SQL. Let HSQL tell you what the problem it has with the statement.

It's quite rare but I recall a few of instances where a table exhibited unusual behavior and the only solution I found was to copy the data into a new table and delete the old one.
In one case I had to create a new database and copy the tables into that.
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1338
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Can't alter (reduce) text length in column

Postby AfTech54 » Sun Nov 24, 2019 9:13 pm

OK and Thanks!

I'll copy the DB and try to solve it as per your recommendation, thanks again.

I don't know why, but if I delete the view "vShortNames_In_Columns", I'm able to alter from 50 to 15 signs. I get the impression that the view has inherited the text lenght from the "Person" table. But I'm a real amateur.

-- Place "Person.ShortName" in order of "PhotoPerson.ID_PhotoPerson" into columns
Select "ID_Photo",

Max(case when POS = 0 then PE."ShortName" end) C0,
Max(case when POS = 1 then PE."ShortName" end) C1,
Max(case when POS = 2 then PE."ShortName" end) C2,
Max(case when POS = 3 then PE."ShortName" end) C3,
Max(case when POS = 4 then PE."ShortName" end) C4,
Max(case when POS = 5 then PE."ShortName" end) C5,
Max(case when POS = 6 then PE."ShortName" end) C6,
Max(case when POS = 7 then PE."ShortName" end) C7,
Max(case when POS = 8 then PE."ShortName" end) C8,
Max(case when POS = 9 then PE."ShortName" end) C9,
Max(case when POS = 10 then PE."ShortName" end) C10,
Max(case when POS = 11 then PE."ShortName" end) C11,
Max(case when POS = 12 then PE."ShortName" end) C12,
Max(case when POS = 13 then PE."ShortName" end) C13,
Max(case when POS = 14 then PE."ShortName" end) C14,
Max(case when POS = 15 then PE."ShortName" end) C15,
Max(case when POS = 16 then PE."ShortName" end) C16,
Max(case when POS = 17 then PE."ShortName" end) C17,
Max(case when POS = 18 then PE."ShortName" end) C18,
Max(case when POS = 19 then PE."ShortName" end) C19
-- Get from sorted table
From
(
Select PO."ID_Photo", PO."ID_Person", PO."Position" POS
From "vPhotoPersonPositionOrder" PO
) A
-- Join to get ShortName
Join "Person" PE
On PE."ID_Person" = A."ID_Person"
-- To get one row for each Photo
Group By "ID_Photo"
Ooo v4.1.2, Windows 10
AfTech54
 
Posts: 38
Joined: Tue Dec 31, 2013 10:08 am

Re: Can't alter (reduce) text length in column

Postby chrisb » Mon Nov 25, 2019 12:26 am

hello AfTech54,

even though the table in question is referenced by a view you should still be able to resize your varchar() column using the GUI.

i recognise the code that you pasted & remember that in the past i was able to help you with your db. i see that you are using the original queries/views.

shortly after our communications i improved the code by integrating the queries & eliminating all views.
once a thread has been marked as solved i am reluctant to offer further support because although rarely perfect the issue has been resolved.

i suggest you download the attachment.
Attachments
AFTechRevisited.odb
(21.77 KiB) Downloaded 37 times
open office 4.1.7 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.1 (Split) on Windows 10
chrisb
 
Posts: 229
Joined: Mon Jun 07, 2010 4:16 pm

Re: Can't alter (reduce) text length in column

Postby UnklDonald418 » Mon Nov 25, 2019 1:36 am

When you followed my earlier instructions and executed the ALTER TABLE statement at TOOL>SQL you should have received an error message something like:

1: Column is referenced in constraint or view: vShortName_In_Columns in statement [ALTER TABLE "Persons" DROP "ShortName"]

Views are stored in the back end so HSQLDB is fully aware of them and apparently considers them equivalent to a constraint.
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1338
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Can't alter (reduce) text length in column

Postby AfTech54 » Mon Nov 25, 2019 8:41 am

Thanks again!

Well the help I got from you in the past has been a great one! I'm not sure that the AfTechRevisited is enough. I'm tagging each person with position so I can determine how names will appear (specific order) in the album reports. Therefore you created the two views for me.
I've documented more than 2000 photos (excl. names, places) so far so I'm afraid of mess the 2.5 Mb db up. The 50 sign text length is no big issue for me more than it scares me that the data might be messed up or the db stops to work in the future. I'm not an expert on Base/DBs.
So I would be grateful could you give me step by step instructions to solve it.
Ooo v4.1.2, Windows 10
AfTech54
 
Posts: 38
Joined: Tue Dec 31, 2013 10:08 am

Re: Can't alter (reduce) text length in column

Postby chrisb » Mon Nov 25, 2019 12:42 pm

when i read your first post i suspected that the table 'Person' was corrupt, your second post confirmed it.
your table 'Person' is referenced by another table via a foreign key & at least one view.
to delete such tables the foreign key constraint must be dropped along with any views.
i use OpenOffice 4.1.7 on windows 10 & am able to resize varchar() fields in tables like yours.
your signature says Ooo v4.1.2. i wonder if this could be the cause of the problem.
AfTech54 said
I don't know why, but if I delete the view "vShortNames_In_Columns", I'm able to alter from 50 to 15.
this is good news & suggests that the issue is constraint related.
i think you have found your own solution. all you need to do now is recreate the view.

i am sorry that i rushed my initial post without thoroughly reading the thread, your db has more tables than the stripped version that i have.
if you drag & drop the queries from my upload into your db they may still execute, it's a professional solution to the initial issue.

make the effort to update OO & if you have not already done so split your db.
if you have a specific issue & provide adequate info then i am certain that i or others will be willing & able help.
open office 4.1.7 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.1 (Split) on Windows 10
chrisb
 
Posts: 229
Joined: Mon Jun 07, 2010 4:16 pm

Re: Can't alter (reduce) text length in column

Postby AfTech54 » Mon Nov 25, 2019 1:22 pm

Thanks chrisb!

Didn't help to update ooo.

    I deleted the view.
    Altered text length (works)
    Recreated the view
    DB seems to work

I can upload the db to Goggle drive if you want to look into it and then give you access, but in that case I need your email address.
Ooo v4.1.2, Windows 10
AfTech54
 
Posts: 38
Joined: Tue Dec 31, 2013 10:08 am

Re: Can't alter (reduce) text length in column

Postby chrisb » Mon Nov 25, 2019 3:18 pm

AfTech54,
i am sorry but i can not collaborate with you on a personal basis any info supplied must be accessible to all users.
this forum is for the benefit of the community. if the community is excluded then there is no benefit.

if your issue is not related to this topic then please start a new thread.
be clear & specific about your aims & issues, show us what you have.
if possible upload an embedded stripped down version of your db. do not upload personal info.
everyone is willing to help within the ethics which govern this forum.
open office 4.1.7 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.1 (Split) on Windows 10
chrisb
 
Posts: 229
Joined: Mon Jun 07, 2010 4:16 pm

Re: Can't alter (reduce) text length in column

Postby AfTech54 » Mon Nov 25, 2019 3:25 pm

I'm really sorry, I didn't understand that all info has to pass the forum.
As I worked around the problem I can wait with that until I run in to another issue. I'll try to add "Solved" in the header.
Ooo v4.1.2, Windows 10
AfTech54
 
Posts: 38
Joined: Tue Dec 31, 2013 10:08 am


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests