Hi Johannes
2014-02-11 0:27 GMT+01:00 Johannes Bühler <[email protected]>:
> 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_NAMEfrom INFORMATION_SCHEMA.COLUMNSwhere
> TABLE_SCHEMA = 'dbo'and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME,
> 'IsIdentity') = 1order 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_identityFROM
[INFORMATION_SCHEMA].[COLUMNS]JOIN sys.schemas sON
[INFORMATION_SCHEMA].[COLUMNS].[TABLE_SCHEMA] = s.nameJOIN sys.objects
tON ((t.type IN ('U', 'V'))AND
(t.schema_id = s.schema_id)AND
[INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME] = t.name)JOIN sys.columns
cON ((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
--
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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
