10 Replies Latest reply on May 28, 2009 9:42 AM by 843859

    Derby - Syntax error, why?

    843859

      Hello,

      I'm stumped as to why this doesn't work (the commented part works fine)

          try { String connn = "jdbc:derby:test;create=true"; Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance(); Connection connect = DriverManager.getConnection( connn ); //     Statement stat; // stat = connect.createStatement(); // stat.executeUpdate( "CREATE TABLE City ( NAME VARCHAR(35) NOT NULL )" ); // // Statement st; // st = connect.createStatement(); // st.executeUpdate("INSERT INTO City( Name ) " + // "VALUES( 'Amsterdam' )" ); // // PreparedStatement statement = connect.prepareStatement("SELECT Name FROM City"); // ResultSet resultSet = statement.executeQuery(); // while (resultSet.next()) { // System.out.println( resultSet.getString("Name") ); // }     Statement stat; stat = connect.createStatement(); stat.executeUpdate( "CREATE TABLE  weerstations ( "+ "stationsnummer VARCHAR(5) NOT NULL,  "+ "voortzetting  VARCHAR(5),"+ "naam          String NOT NULL,  "+ "positie        string NOT NULL,  "+ "terreinhoogte  DOUBLE(2, 1),  "+ "karakteristiek string NOT NULL,  "+ "grondsoort    string NOT NULL,  "+ "barometer      DOUBLE(3, 1),  "+ "windmeetmast  INT(2)" ); }     catch( Exception e ) {     e.printStackTrace();     }     finally{     System.exit( 1);     }

      The error it throws is

      java.sql.SQLSyntaxErrorException: Syntax error: Encountered "String" at line 1, column 110. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(Unknown Source) at Database.<init>(Database.java:49) at DatumSelectiePanel.<init>(DatumSelectiePanel.java:25) at WeerApp.<init>(WeerApp.java:88) at Test.createAndShowGUI(Test.java:32) at Test.access$0(Test.java:25) at Test$1.run(Test.java:49) at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:226) at java.awt.EventQueue.dispatchEvent(EventQueue.java:602) at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:275) at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:200) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:190) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:185) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:177) at java.awt.EventDispatchThread.run(EventDispatchThread.java:138) Caused by: java.sql.SQLException: Syntax error: Encountered "String" at line 1, column 110. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) ... 22 more Caused by: ERROR 42X01: Syntax error: Encountered "String" at line 1, column 110. at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source) ... 16 more

      (line 49 is stat.executeUpdate()

        • 1. Re: Derby - Syntax error, why?
          jschellSomeoneStoleMyAlias
          I suggest that you put spaces at the beginning of multiline concatenations. It makes them easier to see.

          Presumably 'string' is a data type in Derby. If it isn't then that won't work. Conversely 'varchar' is a data type in most databases.

          You might want to check on whether 'int' has a size.

          You are missing a closing parens on the create table.
          • 2. Re: Derby - Syntax error, why?
            843859
            Ah yes, cut off the parenthesis by accident while removing an extra line that was in there while it was still intended for sqlite.
            Which is also why the datatypes are a mess, which I really need to fix as this is supposed to run on postgresql and mysql as well.

            Anyhow.. it looks like this now
            stat.executeUpdate(
                 "CREATE TABLE    weerstations ( "+
                 " stationsnummer VARCHAR(5) NOT NULL,"+
                 " voortzetting   VARCHAR(5),"+
                 " naam           VARCHAR(255) NOT NULL,"+
                 " positie        VARCHAR(255) NOT NULL,"+
                 " terreinhoogte  DOUBLE(2, 1), "+
                 " karakteristiek VARCHAR(255) NOT NULL,"+
                 " grondsoort     VARCHAR(255) NOT NULL,"+
                 " barometer      DOUBLE(3, 1),"+
                 " windmeetmast   INT(2)" +
                 " )"
            );
            Which looks correct to me, but it's throwing
            java.sql.SQLSyntaxErrorException: Syntax error: Encountered "(" at line 1, column 194.
            [edit]:
            Thinking "I need this working" more than "I need this working properly" I also attempted
                           stat.executeUpdate(
                                "CREATE TABLE    weerstations ( "+
                                " stationsnummer VARCHAR NOT NULL,"+
                                " voortzetting   VARCHAR,"+
                                " naam           VARCHAR NOT NULL,"+
                                " positie        VARCHAR NOT NULL,"+
                                " terreinhoogte  DOUBLE(2, 1), "+
                                " karakteristiek VARCHAR NOT NULL,"+
                                " grondsoort     VARCHAR NOT NULL,"+
                                " barometer      DOUBLE(3, 1),"+
                                " windmeetmast   INT" +
                                " )"
                           );
            Which gets me a
            java.sql.SQLSyntaxErrorException: Syntax error: Encountered "NOT" at line 1, column 56.
            Edited by: Axeia on May 21, 2009 12:28 PM
            • 3. Re: Derby - Syntax error, why?
              jschellSomeoneStoleMyAlias
              Axeia wrote:
              Ah yes, cut off the parenthesis by accident while removing an extra line that was in there while it was still intended for sqlite.
              Which is also why the datatypes are a mess, which I really need to fix as this is supposed to run on postgresql and mysql as well.
              Unlikely. SQL is standarized. DDL isn't.

              You need to validate if 'double' is a valid data type. I do not think I have seen that in any database.

              varchar probably always requires a size to be useful. Because the only variant without would be of size one.
              • 4. Re: Derby - Syntax error, why?
                843859
                jschell wrote:
                Axeia wrote:
                Ah yes, cut off the parenthesis by accident while removing an extra line that was in there while it was still intended for sqlite.
                Which is also why the datatypes are a mess, which I really need to fix as this is supposed to run on postgresql and mysql as well.
                Unlikely. SQL is standarized. DDL isn't.
                Sure it is standardized except for the same vendor-specific differences that plague DML: SQL 2003 Foundation, chapter 11, paragraph 3 (at least in a 'freely' available draft version of the standard).
                You need to validate if 'double' is a valid data type. I do not think I have seen that in any database.
                DOUBLE is an abbreviation for the data type DOUBLE PRECISION (SQL 2003 Foundation chapter 4 paragraph 4). However DOUBLE does not have a precision and scale as shown in the code of the OP.
                varchar probably always requires a size to be useful. Because the only variant without would be of size one.
                I believe that is implementation dependent.

                Edited by: TheAvalanche on 22-mei-2009 14:56
                • 5. Re: Derby - Syntax error, why?
                  jschellSomeoneStoleMyAlias
                  TheAvalanche wrote:
                  jschell wrote:
                  Axeia wrote:
                  Ah yes, cut off the parenthesis by accident while removing an extra line that was in there while it was still intended for sqlite.
                  Which is also why the datatypes are a mess, which I really need to fix as this is supposed to run on postgresql and mysql as well.
                  Unlikely. SQL is standarized. DDL isn't.
                  Sure it is standardized except for the same vendor-specific differences that plague DML: SQL 2003 Foundation, chapter 11, paragraph 3 (at least in a 'freely' available draft version of the standard).
                  Not to the extent that non-trivial databases using anything more than very basic functionality are going to work across domains.
                  You need to validate if 'double' is a valid data type. I do not think I have seen that in any database.
                  DOUBLE is an abbreviation for the data type DOUBLE PRECISION (SQL 2003 Foundation chapter 4 paragraph 4). However DOUBLE does not have a precision and scale as shown in the code of the OP.
                  varchar probably always requires a size to be useful. Because the only variant without would be of size one.
                  I believe that is implementation dependent.
                  Which would be a very obvious example of how the language isn't standarized.
                  • 6. Re: Derby - Syntax error, why?
                    843859
                    jschell wrote:
                    Not to the extent that non-trivial databases using anything more than very basic functionality are going to work across domains.
                    <snip>
                    I believe that is implementation dependent.
                    Which would be a very obvious example of how the language isn't standarized.
                    All very true, but it applies equally to both DML and DDL, not specifically to DDL.
                    • 7. Re: Derby - Syntax error, why?
                      jschellSomeoneStoleMyAlias
                      TheAvalanche wrote:
                      jschell wrote:
                      Not to the extent that non-trivial databases using anything more than very basic functionality are going to work across domains.
                      <snip>
                      I believe that is implementation dependent.
                      Which would be a very obvious example of how the language isn't standarized.
                      All very true, but it applies equally to both DML and DDL, not specifically to DDL.
                      If you want to create a database independent solution then you are going to get further in a reasonable manner with DML versus DDL.

                      That isn't to say that attempting that is a good idea for all or even most solutions. I would say it isn't going to work for anything but smaller apps which do not need to scale.

                      Of course since the OP is using DDL then intent of my original point is still valid and presumably you are not suggesting that it isn't.
                      • 8. Re: Derby - Syntax error, why?
                        796311
                        int(2) is most likely not valid. int is usually a predefined size. Varchar it is good (and often required) to put in size.

                        so, you probably removed too much. Do one data type at a time
                        • 9. Re: Derby - Syntax error, why?
                          r035198x
                          Seems that the problem is about knowing which [data types are available in Derby|http://db.apache.org/derby/docs/10.1/ref/crefsqlj21305.html] .
                          I suggest you try the SQL in some SQL tool before putting it in Java code.

                          Edited by: r035198x on May 28, 2009 11:21 AM
                          • 10. Re: Derby - Syntax error, why?
                            843859
                            Yes, and a tip I've found useful on a number of similar occasions: System.out.println the SQL-statement, and copy it to an SQL-tool.