SQL Insert fails with fatal error - db connection is terminated.

[Brad Syputa - MS]

Hi Rolf,

 

I am not sure this is your problem, but when I removed the 2 NULL columns from the insert, I got error 25026 that states I have a Referential Integrity Violation. Are you trying to insert a Null value into a column that has RI? In SQL CE, you are not allowed to do this. Not even one column that is a Primary or Foreign Key is allowed to have a Null value.

 

If this does not help at all, let me know.

 

By the way, when I tried your exact insert, I did not get a Fatal Error, just the busy Hour Glass running until I closed ISQLW. I also picked this out of the SQL CE Readme that refers to ISQLW "This utility is a developer tool and is not supported for deployment." What I mean by saying this is how does your eVB or eVC application handle the insert?

Brad

bra...@microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.

"Rolf Huijbrechts" <rolf.hui...@realsoftware.be> wrote in message news:e7EVBVfeBHA.2144@tkmsftngp03...

Hi,

 

Using PocketPC2000, SSCE V1.1 on Compaq iPaq.

 

Running this SQL Insert command on the SSCE database hereby attached

using isqlw causes a fatal error, it seems that the database connection is terminated abruptly.

 

Please advise how to resolve this or to find a workaround or how to diagnose the problem.

It is extremely important that we can fix this asap, it is a production error..

 

* SQL:

INSERT INTO COMMENTS ( CMT_CRT_DATE,CMT_CST_ID,
CMT_EMP_ID,CMT_ID,CMT_JOB_ID,
CMT_LOG_EMP_ID,CMT_LOG_STAMP,CMT_MEMO,CMT_ORIGIN_ID )
VALUES ( '2001/10/31 14:42:59',NULL,52,'07F3960004B2C41C41',145,52,'2001/10/31 14:42:59',NULL,'M')

* SDF file is attached (zipped).

 

Feel free to contact me directly if you require more info.

 

--
Kind regards,
Rolf Huijbrechts
rolf.hui...@realsoftware.be

[Rolf Huijbrechts]

Hi,

 

Thanks for the fast reply.

 

>I am not sure this is your problem, but when I removed the 2 NULL columns from the insert, I got error 25026 that states I have a Referential Integrity Violation. Are you >trying to insert a Null value into a column that has RI? In SQL CE, you are not allowed to do this. Not even one column that is a Primary or Foreign Key is allowed to >have a Null value.

 

I will verify this today.

 

>By the way, when I tried your exact insert, I did not get a Fatal Error, just the busy Hour Glass running until I closed ISQLW. I also picked this out of the SQL >CE Readme that refers to ISQLW "This utility is a developer tool and is not supported for deployment." What I mean by saying this is how does your eVB or eVC >application handle the insert?

 

Fatal Error meant that the db connection is terminated. I also get the hourglass.

 

 

>  ISQLW "This utility is a developer tool and is not supported for deployment."

 

It also occurs when executing the statement in our application which is written in EVC++ (works fine for the rest).

--
Kind regards,
Rolf Huijbrechts

rolf.hui...@realsoftware.be

"Brad Syputa - MS" <brads...@Microsoft.com> wrote in message news:e6DpTogeBHA.2096@tkmsftngp07...

[Rolf Huijbrechts]

Hi,

 

>Are you >trying to insert a Null value into a column that has RI? In SQL CE, you are not allowed to do this. Not even one column that is a Primary or Foreign Key is allowed to >have a Null value.

 

We are pretty sure that we can put a NULL value in a Foreign Key column when the column is defined as not_null. So where did you obtain this information?

We would expect that the original sql statement returns a Referential Integrity Violation error in all cases

Please advise how to proceed.

 

--
Kind regards,
Rolf Huijbrechts

"Rolf Huijbrechts" <rolf.hui...@realsoftware.be> wrote in message news:#cSvWXkeBHA.2120@tkmsftngp07...

[Brad Syputa - MS]

Selected text from the Create Table page of the SQLCE Books On Line:

 

All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL. If nullability is not specified, all columns participating in a PRIMARY KEY constraint have their nullability set to NOT NULL.

 

All columns defined as a UNIQUE constraint must be defined as NOT NULL. If nullability is not specified, all columns defined as UNIQUE constraints have their nullability set to NOT NULL.

 

Is a constraint that provides referential integrity for the data in the column. FOREIGN KEY constraints require that each value in the column exists in the specified column in the referenced table.

 

FOREIGN KEY Constraints
When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned.

 

FOREIGN KEY constraints can reference only tables within the same database.

 

FOREIGN KEY constraints can reference another column in the same table (a self-reference). However, FOREIGN KEY constraints cannot be used to create a self-referencing or circular FOREIGN KEY constraint with the CASCADE option.

 

The REFERENCES clause of a column-level FOREIGN KEY constraint can list only one reference column, which must have the same data type as the column on which the constraint is defined.

 

The REFERENCES clause of a table-level FOREIGN KEY constraint must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column also must be the same as the corresponding column in the column list.

 

FOREIGN KEY constraints can reference only columns in PRIMARY KEY or UNIQUE constraints in the referenced table.

 

So, the jist of this leads me to ask, how can you put a NULL value in a Foreign Key column? According to the above, there is no way to do this. Why? Because you cannot put a NULL in a Primary or Unique key. The value must exist in the PK or UK for there to be a value in the FK. PK or UK do not allow a value of NULL, so that cannot be a value of the FK either.

 

My recollection, and I am not sure of this, is that SQL Server will allow one Null per column. SQL CE does not do this.

 

Also curious how you get a Null value in a column specified as Not Null.

 

Brad

bra...@microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.

"Rolf Huijbrechts" <rolf.hui...@realsoftware.be> wrote in message news:#k9QaPEfBHA.1836@tkmsftngp07...

[Rolf Huijbrechts]

Hi,

 

A SSCE script that proves practically that optional FK column values can

be specified as NULL value in an sql_insert command: enjoy it.

 

 

/* */

CREATE TABLE couleurs (COL_ID  NVARCHAR(3) NOT NULL)

 

CREATE TABLE produits (PRD_ID NVARCHAR(3) NOT NULL, PRD_COL_ID NVARCHAR(3) NULL)

 

ALTER TABLE couleurs   ADD CONSTRAINT XPKcouleurs PRIMARY KEY (COL_ID)

 

ALTER TABLE produits   ADD CONSTRAINT COL_PRD_1 FOREIGN KEY (PRD_COL_ID) REFERENCES couleurs ( COL_ID )

 

INSERT INTO couleurs (col_id) values ('B')

 

INSERT INTO couleurs (col_id) values ('W')

 

INSERT INTO produits (prd_id,prd_col_id) values ('F16','B')

 

INSERT INTO produits (prd_id,PRD_col_id) values ('F16',NULL)

 

/* PS all commands including the last one execfute fine */

 

 

 

***

The root cause of our problem (see 1st msg) must be something else.

 

--
Kind regards,
Rolf Huijbrechts

"Brad Syputa - MS" <brads...@Microsoft.com> wrote in message news:uH4riPFfBHA.552@tkmsftngp02...

[Brad Syputa - MS]

Correct your are. Thank you Rolf. Since this is so, did you figure out your original issue, or not?

 

Brad

bra...@microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.

"Rolf Huijbrechts" <rolf.hui...@realsoftware.be> wrote in message news:eoEmCrafBHA.2096@tkmsftngp07...