Skip to Content.
Sympa Menu

freetds - RE: problem inserting nulls in a temporary table in a s tored procedure

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Michael Peppler <mpeppler AT peppler.org>
  • To: TDS Development Group <freetds AT franklin.metalab.unc.edu>
  • Subject: RE: problem inserting nulls in a temporary table in a s tored procedure
  • Date: 08 Nov 2002 17:03:25 -0800

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



  • RE: problem inserting nulls in a temporary table in a s tored procedure, Michael Peppler, 11/08/2002

Archive powered by MHonArc 2.6.24.

Top of Page