This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes DROP table question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "DROP table question" Watch "DROP table question" New topic
Author

DROP table question

Melinda Savoy
Ranch Hand

Joined: Jun 21, 2005
Posts: 386

In the following code I am trying to DROP table from a sql DB:

public class SQLServerInitTest extends TestCase {

static String[] DB_TABLE = {"POI"};

/*
* @getConnect
*/
public Connection getConnection(){[t][t]
// Make connection to a SQL Server 2000 database.
Connection con = null;
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://"+ HOST_NAME +":1433;DatabaseName="+ DB_NAME +";User="+ USER_NAME +";Password="+ PASSWORD +"");
}
catch (Exception e) {
fail("Get Connection getConnection threw an exception: " +(e.getMessage()));
}
return con;
}
/*
* @testDropTable
*/
public void testDropTable () throws SQLException{
try {
Connection con = getConnection();
Statement stmt = con.createStatement();
String dropTable = "DROP TABLE ";
String[] tables = DB_TABLE;
for (int i = 0; i < tables.length; i++){
int temp = stmt.executeUpdate(dropTable + tables);
}
}
catch (Exception e) {
fail("Drop Table testDropTable threw an exception: " +(e.getMessage()));
}
}
}

I am getting the following error within the testDropTable method:

Drop Table testDropTable threw an exception: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1:Unclosed quotation mark before the character string 'Ljava.lang.String;@1dd9c6d5'.

In this array I can add additional tables if I need but when I try and drop a this single table from the array I get the error above.

Any help or direction would be appreciated.

[ August 01, 2005: Message edited by: Melinda Savoy ]
[ August 01, 2005: Message edited by: Melinda Savoy ]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41040
    
  43

for (int i = 0; i < tables.length; i++) {
String stringCode = new String();
stringCode = stringCode + tables;
}


This piece of code doesn't do anything. Are you trying to concatenate all the table names into one piece of SQL? In cases like these it helps to print out the actual statement sent to the DB, something like



That should point to your problem pretty quickly.
[ August 01, 2005: Message edited by: Ulf Dittmer ]

Ping & DNS - my free Android networking tools app
Melinda Savoy
Ranch Hand

Joined: Jun 21, 2005
Posts: 386

Ulf,

Thanks so much for the reply. Actually I just edited my question because I moved the DROP TABLE statement up into the FOR LOOP because I figured out that the "stringcode" statements was not doing anything. I had gotten the code online. I am now getting another error that I put in my edited statement. If you don't mind looking at that again it would be appreciated.

Thanks.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

int temp = stmt.executeUpdate(dropTable + tables);

Do you mean to refer to the whole tables array or just one element at a time?
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Originally posted by Ulf Dittmer:
System.out.println(dropTable + tables);
That should point to your problem pretty quickly.


Oh yeah, what he said. :roll:
Melinda Savoy
Ranch Hand

Joined: Jun 21, 2005
Posts: 386

I thought I had figured out my problem. I have a new twist to this drop table code whereby I am trying to check if a table exists in the DB to see whether I need to drop the table or not.

Here is my I have tried in trying to use the getMetaData object ( I saw this in the search function of this site but apparently can't make it work):

public void testDropTable () throws SQLException{
Connection con = getConnection();
Statement stmt = con.createStatement();
if (con.getMetaData().getTables(null, null, "POITest", null) != null){
try {
String dropTable = "DROP TABLE ";
String[] tables = DB_TABLE;
for (int i = 0; i < tables.length; i++){
String stringCode = new String();
stringCode = stringCode + tables[i];
//System.out.println(dropTable + tables[i]);
// Drop each table in the array.
int temp = stmt.executeUpdate(dropTable + tables[i]);
}
}
catch (Exception e) {
fail("Drop Table testDropTable threw an exception: " +(e.getMessage()));
}
}
con.close();
}


Any direction or help would be appreciated. I thank you all for your help and time.

Thank you.
[ August 01, 2005: Message edited by: Melinda Savoy ]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41040
    
  43

String stringCode = new String();
stringCode = stringCode + tables[i];


This code still doesn't do anything. It can be deleted w/o altering the program.

It's hard to give advice when you don't say what's happening. What does "it doesn't work" mean? Are you getting exceptions? If so, which ones? In which line? What are your preconditions that are not being transformed to your postconditions the way you are expecting?
 
Consider Paul's rocket mass heater.
 
subject: DROP table question
 
Similar Threads
MetaData question
Why cant connect to another computer in lan?
connection object question
Newbie help please on SQL connection problem
Hi,I have a problem in database connection