def testCSVPipe(self):
"""testing the CSV pipe"""
from java.io import PrintWriter, FileWriter
from com.ziclix.python.sql.pipe import Pipe
from com.ziclix.python.sql.pipe.db import DBSource
from com.ziclix.python.sql.pipe.csv import CSVSink
try:
src = self.connect()
fn = tempfile.mktemp(suffix="csv")
writer = PrintWriter(FileWriter(fn))
csvSink = CSVSink(writer)
c = self.cursor()
try:
c.execute("insert into zxtesting (id, name, state) values (?, ?, ?)", [(1000, 'this,has,a,comma', 'and a " quote')])
c.execute("insert into zxtesting (id, name, state) values (?, ?, ?)", [(1001, 'this,has,a,comma and a "', 'and a " quote')])
# ORACLE has a problem calling stmt.setObject(index, null)
c.execute("insert into zxtesting (id, name, state) values (?, ?, ?)", [(1010, '"this,has,a,comma"', None)], {2:zxJDBC.VARCHAR})
self.db.commit()
finally:
self.db.rollback()
c.close()
dbSource = DBSource(src, c.datahandler.__class__, "zxtesting", None, None, None)
cnt = Pipe().pipe(dbSource, csvSink) - 1 # ignore the header row
finally:
writer.close()
src.close()
os.remove(fn)
def _testXMLPipe(self):
"""testing the XML pipe"""
from java.io import PrintWriter, FileWriter
from com.ziclix.python.sql.pipe import Pipe
from com.ziclix.python.sql.pipe.db import DBSource
from com.ziclix.python.sql.pipe.xml import XMLSink
try:
src = self.connect()
fn = tempfile.mktemp(suffix="csv")
writer = PrintWriter(FileWriter(fn))
xmlSink = XMLSink(writer)
dbSource = DBSource(src, self.datahandler, "zxtesting", None, None,
None)
cnt = Pipe().pipe(dbSource, xmlSink) - 1 # ignore the header row
finally:
writer.close()
src.close()
os.remove(fn)
def testDBPipe(self):
"""testing the DB pipe"""
from com.ziclix.python.sql.pipe import Pipe
from com.ziclix.python.sql.pipe.db import DBSource, DBSink
try:
src = self.connect()
dst = self.connect()
c = self.cursor()
c.execute("create table zxtestingbcp (id int not null, name varchar(20), state varchar(2), primary key (id))")
self.db.commit()
c.execute("select count(*) from zxtesting")
one = c.fetchone()[0]
c.close()
dbSource = DBSource(src, c.datahandler.__class__, "zxtesting", None, None, None)
dbSink = DBSink(dst, c.datahandler.__class__, "zxtestingbcp", None, None, 1)
cnt = Pipe().pipe(dbSource, dbSink) - 1 # ignore the header row
c = self.cursor()
c.execute("select count(*) from zxtestingbcp")
two = c.fetchone()[0]
c.execute("delete from zxtestingbcp")
self.db.commit()
c.close()
assert one == two, "expected [%d] rows in destination, got [%d] (sql)" % (one, two)
assert one == cnt, "expected [%d] rows in destination, got [%d] (bcp)" % (one, cnt)
# this tests the internal assert in BCP. we need to handle the case where we exclude
# all the rows queried (based on the fact no columns exist) but rows were fetched
# also make sure (eg, Oracle) that the column name case is ignored
dbSource = DBSource(src, c.datahandler.__class__, "zxtesting", None, ["id"], None)
dbSink = DBSink(dst, c.datahandler.__class__, "zxtestingbcp", ["id"], None, 1)
self.assertRaises(zxJDBC.Error, Pipe().pipe, dbSource, dbSink)
params = [(4,)]
dbSource = DBSource(src, c.datahandler.__class__, "zxtesting", "id > ?", None, params)
dbSink = DBSink(dst, c.datahandler.__class__, "zxtestingbcp", None, None, 1)
cnt = Pipe().pipe(dbSource, dbSink) - 1 # ignore the header row
c = self.cursor()
c.execute("select count(*) from zxtesting where id > ?", params)
one = c.fetchone()[0]
c.execute("select count(*) from zxtestingbcp")
two = c.fetchone()[0]
c.close()
assert one == two, "expected [%d] rows in destination, got [%d] (sql)" % (one, two)
assert one == cnt, "expected [%d] rows in destination, got [%d] (bcp)" % (one, cnt)
finally:
try:
c = self.cursor()
try:
c.execute("drop table zxtestingbcp")
self.db.commit()
except:
self.db.rollback()
finally:
c.close()
try:
src.close()
except:
src = None
try:
dst.close()
except:
dst = None