6 Replies Latest reply on Jun 21, 2006 2:22 PM by 519306

    DataAdapter.Update inserting null instead of string

    519306
      Forgive me if someone covered this already. I've searched for a day now and can't find anything on this issue I'm having.

      I'm using VS 2005 VB.NET, ODP.NET 10.2.0.1, and 10g.

      In short, my problem is that I'm adding a new DataRow to a DataTable and then call DataAdapter.Update(DataTable) to have the DataAdapter insert the new row into the table. I'm using the OracleCommandBuilder to build the insert command. Now everything seems to work fine EXCEPT that any varchar2 fields in the table are getting populated with null instead of the passed parameterized string. Interestingly enough, if I modify an existing DataRow in a DataTable and call DataAdapter.Update(DataTable), the varchar2 fields accept the strings just fine, no nulls.

      I'm not receiving any error codes or messages and if I look at the v_$sql, the command Oracle received looks correct (although I'm not sure how to verify on the Oracle side the parameter values that are being sent).

      I know I can write a workaround that would retrieve the newly inserted row, update the varchar2 fields with the strings again, and recall .Update but that seems silly and inefficient.

      Has anyone seen this issue or, better yet, figured out how to fix it? I can post code if my description doesn't make sense.

      Thank you,
      Josh
        • 1. Re: DataAdapter.Update inserting null instead of string
          24208
          Hi Josh,

          I'm not sure how helpful this will be, but if I understand the issue you are experiencing, this seems to work correctly for me...

          I have copied the SCOTT.EMP table into my schema and here is a quick test:
          using System;
          using System.Data;
          using Oracle.DataAccess.Client;
          using Oracle.DataAccess.Types;
          
          namespace OracleCommandBuilderTest
          {
            class Program
            {
              static void Main(string[] args)
              {
                string sql_select = "select empno, ename, job from emp";
                string constr = "User Id=/; Enlist=false; Pooling=false";
                OracleConnection con = new OracleConnection(constr);
                con.Open();
          
                OracleDataAdapter da = new OracleDataAdapter(sql_select, con);
          
                OracleCommandBuilder cb = new OracleCommandBuilder(da);
          
                DataSet ds = new DataSet();
                da.Fill(ds, "EMP");
          
                DataTable dt = ds.Tables["EMP"];
                dt.Columns["EMPNO"].Unique = true;
          
                DataRow dr = dt.NewRow();
                dr["EMPNO"] = 9999;
                dr["ENAME"] = "Mark";
                dr["JOB"] = "DBA";
          
                dt.Rows.Add(dr);
          
                da.Update(dt);
          
                dt.Dispose();
                cb.Dispose();
                da.Dispose();
                con.Dispose();
              }
            }
          }
          When run this results in:
          SQL> select * from emp;
          
               EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
          ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                7369 SMITH      CLERK           7902 17-DEC-80        800                    20
                7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
                7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
                7566 JONES      MANAGER         7839 02-APR-81       2975                    20
                7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
                7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
                7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
                7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
                7839 KING       PRESIDENT            17-NOV-81       5000                    10
                7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
                7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
                7900 JAMES      CLERK           7698 03-DEC-81        950                    30
                7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
                7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
                9999 Mark       DBA
          
          15 rows selected.
          I think you are not seeing the ENAME and JOB columns (the VARCHAR2 columns) in my sample?

          I am using the 10.2.0.2.0 version of the provider as well...

          - Mark
          • 2. Re: DataAdapter.Update inserting null instead of string
            519306
            I was interested that this worked for you so I decided to run my own set of tests similar to what you posted. Here's what I found:

            I can also reproduce the results that you did using the specific Oracle.DataAccess.Client types. No problems inserting strings. However, if I use abstract System.Data.Common classes and DBProviderFactories.GetFactory("Oracle.DataAccess.Client") and then subsequently use the returned factory to create the dataadapter, commands, connection, etc. I can reproduce the behavior. The weird thing is it inserts the numbers just fine, it only has problems with strings.

            Here's the code that I used to produce the problem:
             
                    Dim factory As DbProviderFactory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client")

                    Dim cnDB As DbConnection = factory.CreateConnection
                    cnDB.ConnectionString = "Data Source=TESTDB;User Id=;Password=;"
                    Dim da As DbDataAdapter = factory.CreateDataAdapter
                    Dim selectCmd As DbCommand = factory.CreateCommand
                    Dim cmdBuilder As DbCommandBuilder = factory.CreateCommandBuilder()

                    cmdBuilder.DataAdapter = da

                    selectCmd.CommandText = "select * from roster where user_id = 1"
                    selectCmd.Connection = cnDB

                    da.SelectCommand = selectCmd

                    da.InsertCommand = cmdBuilder.GetInsertCommand

                    Dim ds As New DataSet
                    da.Fill(ds, "ROSTER")

                    Dim dt As DataTable = ds.Tables("ROSTER")

                    Dim dr As DataRow = dt.NewRow

                    dr.Item("ROSTER_ID") = 99999
                    dr.Item("USER_ID") = 1
                    dr.Item("NAME") = "New Direct Entry 2"
                    dr.Item("TYPE") = 0

                    dt.Rows.Add(dr)

                    da.Update(dt)
            Although, at this point it may make sense to change my code to just instantiate the classes directly as opposed to using the factory class.

            Maybe I'm missing something obvious?

            Thanks,
            Josh

            Message was edited by:
            jtspanky
            • 3. Re: DataAdapter.Update inserting null instead of string
              24208
              Hi Josh,

              I don't think you are missing anything and this looks like incorrect behavior to me.

              Kind of long, but I took what you provided and extended it a bit by converting my sample code to VB, which I don't too speak well! So please excuse any VB faux-pas you may find.
              Imports System.Data.Common
              Imports Oracle.DataAccess.Client
              
              Module Module1
                Sub Main()
                  ' this works correctly
                  ' OracleMethod()
              
                  ' this does not work correctly
                  ' varchar2 data is null
                  FactoryMethod()
                End Sub
              
                Private Sub OracleMethod()
                  Dim sql_select As String = "select empno, ename, job from emp"
                  Dim constr As String = "User Id=/; Enlist=false; Pooling=false"
              
                  Dim con As OracleConnection = New OracleConnection(constr)
                  con.Open()
              
                  Dim da As OracleDataAdapter = New OracleDataAdapter(sql_select, con)
                  Dim cb As OracleCommandBuilder = New OracleCommandBuilder(da)
              
                  Dim ds As DataSet = New DataSet()
                  da.Fill(ds, "EMP")
              
                  Dim dt As DataTable = ds.Tables("EMP")
                  dt.Columns("EMPNO").Unique = True
              
                  Dim dr As DataRow = dt.NewRow()
                  dr("EMPNO") = 9999
                  dr("ENAME") = "Mark"
                  dr("JOB") = "DBA"
              
                  dt.Rows.Add(dr)
              
                  da.Update(dt)
              
                  dt.Dispose()
                  cb.Dispose()
                  da.Dispose()
                  con.Dispose()
                End Sub
              
                Private Sub FactoryMethod()
                  Dim sql_select As String = "select empno, ename, job from emp"
                  Dim constr As String = "User Id=/; Enlist=false; Pooling=false"
              
                  Dim factory As DbProviderFactory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client")
                  Dim cnDB As DbConnection = factory.CreateConnection()
              
                  cnDB.ConnectionString = constr
                  cnDB.Open()
              
                  Dim da As DbDataAdapter = factory.CreateDataAdapter()
                  Dim selectCmd As DbCommand = factory.CreateCommand()
              
                  Dim cmdBuilder As DbCommandBuilder = factory.CreateCommandBuilder()
                  cmdBuilder.DataAdapter = da
              
                  selectCmd.CommandText = sql_select
                  selectCmd.Connection = cnDB
              
                  da.SelectCommand = selectCmd
                  da.InsertCommand = cmdBuilder.GetInsertCommand()
              
                  Dim ds As New DataSet
                  da.Fill(ds, "EMP")
              
                  Dim dt As DataTable = ds.Tables("EMP")
              
                  Dim dr As DataRow = dt.NewRow()
                  dr("EMPNO") = 9999
                  dr("ENAME") = "Mark"
                  dr("JOB") = "DBA"
              
                  dt.Rows.Add(dr)
                  da.Update(dt)
                End Sub
              End Module
              I then created a database logon trigger to enable tracing when I ran the code:
              create or replace trigger logon_trigger
              after logon on database
              begin
               if (user = 'OPS$LIVERPOOL\ORATEST') then
                 execute immediate
                 'alter session set events ''10046 trace name context forever, level 4''';
               end if;
              end;
              So here is the relevant section from the resulting trace file when using the OracleMethod call:
              PARSING IN CURSOR #8 len=64 dep=0 uid=93 oct=2 lid=93 tim=11811362772 hv=3773706982 ad='1c21cecc'
              INSERT INTO "EMP"( "EMPNO", "ENAME", "JOB") VALUES ( :1, :2, :3)
              END OF STMT
              PARSE #8:c=0,e=426,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=11811362763
              BINDS #8:
              kkscoacd
               Bind#0
                oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
                oacflg=03 fl2=1000000 frm=00 csi=00 siz=88 off=0
                kxsbbbfp=076da370  bln=22  avl=03  flg=05
                value=9999
               Bind#1
                oacdty=01 mxl=32(04) mxlc=04 mal=00 scl=00 pre=00
                oacflg=03 fl2=1000010 frm=01 csi=178 siz=0 off=24
                kxsbbbfp=076da388  bln=32  avl=04  flg=01
                value="Mark"
               Bind#2
                oacdty=01 mxl=32(03) mxlc=03 mal=00 scl=00 pre=00
                oacflg=03 fl2=1000010 frm=01 csi=178 siz=0 off=56
                kxsbbbfp=076da3a8  bln=32  avl=03  flg=01
                value="DBA"
              EXEC #8:c=31250,e=33052,p=0,cr=1,cu=7,mis=1,r=1,dep=0,og=1,tim=11811405296
              XCTEND rlbk=0, rd_only=0
              As you can see all is as you would expect.

              Now here is the relevant section from the resulting trace file when using the FactoryMethod call:
              PARSING IN CURSOR #7 len=66 dep=0 uid=93 oct=2 lid=93 tim=11870270482 hv=3493788180 ad='1c0955cc'
              INSERT INTO "EMP" ("EMPNO", "ENAME", "JOB") VALUES (:p1, :p2, :p3)
              END OF STMT
              PARSE #7:c=0,e=80,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=11870270472
              BINDS #7:
              kkscoacd
               Bind#0
                oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
                oacflg=03 fl2=1000000 frm=00 csi=00 siz=88 off=0
                kxsbbbfp=0759ca54  bln=22  avl=03  flg=05
                value=9999
               Bind#1
                oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
                oacflg=03 fl2=1000010 frm=01 csi=178 siz=0 off=24
                kxsbbbfp=0759ca6c  bln=32  avl=00  flg=01
               Bind#2
                oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
                oacflg=03 fl2=1000010 frm=01 csi=178 siz=0 off=56
                kxsbbbfp=0759ca8c  bln=32  avl=00  flg=01
              EXEC #7:c=31250,e=27426,p=0,cr=1,cu=7,mis=0,r=1,dep=0,og=1,tim=11870307082
              XCTEND rlbk=0, rd_only=0
              The "Bind#1" and "Bind#2" sub-sections show the values are NULL for these binds. The various length attributes such as avl=00 are all 0.

              Interesting indeed. I've taken the liberty to forward a link to this thread to a contact at Oracle to see if there is any input as to the correctness of the behavior, etc.

              - Mark
              • 4. Re: DataAdapter.Update inserting null instead of string
                461404
                This seems to be a bug with the OracleCommandBuilder when using generic classes which occurs only if you generate a generic INSERT, UPDATE, or DELETE command.

                Your test works fine if I just remove the following line from code:

                da.InsertCommand = cmdBuilder.GetInsertCommand

                This code line sets the InsertCommand property of OracleDataAdapter that should be used for Insert operation. You should set this property only if you want to use one of your SQL statements instead of the implicitly generated one. There is not much value of this statement in the context of this test case.
                • 5. Re: DataAdapter.Update inserting null instead of string
                  24208
                  Neeraj,

                  Thanks alot - I really appreciate your input on this.

                  - Mark
                  • 6. Re: DataAdapter.Update inserting null instead of string
                    519306
                    I've changed my code to use the specific Oracle classes and not the factory class and everything is working great. Thank you both for your time and effort.

                    Josh