CREATE TABLE issue: Encountered "NOT"

This statement

CREATE TABLE country (
    pk_country_id                    BIGINT                    NOT NULL,
    country_map                      TEXT ENCODING DICT(16),
    created                          TIMESTAMP(0)              NOT NULL,
    iso3166                          TEXT ENCODING DICT,
    modified                         TIMESTAMP(0),
    name                             TEXT ENCODING DICT        NOT NULL
)

creates the following issue:

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error: Query failed : [OmniSci.java:read:43901:TOmniSciException(error_msg:Exception: SQL Error: Encountered "NOT" at line 7, column 64.
Was expecting one of:
    "AS" ...
    "DEFAULT" ...
    "GENERATED" ...
    "(" ...
    ")" ...
    "," ...
    )]

	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)

	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:510)

	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:441)

	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:169)

	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:428)

	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:813)

	at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3278)

	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118)

	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:169)

	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)

	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4589)

	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)

	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

Caused by: java.sql.SQLException: Query failed : [OmniSci.java:read:43901:TOmniSciException(error_msg:Exception: SQL Error: Encountered "NOT" at line 7, column 64.
Was expecting one of:
    "AS" ...
    "DEFAULT" ...
    "GENERATED" ...
    "(" ...
    ")" ...
    "," ...
    )]

	at com.omnisci.jdbc.OmniSciStatement.executeQuery(OmniSciStatement.java:100)

	at com.omnisci.jdbc.OmniSciStatement.execute(OmniSciStatement.java:238)

	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)

	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:130)

	... 12 more

Hi @walter ,

the parser is expecting the NOT NULL, after the data type, not the encoding, that is this case is TEXT.

so the right syntax to create your table would be

CREATE TABLE country (
    pk_country_id                    BIGINT                    NOT NULL,
    country_map                      TEXT ENCODING DICT(16),
    created                          TIMESTAMP(0)              NOT NULL,
    iso3166                          TEXT ENCODING DICT,
    modified                         TIMESTAMP(0),
    name                             TEXT NOT NULL ENCODING DICT        
)

You can check the syntax and samples of create table statement here

image

Regards,
Candido

My mistake - sorry :rage:

Thank you ver much :grinning:

Nevermind @walter,

sometimes the encoding syntax could lead to those errors.

Regards.