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:
@Overridepublic 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:
@Overridepublic 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
2014-02-10 9:16 GMT+01:00 Johannes Bühler <[email protected]>:
> 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
>
>
> Am Freitag, 7. Februar 2014 19:39:21 UTC+1 schrieb 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
>>
>>
>> 2014-02-07 16:48 GMT+01:00 <[email protected]>:
>>
>>> 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
>>>
>>> --
>>> 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.
>>>
>>
>> --
> 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.
>
--
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.
