Dataset column remove problem

ragipsavas · Post by **ragipsavas** » Tue 29 Jun 2010 10:23

Hi,

I am just start using dotConnect.
When i remove and not null column form the table adapter using the query builder, the insert command is removed from the tableadapter.

My Steps are;
1. The dataset is created using the wizard.
2. On the tables table adapter, i am click configure and remove some not null column form table and go on with the configuration wizard.

After those steps, when i check the table adapters properities, i noticed that the insert command is removed.

Is that an bug or i am dunig somthing wrong ?

Note: When i am using the wizard it creates the command as expected.

Thans

Shalex · Post by **Shalex** » Thu 01 Jul 2010 11:15

We will investigate the situation why Devart DataSet Wizard generates the insert command if not null column is not present in the select query. Probably, no insert command should be generated in this case, because if you try to use this command, you will get the following error:
ORA-01400: cannot insert NULL into the column "USER"."TABLE"."NOT_NULL_COLUMN".

We will notify you about the results of our investigation.

ragipsavas · Post by **ragipsavas** » Thu 01 Jul 2010 12:20

Hi Shalex,

You're right, but in our model, some of notnull columns has default values and some of them are supplied with an value by the tables before insert trigger.

In our application we have more 1500 tables with this columns; INSERT_USER, INSERT_DATETIME, UPDATE_USER, UPDATE_DATETIME, PROCESS_TS

As i described before, when i am using the wizard and don't select this columns the insert statment will be genereted.

But when i am remove the columns from the dataset without using the wizard, the insert statment is removed. I am wonder why does not the wizard do not remove the insert statment !

Here is an example;

CREATE TABLE COUNTRIES
(
COUNTRY_ID VARCHAR2(4) NOT NULL,
COUNTRY_NAME VARCHAR2(50) NOT NULL,
INSERT_USER VARCHAR2(50) DEFAULT USER NOT NULL, INSERT_DATETIME DATE DEFAULT SYSDATE, UPDATE_USER VARCHAR2(50) DEFAULT USER NOT NULL, UPDATE_DATETIME DATE DEFAULT SYSDATE, PROCESS_TS NUMBER );

CREATE OR REPLACE TRIGGER COUNTRIES_TBIU BEFORE INSERT OR UPDATE ON COUNTRIES REFERENCES OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN
:NEW.PROCESS_TS:= TO_NUMBER(TO_CHAR(SYSTIMESTAMP,'RRRRMMDDHH24MISSFF'));
IF UPDATING THEN
:NEW.UPDATE_USER := USER;
:NEW.UPDATE_DATETIME := SYSDATE;
END IF;
END;
/

INSERT INTO COUNTRIES (COUNTRY_ID,COUNTRY_NAME) VALUES ('TR','Turkey');

INSERT INTO COUNTRIES (COUNTRY_ID,COUNTRY_NAME) VALUES ('D','Germany');

Commit;

SELECT * FROM COUNTRIES;

As you see we will don't get an error.

Not: I have also sen a e-mail to devart support with the same subject. Which support chanel are devart prefer to used by customer.

Thanks

Ragıp Savaş

Shalex · Post by **Shalex** » Fri 02 Jul 2010 11:42

We have answered you by e-mail. We are investigating the Devart DataSet Wizard behaviour.