Exception executing INSERT statement HSQL Java

Question

I have this simple code that it's driving me crazy...

public void insertVectorEstacionario() {
    DBManager dbM = DBManager.instance();
    if(N == 3) {
        dbM.insert("INSERT INTO Markov(IdIndicador, FechaDesde, FechaHasta, Pv, Pa, Pr, Pn) VALUES(" + 
                Integer.toString(idIndicador) + "," + "#" + inicio_periodo.toString() + "#" + "," + "#" + fin_periodo.toString() + "#" + "," +
                Double.toString(vector_estacionario[0]) + "," + Double.toString(vector_estacionario[1]) + "," + Double.toString(vector_estacionario[2]) + "," + Double.toString(0.0) + ")");

    }

    if(N == 4) {
        dbM.insert("INSERT INTO Markov(IdIndicador, FechaDesde, FechaHasta, Pv, Pa, Pr, Pn) VALUES(" + 
        Integer.toString(idIndicador) + "," + "#" + inicio_periodo.toString() + "#" + "," + "#" + fin_periodo.toString() + "#" + "," +
        Double.toString(vector_estacionario[0]) + "," + Double.toString(vector_estacionario[1]) + "," + Double.toString(vector_estacionario[2]) + "," + Double.toString(vector_estacionario[3]) + ")");
    }
}

Now, the second insert statement that executes when N = 4 works just fine, the problem is with the first statement (when N = 3) that throws a nested Exception that goes like this:

