I'm new to SQL Anywhere. I have a database that was not designed by me. I'm trying to insert a new row in a table with let's say 3 columns. When I execute the sql statement insert into table (Col, Col2, Col3) values('1','2','3') I get a message: Column 'ColX' not found Error code=-143, SQL state=42S22 There is no such ColX. I tried to search in the triggers, but they are too many. With other tables I do not have that problem. Could anyone please help me?

asked 09 Jun '23, 14:46

webdbase's gravatar image

webdbase
15115
accept rate: 0%

edited 09 Jun '23, 14:48


Hi, Generate a full database script (extract structure) to a file and search the keyword

permanent link

answered 10 Jun '23, 05:12

SamuelCosta's gravatar image

SamuelCosta
71338
accept rate: 20%

Try select traceback() directly after the error. This will give you more information on the error. In which script it occurs and the line number in the script.

permanent link

answered 12 Jun '23, 02:12

Frank%20Vestjens's gravatar image

Frank Vestjens
1.3k344462
accept rate: 21%

I assume that you have tried select * from table; in an ISQL to verify that the column name exists...

If there are triggers on the table, this may also be an error in a trigger that has nothing to do with your statement. The trigger might even try to insert in a completely different table.

Or "table" may be a view, and the problematic column is a calculated value.

So, in principle I agree @SamuelCosta and these are just some things that are easily overlooked in such an analysis. ;-)

permanent link

answered 10 Jun '23, 08:11

tedfroehlich's gravatar image

tedfroehlich
35071022
accept rate: 22%

Thank's a lot both of you! It's definitely the triggers and procedures called from them. There are some temporary tables. Obviously the application that uses the database creates them, and then performs inserts. For the time being

SET TEMPORARY OPTION FIRE_TRIGGERS = OFF

suits me fine, although there are some side effects. I intend to do a bulk insert.

permanent link

answered 10 Jun '23, 10:08

webdbase's gravatar image

webdbase
15115
accept rate: 0%

edited 10 Jun '23, 10:09

If you want to do a bulk insert, you may also use the LOAD TABLE statement, as the docs state:

The LOAD TABLE statement does not fire any triggers associated with the table.

And you can additionaly specify whether COMUPUTED columns should be re-caluculated and DEFAULTS should be regarded.

(Of course we can't tell whether ignoring these may lead to desired or undesired side effects.)

(12 Jun '23, 03:01) Volker Barth

Well, it may be happen because of triggers, you can disable trigger and then try again adding new row. You can disable trigger in below way.

USE mydatabase;

ALTER TABLE mytable DISABLE TRIGGER ALL;

INSERT INTO mytable (Col, Col2, Col3) VALUES('1','2','3');

ALTER TABLE mytable ENABLE TRIGGER ALL;

Thanks

permanent link

answered 12 Jul '23, 05:52

gulshan212's gravatar image

gulshan212
-13113
accept rate: 0%

2

This syntax is not supported by SQL Anywhere.

(12 Jul '23, 10:44) Chris Keating
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×34
×34

question asked: 09 Jun '23, 14:46

question was seen: 628 times

last updated: 12 Jul '23, 10:44