How to insert into a column named 'end'

Ignition
1

I am doing the gold cert practice test, and part of it requires writing to a table with a column named 'end'. 'end' is a keyword in sql which leads to all sorts of issues trying to do that. So far this is what I have:

insert into downtime_events
	("end")
values
	(:end)

However for some reason it returns this error:

GatewayException: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'line', table 'test.dbo.downtime_events'; column does not allow nulls. INSERT fails.
	caused by Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'line', table 'test.dbo.downtime_events'; column does not allow nulls. INSERT fails.

Ignition v8.1.48 (b2025042910)
Java: Azul Systems, Inc. 17.0.14

It seems to be trying to insert into an entirely different column. Why would that happen? Is there a better way to insert data into a column with a keyword for a name?

2

Use square brackets around the column name, eg.[end]. The square brackets [] are used to delimit identifiers.

Based on the error, it looks like that table has a column that does not allow null inserts, so you will need to define a value for that column when you are inserting.

1 Like
3

Here is what I have now

Insert into downtime_events
	([end])
values
	(:end)

And I am still getting the previous error. I do have a value, but that should not be a concern since end does allow nulls. The real concern is why is it trying to write to line, when I have only told it to write to end?

4

INSERT INTO....

will attempt to insert a row.

Assuming you want to update an existing row's end column with a timestamp. You should be using UPDATE instead

5

INSERT inserts a entire row, so if a value is not defined for a column in the table being inserted to, a default value (most commonly NULL) will be inserted for that column.

2 Likes
6

Maybe try backticks (`) instead of brackets. That's what I always use when running into keywords as column names. I wasn't aware of brackets as an alternative.

7

Thanks, that was the problem

8

Those are MySQL and MariaDB identifier quotes. Square brackets are Microsoft identifier quotes. Double quotes are SQL standard identifier quotes, and are the only quotes allowed in Oracle and Postgres.

Most DB brands with non-standard quotes will accept standard quotes, but sometimes only with a setting change ("ANSI" mode in MS).

Single ordinary quote characters are value quotes in the standard. Some DB brands with non-standard identifier quotes accept double quotes as value quotes. (Oy!)

If you care about standards, use double quotes for SQL identifiers.

3 Likes