on 2023 Jun 09 3:46 PM
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?
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi, Generate a full database script (extract structure) to a file and search the keyword
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.)
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. 😉
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This syntax is not supported by SQL Anywhere.
User | Count |
---|---|
76 | |
30 | |
10 | |
8 | |
8 | |
7 | |
7 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.