Karthikeyan Subramaniam Karthikeyan Subramaniam - 6 months ago 53
Linux Question

BCP Utility Unable to Export Data in Linux Using JAVA:

I tried to execute the following piece of code to export data(myFileName.csv) :

bcp "select * from DataBase.schema.TABLE_NAME" queryout tableData.csv -c -t, , -S [server] -U [user] -P '[password(with special characters)]' > LogFile.txt


the above code is working fine in terminal.

In contrast, I tried the same piece of code using java.

File dir = new File("Mydirectory");
Path dataPath = Paths.get("tableData.csv");
List<String> val = new ArrayList();
val.add("bcp");
val.add("\"select * from " + [Database] + ".[Schema]." + table_name + "\"");
val.add("queryout");
val.add(dataPath.toString());
val.add("-c");
val.add("-t");
val.add(",");
val.add("-S");
val.add([server]);// ex: if Server is 10.0.0.1 then val.add("10.0.0.1");
val.add("-U");
val.add([user]); // ex: if User_name is TestA then val.add("TestA");
val.add("-P");
val.add([password(with special characters)]); // ex: if Password is !@#MyPassword*& then val.add("!@#MyPassword*&");
ProcessBuilder builder = new ProcessBuilder(val);
File logFile = new File("LogFile.txt");
System.out.println("BCP command :" + builder.command());
builder.redirectlogFile(logFile);
builder.directory(dir);
Process exec = builder.start();
System.out.println("BCP process completed : with errors :" + exec.waitFor());
System.out.println("BCP logFile :" + org.​apache.​commons.​io.FileUtils.readFileToString(logFile));


I got the following error:


BCP command :[bcp, "select * from DataBase.schema.TABLE_NAME",
queryout,tableData.csv, -c, -t, ,, -S, 10.0.0.1, -U, TestA, -P,
!@#MyPassword*&]

BCP process completed : with errors :1

BCP logFile : Starting copy... SQLState = S1000, NativeError = 0 Error
= [Microsoft][SQL Server Native Client 11.0]Unable to resolve column level collations

SQLState = 37000, NativeError = 102 Error = [Microsoft][SQL Server
Native Client 11.0][SQL Server]Incorrect syntax near 'select * from
DataBase.schema.TABLE_NAME'.


By looking the error I checked the collations of
Server
,
Database
and
Table
all seems similar SQL_Latin1_General_CP1_CI_AS

System Specifications :



Linux :
uname -mrs
Linux 3.10.0-327.10.1.el7.x86_64 x86_64
uname -a
Linux [domain] 3.10.0-327.10.1.el7.x86_64 #1 SMP Tue Feb 16 17:03:50 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)

ODBC Driver for linux:
isql --version
unixODBC 2.3.0

odbcinst -q -d -n "SQL Server Native Client 11.0"
[SQL Server Native Client 11.0]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0


Any anyone help me What i am doing wrong over here.

Answer

I have tried by creating shell script its working fine.

File dir = new File("Mydirectory"); 
Path dataPath = Paths.get("tableData.csv"); 
SStringBuilder strb = new StringBuilder();
strb.append("bcp ");
strb.append("\"select " + column + "  from " + credentials.getSchema() + ".dbo." + table_name + "\" ");
strb.append("queryout ");
strb.append(dataPath.toString());
strb.append(" -c ");
strb.append("-t ");
strb.append(", ");
strb.append("-S ");
strb.append(credentials.getServer());
strb.append(" -U ");
strb.append(credentials.getUser());
strb.append(" -P ");
strb.append(credentials.getPassword());

File shellFile = new File(folderName + File.separator + "exec.sh");

try (FileOutputStream outShell = new FileOutputStream(shellFile)) {
    outShell.write(strb.toString().getBytes());
    outShell.flush();
}
shellFile.setExecutable(true, false);
shellFile.setWritable(true, false);
shellFile.setReadable(true, false);
builder = new ProcessBuilder(shellFile.getAbsolutePath());

File logFile = new File("LogFile.txt");
System.out.println("BCP command :" + builder.command());
builder.redirectlogFile(logFile); builder.directory(dir); 
Process exec = builder.start();
System.out.println("BCP process completed : with errors :" + exec.waitFor());
System.out.println("BCP logFile :" + org.​apache.​commons.​io.FileUtils.readFileToString(logFile));

Thanks to All...