Bulk Import Derby error SYSCS_IMPORT_DATA CHARACTERDELIMITER must be CHAR(1)

Question

Hi I trying to import txt values file into derby database using CALL SYSCS_UTIL.SYSCS_IMPORT_DATA.

Each value column is delimited by ; and the line of values is delimited by LF Line Feed.

111142501;283811110;01111000;28111004;EXPO[LF]

In my java method I'm invoking SYSCS_IMPORT_DATA like this

        String driver = "org.apache.derby.jdbc.EmbeddedDriver";
        String protocol = "jdbc:derby:";
        Connection conexion = null;

        File bd = new File(configProperties.getDdbbDir());
        String absolutePathDB = bd.getAbsolutePath();
        Class.forName(driver).newInstance();
        Properties props = new Properties();
        conexion = DriverManager.getConnection(protocol + absolutePathDB, props);
        conexion.setAutoCommit(false);
        Statement s = conexion.createStatement();

        String queryCer = "CALL SYSCS_UTIL.SYSCS_IMPORT_DATA('ROOT','ELECTOR','COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5',null,'" +                   + StringUtils.replace(file.getAbsolutePath(), "\\", "\\\\") + "',';', '\\n', 'UTF-8', 0)";
            s.execute(queryCer);

When my applications goes by statement execute finishes with the following error:

Caused by: ERROR 22001: A truncation error was encountered trying to shrink CHAR '\n' to length 1.

I've tried to change CHARACTERDELIMITER for other values , like ", ' or null, but other errors appears. Also I've tried to import the lines enclosed by "", but does not working.

As Christoph adviced me, I've changed the invocation replacing '\n' by null (the column names are in Spanish)

CALL SYSCS_UTIL.SYSCS_IMPORT_DATA('ROOT','ELECTOR','CODIGO_CENSO, CODIGO_PROVINCIA, NOMBRE_PROVINCIA, CODIGO_MUNICIPIO, NOMBRE_MUNICIPIO, CODIGO_DISTRITO,
CODIGO_SECCION, CODIGO_SUB_SECCION, MESA, NUMERO_ORDEN, IDENTIFICADOR, PRIMER_APELLIDO_NORMALIZADO,
SEGUNDO_APELLIDO_NORMALIZADO, NOMBRE_NORMALIZADO, FECHA_NACIMIENTO, INDICADOR_CORREO, SEXO, GRADO_ESC,
PRIMER_APELLIDO_SIN_NORMALIZAR, SEGUNDO_APELLIDO_SIN_NORMALIZAR, NOMBRE_SIN_NORMALIZAR, CODIGO_CIERRE, NIE, EATIM, JUNTA_ZONA, PERIODO'
,'7,17,54,12,51,9,20,21,47,56,39,63,67,52,36,40,69,37,64,68,55,8,49,33,43,60',
'fichero.csv',';', null, 'UTF-8', 0)

but now I have the following error: java.sql.SQLException: The exception 'java.sql.SQLSyntaxErrorException: Column 'COLUMNXX' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN54' is not a column in the target table.' was thrown while evaluating an expression.

As you can see, I've included the index of column to import data values, because if it wasn't indicated the error was the following: Caused by: java.sql.SQLException: The column ID in table ELECTOR does not allow null values.

So, I have to put the index columns.

Here one register from csv file:

1;28;MADRID                   ;903;XXXX XXXXXX                                       ;01;022 ;  ;B;00000466;*****7***;SOMBRERO                         ;ROZAS                            ;PEPITO                           ;200X0404; ;V;3;PISCINAS                         ;ROZAS                            ;PEPITO                           ;000042501;288000000;00000000;20000000;EXPO

Y la data table has the following column names:

