Bug: ORA-01401: inserted value too large for column?

[dqminh]

Hi,

I use EasyObject20 to generate code from database Oracle. A few days ago, I've asked Matt for the sequence in Oracle, but...

First, when I apply the sequence by mask property SEQ:I, I found that in insert command, there are two parameters of primary key: first in

Code: Select all

protected override DbCommand GetInsertCommand(CommandType commandType)
      {
         DbCommand dbCommand;

         // Create the Database object, using the default database service. The
         // default database service is determined through configuration.
         Database db = GetDatabase();

         switch (commandType)
         {
            case CommandType.StoredProcedure:
               string sqlCommand = this.SchemaStoredProcedureWithSeparator + "daab_AddV2_DATA_MESSAGE";
               dbCommand = db.GetStoredProcCommand(sqlCommand);

               db.AddParameter(dbCommand, "MESSAGEID", DbType.Decimal, 0, ParameterDirection.Output, true, 0, 0, "MESSAGEID", DataRowVersion.Default, Convert.DBNull);
               CreateParameters(db, dbCommand);

               return dbCommand;

            case CommandType.Text:
               ...

            default:
               throw new ArgumentException("Invalid CommandType", "commandType");
         }
      }

and second in

Code: Select all

private void CreateParameters(Database db, DbCommand dbCommand)
{
   db.AddInParameter(dbCommand, "MESSAGEID", DbType.Decimal, "MESSAGEID", DataRowVersion.Current); // duplicate here
   db.AddInParameter(dbCommand, "BODY", DbType.String, "BODY", DataRowVersion.Current);
   db.AddInParameter(dbCommand, "CREATEDDATE", DbType.DateTime, "CREATEDDATE", DataRowVersion.Current);
   db.AddInParameter(dbCommand, "CREATEDBY", DbType.String, "CREATEDBY", DataRowVersion.Current);
   db.AddInParameter(dbCommand, "FLAG", DbType.AnsiStringFixedLength, "FLAG", DataRowVersion.Current);
}


Another bug is, when I remove the second parameter, build and run, the app alway throw exception: ORA-01401: inserted value too large for column (the store procedure I generated in oracle is tested ok)

How can I fix it?

[dqminh]

I found that the column that cause error ORA-01401: inserted value too large for column is TimeStamp type. I test by create a new table with at least one column TimeStamp. First, I load data to the dataset like

Code: Select all

string sqlCommand = @"SELECT * FROM MESSAGE";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
DataSet ds = db.ExecuteDataSet(dbCommand);

After, I add new row:

Code: Select all

DataRow newRow = ds.Tables[0].NewRow();
//newRow.ItemArray[0] = 1;
newRow.SetField(ds.Tables[0].Columns[1], "B");
newRow.SetField(ds.Tables[0].Columns[2], "a");
newRow.SetField(ds.Tables[0].Columns[3], "c");
newRow.SetField(ds.Tables[0].Columns[4], DateTime.Now);
newRow.SetField(ds.Tables[0].Columns[5], "N");
newRow.SetField(ds.Tables[0].Columns[6], "1");
ds.Tables[0].Rows.Add(newRow);


Call the UpdateDataSet

Code: Select all

db.UpdateDataSet(ds, "Table", GetInsertCommand(), GetUpdateCommand(), GetDeleteCommand(), UpdateBehavior.Standard);


Note that, GetInsertCommand() is copy and change from EasyObject:

Code: Select all

protected DbCommand GetInsertCommand()
{
   DbCommand dbCommand;

   // Create the Database object, using the default database service. The
   // default database service is determined through configuration.
   Database db = DatabaseFactory.CreateDatabase();

   string sqlCommand = "VDMS.daab_AddMESSAGE";
   dbCommand = db.GetStoredProcCommand(sqlCommand);

   db.AddParameter(dbCommand, "MESSAGEID", DbType.Decimal, 0, ParameterDirection.Output, true, 0, 0, "MESSAGEID", DataRowVersion.Default, Convert.DBNull);
   CreateParameters(db, dbCommand);

   return dbCommand;
}

