Sorry for my bad, I have not delete all data loaded before when executing
in debug mode.
Here is the debug output:
Executing query : select public.providers.id,
public.providers.name, public.providers.prices_container,
public.providers.is_enabled, public.providers.filepath,
public.providers.col_vendor_code, public.providers.col_name,
public.providers.col_manufacturer, public.providers.col_min_count,
public.providers.col_count, public.providers.col_price,
public.providers.is_delete_files, public.providers.partner_id from
public.providers where (public.providers.is_enabled = ? and
public.providers.prices_container = ?)
-> with bind values : select public.providers.id,
public.providers.name, public.providers.prices_container,
public.providers.is_enabled, public.providers.filepath,
public.providers.col_vendor_code, public.providers.col_name,
public.providers.col_manufacturer, public.providers.col_min_count,
public.providers.col_count, public.providers.col_price,
public.providers.is_delete_files, public.providers.partner_id from
public.providers where (public.providers.is_enabled = 1 and
public.providers.prices_container = 1)
Query executed : Total: 78.918ms
Fetched result :
+----+----+----------------+----------+-------------------------------------------------+---------------+--------+----------------+-------------+---------+---------+---------------+----------+
: | id|name|prices_container|is_enabled|filepath
|col_vendor_code|col_name|col_manufacturer|col_min_count|col_count|col_price|is_delete_files|partner_id|
:
+----+----+----------------+----------+-------------------------------------------------+---------------+--------+----------------+-------------+---------+---------+---------------+----------+
: | 10|тест| 1|true
|C:\Users\bvn13\Documents\_Бонифаций\Загрузка\BERG| 1|
2| 3| 8| 5| 6|true |0900
|
:
+----+----+----------------+----------+-------------------------------------------------+---------------+--------+----------------+-------------+---------+---------+---------------+----------+
Finishing : Total: 129.843ms, +50.925ms
PriceLoaderFromFileWorker: found 1 files
Executing query : select public.files_statuses.id,
public.files_statuses.filename, public.files_statuses.filepath,
public.files_statuses.is_loaded, public.files_statuses.error,
public.files_statuses.date_processed, public.files_statuses.is_error,
public.files_statuses.stacktrace from public.files_statuses where
public.files_statuses.filename = ?
-> with bind values : select public.files_statuses.id,
public.files_statuses.filename, public.files_statuses.filepath,
public.files_statuses.is_loaded, public.files_statuses.error,
public.files_statuses.date_processed, public.files_statuses.is_error,
public.files_statuses.stacktrace from public.files_statuses where
public.files_statuses.filename = 'BERG_20170522_123031.xls'
Query executed : Total: 3.413ms
Finishing : Total: 5.009ms, +1.595ms
Fetched result :
+----+--------+--------+---------+-----+--------------+--------+----------+
: |
id|filename|filepath|is_loaded|error|date_processed|is_error|stacktrace|
:
+----+--------+--------+---------+-----+--------------+--------+----------+
PriceLoaderFromFileWorker: start loading
C:\Users\bvn13\Documents\_Бонифаций\Загрузка\BERG\BERG_20170522_123031.xls
PriceLoaderFromFileWorker: loading
C:\Users\bvn13\Documents\_Бонифаций\Загрузка\BERG\BERG_20170522_123031.xls
PriceLoaderFromFileWorker: файл
C:\Users\bvn13\Documents\_Бонифаций\Загрузка\BERG\BERG_20170522_123031.xls
- строк: 26607
setting auto commit : false
set savepoint
Executing query : select public.products.id,
public.products.provider_id, public.products.name,
public.products.vendor_code, public.products.manufacturer,
public.products.min_count, public.products.count, public.products.price,
public.products.date_updated, public.products.is_processed from
public.products where (public.products.name = ? and
public.products.manufacturer = ? and public.products.vendor_code = ?)
-> with bind values : select public.products.id,
public.products.provider_id, public.products.name,
public.products.vendor_code, public.products.manufacturer,
public.products.min_count, public.products.count, public.products.price,
public.products.date_updated, public.products.is_processed from
public.products where (public.products.name = 'Опора шаровая TOYOTA
VITZ/PLATZ/FUNCARGO 99-, SB-3602' and public.products.manufacturer = '555'
and public.products.vendor_code = 'SB-3602')
Query executed : Total: 9.862ms
Finishing : Total: 14.955ms, +5.092ms
Fetched result :
+----+-----------+----+-----------+------------+---------+-----+-----+------------+------------+
: |
id|provider_id|name|vendor_code|manufacturer|min_count|count|price|date_updated|is_processed|
:
+----+-----------+----+-----------+------------+---------+-----+-----+------------+------------+
Executing query : insert into public.products (provider_id, name,
vendor_code, manufacturer, count, min_count, price, date_updated) values
(?, ?, ?, ?, ?, ?, ?, ?)
-> with bind values : insert into public.products (provider_id, name,
vendor_code, manufacturer, count, min_count, price, date_updated) values
(10, 'Опора шаровая TOYOTA VITZ/PLATZ/FUNCARGO 99-, SB-3602', 'SB-3602',
'555', 2, 1, 395.779999999999972715158946812152862548828125, '2017/05/29
09:02:21')
Exception
org.jooq.exception.DataAccessException: SQL [insert into public.products
(provider_id, name, vendor_code, manufacturer, count, min_count, price,
date_updated) values (?, ?, ?, ?, ?, ?, ?, ?)]; ОШИБКА: столбец "count"
имеет тип numeric, а выражение - character varying
Подсказка: Перепишите выражение или преобразуйте его тип.
Позиция: 139
at org.jooq_3.9.2.SQLITE.debug(Unknown Source)
at org.jooq.impl.Tools.translate(Tools.java:1983)
at
org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:676)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:363)
at
org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:135)
at
ru.bvn13.priceprocessor.database.Managers.ProductsManager.lambda$saveProduct$1(ProductsManager.java:159)
at org.jooq.impl.DefaultDSLContext$3.run(DefaultDSLContext.java:525)
at org.jooq.impl.DefaultDSLContext$3.run(DefaultDSLContext.java:522)
at
org.jooq.impl.DefaultDSLContext.lambda$transactionResult0$0(DefaultDSLContext.java:468)
at org.jooq.impl.Tools$10$1.block(Tools.java:3291)
at java.util.concurrent.ForkJoinPool.managedBlock(ForkJoinPool.java:3323)
at org.jooq.impl.Tools$10.get(Tools.java:3288)
at
org.jooq.impl.DefaultDSLContext.transactionResult0(DefaultDSLContext.java:505)
at
org.jooq.impl.DefaultDSLContext.transactionResult(DefaultDSLContext.java:438)
at org.jooq.impl.DefaultDSLContext.transaction(DefaultDSLContext.java:522)
at
ru.bvn13.priceprocessor.database.Managers.ProductsManager.saveProduct(ProductsManager.java:127)
at
ru.bvn13.priceprocessor.workers.PriceLoaderFromFileWorker.readFile(PriceLoaderFromFileWorker.java:316)
at
ru.bvn13.priceprocessor.workers.PriceLoaderFromFileWorker.loadFile(PriceLoaderFromFileWorker.java:175)
at
ru.bvn13.priceprocessor.workers.PriceLoaderFromFileWorker.lambda$startLoadingFile$0(PriceLoaderFromFileWorker.java:122)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ОШИБКА: столбец "count" имеет
тип numeric, а выражение - character varying
Подсказка: Перепишите выражение или преобразуйте его тип.
Позиция: 139
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2189)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
at
org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
at
org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:158)
at
org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:195)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:431)
at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:335)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:349)
... 16 more
Exception : Total: 17.368ms
Finishing : Total: 17.704ms, +0.335ms
rollback to savepoint
setting auto commit : true
PriceLoaderFromFileWorker: file
C:\Users\bvn13\Documents\_Бонифаций\Загрузка\BERG\BERG_20170522_123031.xls
- error loading: SQL [insert into public.products (provider_id, name,
vendor_code, manufacturer, count, min_count, price, date_updated) values
(?, ?, ?, ?, ?, ?, ?, ?)]; ОШИБКА: столбец "count" имеет тип numeric, а
выражение - character varying
Подсказка: Перепишите выражение или преобразуйте его тип.
Позиция: 139
Executing query : select public.files_statuses.id,
public.files_statuses.filename, public.files_statuses.filepath,
public.files_statuses.is_loaded, public.files_statuses.error,
public.files_statuses.date_processed, public.files_statuses.is_error,
public.files_statuses.stacktrace from public.files_statuses where
public.files_statuses.filename = ?
-> with bind values : select public.files_statuses.id,
public.files_statuses.filename, public.files_statuses.filepath,
public.files_statuses.is_loaded, public.files_statuses.error,
public.files_statuses.date_processed, public.files_statuses.is_error,
public.files_statuses.stacktrace from public.files_statuses where
public.files_statuses.filename = 'BERG_20170522_123031.xls'
Query executed : Total: 6.33ms
Finishing : Total: 6.604ms, +0.273ms
Fetched result :
+----+--------+--------+---------+-----+--------------+--------+----------+
: |
id|filename|filepath|is_loaded|error|date_processed|is_error|stacktrace|
:
+----+--------+--------+---------+-----+--------------+--------+----------+
setting auto commit : false
set savepoint
Executing query : insert into public.files_statuses (filepath,
filename, date_processed) values (?, ?, ?)
-> with bind values : insert into public.files_statuses (filepath,
filename, date_processed) values
('C:\Users\bvn13\Documents\_Бонифаций\Загрузка\BERG\BERG_20170522_123031.xls',
'BERG_20170522_123031.xls', '2017/05/29 09:02:21')
Affected row(s) : 1
Query executed : Total: 4.052ms
Finishing : Total: 4.127ms, +0.075ms
Executing query : SELECT
currval(pg_get_serial_sequence('files_statuses','id'));
Query executed : Total: 2.57ms
Fetched result : +-------+
: |currval|
: +-------+
: | 19|
: +-------+
Finishing : Total: 14.301ms, +11.731ms
release savepoint
commit
setting auto commit : true
Executing query : select public.files_statuses.id,
public.files_statuses.filename, public.files_statuses.filepath,
public.files_statuses.is_loaded, public.files_statuses.error,
public.files_statuses.date_processed, public.files_statuses.is_error,
public.files_statuses.stacktrace from public.files_statuses where
public.files_statuses.id = ?
-> with bind values : select public.files_statuses.id,
public.files_statuses.filename, public.files_statuses.filepath,
public.files_statuses.is_loaded, public.files_statuses.error,
public.files_statuses.date_processed, public.files_statuses.is_error,
public.files_statuses.stacktrace from public.files_statuses where
public.files_statuses.id = 19
Query executed : Total: 5.77ms
Fetched result :
+----+------------------------+--------------------------------------------------+---------+------+-------------------+--------+----------+
: | id|filename |filepath
|is_loaded|error |date_processed
|is_error|stacktrace|
:
+----+------------------------+--------------------------------------------------+---------+------+-------------------+--------+----------+
: |
19|BERG_20170522_123031.xls|C:\Users\bvn13\Documents\_Бонифаций\Загрузка\BE...|false
|{null}|2017/05/29 09:02:21|false |{null} |
:
+----+------------------------+--------------------------------------------------+---------+------+-------------------+--------+----------+
Finishing : Total: 8.482ms, +2.711ms
Executing query : update public.files_statuses set filename = ?,
filepath = ?, error = ?, is_loaded = ?, is_error = ?, stacktrace = ?,
date_processed = ? where public.files_statuses.id = ?
-> with bind values : update public.files_statuses set filename =
'BERG_20170522_123031.xls', filepath =
'C:\Users\bvn13\Documents\_Бонифаций\Загрузка\BERG\BERG_20170522_123031.xls',
error = 'SQL [insert into public.products (provider_id, name, vendor_code,
manufacturer, count, min_count, price, date_updated) values (?, ?, ?, ?, ?,
?, ?, ?)]; ОШИБКА: столбец "count" имеет тип numeric, а выражение -
character varying
Подсказка: Перепишите выражение или преобразуйте его тип.
Позиция: 139', is_loaded = 0, is_error = 1, stacktrace =
'org.jooq.exception.DataAccessException: SQL [insert into public.products
(provider_id, name, vendor_code, manufacturer, count, min_count, price,
date_updated) values (?, ?, ?, ?, ?, ?, ?, ?)]; ОШИБКА: столбец "count"
имеет тип numeric, а выражение - character varying
Подсказка: Перепишите выражение или преобразуйте его тип.
Позиция: 139
at org.jooq_3.9.2.SQLITE.debug(Unknown Source)
at org.jooq.impl.Tools.translate(Tools.java:1983)
at
org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:676)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:363)
at
org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:135)
at
ru.bvn13.priceprocessor.database.Managers.ProductsManager.lambda$saveProduct$1(ProductsManager.java:159)
at org.jooq.impl.DefaultDSLContext$3.run(DefaultDSLContext.java:525)
at org.jooq.impl.DefaultDSLContext$3.run(DefaultDSLContext.java:522)
at
org.jooq.impl.DefaultDSLContext.lambda$transactionResult0$0(DefaultDSLContext.java:468)
at org.jooq.impl.Tools$10$1.block(Tools.java:3291)
at java.util.concurrent.ForkJoinPool.managedBlock(ForkJoinPool.java:3323)
at org.jooq.impl.Tools$10.get(Tools.java:3288)
at
org.jooq.impl.DefaultDSLContext.transactionResult0(DefaultDSLContext.java:505)
at
org.jooq.impl.DefaultDSLContext.transactionResult(DefaultDSLContext.java:438)
at org.jooq.impl.DefaultDSLContext.transaction(DefaultDSLContext.java:522)
at
ru.bvn13.priceprocessor.database.Managers.ProductsManager.saveProduct(ProductsManager.java:127)
at
ru.bvn13.priceprocessor.workers.PriceLoaderFromFileWorker.readFile(PriceLoaderFromFileWorker.java:316)
at
ru.bvn13.priceprocessor.workers.PriceLoaderFromFileWorker.loadFile(PriceLoaderFromFileWorker.java:175)
at
ru.bvn13.priceprocessor.workers.PriceLoaderFromFileWorker.lambda$startLoadingFile$0(PriceLoaderFromFileWorker.java:122)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ОШИБКА: столбец "c...',
date_processed = '2017/05/29 09:02:21' where public.files_statuses.id = 19
-- Bind values may have been abbreviated for DEBUG logging. Use TRACE
logging for very large bind variables.
org.jooq.exception.DataAccessException: SQL [insert into public.products
(provider_id, name, vendor_code, manufacturer, count, min_count, price,
date_updated) values (?, ?, ?, ?, ?, ?, ?, ?)]; ОШИБКА: столбец "count"
имеет тип numeric, а выражение - character varying
Подсказка: Перепишите выражение или преобразуйте его тип.
Позиция: 139
at org.jooq_3.9.2.SQLITE.debug(Unknown Source)
at org.jooq.impl.Tools.translate(Tools.java:1983)
at
org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:676)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:363)
at
org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:135)
at
ru.bvn13.priceprocessor.database.Managers.ProductsManager.lambda$saveProduct$1(ProductsManager.java:159)
at org.jooq.impl.DefaultDSLContext$3.run(DefaultDSLContext.java:525)
at org.jooq.impl.DefaultDSLContext$3.run(DefaultDSLContext.java:522)
at
org.jooq.impl.DefaultDSLContext.lambda$transactionResult0$0(DefaultDSLContext.java:468)
at org.jooq.impl.Tools$10$1.block(Tools.java:3291)
at java.util.concurrent.ForkJoinPool.managedBlock(ForkJoinPool.java:3323)
at org.jooq.impl.Tools$10.get(Tools.java:3288)
at
org.jooq.impl.DefaultDSLContext.transactionResult0(DefaultDSLContext.java:505)
at
org.jooq.impl.DefaultDSLContext.transactionResult(DefaultDSLContext.java:438)
at org.jooq.impl.DefaultDSLContext.transaction(DefaultDSLContext.java:522)
at
ru.bvn13.priceprocessor.database.Managers.ProductsManager.saveProduct(ProductsManager.java:127)
at
ru.bvn13.priceprocessor.workers.PriceLoaderFromFileWorker.readFile(PriceLoaderFromFileWorker.java:316)
at
ru.bvn13.priceprocessor.workers.PriceLoaderFromFileWorker.loadFile(PriceLoaderFromFileWorker.java:175)
at
ru.bvn13.priceprocessor.workers.PriceLoaderFromFileWorker.lambda$startLoadingFile$0(PriceLoaderFromFileWorker.java:122)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ОШИБКА: столбец "count" имеет
тип numeric, а выражение - character varying
Подсказка: Перепишите выражение или преобразуйте его тип.
Позиция: 139
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2189)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
at
org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
at
org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:158)
at
org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:195)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:431)
at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:335)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:349)
... 16 more
Affected row(s) : 1
Query executed : Total: 5.158ms
Finishing : Total: 5.285ms, +0.126ms
воскресенье, 28 мая 2017 г., 20:31:37 UTC+3 пользователь Lukas Eder написал:
>
> Thank you very much for your message. Could you show the stack trace that
> is producing this error? Also, if you turn on debug logging (
> https://www.jooq.org/doc/latest/manual/sql-execution/logging/), what is
> the generated SQL string that is sent to the JDBC driver by jOOQ?
>
> 2017-05-28 12:11 GMT+02:00 Вячеслав Бойко <[email protected] <javascript:>
> >:
>
>>
>> I have error descibed in subject.
>>
>> Having simple table into PostgreSQL 9.6 database:
>>
>> CREATE TABLE public.products_new
>> (
>> id bigint NOT NULL DEFAULT nextval('products_new_id_seq'::regclass),
>> provider_id bigint NOT NULL,
>> name character varying NOT NULL,
>> vendor_code character varying,
>> manufacturer character varying,
>> min_count numeric NOT NULL DEFAULT 0,
>> count numeric NOT NULL DEFAULT 0,
>> price numeric NOT NULL DEFAULT 0,
>> date_loaded character varying,
>> is_processed boolean NOT NULL DEFAULT false,
>> CONSTRAINT pri_products_new_id PRIMARY KEY (id),
>> CONSTRAINT fk_products_new_provider_id FOREIGN KEY (provider_id)
>> REFERENCES public.providers (id) MATCH SIMPLE
>> ON UPDATE NO ACTION ON DELETE NO ACTION
>> )
>> WITH (
>> OIDS=FALSE
>> );
>> ALTER TABLE public.products_new
>> OWNER TO priceprocessoruser;
>>
>>
>>
>> using simple insert:
>>
>> public static class Product {
>> public Long providerId = 0L;
>> public String vendorCode = "";
>> public String name = "";
>> public String manufacturer = "";
>> public BigDecimal minCount = new BigDecimal(0);
>> public BigDecimal count = new BigDecimal(0);
>> public BigDecimal price = new BigDecimal(0);
>> }
>>
>> /* initializing Product skipped */
>>
>> DSL.using(ctx)
>> .insertInto(Products.PRODUCTS)
>> .set(Products.PRODUCTS.PROVIDER_ID, productData.providerId)
>> .set(Products.PRODUCTS.NAME, productData.name)
>> .set(Products.PRODUCTS.VENDOR_CODE, productData.vendorCode)
>> .set(Products.PRODUCTS.MANUFACTURER, productData.manufacturer)
>> .set(Products.PRODUCTS.COUNT, productData.count)
>> .set(Products.PRODUCTS.MIN_COUNT, productData.minCount)
>> .set(Products.PRODUCTS.PRICE, productData.price)
>> .set(Products.PRODUCTS.DATE_UPDATED, dateFormat.format(dateNow))
>> .execute();
>>
>>
>>
>>
>>
>>
>>
>> made using jOOQ from maven:
>>
>>
>> <dependency>
>> <groupId>org.jooq</groupId>
>> <artifactId>jooq</artifactId>
>> <version>3.9.2</version>
>> </dependency>
>>
>>
>>
>> *What's wrong???*
>>
>> --
>> 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] <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 [email protected].
For more options, visit https://groups.google.com/d/optout.
