freebcp （kshell 使用 freebcp 然后调用python，执行insert操作。当DB连接加密时，bcp失败）

(http://blog.csdn.net/rgb_rgb/article/details/8769729)

安装配置(必须在root下用户安装)
1、安装unixODBC
# tar vxzf unixODBC-2.2.8.tar.gz
# cd unixODBC-2.2.8
# ./configure --prefix=/usr/local/unixODBC --enable-gui=no
# make
# make install
2、安装freetds
# tar vxzf freetds-0.62.4.tar.gz
# cd freetds-0.62.4
# ./configure --prefix=/usr/local/freetds --with-unixodbc=/usr/local/unixODBC --with-tdsver=8.0
# make
# make install
3、配置freetds
# vi /usr/local/freetds/etc/freetds.conf
[global]
        # TDS protocol version
        tds version = 8.0
  client charset = UTF-8
[SQLServer2000]
        host = 192.168.1.110
        port = 1433
        tds version = 8.0
        client charset = UTF-8
[root@pc02 bin]# ./freebcp
usage:  freebcp [[database_name.]owner.]table_name {in | out} datafile
        [-m maxerrors] [-f formatfile] [-e errfile]
        [-F firstrow] [-L lastrow] [-b batchsize]
        [-n] [-c] [-t field_terminator] [-r row_terminator]
        [-U username] [-P password] [-I interfaces_file] [-S server]
        [-v] [-d] [-h "hint [,...]" [-O "set connection_option on|off, ...]"
        [-A packet size] [-T text or image size] [-E]
        
example: freebcp testdb.dbo.inserttest in inserttest.txt -S mssql -U guest -P password -c
[root@pc02 bin]# 
1，按表导出
/usr/local/freetds/bin/freebcp tb1 out tb1.txt -S ssgg -U ssgg -P ssgg -c
2，按查询导出
/usr/local/freetds/bin/freebcp "select * from xxx.[xxx].tb1" queryout /home/oracle/tb1.txt -c -t "|" -S 192.100.251.218 -U sa -P 11 
如果不设置字符集会出现：
Some character(s) could not be converted into client's character set.  Unconverted bytes were changed to question marks ('?')

=========================================================================

freebcp 提供interface file， 其实就是一个configuration file。 命名规则为freedts.conf。

里面配置了连接dbserver的 host的url，端口号。还有tds version。

然后如果db server 采取了加密，这样freebcp就不会成功。导入和导出。

例如 db_server_url=jdbc:sybase:Tds:XXXX.nsroot.net:3463/XXXX?ENCRYPT_PASSWORD\=true\&JCE_PROVIDER_CLASS\=org.bouncycastle.jce.provider.BouncyCastleProvider  (加密)

则可以用python 然后建立connect，去取得数据，然后执行sql文，插入到数据库中。

例子：kshell中的代码截取

		${JAVA_HOME}/bin/java \
		-ms256m -mx2048m \
		-classpath ${CLASSPATH} \
		-Djava.library.path=${HOME}/lib: \
		-DHOME=${HOME} \
		org.python.util.jython ${HOME}/bin/XXXX.py 'DBserverName,,DBtableName' read_data_file  '|~|' ${DB_SERVER_URL} ${DB_SERVER_USER} ${DB_SERVER_PASS}
		${executable} > /dev/null &
		echo "-jython script end-"

py后用空格分离开参数， 这个是XXX.py中定义的参数。看你的py如何定义，第一个是要插入的表。第二个是读取哪一个文件。第三个是分隔符，后面分别是连接db的url，用户名和密码。

py 截取部分代码: （取得参数）

targetTable = sys.argv[1]
inputFileName = sys.argv[2]
spliter = sys.argv[3]
db_url = sys.argv[4]
db_usrId = sys.argv[5]
db_pwd = sys.argv[6]

#preparing db
print 'Connecting DB...'
Class.forName("com.sybase.jdbc3.jdbc.SybDriver").newInstance()
Conn = DriverManager.getConnection(db_url,db_usrId,db_pwd)
Stmt = Conn.createStatement()
print 'Connecting DB done.\n'

#parsing file


sql = ''
f1 = open(inputFileName,'r')
print 'file openned'
intMapping = [0,1,2,3,4,43]
lnumber = 0

for raw in f1:
    lnumber = lnumber + 1
    raw = raw.rstrip('\r\n')
    line = raw.split(spliter)
    lineSql = ''
    for j in range(0,len(line)):
            c = line[j].replace("'","''")
            if j in intMapping:
                    lineSql = lineSql +c+","
            else:
                    lineSql = lineSql +"'"+c+"',"
    lineSql = lineSql[:-1]
    sql = 'insert into '+ targetTable  + ' values ( ' +lineSql+ ')'
    

    Stmt.addBatch(sql)
    if lnumber == 1000:
        
        
        Stmt.executeBatch()
        Stmt.clearBatch()

        print 'BCP in '+ str(lnumber) + ' rows.'
        lnumber = 0
        lineSql = ''
        sql = ''


Stmt.executeBatch()
print 'BCP in remain rows.'

# Close ...
Stmt.close()
Conn.close()