Forum Stats

  • 3,852,812 Users
  • 2,264,140 Discussions
  • 7,905,150 Comments

Discussions

Liquibase has problems with column names with non standard characters

Lothar Armbrüster
Lothar Armbrüster Member Posts: 41 Bronze Badge

Hello out there, I'm testing a little bit with Liquibase and stumbled upon handling german umlauts in column names. I'm trying this:

create table test (id number(5),schön varchar2(10));

Then I call Liquibase:

SQL> lb genobject -type table -name test
--Starte Liquibase am 11:34:23 (Version 4.9.1 #0, kompiliert am 2022-05-03 17:23+2221)

Aktion erfolgreich abgeschlossen. Pr³fen Sie die erstellte Datei test_table1.xml

When I now try to use the generated changelog I get this error:

SQL> lb status -changelog test_table1.xml
--Starte Liquibase am 11:35:11 (Version 4.9.1 #0, kompiliert am 2022-05-03 17:23+2221)

Invalid UTF-8 encoding.

Looking into the XML reveals that the XML header announces UTF-8 but my column name is encoded in a way that looks like WIN-1252.

Normally we do not create columns like this but sometimes the column names origin from excel an may contain these characters.

Answers

  • Paavo
    Paavo Member Posts: 747 Silver Badge

    Hi

    didn't copy paste schön but typed that in the newest sqlcl and see:

    SQL> create table test (id number(5),schön varchar2(10));
    
    Table TEST created.
    
    SQL> !mkdir kikkare
    
    SQL> cd kikkare
    SQL> lb genobject -type table -name test
    --Starting Liquibase at 15:11:30 (version 4.9.1 #0 built at 2022-05-03 17:23+2221)
    
    Action successfully completed please review created file test_table.xml
    SQL> lb status -changelog test_table.xml
    --Starting Liquibase at 15:12:17 (version 4.9.1 #0 built at 2022-05-03 17:23+2221)
    
    -- Loaded 1 changeSets
    1 change sets have not been applied to obfusced....
      test_table.xml::904eec109f64332dae97dca015b8a8f65b4b7e42::(obfusced...)-Generated
    SQL> lb version
    --Starting Liquibase at 15:12:48 (version 4.9.1 #0 built at 2022-05-03 17:23+2221)
    
    
    Liquibase version: 4.9.1 #0 built at 2022-05-03 17:23+2221
    Extension Version: 22.2.1.0
    
    
    SQL> lb update -changelog test_table.xml
    --Starting Liquibase at 15:13:01 (version 4.9.1 #0 built at 2022-05-03 17:23+2221)
    
    -- Loaded 1 changeSets
    Running Changeset: test_table.xml::904eec109f64332dae97dca015b8a8f65b4b7e42::(obfusced)-Generated
    
    Action logged sucessfully.
    No Errors Encountered
    SQL> !cat test_table.xml
    <?xml version="1.0" encoding="UTF-8"?>
    <databaseChangeLog
      xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext"
      xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">
      <changeSet id="904eec109f64332dae97dca015b8a8f65b4b7e42" author="(obfusced)-Generated" failOnError="false" >
      <n0:createSxmlObject objectName="TEST" objectType="table" ownerName="obfusced" >
      <n0:source><![CDATA[
      <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
      <SCHEMA>obfusced</SCHEMA>
      <NAME>TEST</NAME>
      <RELATIONAL_TABLE>
      <COL_LIST>
      <COL_LIST_ITEM>
      <NAME>ID</NAME>
      <DATATYPE>NUMBER</DATATYPE>
      <PRECISION>5</PRECISION>
      <SCALE>0</SCALE>
      </COL_LIST_ITEM>
      <COL_LIST_ITEM>
      <NAME>SCHÖN</NAME>
      <DATATYPE>VARCHAR2</DATATYPE>
    
    

    I think it is not wise to copy paste texts from excel or from web content. Perhaps try quicksql in apex instead?

    rgrds Paavo

  • Lothar Armbrüster
    Lothar Armbrüster Member Posts: 41 Bronze Badge

    So that leads to the question how you called SQLCl. I called mine from Windows Powershell. Maybe that matters.

    I used an account for testing where I import Excel files from time to time. SQL Developer allows columns containing umlauts and when I overlook them they make it into the final table.

    Anyway, it seems not to be a general problem with Liquibase.

    Paavo