How to insert string literals?

[dsmyth]

I'm trying to insert file paths, and sqlite is erroring out on the embedded '/' characters. Normally I'd just surround the string with single quotes, but it looks like your template handler doesn't like that.

i.e.

QUERY(createCapture,
      "INSERT INTO captures VALUES ("
      ":capture.id, "
      ":capture.timeStamp,"
      "':capture.sampleName',"
      "':capture.previewName',"
      "':capture.imageName',"
      "':capture.depthName',"
      "':capture.videoName',"
      "':capture.note',"
      ":capture.floor,"
      ":capture.pin,"
      ":capture:updateTimeStamp"
      ");",
      PARAM(oatpp::Object<CaptureDto>, capture))

None of the parameters surrounded with single quotes get expanded. If I put a debug log in oatpp::sqlite::Executor::execute it gets expanded to:

INSERT INTO captures VALUES (?,':capture.sampleName',':capture.previewName',':capture.imageName',':capture.depthName',':capture.videoName',':capture.note',?,?,??);

[lganzzzo]

Hey @dsmyth ,

You don't need those quotes. Just remove them. SQLite and oatpp know that those parameters are strings and will escape all values correctly.

From the crud-example project:

  QUERY(createUser,
        "INSERT INTO AppUser"
        "(username, email, password, role) VALUES "
        "(:user.username, :user.email, :user.password, :user.role);",
        PARAM(oatpp::Object<UserDto>, user))

You see, username, email, and passwords are strings. No quotes needed.

Regards, Leonid

[lganzzzo]

Hey @dsmyth ,

I'm trying to insert file paths, and sqlite is erroring out on the embedded '/' characters.

I double-checked with file paths and URLs - just remove those quotes and everything should work.

Please let me know if it worked for you.

Regards, Leonid

[lganzzzo]

@dsmyth ,

Here is the detailed example:

DDL

CREATE TABLE files (
  filepath TEXT
);

DbClient

  QUERY(insertFile,
        "INSERT INTO files VALUES (:filepath);",
        PARAM(oatpp::String, filepath))

  QUERY(insertHardcodedFile,
        "INSERT INTO files VALUES ('/my/hardcoded/file/path');")

  QUERY(selectAllFiles, "SELECT * FROM files;")

Usage

  {
    auto connection = client.getConnection();

    client.insertFile("/file/path", connection);
    client.insertFile("/dir/path/", connection);
    client.insertFile("\\c\\win\\file\\path", connection);
    client.insertHardcodedFile(connection);
  }

  {
    auto res = client.selectAllFiles();
    auto dataset = res->fetch<oatpp::Vector<oatpp::Fields<oatpp::Any>>>();

    oatpp::parser::json::mapping::ObjectMapper om;
    om.getSerializer()->getConfig()->useBeautifier = true;
    auto str = om.writeToString(dataset);

    OATPP_LOGD(TAG, "res=%s", str->c_str());
  }

Output

[
  {
    "filepath": "\/file\/path"
  },
  {
    "filepath": "\/dir\/path\/"
  },
  {
    "filepath": "\\c\\win\\file\\path"
  },
  {
    "filepath": "\/my\/hardcoded\/file\/path"
  }
]

[dsmyth]

Interesting. Originally I didn't have the quotes -- it was like your example -- but I was getting an exception on the insert. I'll write a small standalone example so I can figure out what's going on.

[lganzzzo]

Hm, interesting... What SQLite version do you have? Or are you using the amalgamation shipped and installed together with oatpp-sqlite ?

[dsmyth]

I'm using the amalgamation. I can try using sqlite3 -- I'm on Ubuntu 20.04, so it's 3.31.1-4ubuntu0.2. I was using sqlite_orm, but was having some issues with it, so I thought I switch over to oatpp-sqlite, since I was already using oatpp and oatpp-swagger. I want to be able to move to oatpp-postgresql, so I thought this would be a good intermediate step. Everything else works except for creating new records with this particular table.

Thanks for your help!

[lganzzzo]

Can you please share the following for me to test:

• DDL - captures table
• CaptureDto definition
• Exact values of the CaptureDto fields you are trying to insert
• And the exception you are getting.

[dsmyth]

@lganzzzo I forked your example-crud project, verified that it was working in my environment -- it accepts file paths in text fields no problem. Then I modified it to use my capture object instead of the user object. This fails on an insert.

The error is: {"status":"ERROR","code":500,"message":"[oatpp::sqlite::mapping::Serializer::serialize()]: Error. No serialize method for type 'Object'"}

The modified example is here: https://github.com/dsmyth/example-crud

A sample record (taken from our live system using oatpp-1.1 and sqlite_orm) is in file 'data.json' in the forked repo.

Thanks for all your help! This is a great framework -- I came from rolling my own using Simple Web Server + nlohmann's JSON library + sqlite_orm. Previously I had used Dropwizard for Java and this is getting close to having that kind of capability in C++.

[lganzzzo]

Hey @dsmyth ,

This fails on an insert.

The error is: {"status":"ERROR","code":500,"message":"[oatpp::sqlite::mapping::Serializer::serialize()]: Error. No serialize method for type 'Object'"}

There is a typo here. Please change :capture:updateTimeStamp, --> :capture.updateTimeStamp,

When I fixed it - the insert worked fine.

I also was able to insert the following object:

{
  "id": 1,
  "timeStamp": 0,
  "updateTimeStamp": 0,
  "sampleName": "/file/path",
  "previewName": "/file/path",
  "imageName": "string",
  "depthName": "string",
  "videoName": "string",
  "note": "string",
  "floor": 0,
  "pin": true
}

And thanks for the feedback! - it's really important for understanding what oatpp users are looking for!

Regards, Leonid

[lganzzzo]

:capture:updateTimeStamp was treated as two different parameters :capture and :updateTimeStamp

The first one is of type oatpp::Object which can't be bind to query params. And the second one is the non-existing parameter.

Yeah, it's really not so easy to see the difference between : and . there. Maybe it makes sense to change : to something like @ (but it's not currently in the scope)

[dsmyth]

Good eye! That typo came from my original code. All of my endpoints are working now -- thank you!