0

I am using Altova XMLSpy to create database from one GML schema. But, all string type fields are converted as char(255). I need to make it nvarchar(255). Can I do this in Java and how? I think it would be usefull because I need to make more than one DB at this way, so I need to automate process. Or, (I know it is not the question's title) can I override xsd:String to something else that XMLSpy wolud recognize as nvarchar(255)?

P.S. XMLSpy offers me on export to I manualy change all of this, but column by column.

EDIT: This DB is empty, no records at all.

EDIT2: I solved my problem, partly. Now, it changes some of columns but not all. Here is my code. Where I am wrong?

public static void main(String[] args){
    DataTypeChanger test=new DataTypeChanger();
    test.connect();
    test.dropCheckConstraints(); // it drops them successfully, I don't need them.
    test.disableConstraints();
    test.changeDataType();
    //test.enableConstraints();

    try {
        /*test.st.close();
        test.rs.close();*/
        test.conn.close();
        System.out.println("Disconnected!");
    } catch (SQLException e) {
        e.printStackTrace();
    }

}

public void disableConstraints(){
    try {
        st = conn.createStatement();
        st.execute("EXEC sp_msforeachtable \"ALTER TABLE ? NOCHECK CONSTRAINT all\"");
    } catch (SQLException e) {
        e.printStackTrace();
        System.out.println("Query ne radi!");
    }
}

public void enableConstraints(){
    try {
        st = conn.createStatement();
        st.execute("exec sp_msforeachtable @command1=\"print '?'\", @command2=\"ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all\"");
    } catch (SQLException e) {
        e.printStackTrace();
        System.out.println("Query ne radi!");
    }
}

public void changeDataType(){
    try {
        st = conn.createStatement();
        Statement stt = conn.createStatement();
        rs = st.executeQuery("SELECT a.name as ColName, o.name AS TableName FROM sys.syscolumns AS a INNER JOIN sys.systypes AS b ON a.xtype = b.xtype AND b.name = 'char' AND a.length = 255 INNER JOIN sys.objects AS o ON a.id = o.object_id WHERE (o.type = 'u') AND (o.schema_id = 1)");
        while (rs.next()){
            String tName=rs.getString("TableName");
            String cName=rs.getString("ColName");
            //System.out.println(tName+"  "+cName);
            try{
                stt.execute("ALTER TABLE "+ tName+" ALTER COLUMN "+cName+" nvarchar(255)");
            }catch(SQLException ee){

            }
        }
        stt.close();
    } catch (SQLException e) {
        e.printStackTrace();
        System.out.println("Query ne radi!");
    }
}
Aleksandar
  • 1,163
  • 22
  • 41
  • Personally what I would do (since not knowing conversion of a single column), is make a new column, convert the data per-row, and then delete the old column and rename the new one. – Rogue Apr 29 '14 at 13:45
  • But how can I loop all tables and their columns od specific type? – Aleksandar Apr 29 '14 at 13:53
  • If there are no records, what are you converting...? – Rogue Apr 29 '14 at 14:18
  • @Rogue I am new in SQL. I need to change datatype of columns without knowing ther names. – Aleksandar Apr 29 '14 at 14:23
  • you probably don't need to use java to do this, you could just use sql server. and if you don't know the names of the columns you could query them from the database itself. see this post: http://stackoverflow.com/questions/2729126/how-to-find-column-names-for-all-tables-in-all-databases-in-sql-server – rhealitycheck Apr 29 '14 at 20:59

0 Answers0