Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    3

    Unanswered: problem dropping and creating table in java stored procedure

    Hi,

    I am newbie to java stored procedure and db2. In my java stored procedure I am trying trying to drop a table and then create it. But it is not working. Any suggestions please??

    java stored procedure code snippet

    Code:
    String localquery=null;
    		//Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
    		Connection con1 = 
    			DriverManager.getConnection("jdbc:default:connection");
    		
    		
    		PreparedStatement stmt = null;
    		//ResultSet rs = null;
    		try{
    			localquery= "drop table features";
    			stmt = con1.prepareStatement(localquery);
    			stmt.execute(localquery);
    			//con.commit();
    			System.out.println("Features Table deleted");
    		}
    		catch(SQLException tabledoesnotExist)
    		{
    			System.out.println("Features Table does not exist");
    		}
    		/*TABLE DELETED*/
    			
    		/*CREATE TABLE FEATURES*/
    		try{
    			localquery="CREATE TABLE FEATURES(ColumnName Varchar(50), Keyword Varchar(50),Weight float)";
    			stmt = con1.prepareStatement(localquery);
    			stmt.execute(localquery);
    			//con.commit();
    			System.out.println("Features Table created");
    		}
    		catch(SQLException tabledoesnotExist)
    		{
    			System.out.println("Problem in creating Features Table");
    		}
    		
    		/*TABLE CREATED*/
    regards,
    govind

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by govindk
    it is not working. Any suggestions please??
    Certainly. Define "not working".
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    A few things to think about:
    • Dropping/creating a table may not only fail because the table doesn't exist but also due to many other reasons.
    • Each database object is uniquely identified by a fully-qualified name, comprised of a schema name and an unqualified name. You only specify the unqualified name...
    • "DROP TABLE" is not a query, so I would use proper variable names and not "localquery".
    • A stored procedure is executed in the context of DB2 - which is a completely different process than your shell. So where should the "println" become visible?
    • You should use some proper indentation to make your code readable.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Aug 2008
    Posts
    3
    thanks for suggestions.

    • inspite of giving the fully qualified name it doesn't works.
    • I inserted println while testing stored procedure as a stand alone program. So my mistake i forgot to remove it.
    • I will be more careful about proper indentation from next time.



    My stored procedure runs properly if i run it with db2admin account. Hence I think it is a privilege problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •