SQL Insert embedded using prepared statement - not supported

[LukasWysocki]

OrientDB Version: 3.0.2

Java Version: "1.8.0_171"

OS: Linux 4.13.0-43-generic #48~16.04.1-Ubuntu SMP Thu May 17 12:56:46 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

Expected behavior

Ability to insert an embedded json no schema document into a field using a prepared statement.

Actual behavior

Diffrent OValidationException(s) depending on parameter type.
e.g. for ODocument it's:
com.orientechnologies.orient.core.exception.OValidationException: The field 'order.json' has been declared as EMBEDDED but the value is the RecordID #-1:-1

Note that one of these methods worked on 2.2.35 (converting ODocument to Map and adding a "@type"="d" key value pair).

#Suspected underlying cause
The root of the problem seems to be the
missing implementation for handling a ODocument with invalid identity in com.orientechnologies.orient.core.sql.parser.OInputParameter

    if (value instanceof OIdentifiable) {
      // TODO if invalid build a JSON

Steps to reproduce

Start orientDB, create a new empty database (the junit test is using db named test and user root/root).
Run a junit test:

package de.logopak;

import com.orientechnologies.orient.core.db.document.ODatabaseDocumentTx;
import com.orientechnologies.orient.core.metadata.schema.OClass;
import com.orientechnologies.orient.core.metadata.schema.OClass.INDEX_TYPE;
import com.orientechnologies.orient.core.metadata.schema.OSchema;
import com.orientechnologies.orient.core.metadata.schema.OType;
import com.orientechnologies.orient.core.record.impl.ODocument;
import com.orientechnologies.orient.core.sql.OCommandSQL;
import java.util.Map;
import java.util.UUID;
import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.TestInstance;
import org.junit.jupiter.api.TestInstance.Lifecycle;

@TestInstance(Lifecycle.PER_CLASS)
public class TestPreparedStatementEmbedded {

  private String insertString = "insert into order (id, json) values (:id, :json)";
  private String embeddedRecordJson = "{\"UniqueMessageID\":\"ASDF1234ADSDDFFF\",\"ProcessOrder\":{\"PONumber\":\"10548598\",\"POStatus\":\"REL\",\"PODateTime\":\"2011-11-03T15:05:00Z\",\"MaterialNumber\":\"50033AD\",\"MaterialDescription\":\"Kozel0,5dark\",\"Quantity\":\"100\",\"Pallet\":{\"PalletTypeDescription\":\"SK_blablabla\",\"NoOfCases\":\"80\",\"Weight\":\"2000\"},\"Country\":\"Finland\",\"EANCode\":\"0405734018761210564\",\"BatchNumber\":\"5781638\",\"BestBeforeDate\":\"2012-12-01T15:05:00Z\",\"Resource\":\"LK1\",\"Plant\":\"8C02\"},\"@type\":\"d\"}";
  private ODatabaseDocumentTx db;


  @BeforeAll
  public void beforeAll() {
    db = new ODatabaseDocumentTx(
        "remote:localhost/test").open("root", "root");
    OSchema schema = this.db.getMetadata().getSchema();
    OClass aClass = schema.createClass("order");
    aClass.createProperty("id", OType.STRING).setMandatory(true).createIndex(INDEX_TYPE.UNIQUE);
    aClass.createProperty("json", OType.EMBEDDED).setMandatory(true);
  }

  @AfterAll
  public void afterAll() {
    OSchema schema = db.getMetadata().getSchema();
    schema.dropClass("order");
    db.close();
  }


  @Test
  public void insertPreparedEmbeddedVarargJsonMap() {
    // FAILS on both 2.2.35 and 3.0.2
    OCommandSQL insert = new OCommandSQL("insert into order (id, json) values (?, ?)");
    Map<String, Object> stringObjectMap = new ODocument().fromJSON(embeddedRecordJson).toMap();
    stringObjectMap.put("@type", "d");
    Object execute = db.command(insert).execute(String.valueOf(UUID.randomUUID()), stringObjectMap);
  }

  @Test
  public void insertPreparedEmbeddedVarargString() {
    // FAILS on both 2.2.35 and 3.0.2
    OCommandSQL insert = new OCommandSQL("insert into order (id, json) values (?, ?)");
    Object execute = db.command(insert).execute(String.valueOf(UUID.randomUUID()), embeddedRecordJson);
  }

  @Test
  public void insertPreparedEmbeddedVarargODocument() {
    // WORKS on 2.2.35, FAILS on 3.0.2
    OCommandSQL insert = new OCommandSQL("insert into order (id, json) values (?, ?)");
    Object execute = db.command(insert).execute(String.valueOf(UUID.randomUUID()), new ODocument().fromJSON(embeddedRecordJson));
  }

  @Test
  public void insertPreparedEmbeddedNoArgs() {
    // WORKS 2.2.35 and 3.0.2
    OCommandSQL insert = new OCommandSQL(
        String.format("insert into order (id, json) values ('%1$s', %2$s)", UUID.randomUUID(), embeddedRecordJson));
    Object execute = db.command(insert).execute();
  }

}

Maven pom to run the test. (The test in SOME_DIR/src/test/de/logopak/ and the pom.xml in SOME_DIR, run mvn test)

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>de.logopak</groupId>
  <artifactId>test-orientdb-prepared</artifactId>
  <version>1.0-SNAPSHOT</version>


  <dependencies>
    <dependency>
      <groupId>com.orientechnologies</groupId>
      <artifactId>orientdb-parent</artifactId>
      <version>3.0.2</version>
      <!--<version>2.2.35</version>-->
    </dependency>
    <dependency>
      <groupId>com.orientechnologies</groupId>
      <artifactId>orientdb-core</artifactId>
      <version>3.0.2</version>
      <!--<version>2.2.35</version>-->
    </dependency>
    <dependency>
      <groupId>com.orientechnologies</groupId>
      <artifactId>orientdb-client</artifactId>
      <version>3.0.2</version>
      <!--<version>2.2.35</version>-->
    </dependency>
    <dependency>
      <groupId>org.junit.jupiter</groupId>
      <artifactId>junit-jupiter-api</artifactId>
      <version>RELEASE</version>
      <scope>test</scope>
    </dependency>
  </dependencies>

</project>

[markodjurovic]

@LukasWysocki

with version 3.0.2 try to use new API, instead old one. So for example insertPreparedEmbeddedVarargJsonMap may look like:

  String insert = "insert into order (id, json) values (?, ?)";
  Map<String, Object> stringObjectMap = new  ODocument().fromJSON(embeddedRecordJson).toMap();
  stringObjectMap.put("@type", "d");
  db.command(insert, String.valueOf(UUID.randomUUID()), stringObjectMap);

With beforeAll:

  OrientDB orientDB = new OrientDB("remote:localhost", "root", "root", OrientDBConfig.defaultConfig());
  ODatabaseSession db = orientDB.open("test", "admin", "admin");
  OClass aClass = db.createClass("order");
  aClass.createProperty("id", OType.STRING).setMandatory(true).createIndex(INDEX_TYPE.UNIQUE);
  aClass.createProperty("json", OType.EMBEDDEDMAP).setMandatory(true);

[LukasWysocki]

Thank you.