Home » SQL & PL/SQL » SQL & PL/SQL » get data from column name with "ö" (M$ SQL, JDBC) and import it with sqlldr to Oracle (Oracle 10g and M$ SQL Server)
get data from column name with "ö" (M$ SQL, JDBC) and import it with sqlldr to Oracle [message #601826] |
Tue, 26 November 2013 03:49 |
|
rc3d
Messages: 213 Registered: September 2013 Location: Baden-Württemberg
|
Senior Member |
|
|
Hi
with this java code I get the data from SQL Server and write it to a file. This file I would import to Oracle DB.
> cat getDB.java
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.sql.*;
public class getDB {
public static void main(String[] args) throws Exception {
System.out.println("00000");
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager
.getConnection("jdbc:sqlserver://hostname.domain:1433;databaseName=test;user=test;password=test");
Statement stmt = con.createStatement();
ResultSet rset = stmt.executeQuery("select DB_DatenbankLösung, DB_Instanz, DB_Name, DB_Gruppe, DB_Software, DB_Version, DB_Description, DB_Type, DB_Edition, DB_Architecture, DB_SupportTime, DB_BackupType, DB_DBID, DB_Export, DB_Monitoring, DB_Connect, DB_Port, DB_Login, DB_Verantwortlich, DB_DBA1, DB_DBA2, DB_DBA3, DB_Remark, HW_Hardwarename, HW_Standort, OS_HostName, OS_OS, OS_Version, OS_Verwendung, OS_ServerArt, OS_CPU, OS_Bemerkungen, CON_Vertrag, CON_Vertragsbezeichnung, KL_Kunde, KL_Kundenloesung from test");
BufferedWriter SERVER_DB_DAT = new BufferedWriter(new
FileWriter("/opt/tools/db.csv"));
while (rset.next()) {
System.out.println (rset.getString("DB_DatenbankLösung"));
// System.out.println (rset.getString (3));
//
SERVER_DB_DAT.write("\"" + rset.getString("DB_DatenbankLösung")
+ "\";\"" + rset.getString("DB_Instanz")
+ "\";\"" + rset.getString("DB_Name")
+ "\";\"" + rset.getString("DB_Gruppe")
+ "\";\"" + rset.getString("DB_Software")
+ "\";\"" + rset.getString("DB_Version")
+ "\";\"" + rset.getString("DB_Description")
+ "\";\"" + rset.getString("DB_Type")
+ "\";\"" + rset.getString("DB_Edition")
+ "\";\"" + rset.getString("DB_Architecture")
+ "\";\"" + rset.getString("DB_SupportTime")
+ "\";\"" + rset.getString("DB_BackupType")
+ "\";\"" + rset.getString("DB_DBID")
+ "\";\"" + rset.getString("DB_Export")
+ "\";\"" + rset.getString("DB_Monitoring")
+ "\";\"" + rset.getString("DB_Connect")
+ "\";\"" + rset.getString("DB_Port")
+ "\";\"" + rset.getString("DB_Login")
+ "\";\"" + rset.getString("DB_Verantwortlich")
+ "\";\"" + rset.getString("DB_DBA1")
+ "\";\"" + rset.getString("DB_DBA2")
+ "\";\"" + rset.getString("DB_DBA3")
+ "\";\"" + rset.getString("DB_Remark")
+ "\";\"" + rset.getString("HW_Hardwarename")
+ "\";\"" + rset.getString("HW_Standort")
+ "\";\"" + rset.getString("OS_HostName")
+ "\";\"" + rset.getString("OS_OS")
+ "\";\"" + rset.getString("OS_Version")
+ "\";\"" + rset.getString("OS_Verwendung")
+ "\";\"" + rset.getString("OS_ServerArt")
+ "\";\"" + rset.getString("OS_CPU")
+ "\";\"" + rset.getString("OS_Bemerkungen")
+ "\";\"" + rset.getString("CON_Vertrag")
+ "\";\"" + rset.getString("CON_Vertragsbezeichnung")
+ "\";\"" + rset.getString("KL_Kunde")
+ "\";\"" + rset.getString("KL_Kundenloesung")
+ "\";\""
+ "\n");
}
SERVER_DB_DAT.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
error (because of ö in column name):
> ./getDB.sh
/opt/tools/db.csv: No such file or directory
getDB.java:17: warning: unmappable character for encoding ASCII
ResultSet rset = stmt.executeQuery("select DB_DatenbankL?sung, DB_Instanz, DB_Name, DB_Gruppe, DB_Software, DB_Version, DB_Description, DB_Type, DB_Edition, DB_Architecture, DB_SupportTime, DB_BackupType, DB_DBID, DB_Export, DB_Monitoring, DB_Connect, DB_Port, DB_Login, DB_Verantwortlich, DB_DBA1, DB_DBA2, DB_DBA3, DB_Remark, HW_Hardwarename, HW_Standort, OS_HostName, OS_OS, OS_Version, OS_Verwendung, OS_ServerArt, OS_CPU, OS_Bemerkungen, CON_Vertrag, CON_Vertragsbezeichnung, KL_Kunde, KL_Kundenloesung from Export_Database");
^
getDB.java:26: warning: unmappable character for encoding ASCII
System.out.println (rset.getString("DB_DatenbankL?sung"));
^
getDB.java:29: warning: unmappable character for encoding ASCII
SERVER_DB_DAT.write("\"" + rset.getString("DB_DatenbankL?sung")
^
3 warnings
00000
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '?'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
at getDB.main(getDB.java:17)
cp: cannot access /opt/tools/db.csv
It's a Solaris 10 box.
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 20:46:29 CDT 2024
|