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!");
}
}