Deal with null value in sqlite database

[Jérémy MARQUER]

Hi,

I'm facing to a problem with load/read data in database. In case of integer or long column type, we can't find/update/save object with null values since these methods return primitive types : 

  /**
     * Gets column value by index.
     *
     * @param index starts with zero
     * @return a int data from the database
     * @throws IOException
     */
    public int getInteger(int index)throws IOException;


    /**
     * Gets column value by index.
     *
     * @param index starts with zero
     * @return a long data from the database
     * @throws IOException
     */
    public long getLong(int index)throws IOException;

I absolutely need to save null value in my database. 

FYI, I use cn1-data-lib-access shannah's library.

Thanks.

If you are experiencing an issue please mention the full platform your issue applies to:
IDE: NetBeans/Eclipse/IDEA
Desktop OS
Simulator
Device

[Steve Hannah]

You can save null values with custom SQL queries.  (e.g. UPDATE mytable set somecol=NULL where somekey='val')

I believe you're correct that the Row interface won't return null values.  getInteger()/getLong() etc should just return zero in these cases.

Steve

--
You received this message because you are subscribed to the Google Groups "CodenameOne Discussions" group.
To unsubscribe from this group and stop receiving emails from it, send an email to codenameone-discu...@googlegroups.com.
Visit this group at https://groups.google.com/group/codenameone-discussions.
To view this discussion on the web visit https://groups.google.com/d/msgid/codenameone-discussions/2fb942eb-aec7-40a1-902a-d20e5781ce43%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Steve Hannah

Software Developer

Codename One

http://www.codenameone.com

[Shai Almog]

I haven't tried this but did you try getting the value as a String?
If null will be returned you can know it's null and then you don't need to get the integer value.

I'm not sure if it will work across devices though...

[Jérémy MARQUER]

Thanks for both your responses.

Sure I know I can save null values with custom queries. In fact, my code is already adapted to invoke framework's method (eg : update/save/find), so I don't use custom query (and prefer don't use it).

When I initialize my database, Integer/Long column contains NULL values. I retrieve object with getById(long id) method (that invoke unmap method) and it's there that the field with null value return 0 instead. So I can't distinguish a field that really have "0" value. (Some of my fields doesn't need the 0 value but others need).

Shai : I've already thinking of using String but it requires that I modify all my code (that's pretty huge). Moreover, I will have to implement special interface to treat such kind of field.

[Shai Almog]

What I was trying to say is this:
Use your number code as before without a problem. However, when you need to check if a value is null use the getString() method to check that special case.

[Jérémy MARQUER]

Ok I've misunderstood ! 

Problem is I've no control on this code since it's included in shannah's plugin (cn1-lib-access).

Whatever, I will bypass my problem differently.

[Jérémy MARQUER]

Hi,

FYI Shannah, 

I modified DAO.java in your framework like : 

/**

* Fills a map with the data of the current row of database Cursor

*

* @param c

*            The database cursor from a query.

* @param m

*            The map to fill.

* @throws IOException

*/

protected void fillMap(Cursor c, Map m) throws IOException {

Row row = c.getRow();

int len = c.getColumnCount();

for (int i = 0; i < len; i++) {

String colName = c.getColumnName(i);

ColType colType = colTypes.get(colName);

if (colType == null) {

continue;

}

switch (colType) {

case FLOAT:

if (isNotNullValue(row, i))

m.put(colName, row.getFloat(i));

break;

case DOUBLE:

if (isNotNullValue(row, i))

m.put(colName, row.getDouble(i));

break;

case BLOB:

m.put(colName, row.getBlob(i));

break;

case STRING:

case VARCHAR:

m.put(colName, row.getString(i));

break;

case INTEGER:

if ("id".equals(colName) || colName.startsWith("id")) { // if it's the object's id or foreign key id

m.put(colName, row.getLong(i));

} else {

if (isNotNullValue(row, i))

m.put(colName, row.getInteger(i));

}

break;

case LONG:

if (isNotNullValue(row, i))

m.put(colName, row.getLong(i));

break;

case SHORT:

if (isNotNullValue(row, i))

m.put(colName, row.getShort(i));

break;

default:

}

}

}

private boolean isNotNullValue(Row row, int ind) throws IOException {

return !"null".equals(row.getString(ind));

}

If it's null in database, it doesn't fill map with the value so when your try to retrieve your value like :

map.get("my_value_key");

it will return null (as it is in database.

Won't hesitate if I am wrong !

[Jérémy MARQUER]

I come back to this topic which is very problematic for me. The manner which I use permit me to read/save null values in database. But in fact, it's "null" string values that are stored in db. So, there is no constraint validation possible with that. Even worse, foreign key constraint can failed if you want to put a null foreign key since it's not really a null value....

I don't understand why query execution can take Object[] as parameter while it's converted to String[]. Why can't we inject object in the query and not string as params ???

Thanks !

On Tuesday, May 24, 2016 at 10:32:33 AM UTC+2, Jérémy MARQUER wrote:

[Shai Almog]

The main issue is old fallback code. The iOS implementation is native so the sqlite layer has no notion of "object".

Notice that in the string conversion "null" is preserved as null so it should work correctly:

                if (params[i] == null) {
                    strParams[i] = null;
                } else {
                    strParams[i] = params[i].toString();
                }

I'm not sure exactly where this is failing.

If you have a specific use case that you can reproduce with a simple standalone test case you can file an RFE but our plates are really overflowing at the moment and I'm not sure when we will get around to fixing issues.

[Jérémy MARQUER]

You're right ! In fact, I think the problem is in cn1-data-lib-access. As object array is construct, it can't receive NULL value since all object are converted to String. For example, for insertion :

/**

* Gets generic insert args for insert statement for the given map of row data.

*

* @param m

*            Map containing row data to insert. Maps column names to column values.

* @return Object[] array that can be used in Database.execute()

* 

*/

private Object[] insertArgs(Map m) {

List largs = new ArrayList();

for (Map.Entry<String, ColType> e : colTypes.entrySet()) {

if ("id".equals(e.getKey()) && m.get("id") == null) {

continue;

}

if ("id".equals(e.getKey()) && NumberUtil.longValue(m.get("id")) <= 0) {

continue;

}

largs.add("" + m.get(e.getKey()));

}

return largs.toArray();

}

[Shai Almog]

Steve will have to help with that as I'm not familiar with that code.