FreeTDS Development Group

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

On Mon, 9 Aug 2004 "Charles Bearden" wrote:
> 
>   INSERT INTO pmCiteTest
>   (ID, PMID, MID, ISSN, Volume, Issue, PDYear, PDMonth, PDDay, PDSeason,
>    PDMedDate, ArticleTitle, Pagination, Affiliation, Language,
> VernacularTitle,
>    Country, MedlineTA, NlmUniqueID, PubDate, Cite)
>   VALUES
>   (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
> 
> (1, 'foo', ' ', '', 'foo', 'foo', 'foo', 'foo', 'foo', 'foo', 'foo',
> 'foo',
>  'foo', 'foo', 'foo', 'foo', 'foo', 'foo', 'foo', 'foo', 'foo')
> 
> --------------------------------------------------
> Starting log file for FreeTDS 0.62.4
>       on 2004-08-09 14:00:35 with debug level 99.
>
> Sending packet @ 14:00:35.767385
> 0000 03 01 05 18 00 00 01 00-0a 00 73 00 70 00 5f 00 |........ ..s.p._.|
> 0010 70 00 72 00 65 00 70 00-61 00 72 00 65 00 00 00 |p.r.e.p. a.r.e...|
> 0020 00 01 26 04 00 00 00 63-06 02 00 00 06 02 00 00 |..&....c ........|
> 0030 40 00 50 00 31 00 20 00-49 00 4e 00 54 00 2c 00 |@.P.1. . I.N.T.,.|
> 0040 40 00 50 00 32 00 20 00-43 00 48 00 41 00 52 00 |@.P.2. . C.H.A.R.|
> 0050 28 00 33 00 29 00 2c 00-40 00 50 00 33 00 20 00 |(.3.).,. @.P.3. .|
> 0060 43 00 48 00 41 00 52 00-28 00 31 00 29 00 2c 00 |C.H.A.R. (.1.).,.|
> 0070 40 00 50 00 34 00 20 00-43 00 48 00 41 00 52 00 |@.P.4. . C.H.A.R.|
> 0080 28 00 30 00 29 00 2c 00-40 00 50 00 35 00 20 00 |(.0.).,. @.P.5. .|

That doesn't look good.  sp_prepare won't accept "char(0)" as a datatype. 


> Received packet @ 14:00:35.769947
> 0000 aa 8a 00 e9 03 00 00 01-0f 37 00 4c 00 69 00 6e |........ .7.L.i.n|
> 0010 00 65 00 20 00 31 00 3a-00 20 00 4c 00 65 00 6e |.e. .1.: . .L.e.n|
> 0020 00 67 00 74 00 68 00 20-00 6f 00 72 00 20 00 70 |.g.t.h.  .o.r. .p|
> 0030 00 72 00 65 00 63 00 69-00 73 00 69 00 6f 00 6e |.r.e.c.i .s.i.o.n|
> 0040 00 20 00 73 00 70 00 65-00 63 00 69 00 66 00 69 |. .s.p.e .c.i.f.i|
> 0050 00 63 00 61 00 74 00 69-00 6f 00 6e 00 20 00 30 |.c.a.t.i .o.n. .0|

Confirming: "Length or precision specification 0 is invalid".  .

> 0060 00 20 00 69 00 73 00 20-00 69 00 6e 00 76 00 61 |. .i.s.  .i.n.v.a|
> 0070 00 6c 00 69 00 64 00 2e-xx xx xx xx xx xx xx xx |.l.i.d.. ..x.x.x.|
> 0080 xx xx xx xx xx xx xx xx-xx 00 00 01 00 aa 5c 00 |x.x.x.x. x.....\.|
> 0090 89 00 00 00 02 0f 20 00-4d 00 75 00 73 00 74 00 |...... . M.u.s.t.|
> 00a0 20 00 64 00 65 00 63 00-6c 00 61 00 72 00 65 00 | .d.e.c. l.a.r.e.|
> 00b0 20 00 74 00 68 00 65 00-20 00 76 00 61 00 72 00 | .t.h.e.  .v.a.r.|
> 00c0 69 00 61 00 62 00 6c 00-65 00 20 00 27 00 40 00 |i.a.b.l. e. .'.@.|
> 00d0 50 00 34 00 27 00 2e 00-xx xx xx xx xx xx xx xx |P.4.'... .x.x.x.x|

Complaint about declaration of "@P4", specifically.  

I don't know much about the placeholder code.  It may be that the declared
length of @P4 is derived from the data?  If so, I don't know who does the
inferring.  I would think it would be better to derive the length from the
table metadata.  It must be OK to pass short values; if the column is
char(10), it still should be OK to pass 'hello'.  

No solid answer for you, Charles, sorry.  It sure looks like your
zero-length parameter data is inducing a char(0) parameter declaration.  

Respectfully submitted, I think using zero-length strings to circumvent
non-null column policy isn't the best practice.  You're really saying
there's no data for that column (in this row), so NULL accurately reflects
reality.  Zero-length strings are very mysterious in ad-hoc queries,
because it's hard to see nothing.  And some versions of TDS can't
distinguish between NULL and a zero-length string, meaning that some
clients will see a NULL arriving from a (as declared) non-null column. 
So, fwiw, I think you're better off making your column nullable.  And you
get to avoid the bug in the bargain.  :-)

Hope this is of some help.

--jkl