#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    10
    Rep Power
    0

    Creaye type Enum in JDBC Java


    Hi,
    I have a problem. I have a functioning code, written in Java with Mysql. But now, I have to modify some instructions to work with PostgreSQL. And the problem is with the type Enum.
    This is the code in mysql

    Code:
    public static void createFPsTables(ArrayList<ItemsVector> item){
    
    		System.out.println("..............");
    		Statement st;
    		for (ItemsVector it : item){
    			Collections.sort(it.getDistinctAttributeVector());	
    			String columns = "";
    			try {
    
    				for (int i = 0; i < it.getDistinctAttributeVector().size(); i++ ){
    
    					if(i < it.getDistinctAttributeVector().size() - 1)
    						columns = columns + "" + it.getDistinctAttributeVector().get(i) + " VARCHAR(50), ";
    
    					else
    						columns = columns + "" + it.getDistinctAttributeVector().get(i) + " VARCHAR(50)";
    
    				}
    
    				st=(DbAccess.getConnection()).createStatement();
    				st.executeUpdate("CREATE TABLE IF NOT EXISTS fps_"+it.getTable()+" (idPattern INT NOT NULL AUTO_INCREMENT PRIMARY KEY, "+columns+", support INT, itemset INT,marked ENUM('TRUE','FALSE'),emerging enum('yes','no'));");
    				
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    	}
    My problem is how and where define type Enum in PostgreSQL. If I try in this mode:
    Code:
    public static void createFPsTables(ArrayList<ItemsVector> item){
    
    		System.out.println("			- Creazione delle tabelle di FREQUENT PATTERNS...");
    		
    		Statement st;
    		
    		for (ItemsVector it : item){
    			Collections.sort(it.getDistinctAttributeVector());	
    			String columns = "";
    			try {
    
    				for (int i = 0; i < it.getDistinctAttributeVector().size(); i++ ){
    
    					if(i < it.getDistinctAttributeVector().size() - 1)
    						columns = columns + "" + it.getDistinctAttributeVector().get(i) + " VARCHAR(50), ";
    
    					else
    						columns = columns + "" + it.getDistinctAttributeVector().get(i) + " VARCHAR(50)";
    
    				}
    				
    				st=(DbAccess.getConnection()).createStatement();
    				st.executeUpdate("CREATE TYPE tipoy AS ENUM ('yes','no','TRUE','FALSE')");
    				st.executeUpdate("CREATE TABLE IF NOT EXISTS fps_"+it.getTable()+" (idPattern SERIAL NOT NULL PRIMARY KEY, "+columns+", support INTEGER, itemset INTEGER,marked tipoy,emerging tipoy);");
    				
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    	}
    i have this error, that the typoy exists yet. Why? I have wrong to place my statement (create type...) in my code?
    Who can help me, please? Thanks!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    i have this error, that the typoy exists yet.
    You should always post the exact error message you get. And please post SQL only, don't mingle your SQL with your programming language. Print the generated SQL from within your program, then post only the SQL statements here.

    Judging from your obfuscated error message and SQL I'd say you have already created that enum type and therefor you cannot create it again.

    Your basic SQL is correct as far as I can tell: http://sqlfiddle.com/#!12/c554a/1
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    10
    Rep Power
    0
    Originally Posted by shammat
    You should always post the exact error message you get. And please post SQL only, don't mingle your SQL with your programming language. Print the generated SQL from within your program, then post only the SQL statements here.

    Judging from your obfuscated error message and SQL I'd say you have already created that enum type and therefor you cannot create it again.

    Your basic SQL is correct as far as I can tell: http://sqlfiddle.com/#!12/c554a/1
    I posted all the code because the problem appears when I use JDBC with that Postgres' instructions. The exact error message is "ERRORE: il tipo tipoy esiste giÓ". It's in italian but the equivalent i suppose to be "Error: The type tipoy exists yet".

    If i try only the instruction with postgres, the error is the same. But if i could change the name of the variable,it works for once. At the second chance,it will be an error. Probably it stores the type.
    So i suppose, that in my java code,that instruction is repeated more than once time and so it gives that error. But I dont' know where intervene.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    Originally Posted by esaurito
    But if i could change the name of the variable,it works for once. At the second chance,it will be an error. Probably it stores the type.
    Yes of course it does. You are creating a database object, and that is persistent until you drop it. The way your code is written, the second run of it will create that error.

    Unfortunately there is no "CREATE TYPE IF NOT EXISTS". But if you want to make your code work even when run multiple times, you could do a
    Code:
    drop type if exists tipoy cascade
    This removes any definition already present in the database and you can safely run a create type again.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    10
    Rep Power
    0
    Originally Posted by shammat
    Yes of course it does. You are creating a database object, and that is persistent until you drop it. The way your code is written, the second run of it will create that error.

    Unfortunately there is no "CREATE TYPE IF NOT EXISTS". But if you want to make your code work even when run multiple times, you could do a
    Code:
    drop type if exists tipoy cascade
    This removes any definition already present in the database and you can safely run a create type again.
    What an useful information,you give me! Now it seems to work. Even if there is another error Sorry but are few days that I'm using Postgresql. Is it possible that I can't give a number as name of table? If i try this code:
    Code:
    CREATE TABLE 19810916_19851220 (
      date date DEFAULT NULL,
      chn_chn varchar(50) DEFAULT NULL
      )
    i have the error: syntax error at "19810916" (this is my error's translation from italian). Instead, if i try with a letter before the number, like x19810916_19851220, so I haven't problems.

    UPDATE: effectively, i have to put " before and after my number CREATE TABLE "19810916_19851220") to remove the errors.
    I didn't think that was so problems in porting code from Mysql to Postgresql
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    Originally Posted by esaurito
    Is it possible that I can't give a number as name of table?
    Correct.
    A valid SQL identifier does not start with a number.

    This is explained in detail in the manual: http://www.postgresql.org/docs/curre...x-lexical.html

    In general I would strongly recommend to not use identifiers that require double quotes.
    It will save you a lot of trouble in the long run.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    10
    Rep Power
    0
    Originally Posted by shammat
    Correct.
    A valid SQL identifier does not start with a number.

    This is explained in detail in the manual: http://www.postgresql.org/docs/curre...x-lexical.html

    In general I would strongly recommend to not use identifiers that require double quotes.
    It will save you a lot of trouble in the long run.
    UPDATE: solution found, you can't read the rest of this message.

    But unfortunately now I have to "convert" the statements from mysql to Postgresql.
    I have this line
    Code:
    st.executeUpdate("create table if not exists "+it.getTable()+" (date DATE, "+columns+");");
    where it.getTable() returns 19810916_19851220 as table's name.
    How can I do now? I tried to put two others " but I've tested that isn't possible.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    10
    Rep Power
    0
    Originally Posted by shammat
    Yes of course it does. You are creating a database object, and that is persistent until you drop it. The way your code is written, the second run of it will create that error.

    Unfortunately there is no "CREATE TYPE IF NOT EXISTS". But if you want to make your code work even when run multiple times, you could do a
    Code:
    drop type if exists tipoy cascade
    This removes any definition already present in the database and you can safely run a create type again.
    Ok,don't answer to my previous post because I've found the solution.
    But your method
    Code:
    drop type if exists tipoy cascade
    I've noted that delete the object too, paired to that type.
    So if I create an object X of tipoy and then I delete tipoy, X will be deleted too Do you have other solutions,please?
    Thanks so much for your useful help!
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    Originally Posted by esaurito
    But your method
    Code:
    drop type if exists tipoy cascade
    I've noted that delete the object too, paired to that type.
    So if I create an object X of tipoy and then I delete tipoy, X will be deleted too
    That's because of the CASCADE keyword (see the manual for details).
    If you don't specify it and there is a table referencing that enum, you can't drop it.

    This kind of check is much better done through a check constraint anyway. So you might want to get rid of those dreaded ENUMS at all.

    It seems you are trying to implement your own schema migration/script management for your database. I would strongly recommend to have a look into Liquibase or Flyway to manage the maintenance of your DBMS objects. Using something like that is much better than letting your code create the tables "on the fly".
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo