Dear Lucas!

I've faced such issue with the loader api: I have a class that contains 
only id values. This class is actually a composite primary key itself. I 
want to make batch insert for this class using loader api. But if I use 
onDuplicateKeyUpdate - I receive an error like there is no fields to 
update(truth, there is only part of primary key). If I use 
onDuplicateKeyIgnore - I receive an error Cannot apply batch loading with 
onDuplicateKeyIgnore flag. If I use nothing - sometimes I face duplicate 
key violation exception. Can you advice me any approach to deal with this 
case? I will really appreciate any help. 

The point is that I want to make 
insertInto(TABLE).set(record).onDuplicateKeyUpdate().set(record).execute() in 
batch

Thanks in advance,
   Victoria

вторник, 16 августа 2016 г., 0:03:56 UTC+3 пользователь Lukas Eder написал:
>
> Hi Benjamin,
>
> I'm sorry for the delay. Yes, there is a way to tell jOOQ that. 
> Field.equal(T) is just convenience for Field.equal(val(T)), but you can 
> also pass an "inline" bind variable by using DSL.inline(T):
>
> http://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#inline-java.lang.Integer-
>
> This helps having fine-grained control over which bind variable gets to be 
> inlined and which variable gets to be passed as a "?". The other option, as 
> you've already discovered, is to force all bind variables to be inlined via 
> STATIC_STATEMENTS. That flag also executes a java.sql.Statement, rather 
> than a java.sql.PreparedStatement
>
> Both options are documented here, for the reference:
>
> http://www.jooq.org/doc/latest/manual/sql-building/bind-values/inlined-parameters
>
> I'm curious about what takes so long when you use bind variables. Have you 
> profiled your SQL usage? How many bind variables do you have, and how big 
> are they? Do your actual queries look very different from the example that 
> you've posted?
>
> Thanks for the loader hint. I'll investigate that. Quite possibly, there's 
> a bug that prevents using that combination of flags. Note, though, you 
> don't have to use STATIC_STATEMENTS for everything. You can use it only for 
> those few queries that run slowly.
>
> Hope this helps,
> Lukas
>
> 2016-08-11 12:17 GMT+02:00 Benjamin Lang <benjami...@gmail.com 
> <javascript:>>:
>
>> We had another problem with sql server and prepared statements, where it 
>> took forever to execute a statement. 
>>
>> Looking at the example from the docs:
>>
>> create.select().from(BOOK).where(BOOK.ID.equal(5)).and(BOOK.TITLE.equal("Animal
>>  
>> Farm")).fetch();
>>
>> Is there a way to tell jooq, that for example BOOK.ID is not a prepared 
>> statement param ? The opposite of val() ...
>>
>> Because of this problem, we switched completely to STATIC_STATEMENTS and 
>> everything works fast (one statement down from several minutes to 3 sec).
>> Is there a benefit with prepared statements anyway, if you send the same 
>> basically static sql query (without any params) over and over again ... ?
>>
>> But then we have a problem with the Loader, the source code:
>>
>>  getContext()
>>          .loadInto(table)
>>          .batchAll()
>>          .onDuplicateKeyUpdate()
>>          .loadRecords(records)
>>          .fields(fields)
>>          .execute();
>>
>> Doesn't work anymore, because it doesn't fill the queries correctly with 
>> the records anymore. 
>> If I remove the onDuplicateKeyUpdate() it works for inserts as expected.
>>
>> Am I doing it wrong or is there maybe an error in connection with STATIC 
>> - Loader - OnDuplicatedKey - SQL Server ?
>>
>> Best Regards
>> -- Benjamin
>>
>>
>> Am Dienstag, 5. Juli 2016 22:43:41 UTC+2 schrieb Benjamin Lang:
>>>
>>> Hello together,
>>>
>>> we are currently evaluating Jooq in a prototype context in order
>>> to find out if we can use it later in production. So far it seems 
>>> pretty nice, with an almost perfect documentation.
>>>
>>> We need to support different PostgreSql and Sql Server types. 
>>>
>>> One problem we could not yet solve, is the following:
>>>
>>> We want to insert a batch of 1000 entries into a table. If
>>> some entries already exist, we want to update some of the fields instead.
>>>
>>> We used:
>>>
>>> InsertValuesStep insertStep = context.insertInto(Table, SomeFields);
>>> List<UpdateTableRecord> records = SomeInitialzedRecords();
>>>
>>> records.stream().foreach(record -> {
>>> insertStep.values(record.getField1(), record.getField2()...);
>>> insertStep.onDuplicateKeyUpdate().set(Field1, record.getField1());
>>> }
>>>
>>> insertStep.execute();
>>>
>>> This works for PostgresSql as expected, but in sql server it does insert 
>>> only one row per execution instead of 1000.
>>> We also tried:
>>>
>>> context.batchStore(records).execute();
>>>
>>> But this throws the DuplicateKey Exception instead of updating the row 
>>> entry and it seems somewhat slower than the other option under PostgreSql.
>>>
>>> Is this the right way to do what we like to achieve or is there 
>>> something better ? 
>>>
>>> Thanks in advance - Benjamin
>>>
>>> -- 
>> 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 <javascript:>.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>

-- 
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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to