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 Go to next message
rc3d
Messages: 182
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.
Re: get data from column name with "ö" (M$ SQL, JDBC) and import it with sqlldr to Oracle [message #601827 is a reply to message #601826] Tue, 26 November 2013 04:05 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
So the error is coming from the query against sqlserver?
Re: get data from column name with "ö" (M$ SQL, JDBC) and import it with sqlldr to Oracle [message #601830 is a reply to message #601827] Tue, 26 November 2013 04:22 Go to previous messageGo to next message
rc3d
Messages: 182
Registered: September 2013
Location: Baden-Württemberg
Senior Member
cookiemonster wrote on Tue, 26 November 2013 04:05
So the error is coming from the query against sqlserver?


yes. Because of ö in column name. I need to get data from SQL Server and import them to Oracle 10g.
Re: get data from column name with "ö" (M$ SQL, JDBC) and import it with sqlldr to Oracle [message #601831 is a reply to message #601830] Tue, 26 November 2013 04:27 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the error is at the sqlserver end, why are you asking us?
Re: get data from column name with "ö" (M$ SQL, JDBC) and import it with sqlldr to Oracle [message #601833 is a reply to message #601831] Tue, 26 November 2013 04:40 Go to previous message
rc3d
Messages: 182
Registered: September 2013
Location: Baden-Württemberg
Senior Member
I fixed myself. Ignore it.

Ä, ä 		\u00c4, \u00e4
Ö, ö 		\u00d6, \u00f6
Ü, ü 		\u00dc, \u00fc
ß 		\u00df


I replaced strange chars with Unicode code
Previous Topic: would i be able to run this query
Next Topic: Tools to learn to Become PL/SQL Developer
Goto Forum:
  


Current Time: Tue Sep 02 09:01:06 CDT 2014

Total time taken to generate the page: 0.14354 seconds