Results 1 to 5 of 5
  1. #1
    mayub is offline Member
    Join Date
    Apr 2009
    Posts
    6
    Rep Power
    0

    Default drop table with suffix

    Hi Everyone,

    I have already spent 4 hours on finding a solution to creating a dynamic query to drop tables that have a suffix. There are 4 tables in the database.
    2 end with mayub. I need to write a dynamic sql to drop them. I am sure if will only work with SELECT, so the first query is wrong but the second did not work either.

    TableName is a variable that holds table names

    1
    stmt = Con.createStatement();
    String sufix = "mayub";
    String DROP_TABLE = "if (" + TableName.endsWith(sufix) + ") drop table " + TableName;
    stmt.execute(DROP_TABLE);

    2
    stmt = Con.createStatement();
    String DROP_TABLE = "if exists(select table_name from information_schema.tables" +
    "where table_name like '%mayub')"
    + " drop table " + TableName;
    stmt.execute(DROP_TABLE);

    Thanks,
    Mayub

  2. #2
    mayub is offline Member
    Join Date
    Apr 2009
    Posts
    6
    Rep Power
    0

    Default drop table dynamic query

    Could someone please help?

  3. #3
    OrangeDog's Avatar
    OrangeDog is offline Senior Member
    Join Date
    Jan 2009
    Location
    Cambridge, UK
    Posts
    838
    Rep Power
    6

    Default

    Well this is really an SQL question rather than a Java one. If there are only 2, why not just execute a static query to drop them?

    What are the final SQL statements you are sending and what are the error messages?
    Don't forget to mark threads as [SOLVED] and give reps to helpful posts.
    How To Ask Questions The Smart Way

  4. #4
    mayub is offline Member
    Join Date
    Apr 2009
    Posts
    6
    Rep Power
    0

    Default dynamic query to drop table

    OrangeDog, thanks for asking!

    Perhaps you are correct that it is an SQL rather than Java question, but I am using Java.

    I am writing the program in java. This program needs to do everything dynamically. Saying that there are 2 tables was only an example. Basically I need to create tables with a suffix based on the tables that exist in the database. Thus for example, if x, y, z, are tables in the database, I need to create tables x_1, y_1, z_1. I have succeeded in doing that dynamically, meaning if the existing table is x, append _1 and so on. However, before I create them I must drop x_1, y_1, z_1, but not x,y,z. In the following

    String dropTable = "drop table " + TableName. -- after the dot I see many methods in java. One such method is contains(CharSequence s), but it returns boolean, so the error is can't drop table TRUE. Substrings require beginIndex and would return string values, but do not give the correct result.
    Using concat, i.e. "drop table " + TableName + "_1" makes the table X_1_1, Y_1_1, which does not exist. Any ideas?

  5. #5
    OrangeDog's Avatar
    OrangeDog is offline Senior Member
    Join Date
    Jan 2009
    Location
    Cambridge, UK
    Posts
    838
    Rep Power
    6

    Default

    Make a list of the table names and remove the ones that don't match the "_1" pattern. Then just drop everything left in the list.
    Don't forget to mark threads as [SOLVED] and give reps to helpful posts.
    How To Ask Questions The Smart Way

Similar Threads

  1. Replies: 1
    Last Post: 04-01-2009, 10:28 AM
  2. Suffix Tree Library
    By jagatsastry in forum Advanced Java
    Replies: 2
    Last Post: 03-08-2009, 12:27 AM
  3. How to use a drop-down box in Struts
    By piterskiy74 in forum Web Frameworks
    Replies: 1
    Last Post: 02-13-2008, 11:42 AM
  4. Drop down menu
    By BenNeiderlander in forum New To Java
    Replies: 3
    Last Post: 02-05-2008, 07:35 AM
  5. Drag and drop
    By abhivenugopal in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 01-30-2008, 02:10 PM

Posting Permissions

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