Insert fails

[monetdb-team]

Date: 2014-03-20 17:18:01 +0100
From: Radovan Bičiště <<radovan.biciste>>
To: clients devs <>
Version: 11.17.13 (Jan2014-SP1)
CC: @njnes

Last updated: 2014-05-22 09:52:25 +0200

Comment 19703

Date: 2014-03-20 17:18:01 +0100
From: Radovan Bičiště <<radovan.biciste>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:27.0) Gecko/20100101 Firefox/27.0
Build Identifier:

Insert fails with syntax error when running process from KETTLE (http://ketttle.pentaho.com). Issue seems to be related to certain combination of input values bound to a prepared statement.
Log from the tool:
2014/03/20 16:58:12 - Produkty - MonetDB.0 - Prepared statement : INSERT INTO produkty_stage (produkt_id, pdk, sukl, id_zbozi, nazev, brand, atc1, atc2, atc3, ean, vyrobce) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2014/03/20 16:58:12 - Produkty - MonetDB.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Because of an error, this step can't continue:
2014/03/20 16:58:12 - Produkty - MonetDB.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleException:
2014/03/20 16:58:12 - Produkty - MonetDB.0 - Error inserting row into table [produkty_stage] with values: [50071486], [2861681], [-], [50071486], [EUCERIN EVEN BRIGHTER DENNÍ KRÉM 50ML PROMO PACK], [BEIE], [-], [-], [-], [9005800222462], [BEIERSDORF S.R.O.]
2014/03/20 16:58:12 - Produkty - MonetDB.0 -
2014/03/20 16:58:12 - Produkty - MonetDB.0 - Error inserting/updating row
2014/03/20 16:58:12 - Produkty - MonetDB.0 - syntax error, unexpected ',' in: "exec 2(,"
2014/03/20 16:58:12 - Produkty - MonetDB.0 -
2014/03/20 16:58:12 - Produkty - MonetDB.0 -
2014/03/20 16:58:12 - Produkty - MonetDB.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:445)
2014/03/20 16:58:12 - Produkty - MonetDB.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:128)
2014/03/20 16:58:12 - Produkty - MonetDB.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
2014/03/20 16:58:12 - Produkty - MonetDB.0 - at java.lang.Thread.run(Thread.java:744)
2014/03/20 16:58:12 - Produkty - MonetDB.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
2014/03/20 16:58:12 - Produkty - MonetDB.0 - Error inserting/updating row
2014/03/20 16:58:12 - Produkty - MonetDB.0 - syntax error, unexpected ',' in: "exec 2(,"
2014/03/20 16:58:12 - Produkty - MonetDB.0 -
2014/03/20 16:58:12 - Produkty - MonetDB.0 - at org.pentaho.di.core.database.Database.insertRow(Database.java:1193)
2014/03/20 16:58:12 - Produkty - MonetDB.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:284)
2014/03/20 16:58:12 - Produkty - MonetDB.0 - ... 3 more
2014/03/20 16:58:12 - Produkty - MonetDB.0 - Caused by: java.sql.SQLException: syntax error, unexpected ',' in: "exec 2(,"
2014/03/20 16:58:12 - Produkty - MonetDB.0 - at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2535)
2014/03/20 16:58:12 - Produkty - MonetDB.0 - at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2284)
2014/03/20 16:58:12 - Produkty - MonetDB.0 - at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:508)
2014/03/20 16:58:12 - Produkty - MonetDB.0 - at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:349)
2014/03/20 16:58:12 - Produkty - MonetDB.0 - at nl.cwi.monetdb.jdbc.MonetPreparedStatement.execute(MonetPreparedStatement.java:247)
2014/03/20 16:58:12 - Produkty - MonetDB.0 - at nl.cwi.monetdb.jdbc.MonetPreparedStatement.executeUpdate(MonetPreparedStatement.java:287)
2014/03/20 16:58:12 - Produkty - MonetDB.0 - at org.pentaho.di.core.database.Database.insertRow(Database.java:1153)
2014/03/20 16:58:12 - Produkty - MonetDB.0 - ... 4 more
2014/03/20 16:58:12 - cl_crm_dw2monet_produkty - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Errors detected!
2014/03/20 16:58:12 - ceos.cl.dwh.oracle - Statement canceled!

