• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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
0
dkim18
Asked:
dkim18
  • 7
  • 5
  • 2
2 Solutions
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now