Insert NULL into MS Sql Server

General discussion forum about all databases other than SqlBase.
FRBhote
India
Posts: 2177
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Insert NULL into MS Sql Server

Post by FRBhote » 25 Apr 2011, 13:29

I have setzerolengthstringstonull=on & setzerolengthstringstonull=1 in sql.ini (tried both), but:

INSERT INTO XX VALUES ( 'B', '' );

does not insert NULL in the second column.

My real problem is:

SELECT COL INTO :sBind ...

where COL is NULL, followed by

INSERT INTO ... (OTHERCOL) VALUES (:sBind )

deos not insert NULL but the empty string.

Can someone help me?

UVS

Re: Insert NULL into MS Sql Server

Post by UVS » 25 Apr 2011, 17:25

I'm sure you have looked at the Server settings? What is ANSI_NULLS set to?

MartinD

Jeff Luther

Re: Insert NULL into MS Sql Server

Post by Jeff Luther » 26 Apr 2011, 00:42

I think you can do this:

set sBind = STRING_Null
...
INSERT INTO ... (OTHERCOL) VALUES (:sBind )

You can find info. about this in the ccod.PDF manual shipped with TD. I found the info. chapter 1.

FRBhote
India
Posts: 2177
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Insert NULL into MS Sql Server

Post by FRBhote » 26 Apr 2011, 06:38

Thanks - worked perfectly in TD. I wonder from which version's ccod.pdf it is documented - for TD 2.1, it is only in SqlHost.

Jeff Luther

Re: Insert NULL into MS Sql Server

Post by Jeff Luther » 26 Apr 2011, 16:33

Don't know, but you surely must have the \Books folder of PDF's newer than v2.1. 4? 5?

FRBhote
India
Posts: 2177
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Insert NULL into MS Sql Server

Post by FRBhote » 28 Apr 2011, 13:54

I've been through the newer ccod.pdf, but nowhere is setzerolengthstringstonull mentioned.

The book says that for SqlServer the empty string '' is inserted as a space (constant) / NULL (bind variable). But I find that for a bind variable a NULL is NOT inserted unless setzerolengthstringstonull=1 is there inthe sql.ini.

This setzerolengthstringstonull is an excellent feature - which does not seem to be implemented in any other compiler (from my searches on the net) - and deserves it's recognition in ccod.pdf.

Jeff Luther

Re: Insert NULL into MS Sql Server

Post by Jeff Luther » 29 Apr 2011, 00:35

Looks like that INI keyword was dropped ages ago, that's why it's not in newer PDFs. Mike V. found it in a CTD v2.1 version of shinst.pdf. Appears to have been a DB2 keyword (remember that DB brand??) and I quote what it says:

setzerolengthstringstonull

Code: Select all

          Use this keyword to control the handling of bind variables of type STRING.
Section          [winclient] or [win32client]
Syntax          setzerolengthstringstonull={on | off}
Default          off
Description    DB2 does not allow the use of zero-length character strings without the NULL indicator.
          The SQL/API distinguishes between zero-length haracter strings and
          NULL strings through the use of the NULL indicator. SQLWindows and SQLTalk for
          Windows currently do not support the use of the NULL indicator. In order to force
          zero-length character strings to NULL and prevent the occurrence of the DB2 -804 or
          Centura 6804 error code, you must use this keyword when you connect to DB2 and
          set it to on.

          This keyword applies only to bind variables. It does not apply to literals or to SQLWindows INTO variables.

FRBhote
India
Posts: 2177
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Insert NULL into MS Sql Server

Post by FRBhote » 29 Apr 2011, 07:22

Exactly - I found it in the Sqlhost (DB2) manual.

I do think that it deserves recognition in the TD manual too.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests