3.12 CS_BLKDESC Objects

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:

blk_bind( num, databuf)

Calls the Sybase blk_bind() function and returns the Sybase result code. The Sybase-CT blk_bind() function is called like this:

status = blk_bind(blk, num, &datafmt, buffer->buff, buffer->copied, buffer->indicator);

blk_describe( num)

Calls the Sybase blk_describe() function and returns a tuple containing the Sybase result code and a CS_DATAFMT object which describes the column identified by num. 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);

blk_done( type)

Calls the Sybase blk_done() function and returns a tuple containing the Sybase result code and the number of rows copied in the current batch.

The Sybase blk_done() function is called like this:

status = blk_done(blk, type, &num_rows);

blk_drop( )

Calls the Sybase blk_drop() function and returns the Sybase result code.

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.

blk_init( direction, table)

Calls the Sybase blk_init() function and returns the Sybase result code.

The Sybase blk_init() function is called like this:

status = blk_init(blk, direction, table, CS_NULLTERM);

blk_props( action, property [, value])

Sets, retrieves and clears properties of the bulk descriptor object.

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.

blk_rowxfer( )

Calls the Sybase blk_rowxfer() function and returns the Sybase result code.

The Sybase blk_rowxfer() function is called like this:

status = blk_rowxfer(blk);

blk_rowxfer_mult( [row_count])

Calls the Sybase blk_rowxfer_mult() function and returns a tuple containing the Sybase result code and the number of rows transferred.

The Sybase blk_rowxfer_mult() function is called like this:

status = blk_rowxfer_mult(blk, &row_count);

blk_textxfer( [str])

Calls the Sybase blk_textxfer() function. Depending on the direction of the bulkcopy (established via the blk_init() method) the method expects different arguments.

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

`property`	type
`BLK_IDENTITY`	`bool`
`BLK_NOAPI_CHK`	`bool`
`BLK_SENSITIVITY_LBL`	`bool`
`ARRAY_INSERT`	`bool`
`BLK_SLICENUM`	`int`
`BLK_IDSTARTNUM`	`numeric`