net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: Z_0536632167783088E required: )
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:110)
at com.operativa.quartilegenerator.datamodel.DBManager.insert(DBManager.java:71)
at com.operativa.quartilegenerator.GenerarMarkov.insertVectorEstacionario(GenerarMarkov.java:85)
at com.operativa.quartilegenerator.QuartileGUI$16$1.doInBackground(QuartileGUI.java:969)
at com.operativa.quartilegenerator.QuartileGUI$16$1.doInBackground(QuartileGUI.java:1)
at javax.swing.SwingWorker$1.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at javax.swing.SwingWorker.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: Z_0536632167783088E required: )
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
at net.ucanaccess.jdbc.Execute.executeWrapped(Execute.java:62)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:121)
at net.ucanaccess.jdbc.Execute.execute(Execute.java:52)
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:107)
... 10 more
Caused by: org.hsqldb.HsqlException: unexpected token: Z_0536632167783088E required: )
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedTokenRequire(Unknown Source)
at org.hsqldb.ParserBase.readThis(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadValueExpressionOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadValueExpressionWithContext(Unknown Source)
at org.hsqldb.ParserDQL.readRow(Unknown Source)
at org.hsqldb.ParserDQL.XreadContextuallyTypedTable(Unknown Source)
at org.hsqldb.ParserDML.compileInsertStatement(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 16 more

Both statements are almost the same, it only change the fourth argument. I tried everything but cannot make this work. Thanks in advance for any help provided.

WITH PREPARE_STATEMENT:

public void insertVectorEstacionario() {
    DBManager dbM = DBManager.instance();
    PreparedStatement n3 = null;

    String strn3 = String.format("INSERT INTO Markov(IdIndicador, FechaDesde, FechaHasta, Pv, Pa, Pr, Pn) VALUES(" + 
            Integer.toString(idIndicador) + "," + "#" + inicio_periodo.toString() + "#" + "," + "#" + fin_periodo.toString() + "#" + "," +
            Double.toString(vector_estacionario[0]) + "," + Double.toString(vector_estacionario[1]) + "," + Double.toString(vector_estacionario[2]) + "," + "0.823749287592)");

    if(N == 3) {

        try {
            n3 = dbM.getConnection().prepareStatement(strn3);
            n3.execute();
        } catch(Exception e){e.printStackTrace();}
    }

}

AND THIS IS THE STACK:

net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: Z_8901313574667937E required: )
at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:455)
at com.operativa.quartilegenerator.GenerarMarkov.insertVectorEstacionario(GenerarMarkov.java:101)
at com.operativa.quartilegenerator.QuartileGUI$16$1.doInBackground(QuartileGUI.java:969)
at com.operativa.quartilegenerator.QuartileGUI$16$1.doInBackground(QuartileGUI.java:1)
at javax.swing.SwingWorker$1.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at javax.swing.SwingWorker.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: Z_8901313574667937E required: )
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:453)
... 9 more
Caused by: org.hsqldb.HsqlException: unexpected token: Z_8901313574667937E required: )
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedTokenRequire(Unknown Source)
at org.hsqldb.ParserBase.readThis(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadValueExpressionOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadValueExpressionWithContext(Unknown Source)
at org.hsqldb.ParserDQL.readRow(Unknown Source)
at org.hsqldb.ParserDQL.XreadContextuallyTypedTable(Unknown Source)
at org.hsqldb.ParserDML.compileInsertStatement(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 12 more

Use prepared statements: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html. You forgot many quotes in your query, and you're not escaping them when inside the parameter values. prepared statements will do all this for you, and will avoid SQL injection attacks. And the code will be much easier to read as well. — JB Nizet, Feb 22 '14 at 21:47
This is getting tiresome. Someone must write a variation of [ZA̡͊͠͝LGΌ ISͮ̂҉̯͈͕̹̘̱ TO͇̹̺ͅƝ̴ȳ̳ TH̘Ë͖́̉ ͠P̯͍̭O̚N̐Y̡ H̸̡̪̯ͨ͊̽̅̾̎Ȩ̬̩̾͛ͪ̈́̀́͘ ̶̧̨̱̹̭̯ͧ̾ͬC̷̙̲̝͖ͭ̏ͥͮ͟Oͮ͏̮̪̝͍M̲̖͊̒ͪͩͬ̚̚͜Ȇ̴̟̟͙̞ͩ͌͝S̨̥̫͎̭ͯ̿̔̀ͅ](http://stackoverflow.com/questions/1732348/regex-match-open-tags-except-xhtml-self-contained-tags) for people concatenating strings instead of using prepared statements. — Anthony Accioly, Feb 22 '14 at 22:00
Hello guys thank you for your quick response. I tried everything you said... none of it worked for me, when I tried PreparedStatement it throws a similar exception. What really bothers me is that the second insert statement (sloppy as it is) works fine and both are almost the same. — theITguy, Feb 22 '14 at 22:30
Show your code using the prepared statement, and show the complete stack trace of the exception. — JB Nizet, Feb 22 '14 at 22:51
I added the code with prepared statement below the original one, I just added the N = 3 part since that is the one causing trouble. — theITguy, Feb 23 '14 at 00:18

jamadei · Accepted Answer · 2014-02-23T12:56:45.123

0

the sql statement, produced in the way shown above, could contain a scientific notation, like this:

insert into T1(xxx) values(4.0E-16);

and UCanAccess doesn't recognize this numeric format. You should use a prepared statement with parametric values or properly format double values (e.g. using DecimalFormat).

edited Feb 23 '14 at 12:56

answered Feb 23 '14 at 11:35

jamadei

1,700
9
8

I didn't realize that could be the problem, however I tried what you said with this piece of code: DecimalFormat df = new DecimalFormat("#.#####"); and this is the output: INSERT INTO Markov(IdIndicador, FechaDesde, FechaHasta, Pv, Pa, Pr, Pn) VALUES(1,#2000-08-08#,#2010-08-08#,0,50177,0,49822,0,00002,0.0) for some goddamn reason the decimal separator changes in the output from dot to comma even tough I explicitly set it in the format, so now the exception throwned says that there is a column misscount, which is correct since the comma is the column separator. – theITguy Feb 23 '14 at 14:34
I fixed it finally, It was the Scientific Notation as jamadei said. The separator problem has something to do with the Locale, I fixed it following this post http://stackoverflow.com/questions/2282356/in-java-why-does-the-decimal-separator-follow-the-locales-language-not-its-co Thanks everybody for the help. I really appreciate it. – theITguy Feb 23 '14 at 14:43

Exception executing INSERT statement HSQL Java

1 Answers1