private void CreateParameters(Database db, DbCommand dbCommand)
{
   if (dbCommand.Parameters["MESSAGEID"] == null)
      db.AddInParameter(dbCommand, "MESSAGEID", DbType.Decimal, "MESSAGEID", DataRowVersion.Current);
   db.AddInParameter(dbCommand, "FROMDELAER", DbType.AnsiString, "FROMDELAER", DataRowVersion.Current);
   db.AddInParameter(dbCommand, "TODEALER", DbType.AnsiString, "TODEALER", DataRowVersion.Current);
   db.AddInParameter(dbCommand, "BODY", DbType.String, "BODY", DataRowVersion.Current);
   db.AddInParameter(dbCommand, "CREATEDDATE", DbType.DateTime, "CREATEDDATE", DataRowVersion.Current);
   db.AddInParameter(dbCommand, "CREATEDBY", DbType.String, "CREATEDBY", DataRowVersion.Current);
   db.AddInParameter(dbCommand, "FLAG", DbType.AnsiStringFixedLength, "FLAG", DataRowVersion.Current);
}


Still error ORA-01401: inserted value too large for column

Any idea? Thanks

[mgnoonan]

Can I see a CREATE TABLE script that shows the types?

[dqminh]

Hi Matt,

The script to create table:

Code: Select all

CREATE TABLE Message(
    MessageId      NUMBER(10, 0)     NOT NULL,
    FromDealer     VARCHAR2(30),
    ToDealer       VARCHAR2(30),
    Body           NVARCHAR2(512)    NOT NULL,
    CreatedDate    TIMESTAMP(6)      NOT NULL,
    CreatedBy      NVARCHAR2(256)    NOT NULL,
    Flag           CHAR(1)           NOT NULL,
    CONSTRAINT PK1 PRIMARY KEY (MessageId)
)
;


The script that create sequence:

CREATE SEQUENCE seq_ Message MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20;

So, when use the EasyObject, the script for insert here:

Code: Select all

PROCEDURE        "DAAB_ADDMESSAGE"
(
   MESSAGEID OUT V2_DATA_MESSAGE.MESSAGEID%type,
   FROMDELAER IN V2_DATA_MESSAGE.FROMDELAER%type,
   TODEALER IN V2_DATA_MESSAGE.TODEALER%type,
   BODY IN V2_DATA_MESSAGE.BODY%type,
   CREATEDDATE IN V2_DATA_MESSAGE.CREATEDDATE%type,
   CREATEDBY IN V2_DATA_MESSAGE.CREATEDBY%type,
   FLAG IN V2_DATA_MESSAGE.FLAG%type
)
IS
BEGIN

   SELECT seq_Message.NextVal INTO MESSAGEID FROM DUAL;

   EXECUTE IMMEDIATE
   'INSERT
   INTO MESSAGE
   (
      MESSAGEID,
      FROMDELAER,
      TODEALER,
      BODY,
      CREATEDDATE,
      CREATEDBY,
      FLAG
   )
   VALUES
   (
      :MESSAGEID,
      :FROMDELAER,
      :TODEALER,
      :BODY,
      :CREATEDDATE,
      :CREATEDBY,
      :FLAG)'
   USING MESSAGEID, FROMDELAER, TODEALER, BODY, CREATEDDATE, CREATEDBY, FLAG;

END DAAB_ADDMESSAGE;

Because of duplicate in parameter MESSAGEID in generated code, I modify as below:

Code: Select all

private void CreateParameters(Database db, DbCommand dbCommand)
{
   if (dbCommand.Parameters["MESSAGEID"] == null)
      db.AddInParameter(dbCommand, "MESSAGEID", DbType.Decimal, "MESSAGEID", DataRowVersion.Current);
   db.AddInParameter(dbCommand, "FROMDELAER", DbType.AnsiString, "FROMDELAER", DataRowVersion.Current);
   db.AddInParameter(dbCommand, "TODEALER", DbType.AnsiString, "TODEALER", DataRowVersion.Current);
   db.AddInParameter(dbCommand, "BODY", DbType.String, "BODY", DataRowVersion.Current);
   db.AddInParameter(dbCommand, "CREATEDDATE", DbType.DateTime, "CREATEDDATE", DataRowVersion.Current);
   db.AddInParameter(dbCommand, "CREATEDBY", DbType.String, "CREATEDBY", DataRowVersion.Current);
   db.AddInParameter(dbCommand, "FLAG", DbType.AnsiStringFixedLength, "FLAG", DataRowVersion.Current);
}

