Json import fails for IDENTITY_INSERT tables on MSSQL Server

[jonn...@gmail.com]

When trying to import data using the JsonLoader into MSSqlServer 2012 like this : 

         Table<? extends TableRecord> table = tables.get(tableName);

         LoaderJSONStep<? extends TableRecord> loaderJSONStep = create.loadInto(table)

                    .onDuplicateKeyIgnore()

                    .onErrorAbort()

                    .commitNone()

                    .loadJSON(jsonDataFiles.get(table.getName()).getInputStream());

I get 

Cannot insert explicit value for identity column in table 'stamm_daten' when IDENTITY_INSERT is set to OFF.

errors. I tried to set 

create.execute("SET IDENTITY_INSERT " + stringTableEntry + " " + onOff);

Without success. 

The generated insert statements contains the column names, so this should also not be a Problem. 

Any ideas?

Thanks

Johannes

[Lukas Eder]

Hi Johannes,

There is a pending issue related to this flag:

https://github.com/jOOQ/jOOQ/issues/1818

So far, I haven't figured out how to generally work around this issue. One possible workaround would be for you to implement a VisitListener to transform the generated SQL to ignore all inserts to this column. Another would be to explicitly specify the fields to be loaded using the LoaderJSONStep.fields() method.

But I suspect that you actually want to (re-)load those IDs, right? How would this be solved correctly using JDBC? Does this flag "survive" a connection / transaction / statement?

Cheers

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Johannes Bühler]

Hi Lukas,

I need to keep the IDs the same as they are stored in the json file. I am providing the fields to the loaderStep

loaderJSONStep.fields(table.fields()).execute();

As far as I know the 

create.execute("SET IDENTITY_INSERT " + stringTableEntry + " " + onOff);

has Session Scope, so it is valid for the same transaction. 

In plain JDBC you could do something like this:

http://www.windowsazure.com/en-us/documentation/articles/sql-data-java-how-to-use-sql-database/#to_insert_rows

Can I use the VisitListener to change the insert statement to add the  SET IDENTITY_INSERT flag for each and every statement?

Thanks,

Johannes

[Lukas Eder]

Hi Johannes,

That's very interesting, I didn't know the T-SQL INSERT statement had such flags. It's a bit hidden in the documentation here:

http://technet.microsoft.com/en-US/library/ms174335.aspx

(column_list)

Is a list of one or more columns in which to insert data. column_list must be enclosed in parentheses and delimited by commas.
[...]
column_list must be used when explicit values are inserted into an identity column, and the SET IDENTITY_INSERT option must be ON for the table.

Yes, you could inject this clause using a VisitListener. Something like this:

@Override
public void visitStart(VisitContext context) {
    if (ctx.data("prepend-identity-insert") != null) {
        context.renderContext().sql("SET IDENTITY_INSERT table_name ON ");
    }
}

You'd probably need to hack around a little to find a general solution to insert the table_name. The "prepend-identity-insert" data Element would be your external control flag to activate this behaviour on a per-query / per-configuration basis.

Another option would be to implement a simple ExecuteListener instead, and prepend this clause:

@Override
public void renderEnd(ExecuteContext ctx) {
    if (ctx.data("prepend-identity-insert") != null) {
        ctx.sql("SET IDENTITY_INSERT table_name ON " + ctx.sql());
    }
}

Let me know if this works around your problem

Cheers

Lukas

[Johannes Bühler]

Hi Lukas,

I implememened a ExecuteListener as you recommended and the insert statements now find their way into the database :-)

I had to find out which tables actually have a identity column: 

select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME

and than check ob the sql statement whether it is a insert or not:

if (ctx.configuration().data("prepend-identity-insert") != null &&  ctx.sql().toLowerCase().contains("insert into")) {

            Matcher matcher = tablePattern.matcher(ctx.sql());

            while (matcher.find()) {

                String tableName = matcher.group(2).trim();

                if (idTables.contains(tableName)) {

                    ctx.sql("SET IDENTITY_INSERT " + tableName + "  ON " + ctx.sql());

                }

            }

        }

Maybe there is a better way to do this. 

BTW: Wouldn't it be better to have batch insert support for the csv and json loaders?

Thanks for your help!

Johannes

[Lukas Eder]

Hi Johannes

2014-02-11 0:27 GMT+01:00 Johannes Bühler <jonn...@gmail.com>:

Hi Lukas,

I implememened a ExecuteListener as you recommended and the insert statements now find their way into the database :-)

That's great news!

 

I had to find out which tables actually have a identity column: 

select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME

That's similar to what jOOQ-Meta does, when columns from a table are fetched:

SELECT [INFORMATION_SCHEMA].[COLUMNS].[COLUMN_NAME],
  [INFORMATION_SCHEMA].[COLUMNS].[ORDINAL_POSITION],
  [INFORMATION_SCHEMA].[COLUMNS].[DATA_TYPE],
  [INFORMATION_SCHEMA].[COLUMNS].[IS_NULLABLE],
  [INFORMATION_SCHEMA].[COLUMNS].[COLUMN_DEFAULT],
  [INFORMATION_SCHEMA].[COLUMNS].[CHARACTER_MAXIMUM_LENGTH],
  [INFORMATION_SCHEMA].[COLUMNS].[NUMERIC_PRECISION],
  [INFORMATION_SCHEMA].[COLUMNS].[NUMERIC_SCALE],
  c.is_identity
