0

I am using mysql-x-devapi and need to insert a row to a table and put UNIX_TIMESTAMP() of the server in a column:

sql_client_.getSession().getDefaultSchema()
    .getTable("event")
    .insert("title", "time")
    .values("event title", "UNIX_TIMESTAMP()")
    .execute();

This code gives me: CDK Error: Incorrect integer value 'UNIX_TIMESTAMP()' for column 'time' at row 1

How can I do this using xdevapi (not sql command that needs sql string)?

I am able to use mysqlx::expr("UNIX_TIMESTAMP()") in set function when updating the table. The same doesn't work with insert and fails with the following error:

/usr/include/mysql-cppconn-8/mysqlx/devapi/table_crud.h:157:17: error: ‘mysqlx::abi2::r0::internal::Expression::Expression(V&&) [with V = mysqlx::abi2::r0::internal::Expression&]’ is private within this context
  157 |       add_values(get_impl(), rest...);
Null
  • 349
  • 2
  • 12
  • This seems like a bug in xdevapi code, I was able to fix compile error by changing the source code and adding perfect forwarding on `values` function and `add_values` function called inside it. – Null Jun 28 '22 at 01:39

2 Answers2

0

The X DevAPI user guide gives some hints about what should be supported or not. In that case, as you can see from the function definition documentation, the values() method expects a Literal, so, not even mysqlx.expr() will work.

In your case, looking at the error message, it means that the time column probably is some kind of INTEGER data type. Given "UNIX_TIMESTAMP()" is a string (because the expression is not being evaluated), it is not being coerced in any way and would only work for other column data types such as VARCHAR.

In theory, from what I can tell, there is nothing that really prevents the API from allowing computed expressions. The X Protocol accommodates that but I'm not entirely sure it is the same case for the X Plugin itself. I would suggest you request the feature using the public MySQL bug tracker and the corresponding category (maybe MySQL Connectors: Document Store: DevAPI).

Disclaimer: I'm the lead developer of the MySQL X DevAPI Connector for Node.js

ruiquelhas
  • 1,905
  • 1
  • 17
  • 17
  • It seems that documentation is not up to date. `mysqlx::expr()` worked in C++. – Null Jun 29 '22 at 20:09
  • But that's because you changed the Connector/C++ implementation, right? As I mentioned, from the protocol (and apparently from the X Plugin) standpoint, there's nothing that prevents the API to allow that. But as far as I know, it does not (as per the documentation). – ruiquelhas Jun 30 '22 at 09:32
  • I just fixed a bug (as explained in my answer), the feature was already implemented. – Null Jun 30 '22 at 14:42
0

I used mysqlx::expr("UNIX_TIMESTAMP()"):

sql_client_.getSession().getDefaultSchema()
    .getTable("event")
    .insert("title", "time")
    .values("event title", mysqlx::expr("UNIX_TIMESTAMP()"))
    .execute();

then fixed the compile error by touching:

/usr/include/mysql-cppconn-8/mysqlx/devapi/table_crud.h replaced:

  template<typename... Types>
  TableInsert& values(Types... rest)
  {
    try {
      add_values(get_impl(), rest...);
      return *this;
    }
    CATCH_AND_WRAP
  }

with:

  template<typename... Types>
  TableInsert& values(Types&&... rest)
  {
    try {
      add_values(get_impl(), std::forward<Types>(rest)...);
      return *this;
    }
    CATCH_AND_WRAP
  }

/usr/include/mysql-cppconn-8/mysqlx/devapi/detail/crud.h replaced:

  template <typename... T>
  static void add_values(Impl *impl, T... args)
  {
    Add_value::Impl row{ {}, 0 };
    Args_processor<Add_value>::process_args(&row, args...);
    Add_row::process_one(impl, row.first);
  }

with:

  template <typename... T>
  static void add_values(Impl *impl, T&&... args)
  {
    Add_value::Impl row{ {}, 0 };
    Args_processor<Add_value>::process_args(&row, std::forward<T>(args)...);
    Add_row::process_one(impl, row.first);
  }
Null
  • 349
  • 2
  • 12