ngrest-db with mysql db and null fields

[George Saliba]

Hi,

I'm using ngrest and ngrest-db with a mysql database. Some of the tables have fields that can be null and I have defined these fields with ngrest::Nullable<>, however when I try to run a select query, any record that has at least one field null is not returned, only the records which have non-null values for all fields are returned. If all records have some null fields, I get this error message:  MySqlDb.cpp:507  resultString : res.buffer is null!

I defined the tables as follows:

// *table: mytable
struct MyTable
{
  // *pk: true
  // *autoincrement: true
  int id

  ...

  std::string some_non_null_field;

  ...

  ngrest::Nullable<std::string> some_null_field;

  ...
};

// then in the request handler function:

ngrest::MySqlDb mySqlDb({"mydb","user","pass"});

ngrest::Table<MyTable> mytable(mySqlDb); 

...

return mytable.select();

What am I doing wrong? 

Do I need to specify anything else for the null fields to be accepted? 

Thanks,

George

[Dmitry Utkin]

Hello,

I tried it, but cannot reproduce. Maybe you have null fields for the fields you didn't declared as nullable.

I used this to check:

// *table: mytable

struct MyTable

{

    // *pk: true

    // *autoincrement: true

    int id;

    std::string some_non_null_field;

    ngrest::Nullable<std::string> some_null_field;

};

void test2(Db& db)

{

    ngrest::Table<MyTable> table(db);

    table.create();

    table.deleteAll();

    table << MyTable{0, "test10", std::string("test11")}

          << MyTable{0, "test20", nullptr}

          << MyTable{0, "test30", nullptr};

    for (const MyTable& t : table.select()) {

        std::cout << "id: " << t.id << " some_non_null_field: " << t.some_non_null_field << " some_null_field: " << t.some_null_field << std::endl;

    }

}

        ngrest::MySqlDb mysqlDb({"test_ngrestdb", "ngrestdb", "ngrestdb"});

        ngrest::test::test2(mysqlDb);

Result was:

id: 1 some_non_null_field: test10 some_null_field: test11

id: 2 some_non_null_field: test20 some_null_field: null

id: 3 some_non_null_field: test30 some_null_field: null

Output from mysql console client:

mysql> SELECT * FROM test_ngrestdb.mytable;

+----+---------------------+-----------------+

| id | some_non_null_field | some_null_field |

+----+---------------------+-----------------+

|  1 | test10              | test11          |

|  2 | test20              | NULL            |

|  3 | test30              | NULL            |

+----+---------------------+-----------------+

3 rows in set (0,00 sec)

четверг, 3 августа 2017 г., 1:36:23 UTC+3 пользователь George Saliba написал:

[George Saliba]

Hi, 

I have figured out what the issue is. 

My mysql tables are no generated using ngrest, I am using ngrest to access existing tables. In the table creating code (mysql) I had some fields which were set to allow NULL values but I had no default value defined. So if i insert a new record (outside of ngrest) and do not specify a value for those nullable fields, those fields would not be NULL but just empty (not sure if there is a technical word for this state).

So taking your example table above, it would be like this:

mysql> SELECT * FROM test_ngrestdb.mytable;

+----+---------------------+-----------------+

| id | some_non_null_field | some_null_field |

+----+---------------------+-----------------+

|  1 | test10              | test11          |

|  2 | test20              |                 |

|  3 | test30              |                 |

+----+---------------------+-----------------+

In my case the missing fields are empty but not NULL ...In fact if I set them to NULL ngrest Nullable works as expected. I am fixing this by setting all defaults to NULL were applicable but you might want to check this condition too.

Thanks,

George