Error - 8004 : Constant must be compatible with column type and length

[Former Member]

Hi All,

I have using MaxDB 7.7 with SQL Studio to manage the Database. I created a table and trying to insert values into it.

insert into employee values('100','g','h','34'). This query is getting executed successfully.

But

insert into employee values('101','gii','hii','34') This query is throwing an error

" Error in assignment;-8004 POS(36) Constant must be compatible with column type and length".

The table structure is like

employee id - integer - primary key

employee name - varchar(15) , ascii, not null,

employee dept -varchar(15) , ascii, not null,

employee sal - integer

Why is that i cannot insert 2 characters into the column emp department and emp name?

Thanks and Regards,

Divya

[Former Member · Accepted solution]

Hi,

unbelievable, what you are telling.

Please

1. drop the table

2. prepare a sequence of commands

a. create table

b. insert

3. execute this sequence

4. if no syntax error (as with the insert you sent), but -8004, then sent the sequence.

But please copy it from SQLStudio (where, I assume, you are working in) and do not write it anew to avoid typing-errors.

Then perhaps we will see, what you are doing, for example: if you really used a length > 1 for the VARCHAR-columns and so on.

Elke

[Former Member · Accepted solution]

Hi Elke,

Thanks for the help. Problem solved. I dropped the table and created a new one and tried to insert values. It is working fine now.

Thanks a lot.

Regards,

Divya

[Former Member]

Hi,

in your first mail we had

MaxDB 7.7

a table with 1. and 4. column integer

insert with every value surrounded by quotes

later we had

MaxDB 7.8

a table with 3 char-column and the 4. being the only integer

insert with the 1. and 4. value given without, 2. and 3. with quotes.

Which topic do you want to be handled?

You should keep in mind

- numbers should be specified WITHOUT quotes

- character-values should be specified WITH quotes

- that one can come into trouble if in schema xyz a table named A is available, but in the current schema another table A is available. Then that of the current schema will be used (although perhaps not meant to be used). How many tables with the same name do YOU have?

Throw away all those, you do not want to have, then check the datatypes and follow the (non-) quoting-rule above. This should help.

And please, with the next question: be exact and don't change testcase without further notice. It will help us forum-members to help you and avoid making us a little bit angry.

Elke

[Former Member]

Hi Elke,

I tried using single quotes for character values and without quotes for numeric. Then too the same result. Also there is only 1 table present in that name. Checked the schema.

Regards,

Divya

[lbreddemann]

> insert into employee values('100','g','h','34'). This query is getting executed successfully.

>

> But

> insert into employee values('101','gii','hii','34') This query is throwing an error

>

> " Error in assignment;-8004 POS(36) Constant must be compatible with column type and length".

>

> The table structure is like

> employee id - integer - primary key

> employee name - varchar(15) , ascii, not null,

> employee dept -varchar(15) , ascii, not null,

> employee sal - integer

>

> Why is that i cannot insert 2 characters into the column emp department and emp name?

Ok, without testing this, but I rather find it irritating to see that the first command workds, as the value provide is not an integer but a string.

What happens if you run

insert into employee values(101 ,'gii','hii', 34) 

?

regards,

Lars

[Former Member]

Hi Lars,

I am getting the same error on executing this query.

insert into employee values(101 ,'gii','hii', 34)

But this is executing correctly.

insert into employee values(101 ,'g','h', 34)

Regards,

Divya

[lbreddemann]

Hi there,

sorry - but I cannot reproduce this on my 7.7 instance.

Time for you to deliver more precise information:

- what DB version/build?

- exact DDL statement used to create the table (any special stuff like triggers defined?)

- exact DML statements to reproduce the issue

- used client tool and version.

regards,

Lars

[Former Member]

Hi Lars,

- what DB version/build?

SAP MAX DB Version - 7.8

SQL STUDIO - 7.6.03

exact DDL statement used to create the table (any special stuff like triggers defined?) - No

exact DML statements to reproduce the issue - No

Regards,

Divya

[lbreddemann]

> exact DDL statement used to create the table (any special stuff like triggers defined?) - No

> exact DML statements to reproduce the issue - No

these weren't YES/NO questions...

If we should help you, we need to know the exact commands.

regards,

Lars

[Former Member]

Hi lars,

Statements to Create Table

CREATE TABLE "SAPCE1DB"."EMPLOYEE"

(

"EMPID" Varchar (10) NOT NULL,

"EMPNAME" Char (25) ASCII,

"EMPDEPT" Char (25) ASCII,

"EMPSAL" Fixed (8),

PRIMARY KEY("EMPID")

)

//

COMMENT ON COLUMN "EMPLOYEE"."EMPSAL" IS 'Employee Sal'

insert into employee(234,'hl','jk',34)

--- Error -

---

Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed

Error in assignment;-8004 POS(35) Constant must be compatible with column type and length

insert into employee values (234,'hl','jk',34)

Regards,

Divya

[lbreddemann]

>

> insert into employee(234,'hl','jk',34)

>

> --- Error -

---

> Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed

> Error in assignment;-8004 POS(35) Constant must be compatible with column type and length

> insert into employee values (234,'hl','jk',34)

>

in addition to what Elke wrote.

Please don't make up the commands and outputs -

the insert-statement given is syntacially wrong (values keyword missing),

After correcting the statement syntax I sucessfully entered the data into the table.

MaxDB even managed to do the implicit conversions correctly...

All in all: still cannot reproduce the issue.

regards,

Lars

[Former Member]

HI Lars,

Thanks for the help.

Regards,

Divya