Skip to Content.
Sympa Menu

freetds - Re: [freetds] Re: Inserting empty strings into MS SQL Server

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT schemamania.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Re: Inserting empty strings into MS SQL Server
  • Date: Mon, 9 Aug 2004 22:37:32 -0400

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




Archive powered by MHonArc 2.6.24.

Top of Page