Reproducible: Always

Steps to Reproduce:

1. run process for transfer data from Oracle to Monet
2. watch the error happen.

Actual Results:

Syntax error

Expected Results:

Insert of row.

Comment 19736

Date: 2014-04-09 13:07:46 +0200
From: @njnes

Could you tell us what the table definition of produkty_stage is? The values it self and the insert statement do not tell us that.

Comment 19737

Date: 2014-04-09 13:13:03 +0200
From: Radovan Bičiště <<radovan.biciste>>

Thank you for looking into it.
The definition of the table is as follows:

CREATE TABLE drmaxtst.produkty_stage
(
produkt_id bigint,
pdk varchar(25),
forma varchar(25),
atc1 varchar(25),
atc2 varchar(25),
atc3 varchar(25),
ean varchar(25),
sukl varchar(25),
alias_vyrobce varchar(85),
stat_puvodu varchar(15),
doplnek varchar(55),
zbozi_id varchar(25),
pdk_sukl_ean varchar(255),
kod_pzt varchar(20),
nadzasoba varchar(6),
drz_sukl varchar(8),
datum_vytvoreni timestamp,
skupina int,
akce varchar(8),
procento decimal(14,4),
apa varchar(12),
id_zbozi bigint,
ciselnik varchar(6),
pozlist varchar(6),
brand varchar(35),
k_skupina varchar(100),
k_podskupina varchar(100),
nazev_vyrobce varchar(100),
nazev_korporace varchar(100),
atc1_popis varchar(100),
atc2_popis varchar(100),
atc3_popis varchar(100),
typ_vydeje varchar(5),
preference varchar(1),
category_group varchar(10),
subcategory1 varchar(3),
subcategory2 varchar(4),
subcategory3 varchar(6),
datum_prirazeni_vyrobci timestamp,
subcategory4 varchar(8),
subcategory5 varchar(8),
cenova_agresivita varchar(15),
kategorizace int,
kategorie varchar(10),
kategorie_ii varchar(2),
kategorie_iii varchar(3),
kategorie_iv varchar(4),
kategorie_v varchar(6),
kategorie_vi varchar(10),
kategorie_jmeno varchar(200),
kategorie_farmis varchar(50),
vyrobce varchar(100),
id_cenova_agresivita varchar(20),
nazev_orig varchar(150),
nazev varchar(150)
);

Comment 19780

Date: 2014-04-25 16:22:31 +0200
From: Radovan Bičiště <<radovan.biciste>>

Eventuallly I had to fix it myself. Problem was in MonetPreparedStament on line 983:
int dot = xStr.indexOf(".");
if (dot != 0)
xStr = xStr.substring(0, Math.min(xStr.length(), dot + 1 + scale[i]));

works for me as
int dot = xStr.indexOf(".");
if (dot >= 0)
xStr = xStr.substring(0, Math.min(xStr.length(), dot + 1 + scale[i]));

If the number does not contain decimal point ".", the method returns empty string because method indexOf returns -1.

Comment 19781

Date: 2014-04-25 16:37:42 +0200
From: Radovan Bičiště <<radovan.biciste>>

Created attachment 283
MonetPreparedStatement.java

Modified class.

Attached file: MonetPreparedStatement.java (text/x-java, 85819 bytes)
Description: MonetPreparedStatement.java

Comment 19788

Date: 2014-04-30 10:54:29 +0200
From: MonetDB Mercurial Repository <>

Changeset 53d0352eaa3b made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=53d0352eaa3b

Changeset description:

Fix for bug #3456.  Bumped version number.

Comment 19789

Date: 2014-04-30 10:54:50 +0200
From: @sjoerdmullender

Patch applied. Thanks.