mORMot Open Source

chapa

Member

Registered: 2012-04-30

Posts: 117

RawUTF8 properties stored as null

Just a quick question.

Have simple SQLRecord, setting RawUTF8 property to "" (empty) string and use TSQLRestServerDB.Add() to store it.
I see it is bound using sqlite3_bind_text with pointer(value) = nil and length of zero.

Is it normal sqlite to interpret it as null and store null in the database instead of just an empty text, as far as parameter is not bound using sqlite3_bind_null()?

Thanks.

chapa

Member

Registered: 2012-04-30

Posts: 117

Re: RawUTF8 properties stored as null

In SynSQLite3 TSQLRequest.Bind() if I do this:

const
  C_EMPTY: PUTF8Char = #0;

(..)

  if pointer(value) = nil then
    sqlite3_check(RequestDB, sqlite3.bind_text(Request, Param, C_EMPTY, 0, SQLITE_TRANSIENT)) // make private copy of the data
  else
    sqlite3_check(RequestDB,sqlite3.bind_text(Request,Param,pointer(Value), length(Value),SQLITE_TRANSIENT)); // make private copy of the data

works as expected, no NULL added for empty RawUTF8 published property to the database.
Actually any not nil pointer passed to bind_text will work.

But is adding NULL for empty RawUTF8 published properties "by design" for mORMot framework?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: RawUTF8 properties stored as null

This is AFAIK a SQLite3 "feature".
In fact, storing null uses less space and is faster than storing a void string.

Due to SQLite3 column affinity - see http://www.sqlite.org/datatype3.html - it will alway be retrieved as a '' string.

See also this blog post: http://blog.synopse.info/post/2010/01/0 … L-handling

So we may change the default behavior.
Perhaps it is worth it, when working with external database.
Using an option at TSQLRestServer level or mORMotDB.pas could be envisaged.

chapa

Member

Registered: 2012-04-30

Posts: 117

Re: RawUTF8 properties stored as null

Thanks ab.

It worth everytime a text column is part of an index.
Maybe passing @Value[1], length(Value) is more correct.

If this column is involved within index or is part of not null constraint, the logic brokes.
Also, if you try to .Retrieve such a record passing "" (not null) property you will miss previously inserted same record, due to sqlite3 affinity.

Last edited by chapa (2013-03-24 14:30:32)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: RawUTF8 properties stored as null

Now TSQLRequest.Bind(col,'') will bind '' void text instead of null value.
See http://synopse.info/fossil/info/4392ee3b74
We use SQLITE_STATIC since the '' value is a constant.

Sounds better behavior.

I've set the same behavior for TSQLDBSQLite3Statement.BindTextP('') and VarDataToContext().

Thanks for the report.

chapa

Member

Registered: 2012-04-30

Posts: 117

Re: RawUTF8 properties stored as null

Thank ab,
And sorry for disturbing you on Sunday