FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> From: Isaac Grant [mailto:isaac AT silverorange.com]
> Sent: November 8, 2002 3:35 PM
> 
> We had both version 0.51 and 0.60 configured with --with-tdsver=7.0 -
> nothing else changed with the configuration of our server 
> except for the version of tds.

Isaac, 

It seems you found a feature, something that should have been in the release
notes.  

ANSI standard behavior is that columns are nullable by default.  That's
according to Microsoft's and Sybase's documents.  

You can control the adherence of a Microsoft server to ANSI's standard in
several ways using "options", listed below in order of increasing strength:

1.  The vanilla installed default default (off, non-nullable)
2.  Database, sp_dboption
3.  SET, cf. sp_configure and "Using Options in SQL Server"

"The database owner or system administrator can set or turn off particular
database options for all new databases by executing sp_dboption on the model
database."

If that's not confusing enough, parse this if you dare:

"When a Transact-SQL statement refers to objects that are in multiple
databases, the current database context and the current connection context
(the database defined by the USE statement if it is in a batch, or the
database that contains the stored procedure if it is in a stored procedure)
apply to that statement."

If you don't do anything special with a plain vanilla installation, the
default nullability of columns is determined by ... none of the above.  It's
determined by the tool you use.  If you read "SET ANSI_NULL_DFLT_ON (T-SQL)"
very very carefully, you'll see that ODBC turns ANSI behavior on (default
becomes nullable), and db-lib connections turn it off.  Of course, they
don't specify anything for ct-lib; I don't know how or if it's controlled on
a Sybase server.  

If you're getting the impression that the only way to know for sure if your
column is nullable or not is by using explicit DDL, well, yes, that would be
a good impression to get.  :)

The behavior you're describing is non-ANSI, and the connection you're using
is db-lib or ct-lib (I assume, since you're running PHP and our ODBC
implementation in 0.53 wouldn't have worked with PHP).  

Microsoft says db-lib connections get non-ANSI behavior, so I conclude
FreeTDS meets their specification.  

I don't know if that's by design, or if we're just lucky.  ISTR discussing
it, but I haven't found any discussion in the archives.  If there's code to
affect this in FreeTDS, I don't know where it is.  tsql doesn't override the
server's settings.  

You'll be interested to know that AFAICT there's exactly nothing in the TDS
protocol that affects this directly.  It's completely in the server.  I
suspect the server detects the connection type and sets the option
accordingly (there is a field somewhere in TDS to indicate the API in use).
But I don't have any definitive information on that.  And even if I'm right,
I don't know how what changed when why how that you see different behavior
between releases.  :(

Does that leave things good and murky for you?  

Regards, 

--jkl







> 
> -----Original Message-----
> From: bounce-freetds-150436 AT franklin.oit.unc.edu
> [mailto:bounce-freetds-150436 AT franklin.oit.unc.edu] On Behalf 
> Of Lowden,
> James K
> Sent: Friday, November 08, 2002 2:52 PM
> To: TDS Development Group
> Subject: [freetds] RE: problem inserting nulls in a temporary 
> table in a
> s tored procedure
> 
> > From: Isaac Grant [mailto:isaac AT silverorange.com]
> > Sent: November 8, 2002 1:26 PM
> > 
> > Running the following stored procedure returns an error:
> > 
> > CREATE PROCEDURE sp_testing as
> >     SET NOCOUNT ON
> >     CREATE TABLE #test (testcol1 int,testcol2 int)
> >     INSERT INTO #test VALUES (1,NULL) 
> >     
> >     SELECT * FROM #test
> > 
> > Ther error returned is:
> > 
> > Nov  8 14:17:52 zest httpd: PHP Warning:  Sybase error:  
> > Cannot insert the
> > value NULL into column 'testcol2', table
> > 'tempdb.dbo.#test_____________________________________________
> ______________________________________________________________
> ____000100
> 0149
> 89';column
> > does not allow nulls. INSERT fails. (severity 16)
> 
> 
> That's completely normal.  
> 
> There's a database option that determines the default nullness of
> columns.
> It can also be affected by connection settings.  Cf. SET
> ANSI_NULL_DFLT_ON.
> 
> 
> Which API and TDS version are you using?  It might be that the
> connection
> setting changed between versions.  
> 
> --jkl
> 
> 
> 
> 
> The information contained in this transmission may contain privileged
> and
> confidential information and is intended only for the use of the
> person(s)
> named above.  If you are not the intended recipient, or an employee or
> agent
> responsible for delivering this message to the intended recipient, any
> review, dissemination, distribution or duplication of this 
> communication
> is
> strictly prohibited. If you are not the intended recipient, please
> contact
> the sender immediately by reply e-mail and destroy all copies of the
> original message. Please note that we do not accept account orders
> and/or
> instructions by e-mail, and therefore will not be responsible for
> carrying
> out such orders and/or instructions.
> 
> 
> 
> ---
> You are currently subscribed to freetds as: [isaac AT silverorange.com]
> To unsubscribe, forward this message to
> $subst('Email.Unsub')
> 
> 
> ---
> You are currently subscribed to freetds as: [LowdenJK AT bernstein.com]
> To unsubscribe, forward this message to 
> $subst('Email.Unsub')
> 


The information contained in this transmission may contain privileged and
confidential information and is intended only for the use of the person(s)
named above.  If you are not the intended recipient, or an employee or agent
responsible for delivering this message to the intended recipient, any
review, dissemination, distribution or duplication of this communication is
strictly prohibited. If you are not the intended recipient, please contact
the sender immediately by reply e-mail and destroy all copies of the
original message. Please note that we do not accept account orders and/or
instructions by e-mail, and therefore will not be responsible for carrying
out such orders and/or instructions.