Insert NULL into MS Sql Server

[FRBhote]

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]

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

MartinD

[Jeff Luther]

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]

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]

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

[FRBhote]

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]

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]

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

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