SQL Error from JDBC driver?

I am creating tables on oracle database using the JDBC driver i implemented.
I am gettign the sql exception error from the create sequece sql string.
1)
Here is my sql to create table.

strSQL = "create table mytable ( " +
"mytable_id number not null, " +      <====I want to increment this auto...
"subject varchar2(25) null, " +
"title varchar2(25) null, " +
"primary key(mytable_id))";
Statement stmt = ...
stmt.executeUpdate(strSQL.ToString());

strSQL ="create sequece mytable_id " +
"increment  1 start 1 ";

stmt.executeUpdate(strSQL.ToString());   <==== I am getting the error from here I believe.


In my insert sql, i am using myTable_id.nectVal().

2)
I also have a drop method which drops the tables.
When I drop the table, do i need to drop the sequece?
How do I do that?

Because when I create tables and create sequece and then drop only tables and try to recreate tables, I get an error saying the table or view already exist or something..
Sorry I don't have exact message.

THanks much
dkim18Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrigankCommented:
USe

strSQL ="create sequece mytable_id " +
"increment  1 start with 1 ";
0
mrigankCommented:
Use

strSQL ="create sequece mytable_id " +
"start with 1 increment  by 1  ";
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrigankCommented:
drop the sequence as well cos then the new sequence shall start with 1 and not the value at which it originally was incremented to.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

fargoCommented:
valid sequence options with oracle
Syntax:

   CREATE SEQUENCE [schema.]sequence_name option(s)

Options:
      INCREMENT BY int
      START WITH int
      MAXVALUE int | NOMAXVALUE
      MINVALUE int | NOMINVALUE
      CYCLE | NOCYCLE
      CACHE int | NOCACHE
      ORDER | NOORDER

So, the query posted by mrigank for creating the sequence is fine.

For dropping the sequence.
Syntax:

DROP SEQUENCE nameofsequence.

Drop the table using
Syntax:
DROP TABLE my_table (u can also specify the schema name.)



0
dkim18Author Commented:
sqlStr = "DROP sequence cust_id";
stmt.executeUpdate(sqlStr.toString());

I get an error from here, error is null.

0
dkim18Author Commented:
sqlStr = "create table customers (" +
                  "cust_id number not null," +
                  "lastname varchar2(25) null, " +
                  "firstname      varchar2(25) null, " +
                  "address varchar2(60) null, " +
                  "city varchar2(20) null," +
                  "state varchar2(2) null," +                  
                  "zip number null," +
                  "primary key(cust_id))";
                  
           
                   System.out.println("Executing: " + sqlStr);
                   stmt.executeUpdate(sqlStr.toString());
                  
                   sqlStr = "create sequence cust_id start with 1 increment by 1 ";
                  
                   System.out.println("Executing: " + sqlStr);
                   stmt.executeUpdate(sqlStr.toString());    

I get java.lang.NullPointerException again when I try to create the tables again after  I try to drop the table and sequence.

0
fargoCommented:
better use schema name with the db objects.

like for dropping the seq. use
DROP SEQUENCE schema_name.seq_name

for table use
DROP TABLE schema_name.table_name

if even doing this...u get errors..try to print the sql stmts. to see if things are going fine.
Otherwise, post ur code later on.

0
mrigankCommented:
which line do you get a NullPointer from ?
0
dkim18Author Commented:
I will try using the schema_name first...

 

public int executeUpdate(String sql) throws SQLException
    {
          System.out.println("DDWWWWStatement ResultSet executeUpdate");
          checkStatement(sql);
          
          rowCount = myState.executeUpdate(sql);  <===getting nullPointerexception from here
          myState = null;
          return rowCount;
          //return 0;
    }
0
dkim18Author Commented:
Sorry

This is from my Statement Class.

 public int executeUpdate(String sql) throws SQLException
    {
          System.out.println("DDWWWWStatement ResultSet executeUpdate");
          checkStatement(sql);
          
          rowCount = myState.executeUpdate(sql); <====
          myState = null;
          return rowCount;
          //return 0;
    }

Is it because i set myState = null afterward..?

0
mrigankCommented:
Where are you initialising myState

Once it is set to null, you have to re initilaise it...

myState = con.createStatement();
or which ever way you use
0
dkim18Author Commented:
In statement Classs, when I run execute sql, I get the connection back like this.


public void checkStatement(String sql) throws SQLException {
          System.out.println("CheckStaement : " + conn);
        if (myState == null) {
            //DISTINGUISH
            if (sql.indexOf("customers") != -1){
                  myState = conn.oracleCon.createStatement();
            }
            if (sql.indexOf("orders") != -1){
                  myState = conn.oracleCon.createStatement();
            }
            if (sql.indexOf("items") != -1){
                  myState = conn.mysqlCon.createStatement();
            }
            if (sql.indexOf("mySQL") != -1){
                  myState = conn.mysqlCon.createStatement();
            }
            if (sql.indexOf("myOracle") != -1){
                  myState = conn.oracleCon.createStatement();
            }
           
        }
    }
0
dkim18Author Commented:
Without create sequence sql, everything runs fine.
0
dkim18Author Commented:
i think i found my problem.
I am sending sql string and I am not catching it in my checkStatement..

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.