Unable parse OR

[jpink]

Hi,

when I initialize org.drewcarlson:kjob-jdbi the kjob or Jdbi generates query like INSERT OR REPLACE INTO kjobLocks VALUES (?, ?, ?) which I can't parse because I get: ParseException: Encountered unexpected token: "OR".

[manticore-projects]

Greetings.

INSERT OR REPLACE ... is not supported and far away from any standard or RDBMS I know. MERGE ... INTO ... would be more appropriate.

The supported syntax are shown here.

[manticore-projects]

Am I correct to assume, that you talk to MongoDB?
If so, then support is slim because we very much focus on Relational DBs and Standard SQL. When MongoDB is defined as a NoSQL Database then a SQL Parser may be the wrong tool.

[jpink]

The problem is that I'm not writing the SQL the framework (Jdbi? probably) generates it. And the framework targets to SQLite, MySQL, PostegreSQL.

I now use SQL filter or sanitizer in JDBC wrapper driver where I replace INSERT OR REPLACE to INSERT and that workaround works.

[jpink]

Can you support non-standard known syntax also. Or better can you replace it with MERGE ... INTO ... syntax?

[manticore-projects]

Can you support non-standard known syntax also.

We actually do support a lot of non-standard syntax and supporting your statement is possible. However, first of all you would need to point us on the exact syntax definition.

I did not find any INSERT OR REPLACE INTO ... statement (only a MySQL REPLACE INTO... statement)

[manticore-projects]

It seems to be very SQLite specific.

We have the UPSERT statement, which seem to do the same thing.
And there is REPLACE, which is similar too.

[manticore-projects]

I got a POC working and will provide a patch today:

expected: <insert or replace into kjoblocks values(?,?,?)> but was: <upsert into kjoblocks values(?,?,?)>
Expected :insert or replace into kjoblocks values(?,?,?)
Actual   :upsert into kjoblocks values(?,?,?)