Skip to Content.
Sympa Menu

freetds - Re: [freetds] suspected corruption

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT freetds.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] suspected corruption
  • Date: Thu, 6 Dec 2007 18:42:10 -0500

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




Archive powered by MHonArc 2.6.24.

Top of Page