If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > problem dropping and creating table in java stored procedure

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 08-14-08, 06:37
govindk govindk is offline
Registered User
 
Join Date: Aug 2008
Posts: 3
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
Reply With Quote
  #2 (permalink)  
Old 08-14-08, 07:35
n_i n_i is offline
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
Quote:
Originally Posted by govindk
it is not working. Any suggestions please??
Certainly. Define "not working".
Reply With Quote
  #3 (permalink)  
Old 08-14-08, 08:31
stolze stolze is offline
Registered User
 
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
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
Reply With Quote
  #4 (permalink)  
Old 08-19-08, 08:37
govindk govindk is offline
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On