FreeTDS Development Group

["Joseph Geiser" <joe AT mail.paravisions.com>]

Just throwing my US$0.02 worth into the fray here...

> >   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
> >   (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Dynamic parameters are passed to the query, and SQL Server determines their
length should the application not do so.  For example, if passing the value
of a variable, then the variable should probably have been declared or at
least initialized (initialised for our British, Aussie and Canadian friends
;)...

My thoughts here are to have data in variables prior to passing.  For
example, and I'll use some Cold Fusion syntax here:

<cfset myvar=''>

would yield an empty string, and this should pass just fine through a
parameterised query or stored procedure, as it's length is defined as 1,
although empty.

"Empty" and "Null" are two separate and distinct values in SQL, they are not
equal.  As James writes later in the post...

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

<hex snipped>  Yes - sp_prepare won't accept this, but will accept a value
passed to a parameter that is defined as char(1) in the script/program.
Doing this will cause sp_prepare to use char(1) as the length, even though
the string is "empty".

> 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'.

Very true - the parameter should be declared somewhere, either as T-SQL, or
as a variable in the script.  If you ensure that the value is either passed
from a variable that is declared with a length of at least 1, such as
char(1), this should solve your problem.

Note that the query above WILL work in isql (Query Analyzer), but it's not
correct.  Each parameter must have a length, and that can be done either as
a declared variable (passing the value of that variable as the parameter),
or as in isql (or even tsql), using the "declare" statement (ie: declare P4
char(1))

> 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.  :-)

Again, Jim is right again.  A non-NULL policy for strings, is not a good
idea as it can cause unpredictable results.  Here, I have a non-null policy
for numerics and dates, but for string data, searching for NULL is
acceptable for columns that are not "required data".  In other words, you
will always get a good result with:

        select columnset from table where columnx is NULL

than with

        select columnset from table where columnx = ''

as depending on your collating sequence/dictionary order, this could
translate differently.

Again, just my US$0.02 worth, which in the UK is worth less than 1p ;)  Hope
it helps though.

Cheers,
Joe