FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

Frediano Ziglio wrote:
> For instance if we want 'foo' returned for NULL
> chars what does library do ?? 

        Welcome!  The db-lib Oracle Knows All, Tells All!  

You do:
        char value[6];
        dbsetnull(dbproc, CHARBIND, 3, "foo");
        dbbind(dbproc, 1, CHARBIND, 6, value);
you get:
        'foo   ' (66 6f 6f 20 20 20)

because you asked for padding (CHARBIND) and provided a length (6).  

> And if client specify 0/-1 as varlen? Does library
> return empty string or unpadded 'foo'? And what happen if we say we want
> 'foo   '? Does library r-trim string?

You do:
        dbbind(dbproc, 1, CHARBIND, 0, value);
you get:
        'foo' (66 6f 6f)

because "if varlen is 0, no padding takes place" 
(http://manuals.sybase.com/onlinebooks/group-cnarc/cng1110e/dblib/@Generi
c__BookTextView/7098;pt=39614)

You do:
        dbsetnull(dbproc, CHARBIND, 5, "foo  ");
        dbbind(dbproc, 1, CHARBIND, 0, value);
you get:
        'foo  ' (66 6f 6f 20 20)

because bindval is literal.  The library only copies it; the library never
alters the null representation.  Padding, if any, is applied after the
representation is copied into the column value.  

(I'm just working from the rules as I understand them.  I haven't tried it
with Sybase's db-lib.  I'm not the Sybase Oracle, you know.) 

I struggled with this for a while.  I wondered, What if the column is
e.g.:

        create table Names ( name varchar(2000) )

then do I allocate 2000 spaces for a CHARBIND null?  

Answer: The DDL doesn't matter!  Only the binding matters.  

The server delivers the *data*; the library converts the data into the the
form requested by dbbind().  The application allocated the memory.  All
db-lib does is copy the data from the rowbuffer to the bound variables,
converting as needed.  

To pad, db-lib must know the length.  If dbbind() is told "don't worry
about the length; I've allocated enough", OK, but it can't pad, because it
doesn't know where to stop!  

Because the DDL doesn't matter, you can do this:

        create table I (int i)
        dbsetnull(dbproc, NTBSTRINGBIND, 3, "n/a"); /* "not available" */
        dbbind(dbproc, 1, NTBSTRINGBIND, 0, value);

and see "n/a" instead of "" for nulls.  

N.B.  dbconvert has the same constraint.  If you give dbconvert a desttype
SYBCHAR and a destlen 6, it will make 'foo' into 'foo   '.  If the destlen
is -1, though, how can it pad?  So -1 means "don't pad".  

One difference between dbbind() and dbconvert() is that dbbind() *never*
trims.  The word "trim" doesn't appear on the page.  So if the server
sends you blanks (CHAR), you get blanks, even with NTBSTRINGBIND.  I think
that's why I favor VARCHAR over CHAR on the server except when the string
has a fixed length, like a code or something.  

The things you learn....

Regards, 

--jkl