How to write NULL to a Sql table

[nageswaragunupudi]

I have tested on MSSql Server 2000 ( thats what is available to me now ).
The table is :
CountryID CountryName Population
IT Italy 58800000
...
...
After
oRs:Fields( 'Population' ):Value := nil,
the Population is now 0 not NULL.

If we link with my modified win32ole.prg or if we write ....Value := VTWrapper( 1, nil ), the updated value is NULL not 0.

[Enrico Maria Giordano]

Sorry, I don't know how to explain that difference.

EMG

[nageswaragunupudi]

The real problem is when we need to pass NULL value to a parameter of a ServerSide procedure or function.

For example, this is a function stored on the MSSQL server.

Code: Select all  Expand view

CREATE FUNCTION nulltest( @nVar Int )
RETURNING Int
AS
BEGIN
DECLARE @RetVal Int
IF @nVar Is Null
   @RetVal = -1
ELSE
   @RetVal = 2 * @nVar

RETURN @RetVal
END
 

When an Integer is passed as parameter, this function returns twice the parameter; when NULL is passed, it returns -1 and when a value of any other datatype is passed the server complains data type mismatch.

Here is the xHarbour implementation of calling this function and retrieving the return value.

Code: Select all  Expand view

Function Main()
   local oCn, oCmd, oParam
   local cConStr  := <our connection string to mssql server>

   oCn      := TOleAuto():new( "ADODB.Connection" )
   with object oCn
      :ConnectionString := cConStr
      :Open()
   end
   oCn:CursorLocation   := adUseClient
   oCmd     := TOleAuto():New( 'ADODB.Command' )
   with object oCmd
      :ActiveConnection := oCn
      :CommandText      := 'nulltest'
      :CommandType      := adCmdStoredProc
      :Prepared         := .t.
   end

   oParam   := oCmd:CreateParameter( 'retval', adInteger, adParamReturnValue )
   oCmd:Parameters:Append( oParam )
   oParam   := oCmd:CreateParameter( 'nval',   adInteger, adParamInput )
   oCmd:Parameters:Append( oParam )

   MsgInfo( ExecCmd( oCmd, 10 ) )         // result 20 : Correct
   MsgInfo( ExecCmd( oCmd, VTWrapper( 1, nil ) ) ) // result -1 : Correct
   MsgInfo( ExecCmd( oCmd, nil ) )   // should result in -1 but generates error

   oCn:Close()

return nil

//--------------------------------------------------------------//
static function ExecCmd( oCmd, uInput )

   local nRet

   oCmd:Parameters( 1 ):Value    := uInput
   TRY
      oCmd:Execute()
      nRet     := oCmd:Parameters( 0 ):Value
   CATCH
      ShowAdoError( oCmd:ActiveConnection )
   END

return nRet
//--------------------------------------------------------------//
static function ShowAdoError( oCon )

   local oErr, nErr, cErr

   nErr   := oCon:Errors:Count()

   if nErr > 0
      oErr   := oCon:Errors[ nErr - 1 ]
      cErr   := "Description : "    + oErr:Description
      cErr   += CRLF + "SubCode : " + cValToChar( oErr:NativeError )
      cErr   += CRLF + "Source : "  + oErr:Source
      MsgInfo( cErr )
   else
      MsgInfo( 'No Ado Error' )
   endif

return nil
//---------------------------------------------------------------//
 

It can be seen that only when we pass VTWrapper( 1, nil ), the parameter value is passed as NULL and when NIL is not taken as NULL and runtime error occurs.

Procedure ‘nulltest’ expects parameter @nVar which was not supplied.

The modification I made to win32ole.prg line 904 is :
Instead of

Code: Select all  Expand view

//pVariant->n1.n2.vt = VT_EMPTY;
 

Substituted

Code: Select all  Expand view

pVariant->n1.n2.vt = VT_NULL;
 

With the patch I suggested above, assigning NIL to a parameter value is passed on as NULL to the background function or procedure.

[Rick Lipkin]

Rao

Just wanted those following this thread to know you modification to WinOle32 workes perfect. Hopefully you will be able to contribute your fix to the xHarb project.

Rick Lipkin

[nageswaragunupudi]

>
Hopefully you will be able to contribute your fix to the xHarb project.
>
What should I do for this? I posted the fix here. I would like xHarbour team to examine this modification and incorporate if they consider it suitable. I do not know how to bring it to their notice. I would welcome if any of our colleagues is kind enough to take the trouble of suggesting this to the xHarbour team.

[Rick Lipkin]

