1. Install the 3.70 ODBC driver.
2. Set the DataType of the numeric field of the remote view to "Y" (currency) as in the following example:

   =DBSETPROP('testview.mykey','Field','KeyField',.T.)
   =DBSETPROP('testview.mykey','Field','Updatable',.T.)
   =DBSETPROP('testview.mykey','Field','UpdateName',lcsysdbo+'myKEY')
   =DBSETPROP('testview.mykey','Field','DataType',"C(12)")
   =DBSETPROP('testview.myamt','Field','KeyField',.F.)
   =DBSETPROP('testview.myamt','Field','Updatable',.T.)
   =DBSETPROP('testview.myamt','Field','UpdateName',lcsysdbo+'myamt')
   =DBSETPROP('testview.myamt','Field','DataType',"Y")

3. Use SQL Pass-through commands as in the following example:

   sqlcommand="INSERT INTO testamount (mykey, myamt)" + ;
      "VALUES ('" + m.cmykey + "', " + STR(0-m.nnumber,6,2) + " )"
   =sqlexec(gnconnhandle,sqlcommand)
   					

Back to the top

• The DataType for the field of the remote view is N (numeric).
• The value inserted into the numeric field is negative.
• The DataType for the corresponding field of the base SQL Server table is NUMERIC.
• The 3.60.0319 version of the SQL Server ODBC driver is being used.

Back to the top

Steps to Reproduce Behavior

1. Create a program file named SQLRND.PRG using the following code and ensuring appropriate permissions for the user:

   LOCAL lcconnstring 
   LOCAL lncounter, lnhandle, lnchecksource
   LOCAL lbsourceexists, lbtableexists, lbconnexists, lbviewexists
   lcconnstring    =  "DRIVER={SQL Server};" + ;
      "SERVER=MY_SERVER;DATABASE=PUBS;UID=UserName;PWD=StrongPassword"
   lncounter       =   0
   lnhandle = SQLSTRINGCONN(lcconnstring)
   IF lnhandle>0
      lnchecksource=sqltables(lnhandle,'TABLE','sourcetabs')
      SELECT sourcetabs
      SCAN
         IF UPPER(ALLTRIM(table_name))="TestSQL"
            lbsourceexists=.T.
            EXIT
         ENDIF
      ENDSCAN
      IF !lbsourceexists
         tmpcommand="CREATE TABLE dbo.TestSQL (MYKEY CHAR(10),"
         tmpcommand=tmpcommand+" MYAMT NUMERIC(18,4))"
         lnreturnresult=sqlexec(lnhandle,tmpcommand)
      ELSE
         sqlcommand="DELETE FROM TestSQL"
         lnreturnresult=SQLEXEC(lnhandle,sqlcommand)
      ENDIF
      =sqldisconnect(lnhandle)
      SET EXCLUSIVE ON
      SET MULTILOCKS ON
      CLOSE DATA ALL
      CREATE DATABASE sqltest
      CREATE CONNECTION TEST1 CONNSTRING (lcconnstring)
      =DBSETPROP('TEST1','Connection','Asynchronous', .F.)
      =DBSETPROP('TEST1','Connection','BatchMode', .T.)
      =DBSETPROP('TEST1','Connection','Comment', '')
      =DBSETPROP('TEST1','Connection','DispLogin', 3)
      =DBSETPROP('TEST1','Connection','ConnectTimeOut', 15)
      =DBSETPROP('TEST1','Connection','DispWarnings', .T.)
      =DBSETPROP('TEST1','Connection','IdleTimeOut', 0)
      =DBSETPROP('TEST1','Connection','QueryTimeOut', 0)
      =DBSETPROP('TEST1','Connection','Transactions', 2)
      CREATE SQL VIEW testview REMOTE CONNECT 'TEST1' SHARE ;
         AS SELECT * ;
         FROM TestSQL ;
         ORDER BY mykey
   ENDIF
   =DBSETPROP('testview','View','SendUpdates',.T.)
   =DBSETPROP('testview','View','UpdateType',1)
   =DBSETPROP('testview','View','WhereType',3)
   =DBSETPROP('testview','View','BatchUpdateCount',1)
   =DBSETPROP('testview','View','UseMemoSize',255)
   =DBSETPROP('testview','View','FetchSize',100)
   =DBSETPROP('testview','View','MaxRecords',-1)
   =DBSETPROP('testview','View','Tables','dbo.TestSQL')
   =DBSETPROP('testview','View','Prepared',.F.)
   =DBSETPROP('testview','View','FetchMemo',.F.)
   =DBSETPROP('testview','View','CompareMemo',.F.)
   =DBSETPROP('testview','View','FetchAsNeeded',.F.)
   =DBSETPROP('testview','View','FetchSize',100)
   =DBSETPROP('testview','View','Comment',"")
   =DBSETPROP('testview','View','ShareConnection',.T.)
   =DBSETPROP('testview.mykey','Field','KeyField',.T.)
   =DBSETPROP('testview.mykey','Field','Updatable',.T.)
   =DBSETPROP('testview.mykey','Field','UpdateName','dbo.TestSQL.myKEY')
   =DBSETPROP('testview.mykey','Field','DataType',"C(12)")
   =DBSETPROP('testview.myamt','Field','KeyField',.F.)
   =DBSETPROP('testview.myamt','Field','Updatable',.T.)
   =DBSETPROP('testview.myamt','Field','UpdateName','dbo.TestSQL.myamt')
   =DBSETPROP('testview.myamt','Field','DataType',"N(15,2)")
   USE testview IN 0
   SELECT testview
   DELETE FROM testview
   =REQUERY('testview')
   FOR lncounter = 1 TO 10
      m.nnumber = 0-lncounter
      m.cmykey = PADL(ALLTRIM(STR(lncounter,10,0)),FSIZE("MYKEY")-4 , '0')
      INSERT INTO Testview (mykey, myamt) VALUES (m.cmykey,m.nnumber )
   NEXT
   BROW TITLE 'Display values inserted into remote view'
   =TABLEUPDATE(.T.)
   =REQUERY('testview')
   BROW TITLE 'Negative Values inserted into view with NUMERIC datatype' + ;
      'are rounded up by 0.01'
   USE
   CLOSE ALL
   CLEAR ALL
   					

   From the Command window, type:

   DO SQLRND
   					

   Note the numeric values when the first BROWSE window appears.
   
   Note that the numeric values displayed in the second BROWSE window have been rounded up by 0.01.
2. From the Command window, type:

   DO SQLRND
   					

   Note the numeric values when the first BROWSE window appears.
   
   Note that the numeric values displayed in the second BROWSE window have been rounded up by 0.01.
3. Note the numeric values when the first BROWSE window appears.
   
   Note that the numeric values displayed in the second BROWSE window have been rounded up by 0.01.
4. Note that the numeric values displayed in the second BROWSE window have been rounded up by 0.01.

Back to the top