Previous issue Thread #966895

Bug/Issue : 26 of 36

TITLE: BUG: The CursorAdapter does not support values NULL with ODBC datasource.

VERSION: 09.00.0000.1720

EXPECTED: The CA must send a NULL of correct type

OBSERVED:
When a CA finds a NULL, ignores the type of data and sendes always a value NULL within to a type parameter CHAR(8000).
This happens also with the commands builded automatically from the CA.

REPRO commented with a money field:

CLEAR
WITH CREATEOBJECT("session")
  SET DATASESSION TO .DatasessionId
  SET MEMOWIDTH TO 300
  SET MULTILOCKS ON
  H=SQLSTRINGCONNECT("DRIVER=SQL Server;SERVER=(local);DATABASE=tempdb;Network=DBMSSOCN;Trusted_Connection=Yes")
  IF m.h>0
    TRY
      IF SQLEXEC(m.h,"CREATE TABLE #CATestTable (id INT IDENTITY PRIMARY KEY,fName MONEY NULL)")>0
        WITH CREATEOBJECT("CursorAdapter") as CursorAdapter
          .DataSourceType  ="ODBC"
          .DataSource      = m.h
          .BreakOnError    = .T.
          .Alias          = "CATestTable"
          .SelectCmd      = [SELECT * FROM #CATestTable]
          IF .CursorFill()
            LIST STRUCTURE
            .InsertCmd    = [INSERT INTO #CATestTable (fName) VALUES (?fName)]
            .UpdateCmd    = [UPDATE #CATestTable SET fName=?fName WHERE id=?id]
            * try INSERT
            INSERT INTO CATestTable VALUES (0,$1)
            * now CA send:
            *   set implicit_transactions on 
            *  exec sp_executesql N'INSERT INTO #CATestTable (fName) VALUES (@P1 )', N'@P1 decimal(19,4)', 1.0000
            *  IF @@TRANCOUNT > 0 COMMIT TRAN
            * Comment: decimal(19,4) it is not perfect but it is correct
            =SendData()
            * try UPDATE with NULL
            REPLACE id WITH 1, fName WITH NULL
            * now CA send:
            *   set implicit_transactions on 
            *  exec sp_executesql N'UPDATE #CATestTable SET fName=@P1  WHERE id=@P2 ', N'@P1 char(8000),@P2 float', NULL, 1.000000000000000e+000
            *  IF @@TRANCOUNT > 0 ROLLBACK TRAN
            * Comment: @P1 it is the bug, @P2 it is not correct, but, if it is not possible to make better, we can survive or to use ?CAST(id AS I)
            =SendData()
            * try INSERT WITH NULL
            INSERT INTO CATestTable VALUES (0,NULL)
            * now CA send:
            *  set implicit_transactions on 
            *  exec sp_executesql N'INSERT INTO #CATestTable (fName) VALUES (@P1 )', N'@P1 char(8000)', NULL
            *  IF @@TRANCOUNT > 0 ROLLBACK TRAN
            * Comment: @P1 it is the bug  //** another error it is the space after @P1 : VFP have to replace the ?expression only ! **//
            =SendData()
          ENDIF
        ENDWITH
      ENDIF
    CATCH TO oex
      ? oex.ErrorNo,oex.Message
    FINALLY
      =SQLDISCONNECT(m.h)
    ENDTRY
  ENDIF
ENDWITH

PROCEDURE sendData
IF NOT TABLEUPDATE()
  ? MESSAGE()
  TABLEREVERT()
ENDIF