Matlab checking for a value in a database

Question

I have the following Boolean statement in Python:

db_connection.query(
   'select storage_time from traces where id=' + trace_id
).dictresult()[0]['storage_time'] == None

It basically checks if there is a value in storage_time and I would like to do the same thing in Matlab, but I can't find anything equivalent to None.

Could you please help me out?

Thanks

Answer 1

Equivalent to None values from Python are NULLs. And since you connect to your database via Matlab Database Toolbox, you need to specify how NULL values retrieved from database are to be presented in Matlab. This may be done by setting of 'NullNumberRead' via calling of setdbprefs function from Matlab Database Toolbox. For instance, you can do this so

setdbprefs('NullNumberRead','NaN')

or so

setdbprefs('NullNumberRead','0')

Unfortunately, there is no guarantee that the NULL representation value specified by this way won't be confused with real non-NULL values obtained as a result of your query (it is your own responsibility in this case to guarantee the query always do not contain NaNs or zeros, respectively, among non-NULL values in its results). But if you have to connect to PostgreSQL, as far as I know, there exists at least one Matlab and PostgreSQL connector that respects NULLs in a fully consistent manner. This is a high-performance PostgreSQL client library PgMex.

In PostgreSQL both a value itself and the value elements can be NULL (for array types). This makes a representation of NULLs in Matlab less trivial than expected.

To illustrate the way PgMex uses to represent NULLs in Matlab, let us consider the following example. Suppose you have to retrieve results for a query returning one field myfield of float8[] type with two tuples. And suppose the value of myfield for the first tuple equals to NULL as itself, while for the second tuple the corresponding value equals {0,NULL,NaN}. Results are obtained as follows (we assume that the argument of the very first command connect below is corrected properly and that the table mytable containing myfield of float8[] type among its fields already exists within the respective database):

% Create the database connection
dbConn=com.allied.pgmex.pgmexec('connect',[...
    'host=<yourhost> dbname=<yourdb> port=<yourport> '...
    'user=<your_postgres_username> password=<your_postgres_password>']);

pgResult=com.allied.pgmex.pgmexec('exec',dbConn,...
    'select myfield from mytable'); % perform the query

SMyField=com.allied.pgmex.pgmexec('getf',pgResult,...
    '%float8[]',0); % retrieve the results

Here SMyField is a structure with three fields: valueVec, isNullVec and isValueNullVec. isValueNullVec equals a column logical array [true;false], that is the entire value for the first tuple equals NULL, while the value for the second tuple does not equal NULL as itself. isNullVec equals the following column cell array: {[];[false,true,false]}. By this way it is possible to indicate that only the second element of the array being the value of myfield for the second tuple is NULL. At last, valueVec equals to the column cell array {[];[0 0 NaN]}. Only the first and the third elements of the second cell are relevant, in contrast to the second element of this array. This is because isNullVec clearly indicates that this second element is NULL, so this zero value does not matter (some default value is chosen for each particular data type).

What concerns your example, the respective code in Matlab may be the following (we assume that dbConn obtained as above already exists as well as that the query below is correct with storage_time field of timestamp type and the variable trace_id being already defined):

pgResult=com.allied.pgmex.pgmexec('exec',dbConn,...
    ['select storage_time from traces where id=' + trace_id]); % perform the query
SStorageTime=com.allied.pgmex.pgmexec('getf',pgResult,...
    '%timestamp',0); % retrieve the results
% check that the value for the first tuple is not NULL
isStorageTime=~SStorageTime.isValueNullVec(1);

Hence it is sufficient to check only isValueNullVec.

EDIT: There are free academic licenses for PgMex.

Answer 2

MATLAB's Database Toolbox has preferences how to handle NULL values. Depending on those settings you can get different values. See SETDBPREFS for details. You can change preferences in GUI as well.

By default you will get NaN if you read the data as numeric, and 'NULL' strings if you read as strings. In the first case for numbers check for NaN with ISNAN function.

null_idx = isnan(fetcheddata);

For strings use STRCMP:

null_idx= strcmp(upper(fetcheddata), 'NULL');

In addition, if you fetch the data as cell array, you may need to deal with them with CELLFUN or convert to matrix with CELL2MAT.