The code for test here:

Code: Select all

protected void Button3_Click(object sender, EventArgs e)
{
   Database db = DatabaseFactory.CreateDatabase();
   string sqlCommand = @"VDMS.daab_AddMESSAGE";
   DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
   db.AddOutParameter(dbCommand, "MESSAGEID", DbType.Decimal, 10);
   db.AddInParameter(dbCommand, "FROMDELAER", DbType.AnsiString, "abc");
   db.AddInParameter(dbCommand, "TODEALER", DbType.AnsiString, "abc");
   db.AddInParameter(dbCommand, "BODY", DbType.String, "abc");
   db.AddInParameter(dbCommand, "CREATEDDATE", DbType.DateTime, DateTime.Now);
   db.AddInParameter(dbCommand, "CREATEDBY", DbType.AnsiString, "minh");
   db.AddInParameter(dbCommand, "FLAG", DbType.AnsiString, "A");
   db.ExecuteNonQuery(dbCommand); // run ok, a new row has been add
   sqlCommand = @"SELECT * FROM MESSAGE";
   dbCommand = db.GetSqlStringCommand(sqlCommand);
   DataSet ds = db.ExecuteDataSet(dbCommand);
   DataRow newRow = ds.Tables[0].NewRow();
   //newRow.ItemArray[0] = 1;
   newRow.SetField(ds.Tables[0].Columns[1], "B");
   newRow.SetField(ds.Tables[0].Columns[2], "a");
   newRow.SetField(ds.Tables[0].Columns[3], "c");
   newRow.SetField(ds.Tables[0].Columns[4], DateTime.Now);
   newRow.SetField(ds.Tables[0].Columns[5], "N");
   newRow.SetField(ds.Tables[0].Columns[6], "1");
   ds.Tables[0].Rows.Add(newRow);
   db.UpdateDataSet(ds, "Table", GetInsertCommand(), GetUpdateCommand(), GetDeleteCommand(), UpdateBehavior.Standard); // not ok, error
}

protected DbCommand GetInsertCommand()
{
   DbCommand dbCommand;

   // Create the Database object, using the default database service. The
   // default database service is determined through configuration.
   Database db = DatabaseFactory.CreateDatabase();

   string sqlCommand = "VDMS.daab_AddMESSAGE";
   dbCommand = db.GetStoredProcCommand(sqlCommand);

   db.AddParameter(dbCommand, "MESSAGEID", DbType.Decimal, 0, ParameterDirection.Output, true, 0, 0, "MESSAGEID", DataRowVersion.Default, Convert.DBNull);
   CreateParameters(db, dbCommand);

   return dbCommand;
}

Please help me. Thanks so much.

[dqminh]

I think the bug is come from Enterprise Library, but I don't know why...

[mgnoonan]

Sounds like the EasyObject is trying to insert a value into TIMESTAMP when it shouldn't. It may be a bug.

Unfortunately my Oracle machine is long dead, so it may take me some time get another machine built to test this.

[dqminh]

Sounds like the EasyObject is trying to insert a value into TIMESTAMP when it shouldn't. It may be a bug.

Unfortunately my Oracle machine is long dead, so it may take me some time get another machine built to test this.

Ok, waiting for you.

But, please think carefully. In my test code

Code: Select all

protected void Button3_Click(object sender, EventArgs e)
{
...
}

I test in 2 case: one is call stored procedure by ExecuteDataSet() method, and it work. The another case is load all data from database to dataset, add a new row, setup the InsertCommand, UpdateCommand and DeleteCommand for UpdateDataSet method, and the exception occur. So I think, the bug caused by Enterprise Library