Hello forum readers!
I try to copy a table from a postgres database to a mssql database (just for test purposes 
, 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>