[Solved] Form Error Updating Current Record Right Truncation

Creating and using forms
Post Reply
Mickey12
Posts: 75
Joined: Thu Apr 16, 2020 3:07 pm

[Solved] Form Error Updating Current Record Right Truncation

Post by Mickey12 »

Hi I get this error when entering text in a form
Is there a way to allow unlimited text entry in a form?
My guess is I have exceeded some limit on how much text I can enter
SQL Status: 22001
Error code: -3401
data exception: string data, right truncation
There is an old post about this but it is to do with decimals
Thanks
Last edited by Mickey12 on Sat May 09, 2020 12:48 pm, edited 1 time in total.
Open Office 4.1.7 on Windows 10
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Form Error Updating Current Record Right Truncation

Post by robleyd »

Is this a VARCHAR column? If so, what size have you defined it as, and how long is the string you are trying to enter?

If not, please provide a bit more information.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Mickey12
Posts: 75
Joined: Thu Apr 16, 2020 3:07 pm

Re: Form Error Updating Current Record Right Truncation

Post by Mickey12 »

Hi Yes
Text[VARCHAR]
Length 32

I am entering comments. Could be a few words or a few paragraphs.
Split dBase so I can only edit in SQL
Open Office 4.1.7 on Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Form Error Updating Current Record Right Truncation

Post by UnklDonald418 »

Try increasing the size with an SQL statement like:

Code: Select all

ALTER TABLE "YourTableName" ALTER COLUMN "YourColumnName" VARCHAR(1000);
if you need more much more than 3000 characters then use

Code: Select all

ALTER TABLE "YourTableName" ALTER COLUMN "YourColumnName" CLOB;
That will allow you to enter a book.
Don't forget to select View>Refresh Tables afterward
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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Mickey12
Posts: 75
Joined: Thu Apr 16, 2020 3:07 pm

Re: Form Error Updating Current Record Right Truncation

Post by Mickey12 »

Thanks. Running that I get dependant object exists. I deleted relationships and tried again. Still get dependant object exists.
Open Office 4.1.7 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Form Error Updating Current Record Right Truncation

Post by Villeroy »

The form control also has its own limit which is independent from the table's column limit. Open the form for editing and adjust the "Max. text length" property of the multi-line text box. A value of 0 seems to remove the limit.
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: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Form Error Updating Current Record Right Truncation

Post by Villeroy »

This is the full error message when I try to convert the column into a CLOB:
1: dependent objects exist: PUBLIC."ExportView" in statement [alter table "Clients" alter column "Comments" CLOB]
and it mentions the dependent object cliearly.

Code: Select all

DROP TABLE "ExportView";
ALTER TABLE "Clients" ALTER COLUMN "Comments" CLOB
Then rebuild "ExportView" from query "qExport" which I saved in the Base document for purposes like this one. Just in case that rebuilding fails for some unknown reason (it failed without error message here on my machine), restart the office suite and try again. Base is a bitch whereas HSQL is a Golden Retriever unless you jail that dog in a Base cage as an "embedded HSQLDB".
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
Mickey12
Posts: 75
Joined: Thu Apr 16, 2020 3:07 pm

Re: Solved Form Error Updating Current Record Right Truncati

Post by Mickey12 »

Thanks everyone for the great help. All good now.
I am learning valuable skills here.
Open Office 4.1.7 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Solved Form Error Updating Current Record Right Truncati

Post by Villeroy »

You are working with 2 independent software products simultaniously. You are editing a HSQLDB through OpenOffice Base. The database driver executing database commands is the hsqldb.jar. The software sending commands to the engine and getting row sets in return is Base. The database driver does not see who or what sends the commands. It has no "notion" of your queries, forms and reports. It just takes commands and returns data, error messages sometimes. This is why some queries need to be stored as views. HSQL is aware of views and takes care that you do not change/rename any tables or columns that are referenced by a view. There is no tight connection between the 2 software components other than the JDBC standard which is why you won't get any more details about errors. You have to read and understand the scarce info from the error messages sent back from the database engine.
When you copy your view "ExportView" and paste it onto a table with option "append data", Base sends a command like:
INSERT INTO "Export" ( list, of, export, columns ) (SELECT list, of, view, columns FROM "ExportView")
When you store a modified record, Base sends an UPDATE ... WHERE "Primary Key"=X command
When you store a new record, Base sends an INSERT ... command.
When you delete a record, Base sends a DELETE ... WHERE "Primary Key"=X command.
This is the reason why Base can not edit any table without primary key. The PK is needed for the exact record position. And this is the reason why editable form records must refer to a single table including its primary key. We can not edit related tables in one form. But forms allow us to emulate relations by means of subforms so every (sub-)form includes a record set from one table.
Same issue with any other database engine connected to Base. Until version 2.0 OpenOffice was not able to create any relational database from scratch. It could connect to existing relational databases built with other tools. The only databases OpenOffice could build were file based (non-relational) pseudo-databases such as spreadsheet connections and dBase. The Base component came from popular demands for something "similar to MS Access". Access is a huge multi-million dollar product. The Base component is a tiny addition of some 30 MB to this office suite, underdeveloped since 15 years.
Same issue with any other database frontend you may connect to your HSQLDB. HSQL is a database product in its own right. It was not made for OpenOffice Base exclusively.
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
Post Reply