embedded apache derby sequence

Question

I have created a sequence in apache derby:

CREATE SEQUENCE app.patient_id_seq AS BIGINT START WITH 100000 INCREMENT BY 1;

I am getting next sequence value in derby console -

values NEXT VALUE FOR patient_id_seq;

But when I use it in java code to get next sequence it says -

java.sql.SQLSyntaxErrorException: SEQUENCE 'APP.PATIENT_ID_SEQ' does not exist

17:52:50,291 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)

17:52:50,301 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)

17:52:50,312 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)

17:52:50,323 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)

17:52:50,336 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)

17:52:50,347 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)

17:52:50,357 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.(Unknown Source)

17:52:50,371 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.jdbc.InternalDriver.newEmbedPreparedStatement(Unknown Source)

17:52:50,382 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)

17:52:50,397 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)

17:52:50,408 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at com.laput.dao.PatientDAO.savePatientDetails(PatientDAO.java:30)

17:52:50,420 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at com.laput.dao.PatientDAO.savePatientInfo(PatientDAO.java:21)

17:52:50,431 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at com.laput.controller.HospController.login(HospController.java:66)

17:52:50,443 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

17:52:50,454 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

17:52:50,465 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

17:52:50,477 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at java.lang.reflect.Method.invoke(Method.java:606)

17:52:50,491 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)

17:52:50,509 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)

17:52:50,522 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114)

17:52:50,535 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)

17:52:50,552 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)

17:52:50,566 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)

17:52:50,579 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)

17:52:50,591 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)

17:52:50,606 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)

17:52:50,618 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)

17:52:50,629 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)

17:52:50,640 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)

17:52:50,657 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)

17:52:50,669 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:295)

17:52:50,685 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)

17:52:50,697 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)

17:52:50,711 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149)

17:52:50,723 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:169)

17:52:50,735 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:145)

17:52:50,747 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:97)

17:52:50,767 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:102)

17:52:50,780 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:336)

17:52:50,792 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:488)

17:52:50,803 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.coyote.ajp.AjpProtocol$AjpConnectionHandler.process(AjpProtocol.java:420)

17:52:50,816 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:920)

17:52:50,827 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at java.lang.Thread.run(Thread.java:744)

17:52:50,838 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) Caused by: ERROR 42X94: SEQUENCE 'APP.PATIENT_ID_SEQ' does not exist.

17:52:50,849 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)

17:52:50,864 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)

17:52:50,875 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.impl.sql.compile.NextSequenceNode.bindExpression(Unknown Source)

17:52:50,886 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.impl.sql.compile.ValueNode.bindExpression(Unknown Source)

17:52:50,898 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(Unknown Source)

17:52:50,911 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(Unknown Source)

17:52:50,924 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.impl.sql.compile.RowResultSetNode.bindExpressions(Unknown Source)

17:52:50,935 ERROR [stderr] (ajp-localhost/127.0.0.1:8009-1) at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown Source)

====================

My java code to get sequence -

        ResultSet rs = conn.prepareStatement( "values  next value for patient_id_seq").executeQuery();

thanks, laput

Possible duplicate of [How to use SEQUENCE in Apache Derby?](http://stackoverflow.com/questions/5729063/how-to-use-sequence-in-apache-derby) — Mick Mnemonic, Aug 13 '16 at 23:22
Okay, the duplicate question probably doesn't help you much because you already seem to be using the correct syntax for getting the sequence value. Are you sure that the sequence is actually created -- and that you're connecting to the correct DB? — Mick Mnemonic, Aug 13 '16 at 23:28
Can you create a minimal test case to demonstrate the problem? — Bryan Pendleton, Aug 14 '16 at 01:09
Yes, I am connecting to same DB and the sequence is created. I can get sequence number when queried from DC prompt. Problem is from java side. — laput, Aug 15 '16 at 20:03

score 0 · Answer 1 · answered Aug 15 '16 at 15:41

OP here again, Also, tried creating sequence within DB with -

create table patient_info ( Patient_id bigint GENERATED ALWAYS AS IDENTITY (START WITH 100000, INCREMENT BY 1), first_name varchar(50), last_name varchar(50), phone_no int, address_ln1 varchar(100), address_ln2 varchar(100), city varchar(100), state varchar(50), CONSTRAINT primary_key PRIMARY KEY (Patient_id) );

But while inserting using prepared statement, it still expects "Patient_id" to be assigned/set in java code which should not be the case.

Both approaches failing.

score 0 · Answer 2 · answered Aug 15 '16 at 20:12

Bryan, Here is the details.

create sequence - CREATE SEQUENCE app.patient_id_seq AS BIGINT START WITH 100000 INCREMENT BY 1;

My code to acces sequence -

package com.laput.dao;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.PreparedStatement;

public class Testcase {
    Connection conn = null;
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Testcase tc = new Testcase();
        tc.createConnection();
        tc.savePatientDetails();

    }


    private void savePatientDetails(){

        try{
            ResultSet rs = conn.prepareStatement( "values  next value for patient_id_seq").executeQuery();
            rs.next();




            conn.commit();
            conn.close();
        }catch(Exception ex){
            ex.printStackTrace();
        }

    }


    private void createConnection()
    {

        try
        {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
            //Get a connection
            conn = DriverManager.getConnection("jdbc:derby:C:\\ProgramData\\tools\\apache-derby\\db-derby-10.12.1.1-bin\\hospdb"); 
        }
        catch (Exception except)
        {
            except.printStackTrace();
        }
    }   

}

I am able to access sequence through derby console. But from java i get the error as mentioned.

score 0 · Answer 3 · edited Oct 13 '16 at 02:42

0

SELECT NEXT VALUE FOR SEQ_ID FROM SYS.SYSSEQUENCES;

You can through this query to get the sequence next value.

edited Oct 13 '16 at 02:42

tarzanbappa

4,930
22
75
117

answered Oct 13 '16 at 02:21

liu246437

279
3
4
10

score 0 · Answer 4 · edited Oct 13 '16 at 04:09

the first query is get sequence name list in database. the second query is get sequence next value .

Oracle： SELECT SEQUENCE_NAME FROM USER_SEQUENCES; SELECT SEQ_ID.NEXTVAL FROM DUAL;

sqlsever： SELECT NAME FROM mydbname.sys.sequences; SELECT NEXT VALUE FOR mydbname.schemaName.SEQ_ID;

DB2 SELECT SEQNAME FROM SYSCAT.SEQUENCES WHERE SEQSCHEMA = 'schemaName'; SELECT SEQID.NEXTVAL FROM SYSIBM.SYSDUMMY1;

postgrsql： SELECT c.relname FROM pg_class c WHERE c.relkind = 'S'; SELECT NEXTVAL('SEQ_ID');

HirDB： SELECT SEQUENCE_NAME FROM MASTER.SQL_SEQUENCES;

JavaDB： SELECT SEQUENCENAME FROM SYS.SYSSEQUENCES； SELECT NEXT VALUE FOR SEQ_ID FROM SYS.SYSSEQUENCES;

embedded apache derby sequence

4 Answers4