aspose file tools*
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
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: 387

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: 42620
    
  65

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: 387

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: 387

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: 42620
    
  65

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?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: DROP table question