Hi,
I try to copy a database from Postgres to SQL Server 2005 (not my choice!). So I need to insert values to an identity field.
I found a similar topic in the forum http://forum.cloveretl.org/viewtopic.php?f=4&t=3342.
So I tried to turn IDENTITY_INSERT on with the following nearly minimal grf file but got an error
“Cannot insert explicit value for identity column in table ‘TEST_TABLE’ when IDENTITY_INSERT is set to OFF”:
<?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" threadSafeConnection="false"/>
<Metadata fileURL="${MetadataOutDir}/TEST_TABLE-out-record.fmt" id="MetadataOut_TEST_TABLE"/>
</Global>
<Phase number="0">
<Node id="TEST_TABLE_OUTPUT_IDENTITY_INSERT"
type="DB_EXECUTE"
dbConnection="ConnectionOut"
sqlQuery="set IDENTITY_INSERT TEST_TABLE ON"
inTransaction="SET"
/>
</Phase>
<Phase number="1">
<Node
id="TEST_TABLE_INPUT"
type="DB_INPUT_TABLE"
dbConnection="ConnectionIn"
sqlQuery="select DESCRIPTION, ID from TEST_TABLE"
/>
<Node
id="TEST_TABLE_OUTPUT"
type="DB_OUTPUT_TABLE"
dbConnection="ConnectionOut"
dbTable="TEST_TABLE"
batchMode="${BatchMode}"
batchSize="1000"
commit="10000"
maxErrors="0"
errorAction="ROLLBACK"
dbFields="DESCRIPTION; ID"
/>
<Edge fromNode="TEST_TABLE_INPUT:0" id="TEST_TABLE_EDGE0" metadata="MetadataOut_TEST_TABLE" toNode="TEST_TABLE_OUTPUT:0"/>
</Phase>
</Graph>
The postgres database table:
CREATE TABLE test_table (
id bigint NOT NULL,
description character varying(50),
CONSTRAINT test_table_pkey PRIMARY KEY (id)
);
The SQL Server table:
create table TEST_TABLE (
ID BIGINT identity not null,
DESCRIPTION VARCHAR(255) not null,
primary key (ID)
);
What is wrong here? Is setting the jdbcSpecific property necessary? In that case: Why do I get the error
WARN [main] - Optimizing connection failed: The CLOSE_CURSORS_AT_COMMIT option is not currently supported by the setHoldability method.
WARN [main] - Try to use another jdbc specific
I tried it with cloveretl version 3.0.2.
I hope someone can help! I’m dealing with this problem since hours.
Thanks in advance