 |
 |
 |
Sybase Module Manual |  |
 |
 |
Calling the blk_alloc() method of a CS_CONNECTION object will create a CS_BLKDESC object. When the CS_BLKDESC object is deallocated the Sybase blk_drop() function will be called for the command.
CS_BLKDESC objects have the following interface:
status = blk_bind(blk, num, &datafmt, buffer->buff, buffer->copied, buffer->indicator);
None
is returned as the CS_DATAFMT object when the result code is
not CS_SUCCEED.
The Sybase blk_describe() function is called like this:
status = blk_describe(blk, num, &datafmt);
The Sybase blk_done() function is called like this:
status = blk_done(blk, type, &num_rows);
The Sybase blk_drop() function is called like this:
status = blk_drop(blk);
This method will be automatically called when the CS_BLKDESC object is deleted. Applications do not need to call the method.
The Sybase blk_init() function is called like this:
status = blk_init(blk, direction, table, CS_NULLTERM);
When action is CS_SET a compatible value argument
must be supplied and the method returns the Sybase result code. The
Sybase blk_props() function is called like this:
/* boolean property value */ status = blk_props(blk, CS_SET, property, &bool_value, CS_UNUSED, NULL); /* int property value */ status = blk_props(blk, CS_SET, property, &int_value, CS_UNUSED, NULL); /* numeric property value */ status = blk_props(blk, CS_SET, property, &numeric_value, CS_UNUSED, NULL);
When action is CS_GET the method returns a tuple
containing the Sybase result code and the property value. The Sybase
blk_props() function is called like this:
/* boolean property value */ status = blk_props(blk, CS_GET, property, &bool_value, CS_UNUSED, NULL); /* int property value */ status = blk_props(blk, CS_GET, property, &int_value, CS_UNUSED, NULL); /* numeric property value */ status = blk_props(blk, CS_GET, property, &numeric_value, CS_UNUSED, NULL);
When action is CS_CLEAR the method returns the Sybase
result code. The Sybase blk_props() function is called
like this:
status = blk_props(blk, CS_CLEAR, property, NULL, CS_UNUSED, NULL);
The recognised properties are:
| property | type |
BLK_IDENTITY |
bool |
BLK_NOAPI_CHK |
bool |
BLK_SENSITIVITY_LBL |
bool |
ARRAY_INSERT |
bool |
BLK_SLICENUM |
int |
BLK_IDSTARTNUM |
numeric |
For an explanation of the property values and get/set/clear semantics please refer to the Sybase documentation.
The Sybase blk_rowxfer() function is called like this:
status = blk_rowxfer(blk);
The Sybase blk_rowxfer_mult() function is called like this:
status = blk_rowxfer_mult(blk, &row_count);
When direction CS_BLK_IN the str argument must be
supplied and method returns the Sybase result code.
The Sybase blk_textxfer() function is called like this:
status = blk_textxfer(blk, str, str_len, NULL);
When direction CS_BLK_OUT the str argument must
not be present and method returns a tuple containing the Sybase result
code and a string.
The Sybase blk_textxfer() function is called like this:
status = blk_textxfer(blk, buff, sizeof(buff), &out_len);
A simplistic program to bulkcopy a table from one server to another server follows:
The first section contains the code to display client and server messages in case of failure.
import sys
from sybasect import *
def print_msgs(conn, type):
status, num_msgs = conn.ct_diag(CS_STATUS, type)
if status != CS_SUCCEED:
return
for i in range(num_msgs):
status, msg = conn.ct_diag(CS_GET, type, i + 1)
if status != CS_SUCCEED:
continue
for attr in dir(msg):
sys.stderr.write('%s: %s\n' % (attr, getattr(msg, attr)))
def die(conn, func):
sys.stderr.write('%s failed!\n' % func)
print_msgs(conn, CS_SERVERMSG_TYPE)
print_msgs(conn, CS_CLIENTMSG_TYPE)
sys.exit(1)
The next section is fairly constant for all CT library programs. A
library context is allocated and connections established. The only
thing which is unique to bulk copy operations is setting the
CS_BULK_LOGIN option on the connection.
def init_db():
status, ctx = cs_ctx_alloc()
if status != CS_SUCCEED:
raise 'cs_ctx_alloc'
if ctx.ct_init(CS_VERSION_100) != CS_SUCCEED:
raise 'ct_init'
return ctx
def connect_db(ctx, server, user, passwd):
status, conn = ctx.ct_con_alloc()
if status != CS_SUCCEED:
raise 'ct_con_alloc'
if conn.ct_diag(CS_INIT) != CS_SUCCEED:
die(conn, 'ct_diag')
if conn.ct_con_props(CS_SET, CS_USERNAME, user) != CS_SUCCEED:
die(conn, 'ct_con_props CS_USERNAME')
if conn.ct_con_props(CS_SET, CS_PASSWORD, passwd) != CS_SUCCEED:
die(conn, 'ct_con_props CS_PASSWORD')
if conn.ct_con_props(CS_SET, CS_BULK_LOGIN, 1) != CS_SUCCEED:
die(conn, 'ct_con_props CS_BULK_LOGIN')
if conn.ct_connect(server) != CS_SUCCEED:
die(conn, 'ct_connect')
return conn
The next segment allocates bulkcopy descriptors, data buffers, and
binds the data buffers to the bulk copy descriptors. The same buffers
are used for copying out and copying in - not bad. Note that for array
binding we need to use loose packing for copy in; hence the line
setting the format member of Databuf CS_DATAFMT to
CS_BLK_ARRAY_MAXLEN. Without this the bulkcopy operation
assumes tight packing and the data is corrupted on input.
def alloc_bcp(conn, dirn, table):
status, blk = conn.blk_alloc()
if status != CS_SUCCEED:
die(conn, 'blk_alloc')
if blk.blk_init(dirn, table) != CS_SUCCEED:
die(conn, 'blk_init')
return blk
def alloc_bufs(bcp, num):
bufs = []
while 1:
status, fmt = bcp.blk_describe(len(bufs) + 1)
if status != CS_SUCCEED:
break
fmt.count = num
bufs.append(DataBuf(fmt))
return bufs
def bcp_bind(bcp, bufs):
for i in range(len(bufs)):
buf = bufs[i]
if bcp.direction == CS_BLK_OUT:
buf.format = 0
else:
buf.format = CS_BLK_ARRAY_MAXLEN
if bcp.blk_bind(i + 1, buf) != CS_SUCCEED:
die(bcp.conn, 'blk_bind')
This next section actually performs the bulkcopy. Note that there is no attempt to deal with BLOB columns.
def bcp_copy(src, dst, batch_size):
total = batch = 0
while 1:
status, num_rows = src.blk_rowxfer_mult()
if status == CS_END_DATA:
break
if status != CS_SUCCEED:
die(src, 'blk_rowxfer_mult out')
status, dummy = dst.blk_rowxfer_mult(num_rows)
if status != CS_SUCCEED:
die(src, 'blk_rowxfer_mult in')
batch = batch + num_rows
if batch >= batch_size:
total = total + batch
batch = 0
src.blk_done(CS_BLK_BATCH)
dst.blk_done(CS_BLK_BATCH)
print 'batch - %d rows transferred' % total
status, num_rows = src.blk_done(CS_BLK_ALL)
status, num_rows = dst.blk_done(CS_BLK_ALL)
return total + batch
Finally the code which drives the whole process.
ctx = init_db() src_conn = connect_db(ctx, 'drama', 'sa', '') dst_conn = connect_db(ctx, 'SYBASE', 'sa', '') src = alloc_bcp(src_conn, CS_BLK_OUT, 'pubs2.dbo.authors') dst = alloc_bcp(dst_conn, CS_BLK_IN, 'test.dbo.authors') bufs = alloc_bufs(src, 5) bcp_bind(src, bufs) bcp_bind(dst, bufs) total = bcp_copy(src, dst, 10) print 'all done - %d rows transferred' % total
|
|
|
Sybase Module Manual | |
|
|