tbMy - how to make a field longer?
lazarus / 16-Databases

----------------------------------------------------------------------
Nicole · 2023-01-02 19:15

I have a table, which looks like shown below.
It is embedded in my code and to touch it in the wrong way, will kill
a lot of items, I do not want to imagine what all.

There is a field
"BEZEICHNUNG VARCHAR(50) "

I would rather prefer to have it as
100 digits
and
UTF8 or so.

Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE TBNEWS(  ID INTEGER NOT NULL,  DATUM DATE NOT NULL,  ZEIT TIME, 
CODE_ INTEGER NOT NULL,  BEZEICHNUNG VARCHAR(50) CHARACTER SET
ISO8859_1 COLLATE DE_DE,  CONSTRAINT PK_TBNEWS PRIMARY KEY
(ID)); ALTER TABLE TBNEWS ADD CONSTRAINT FK_TBNEWS_1  FOREIGN KEY
(DATUM) REFERENCES TBKALENDER (JDATUM);GRANT DELETE, INSERT,
REFERENCES, SELECT, UPDATE ON TBNEWS TO  SYSDBA WITH GRANT OPTION;
And yes, this fields arel filled with stuff already.

Do you see any option to change the length or even the type without
data loss and being busy for half a day?
Or is it better to live with the old version?

Thank you for your ideas.

----------------------------------------------------------------------
dsiders · 2023-01-02 20:01

Quote from: Nicole on January 02, 2023, 08:15:57 pmI have a table,
which looks like shown below.
It is embedded in my code and to touch it in the wrong way, will kill
a lot of items, I do not want to imagine what all.

There is a field
"BEZEICHNUNG VARCHAR(50) "

I would rather prefer to have it as
100 digits
and
UTF8 or so.

Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE TBNEWS(  ID INTEGER NOT NULL,  DATUM DATE NOT NULL,  ZEIT TIME, 
CODE_ INTEGER NOT NULL,  BEZEICHNUNG VARCHAR(50) CHARACTER SET
ISO8859_1 COLLATE DE_DE,  CONSTRAINT PK_TBNEWS PRIMARY KEY
(ID)); ALTER TABLE TBNEWS ADD CONSTRAINT FK_TBNEWS_1  FOREIGN KEY
(DATUM) REFERENCES TBKALENDER (JDATUM);GRANT DELETE, INSERT,
REFERENCES, SELECT, UPDATE ON TBNEWS TO  SYSDBA WITH GRANT OPTION;
And yes, this fields arel filled with stuff already.

Do you see any option to change the length or even the type without
data loss and being busy for half a day?
Or is it better to live with the old version?

Thank you for your ideas.

https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref40
/firebird-40-language-reference.html#fblangref40-ddl-tbl-alter

----------------------------------------------------------------------
Nicole · 2023-01-03 16:38

Thank you for your answer.
Unfortunately this is just what is generally known about how to change
a table.

My questions was: Is it wise to do it in my case and if yes how
exactly?

----------------------------------------------------------------------
paweld · 2023-01-03 20:02

if you only want to change varchar length, use ALTER COLUMN:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ALTER TABLE
TBNEWS ALTER COLUMN BEZEICHNUNG TYPE VARCHAR(100); 
while if you want to change the CHARSET as well, you need to add a new
column and move the data to it
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ALTER TABLE
TBNEWS ADD BEZEICHNUNG_NEW VARCHAR(100) CHARACTER SET UTF8;   UPDATE
TBNEWS SET BEZEICHNUNG_NEW=CAST(BEZEICHNUNG AS VARCHAR(100) CHARACTER
SET UTF8);  ALTER TABLE TBNEWS DROP BEZEICHNUNG;  ALTER TABLE TBNEWS
ALTER COLUMN BEZEICHNUNG_NEW TO BEZEICHNUNG;

----------------------------------------------------------------------
Nicole · 2023-01-05 17:08

Thanks Pawel, this looks very helpful!