FreeTDS Development Group

[Michael Peppler <mpeppler AT peppler.org>]

On Fri, 2002-11-08 at 14:38, Lowden, James K wrote:
> > 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:

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

That SET option doesn't exist on a Sybase server (at least not the
12.5.0.1 server that I have here). Sybase uses "sp_dboption <database>,
'allow nulls', {true, false}" for this. Setting it in the model database
will make this option apply to all databases created after the option
was set.

There is no sp_configure option that pertains to this (AFAIK).

Sybase's default is that columns are not null unless specified.

Michael


> > 
> > -----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.
> 
> 
> 
> ---
> You are currently subscribed to freetds as: [mpeppler AT peppler.org]
> To unsubscribe, forward this message to 
> leave-freetds-91463H AT franklin.oit.unc.edu
> 
-- 
Michael Peppler / mpeppler AT peppler.org / http://www.mbay.net/~mpeppler
mpeppler AT zetatools.com / ZetaTools, Inc / http://www.zetatools.com
ZetaTools: Call perl functions as Sybase stored procedures!

Attachment: signature.asc
Description: This is a digitally signed message part