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

Charles Bearden Charles.F.Bearden at uth.tmc.edu
Tue Aug 10 12:19:20 EDT 2004


I appreciate James's and Joe's taking the time to respond 
to my questions.  I'm still trying to understand.

On Mon, 10 Aug 2004 Joseph Geiser wrote:

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

I'm not sure how what you are saying applies to my situation.
I am using the eGenix mx.ODBC python module, which provides a
DB-API 2.0-compliant interface to the databases managed by
the driver manager in use (in my case, iODBC).  Good style
in this API dictates that placeholders ('?') for parameter
values are used in the SQL and that a tuple of values to be
interpolated in place of the placholders is passed as a second
argument after the string containing the SQL statement to the
.execute() method, e.g.:

sql = 'INSERT INTO tab (ID, Title) VALUES (?, ?)'
vals = (1, 'The title')
cur.execute(sql, vals)

Issues like quoting, statement preparation, and even some
recasting are supposed to be handled transparently by the
mx.ODBC module.  I'm not sure how one would even go about
defining the paramater variables in this API.  In Python,
variables are references to the data, so there is no
difference between using literal values and using
variables when defining a tuple.  If I understand your
CFML example correctly, the Python analog would be:

p1 = 1
p2 = 'The title'
sql = 'INSERT INTO tab (ID, Title) VALUES (?, ?)'
cur.execute(sql, (p1, p2))

No difference in Python between this and the prior example 
with its literal values, I think.

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

I am aware of the difference, but I'm not yet persuaded that
I'm wrong to avoid NULLs :-)  See my response to jkl.

Chuck



More information about the FreeTDS mailing list