Blob从Informix到Postgres的迁移数据 [英] Blob's migration data from Informix to Postgres
本文介绍了Blob从Informix到Postgres的迁移数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
解决方案
我使用Jython和JDBC在各种数据库之间进行了一些这样的转换。
所有你需要的是Informix和PostgreSQL JDBC驱动程序在 CLASSPATH
。
我有Informix表与 BYTE
列,PostgreSQL表与 BYTEA
列:
- Informix
CREATE TABLE _blob_test(
id integer PRIMARY KEY,
image BYTE
)
- PostgreSQL
CREATE TABLE _blob_test(
id integer PRIMARY KEY,
image BYTEA
)
然后,您可以使用JDBC getObject()/ setObject()
方法复制数据:
#!/ usr / bin / env jython
# - * - 编码:utf8 - * -
从java.sql导入DriverManager
from java.lang import Class
Class.forName(com.informix.jdbc.IfxDriver)
Class.forName('org.postgresql.Driver')
def copy_table(db_from, db_to):
col_count = 2
insert_stmt = db_to.prepareStatement('INSERT INTO _blob_test(id,image)VALUES(?,?)')
pstm2 = db_from.createStatement()
pstm2.setFetchSize(10000)
rs_in = pstm2.executeQuery('SELECT * FROM _blob_test')
try:
batch_buffer = 0
batch_size = 100
while (rs_in.next()):
for i in range(1,col_count + 1):
insert_stmt.setObject(i,rs_in.getObject(i))
insert_stmt.addBatch()
batch_buffer + = 1
if(batch_buffer%batch_size == 0):
insert_stmt.executeBatch()
batch_buffer = 0
if(batch_buffer> 0):
insert_stmt.executeBatch()
finally:
rs_in.close()
pstm2.close()
db_from = DriverManager.getConnection('jdbc:informix-sqli:// informix-test:9088 / infdb:informixserver = ol_testifx; DB_LOCALE = pl_PL.CP1250; CLIENT_LOCALE = pl_PL.CP1250; charSet = CP1250','informix','12345')
db_to = DriverManager.getConnection('jdbc:postgresql:// pg-test:5490 / pg_test?stringtype = unspecified','postgres','12345')
copy_table(db_from, db_to)
I am migrating Informix (11.7) DB to PostgreSQL(9.2). There is only one problem: how to move blob(image) from Informix to PostgreSQL(9.2)? Many thanks in advance.
解决方案
I did some such conversions between various databases using Jython and JDBC.
All you need is Informix and PostgreSQL JDBC drivers in CLASSPATH
.
I have Informix table with BYTE
column, and PostgreSQL table with BYTEA
column:
-- Informix
CREATE TABLE _blob_test (
id integer PRIMARY KEY,
image BYTE
)
-- PostgreSQL
CREATE TABLE _blob_test (
id integer PRIMARY KEY,
image BYTEA
)
Then you can use JDBC getObject()/setObject()
methods to copy data:
#!/usr/bin/env jython
# -*- coding: utf8 -*-
from java.sql import DriverManager
from java.lang import Class
Class.forName("com.informix.jdbc.IfxDriver")
Class.forName('org.postgresql.Driver')
def copy_table(db_from, db_to):
col_count = 2
insert_stmt = db_to.prepareStatement('INSERT INTO _blob_test (id, image) VALUES (?, ?)')
pstm2 = db_from.createStatement()
pstm2.setFetchSize(10000)
rs_in = pstm2.executeQuery('SELECT * FROM _blob_test')
try:
batch_buffer = 0
batch_size = 100
while (rs_in.next()):
for i in range(1, col_count + 1):
insert_stmt.setObject(i, rs_in.getObject(i))
insert_stmt.addBatch()
batch_buffer += 1
if (batch_buffer % batch_size == 0):
insert_stmt.executeBatch()
batch_buffer = 0
if (batch_buffer > 0):
insert_stmt.executeBatch()
finally:
rs_in.close()
pstm2.close()
db_from = DriverManager.getConnection('jdbc:informix-sqli://informix-test:9088/infdb:informixserver=ol_testifx;DB_LOCALE=pl_PL.CP1250;CLIENT_LOCALE=pl_PL.CP1250;charSet=CP1250', 'informix', '12345')
db_to = DriverManager.getConnection('jdbc:postgresql://pg-test:5490/pg_test?stringtype=unspecified', 'postgres', '12345')
copy_table(db_from, db_to)
这篇关于Blob从Informix到Postgres的迁移数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文