('ID','APELLIDO_CASADA','BLOQUE','CALIFICADOR','CIAY','CIUDAD','CODIGO_CENSO','CODIGO_CIERRE','CODIGO_DISTRITO',
'CODIGOEC','CODIGOES','CODIGO_MUNICIPIO','CODIGO_MUNICIPIO_NACIMIENTO','CODIGO_NUCLEO','CODIGO_PAIS',
'CODIGO_POSTAL','CODIGO_PROVINCIA','CODIGO_PROVINCIA_NACIMIENTO','CODIGO_PSEUDOVIA','CODIGO_SECCION',
'CODIGO_SUB_SECCION','CODIGO_TIPO_VIA','CODIGO_VIA','CONSULADO','DESCRIPCIONEC','DESCRIPCIONES','DESCRIPCION_NUCLEO',
'DESCRIPCION_PSEUDOVIA','DESCRIPCION_TIPO_VIA','DESCRIPCION_VIA','DIGITO_CONTROL_NIE','DIRECCION_POSTAL',
'EATIM','ESCALERA','FECHA_CARGA','FECHA_NACIMIENTO','GRADO_ESC','HM','IDENTIFICADOR','INDICADOR_CORREO',
'INDICADOR_INHABILITADO','IVOTO','JUNTA_ZONA','KM','LETRA_DNI','LETRA_EXTRANJERO','MESA','MUNICIPIO_CERA',
'NIE','NOMBRE_CONSULADO','NOMBRE_MUNICIPIO','NOMBRE_NORMALIZADO','NOMBRE_PAIS_CERA','NOMBRE_PROVINCIA',
'NOMBRE_SIN_NORMALIZAR','NUMERO_ORDEN','NUMERO_VIA','PAIS_CERA','PAIS_NACION','PERIODO','PISO','PORTAL',
'PRIMER_APELLIDO_NORMALIZADO','PRIMER_APELLIDO_SIN_NORMALIZAR','PROVINCIA_CERA','PUERTA','SEGUNDO_APELLIDO_NORMALIZADO',
'SEGUNDO_APELLIDO_SIN_NORMALIZAR', 'SEXO', 'TIPO_IDENTIFICADOR')

Any help will be appreciate. Thanks Regards, Jose Pascual

You *include* JPA as a dependency, but completely ignore it by your code. It should be sufficient to make spring-boot-starter-data-jdbc a dependency. — Christoph Dahlen, Nov 10 '22 at 11:12

Christoph Dahlen · Answer 1 · 2022-11-10T12:01:53.407

1

The syntax of the stored procedure according to documentation is

SYSCS_UTIL.SYSCS_IMPORT_DATA (IN SCHEMANAME VARCHAR(128),
IN TABLENAME VARCHAR(128), IN INSERTCOLUMNS VARCHAR(32672),
IN COLUMNINDEXES VARCHAR(32672), IN FILENAME VARCHAR(32672),
IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1),
IN CODESET VARCHAR(128), IN REPLACE SMALLINT)

Assuming the file's name is 'C:\import.csv', String queryCer resolves to

CALL SYSCS_UTIL.SYSCS_IMPORT_DATA('ROOT',
'ELECTOR','COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5',
null,'C:\import.csv',
';', '\n', 'UTF-8', 0)

where ";" being the column delimiter (which is correct) and "\n" being the character delimiter, which is incorrect as there is no character delimiter. So,replace '\\n' by null (without quotes).

edited Nov 10 '22 at 12:01

answered Nov 10 '22 at 11:26

Christoph Dahlen

826
3
15

Thanks Christoph, I've replaced the invocation as you said, but now I have the following error: java.sql.SQLException: The exception 'java.sql.SQLSyntaxErrorException: Column 'COLUMNXX' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN54' is not a column in the target table.' was thrown while evaluating an expression. – José Pascual Gimeno Marí Dec 15 '22 at 10:34
@JoséPascualGimenoMarí can you update your question to show the current state of your program, and all of the details that you can include about the *actual* SQL statement that is issued to the database and the *actual* message you received? I don't see how you are getting an error message about `COLUMNXX` and `COLUMN54` if your exact SQL statement is the one shown as `queryCer` above. – Bryan Pendleton Dec 19 '22 at 18:10
I found the solution, the problem was the Id generation strategy. Originally it was GenerationType.AUTO, so I have changed it to GenerationType.IDENTITY, removed the column index and csv values have been imported into the database. Thanks. – José Pascual Gimeno Marí Dec 28 '22 at 09:02

Bulk Import Derby error SYSCS_IMPORT_DATA CHARACTERDELIMITER must be CHAR(1)

1 Answers1