Hello forum readers!
I try to copy a table from a postgres database to a mssql database (just for test purposes :wink: , real life is the other direction and to oracle).
I do this using one node with 'DB_INPUT_TABLE' and another node with 'DB_OUTPUT_TABLE' connected simply by one edge. As my code is generated (with AndroMDA) I have no control on the order of the fields in my metadata record, which describes the table. This means that in my table the order of attributes is e.g. 'id', 'name', 'active', 'email' but in the record definition its 'id', 'email', 'name', 'active'. Now I have the situation that email is nullable and my data indeed contains null values.
This seems to have problems with clover. Clover ends with an error message indicating that it is not possible to insert null values in the column 'active'. The 'active' column is in postgres of type boolean, in mssql of type bit and clover handles this as string type.
Is this a bug in Clover or do i have some misconfigurations? Some ideas whats going wrong here?
Thanks in advance
Alex
Here is my minimal (i hope so) but complete example:
1. Table definition in Postgres:
2. Data in Postgres:
3. Table definition in MSSQL:
4. Clover Record format defintion (file name test_table-record.fmt):
5. Clover Graph definition (my db configurations are files given in properties ${ConfigIn} for Postgres and ${ConfigOut} for MSSQL):
I try to copy a table from a postgres database to a mssql database (just for test purposes :wink: , real life is the other direction and to oracle).
I do this using one node with 'DB_INPUT_TABLE' and another node with 'DB_OUTPUT_TABLE' connected simply by one edge. As my code is generated (with AndroMDA) I have no control on the order of the fields in my metadata record, which describes the table. This means that in my table the order of attributes is e.g. 'id', 'name', 'active', 'email' but in the record definition its 'id', 'email', 'name', 'active'. Now I have the situation that email is nullable and my data indeed contains null values.
This seems to have problems with clover. Clover ends with an error message indicating that it is not possible to insert null values in the column 'active'. The 'active' column is in postgres of type boolean, in mssql of type bit and clover handles this as string type.
Is this a bug in Clover or do i have some misconfigurations? Some ideas whats going wrong here?
Thanks in advance
Alex
Here is my minimal (i hope so) but complete example:
1. Table definition in Postgres:
CREATE TABLE test_table
(
id bigint NOT NULL,
name character varying(100) NOT NULL,
active boolean NOT NULL,
email character varying(200),
CONSTRAINT test_table_pk PRIMARY KEY (id)
)
2. Data in Postgres:
INSERT INTO test_table(id, name, active, email)
VALUES (1, 'clover', true, null);
3. Table definition in MSSQL:
CREATE TABLE test_table
(
id bigint identity NOT NULL,
name character varying(100) NOT NULL,
active bit NOT NULL,
email character varying(200),
CONSTRAINT test_table_pk PRIMARY KEY (id)
)
4. Clover Record format defintion (file name test_table-record.fmt):
<?xml version="1.0" encoding="UTF-8"?>
<!-- Automatically generated from andromda with clover-cartridge template records.fmt.vsl -->
<Record name="test_table_out" type="delimited">
<Field name="id" type="long" delimiter=","/>
<Field name="email" type="string" delimiter=","/>
<Field name="name" type="string" nullable="yes" delimiter=","/>
<Field name="active" type="string" delimiter="\n"/>
</Record>
5. Clover Graph definition (my db configurations are files given in properties ${ConfigIn} for Postgres and ${ConfigOut} for MSSQL):
<?xml version="1.0" encoding="UTF-8"?>
<Graph description="Copy table from one db to another one" name="CopyDB">
<Global>
<Connection dbConfig="${ConfigIn}" id="ConnectionIn" type="JDBC"/>
<Connection dbConfig="${ConfigOut}" id="ConnectionOut" type="JDBC"/>
<Metadata fileURL="test_table-record.fmt" id="MetadataOut_test_table"/>
</Global>
<Phase number="1">
<Node
id="test_table_INPUT"
type="DB_INPUT_TABLE"
dbConnection="ConnectionIn"
enabled="enabled"
fetchSize="1000"
>
<SQLCode>
SELECT id, name, email, active
FROM test_table;
</SQLCode>
</Node>
<Node
id="test_table_OUTPUT"
type="DB_OUTPUT_TABLE"
dbConnection="ConnectionOut"
dbTable="test_table"
enabled="enabled"
batchMode="false"
batchSize="1000"
commit="10000"
maxErrors="0"
errorAction="ROLLBACK"
dbFields="id;name;email;active"
sqlQuery="
SET IDENTITY_INSERT test_table ON;
insert into test_table (id, name, email, active) values (?,?,?,?);
SET IDENTITY_INSERT test_table OFF;
"
/>
<Edge fromNode="test_table_INPUT:0" id="test_table_EDGE0" metadata="MetadataOut_test_table" toNode="test_table_OUTPUT:0"/>
</Phase>
</Graph>
-
I forgot to attach the error message:
WARN [test_table_OUTPUT] - Cannot insert the value NULL into column 'active', table 'clover_test.dbo.test_table'; column does not allow nulls. INSERT fails.
ERROR [test_table_OUTPUT] - java.sql.SQLException: Maximum # of errors exceeded when inserting record: Cannot insert the value NULL into column 'active', table 'clover_test.dbo.test_table'; column does not allow nulls. INSERT fails.
FATAL [WatchDog] - !!! Fatal Error !!! - graph execution is aborting
ERROR [WatchDog] - Node test_table_OUTPUT finished with status: ERROR caused by: Maximum # of errors exceeded when inserting record: Cannot insert the value NULL into column 'active', table 'clover_test.dbo.test_table'; column does not allow nulls. INSERT fails. caused by: java.sql.SQLException: Maximum # of errors exceeded when inserting record: Cannot insert the value NULL into column 'active', table 'clover_test.dbo.test_table'; column does not allow nulls. INSERT fails.
DEBUG [WatchDog] - Node test_table_OUTPUT error details:
org.jetel.exception.JetelException: Maximum # of errors exceeded when inserting record: Cannot insert the value NULL into column 'active', table 'clover_test.dbo.test_table'; column does not allow nulls. INSERT fails. caused by: java.sql.SQLException: Maximum # of errors exceeded when inserting record: Cannot insert the value NULL into column 'active', table 'clover_test.dbo.test_table'; column does not allow nulls. INSERT fails.
at org.jetel.component.DBOutputTable.execute(DBOutputTable.java:522)
at org.jetel.graph.Node.run(Node.java:366)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Maximum # of errors exceeded when inserting record: Cannot insert the value NULL into column 'active', table 'clover_test.dbo.test_table'; column does not allow nulls. INSERT fails.
at org.jetel.component.DBOutputTable.runInNormalMode(DBOutputTable.java:566)
at org.jetel.component.DBOutputTable.execute(DBOutputTable.java:518)
... 2 more -
This does not solve the problem (indeed, i tried it)! I think the metadata is configured properly. Other tables without null values are transferred correct. If I connect a 'DataWriter' to the error port of 'DBOutputTable' I can see that the boolean values for attribute 'active' are missing although in the database there are values.
Thanks
Alex -
I tried it on the same database in postgres with a second identical table 'test_table2' with the same result:
DEBUG [WatchDog] - Node test_table_OUTPUT error details:
org.jetel.exception.JetelException: Maximum # of errors exceeded when inserting record: ERROR: null value in column "act
ive" violates not-null constraint caused by: java.sql.SQLException: Maximum # of errors exceeded when inserting record:
ERROR: null value in column "active" violates not-null constraint
at org.jetel.component.DBOutputTable.execute(DBOutputTable.java:522)
at org.jetel.graph.Node.run(Node.java:366)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Maximum # of errors exceeded when inserting record: ERROR: null value in column "activ
e" violates not-null constraint
at org.jetel.component.DBOutputTable.runInNormalMode(DBOutputTable.java:566)
at org.jetel.component.DBOutputTable.execute(DBOutputTable.java:518)
... 2 more -
We successfully used the mentioned workaround: we generate all boolean types first in the list of attributes.
But we had to face one problem with this: It was possible that nullable columns occured as the last column of a row which results in an error with the following line (as it contains a boolean type in the first column). So we had to take care, that no nullable field is the last in the row.
Alex
Please sign in to leave a comment.
Comments 9