FROM [INFORMATION_SCHEMA].[COLUMNS]
JOIN sys.schemas s
ON [INFORMATION_SCHEMA].[COLUMNS].[TABLE_SCHEMA] = s.name
JOIN sys.objects t
ON ((t.type                                    IN ('U', 'V'))
AND (t.schema_id                                = s.schema_id)
AND [INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME] = t.name)
JOIN sys.columns c
ON ((c.object_id                                     = t.object_id)
AND [INFORMATION_SCHEMA].[COLUMNS].[COLUMN_NAME]     = c.name)
WHERE ([INFORMATION_SCHEMA].[COLUMNS].[TABLE_SCHEMA] = ?
AND [INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME]      = ?)
ORDER BY [INFORMATION_SCHEMA].[COLUMNS].[ORDINAL_POSITION] ASC

I'd say that both ways are about equivalent.

and than check ob the sql statement whether it is a insert or not:

if (ctx.configuration().data("prepend-identity-insert") != null &&  ctx.sql().toLowerCase().contains("insert into")) {

            Matcher matcher = tablePattern.matcher(ctx.sql());

            while (matcher.find()) {

                String tableName = matcher.group(2).trim();

                if (idTables.contains(tableName)) {

                    ctx.sql("SET IDENTITY_INSERT " + tableName + "  ON " + ctx.sql());

                }

            }

        }

Maybe there is a better way to do this.

I would have thought of a similar solution. Of course, this is a workaround for the time being. It would be nice to have such a feature in jOOQ directly.

 

BTW: Wouldn't it be better to have batch insert support for the csv and json loaders?

Yes, of course! I'm hoping to implement this for jOOQ 3.4:

https://github.com/jOOQ/jOOQ/issues/2664

Batching brings significant performant improvements to almost all databases / JDBC drivers:

http://blog.jooq.org/2014/01/16/what-you-didnt-know-about-jdbc-batch/

Regards,
Lukas 

[Johannes Bühler]

Hi Lukas,

 

BTW: Wouldn't it be better to have batch insert support for the csv and json loaders?

Yes, of course! I'm hoping to implement this for jOOQ 3.4:

https://github.com/jOOQ/jOOQ/issues/2664

May I help implementing this feature? 

Cheers,

Johannes

 

[Lukas Eder]

How could I say no to this, after your excellent help with the JSON feature! :-)

It won't be a trivial refactoring, so let's have a quick review of the existing functionality and how this fits with batch:

1. Commit rules

2. Duplicate handling rules

3. Error handling rules

In my opinion, JDBC batching should be applied whenever possible without breaking existing functionality. Ideally, every loader configuration flag should indicate whether it is "batch-compatible" or not in its Javadoc. A new batch size setting would allow to override a default batch size of - say - 1000 statements per batch. A batch size <= 0 would mean that no batching is done.

1. Commit rules

- commitNone() - No interference (default to batch(1000))

- commitEach() - Invalidates the batch size - a warning should be emitted if users set batch(xy) and commitEach() (forced to batch(0))

- commitAll() - No interference (default to batch(1000))

- commitAfter(int) - The batch size cannot be larger than the commit size. A warning should be emitted if users set batch(x) and commitAfter(y) with x > y (default to batch(Math.min(1000, y)))

2. Duplicate handling rules

- onDuplicateKeyIgnore() - No interference as this is handled by jOOQ and will not generate constraint violations (this is currently not implemented very well)

- onDuplicateKeyUpdate() - No interference as this is handled by jOOQ and will not generate constraint violations

- onDuplicateKeyError() - No interference

Here, we'll have to see with our integration tests, how each driver behaves

3. Error handling rules

- onErrorAbort() - Not sure ...

- onErrorIgnore() - Not sure ...

... what'll change when we switch from single statement execution to batch.

So how would you like to proceed with a contribution? The easiest way would be to implement this for H2 and I'll make it work for all the other 15 SQL dialects. Or do you have any other suggestion?

Cheers

Lukas

[Johannes Bühler]

Am Mittwoch, 12. Februar 2014 10:02:30 UTC+1 schrieb Lukas Eder:

2014-02-12 8:59 GMT+01:00 Johannes Bühler <jonn...@gmail.com>:

Hi Lukas,

 

BTW: Wouldn't it be better to have batch insert support for the csv and json loaders?

Yes, of course! I'm hoping to implement this for jOOQ 3.4:

https://github.com/jOOQ/jOOQ/issues/2664

May I help implementing this feature? 

How could I say no to this, after your excellent help with the JSON feature! :-)

 

Thanks, was a pleasure to work with a well designed and extensible piece of software.  

It won't be a trivial refactoring, so let's have a quick review of the existing functionality and how this fits with batch:

 

...

 

So how would you like to proceed with a contribution? The easiest way would be to implement this for H2 and I'll make it work for all the other 15 SQL dialects. Or do you have any other suggestion?

 

This makes absolutely sense. I will start with the H2 implementation and see how far I can get.

Cheers

Johannes

 

[Lukas Eder]

2014-02-12 16:54 GMT+01:00 Johannes Bühler <jonn...@gmail.com>:

So how would you like to proceed with a contribution? The easiest way would be to implement this for H2 and I'll make it work for all the other 15 SQL dialects. Or do you have any other suggestion?

 

This makes absolutely sense. I will start with the H2 implementation and see how far I can get.

Wonderful! Looking forward to that

Lukas