Rao

Here is the link to the xHarb NG on Google .. you will see the thread I started and the discussions from other interested users with the same problem

http://groups.google.com/group/comp.lan ... our/topics

Rick

[nageswaragunupudi]

At times we need to assign NULL value to a parameter. Some other times we need to skip a parameter, leaving it empty and allowing the server-side procedure / function to substitute the skipped parameter with its default value. Both are different. I have found a way to accomplish both which I explain here. I have tested this with functions on Oracle and MSSql servers.

Oracle Server function

Code: Select all  Expand view

CREATE OR REPLACE FUNCTION nulltest( nVar AS NUMBER DEFAULT -2 )
RETURN NUMBER
AS
   RetVal NUMBER;
BEGIN
   IF nVar IS NULL THEN
      RetVal := -1;
   ELSE
      RetVal := 2 * nVar;
   END IF;
   RETURN RetVal;
END nulltest;
/ 

MSSQL Server Function

Code: Select all  Expand view

CREATE FUNCTION dbo.nulltest( @nval Int = -2 )
RETURNS int
AS  
BEGIN
DECLARE @retval Int
IF @nval Is Null
   SET @retval = -1
ELSE
   SET @retval = @nval * 2

RETURN @retval
END 

Both the functions are identical in functionality. If a number is passed as parameter the function returns twice that number. If NULL is passed as parameter it returns -1. If no parameter is passed, its value defaults to -2 and return value is -4.

My xHarbour code ( Same code works for both Oracle or MSSql depending on the connection string )

Code: Select all  Expand view

#include 'fivewin.ch'

#xtranslate NULL => VTWrapper( 1 )  // 1 = VT_NULL from vt.ch
#xtranslate EMPTY_ARG => VTWrapper( 0 ) // 0 = VT_EMPTY from vt.ch

Function Main()
   local oCn, oCmd, oParam
   local cConStr  := <our connection string to mssql server> // works both for Oracle and MSSql

      oCn      := TOleAuto():new( "ADODB.Connection" )
   with object oCn
      :ConnectionString := cConStr
      :Open()
   end
   oCn:CursorLocation   := adUseClient
   oCmd     := TOleAuto():New( 'ADODB.Command' )
   with object oCmd
      :ActiveConnection := oCn
      :CommandText      := 'nulltest'
      :CommandType      := adCmdStoredProc
      :Prepared         := .t.
   end

   oParam   := oCmd:CreateParameter( 'retval', adInteger, adParamReturnValue )
   oCmd:Parameters:Append( oParam )
   oParam   := oCmd:CreateParameter( 'nval',   adInteger, adParamInput )
   oCmd:Parameters:Append( oParam )

   MsgInfo( ExecCmd( oCmd, 10 ) )         // result 20
   MsgInfo( ExecCmd( oCmd, NULL ) ) // result -1
   MsgInfo( ExeCCmd( oCmd, EMPTY_ARG ) ) // result -4
   MsgInfo( ExecCmd( oCmd, nil ) )   // should result in -1 but generates error

   oCn:Close()

return nil

//--------------------------------------------------------------//
static function ExecCmd( oCmd, uInput )

   local nRet

   oCmd:Parameters( 1 ):Value    := uInput
   TRY
      oCmd:Execute()
      nRet     := oCmd:Parameters( 0 ):Value
   CATCH
      ShowAdoError( oCmd:ActiveConnection )
   END

return nRet
//--------------------------------------------------------------//
static function ShowAdoError( oCon )

   local oErr, nErr, cErr

   nErr   := oCon:Errors:Count()

   if nErr > 0
      oErr   := oCon:Errors[ nErr - 1 ]
      cErr   := "Description : "    + oErr:Description
      cErr   += CRLF + "SubCode : " + cValToChar( oErr:NativeError )
      cErr   += CRLF + "Source : "  + oErr:Source
      MsgInfo( cErr )
   else
      MsgInfo( 'No Ado Error' )
   endif

return nil
//---------------------------------------------------------------//

I have gone through the xHarbour forum's thread Mr Rick has provided. As suggested there, OleDetaultArg() does not work. It gives runtime error. Instead VTWrapper( 0 ) works for skipped arguments.

Above translate for NULL becomes unnecessary and assigning NIL will pass proper NULL if the following small modification is made to win32ole.prg ( line 904 )
Instead of

Code: Select all  Expand view

//pVariant->n1.n2.vt = VT_EMPTY;

Substitute

Code: Select all  Expand view

pVariant->n1.n2.vt = VT_NULL;