Unable to display the results of an SQLite DB Query on UX

How-to Discussions
1

I’m building an app that fetches data from SQLite and then displays info to the user. Challenge is in displaying detailed info after selecting an item where I get the below response but I cannot display the specific details i.e. title or beds in the UX

[{"prop_id":"2","title":"This Title","price":"200","beds":"4","baths":"5","excerpt":"Description of this property\n","contract":"RENT"}]

I have an observable which I assign the result of the above database query and then parse it to be able to display it’s result on the console as shown below:

var Observable = require("FuseJS/Observable");
var sqlite = require("SQLite");
var det = sqlite.open("my.sqlite");
var prop = Observable();

this.onParameterChanged(function(param) {
	debug_log("Fetching Property ID: " + param);
	getDetails(param);
});

function getDetails(id) {
	try {
		var data = det.query("SELECT * FROM table WHERE prop_id = ?", id);
		prop.value = JSON.stringify(data);
		console.log(prop.value);
	}
	catch (e) {
		console.log("Unable to retrieve details from DB\n" + e);
	}
}

module.exports = {
	prop: prop,
	back: function() {
		router.goBack();
	},
	search: function() {
		router.push("search");
	}
}

I am able to display the result of prop within a Text element i.e. {prop}" displays [{"prop_id":"2","title":"This Title","price":"200","beds":"4","baths":"5","excerpt":"Description of this property\n","contract":"RENT"}] on preview. However, I cannot get the value of {prop.title}, {prop.beds} etc

Where could I be going wrong in my code?

2

You need to parse your JSON string into JavaScript objects. You can do that with JSON.parse(). Then query the JS object for whatever property you want.

For example:

var jsonString = ...; // The JSON string you get from your database
var obj = JSON.parse(jsonString);
var title = obj[0].title;
var beds = obj[0].beds;
3

prop gets set to the JSON string of the db-query. I would do prop.replaceAll(data);, instead of prop.value = JSON.stringify(data);

4

Vegard Strand Lende wrote:

You need to parse your JSON string into JavaScript objects. You can do that with JSON.parse(). Then query the JS object for whatever property you want.

For example:

var jsonString = ...; // The JSON string you get from your database
var obj = JSON.parse(jsonString);
var title = obj[0].title;
var beds = obj[0].beds;

So I’ve made changes to my code as proposed by both Vegard Strand Lende and Bjorn-Olav Strand as shown below:

var Observable = require(“FuseJS/Observable”);
var sqlite = require(“SQLite”);
var det = sqlite.open(“my.sqlite”);
var prop = Observable();

this.onParameterChanged(function(param) {
debug_log("Fetching Property ID: " + param);
getDetails(param);
});

function getDetails(id) {
try {
var data = det.query(“SELECT * FROM propertiz WHERE prop_id = ?”, id);
var obj = JSON.parse(data);
title.value = obj[0].title;
//prop.value = JSON.stringify(data);
prop.replaceAll(data);
testPahali(prop);
}
catch (e) {
//console.log(“Unable to retrieve details from DB\n” + e);
console.log(“\n” + e);
}
}

module.exports = {
prop: prop,
title: title,
back: function() {
router.goBack();
},
search: function() {
router.push(“search”);
}
}

Unfortunately, I still get nothing displayed on my UX which looks like this:

I’ll appreciate any further assistance on the above issue as I have been banging my head on this issue for the past 2 weeks

5

Martin Kihuha wrote:

> var Observable = require("FuseJS/Observable");
> var sqlite = require("SQLite");
> var det = sqlite.open("my.sqlite");
> var prop = Observable();
> 
> this.onParameterChanged(function(param) {
>     debug_log("Fetching Property ID: " + param);
>     getDetails(param);
> });
> 
> function getDetails(id) {
>     try {
>         var data = det.query("SELECT * FROM propertiz WHERE prop_id = ?", id);
>         var obj = JSON.parse(data);
>         title.value = obj[0].title;
>         //prop.value = JSON.stringify(data);
>         prop.replaceAll(data);
>         testPahali(prop);
>     }
>     catch (e) {
>         //console.log("Unable to retrieve details from DB\n" + e);
>         console.log("\n" + e);
>     }
> }
> 
> module.exports = {
> 	prop: prop,
> 	title: title,
> 	back: function() {
> 		router.goBack();
> 	},
> 	search: function() {
> 		router.push("search");
> 	}
> }

Unfortunately, I still get nothing displayed on my UX which looks like this:

> <StackPanel>
> 				        <pricText Value="{price}" />
> 				        <locText Value="Karen" />
> 				        <Rectangle Height="1" RowSpan="3" Fill="#02344A" Layer="Background" Margin="30,0" />
> 					    <Grid RowCount="2" ColumnCount="2" Margin="40,10">
> 					        <detText Row="0" Column="0" Value="{beds}" />
> 					        <detText Row="0" Column="2"  Value="{baths}" />
> 					        <Rectangle Width="1" ColumnSpan="3" Fill="#02344A" Layer="Background" />
> 					        <fa_bed Row="2" Column="0" TextColor="#000" TextAlignment="Center" />
> 					        <Image Row="2" Column="2" Color="#000" File="Assets/shower.png" Height="20" Alignment="Center" />
> 					    </Grid>
> 					</StackPanel>

I’ll appreciate any further assistance on the above issue as I have been banging my head on this issue for the past 2 weeks

You will not need to JSON.parse the data from SQLite. It is not in JSON format, but returned as JavaScript Objects.

The problem is with your UX. You can try this to start:

<Each Items="{prop}">
<StackPanel>
	<pricText Value="{price}" />
	<locText Value="Karen" />
	<Rectangle Height="1" RowSpan="3" Fill="#02344A" Layer="Background" Margin="30,0" />
	<Grid RowCount="2" ColumnCount="2" Margin="40,10">
		<detText Row="0" Column="0" Value="{beds}" />
		<detText Row="0" Column="2"  Value="{baths}" />
		<Rectangle Width="1" ColumnSpan="3" Fill="#02344A" Layer="Background" />
		<fa_bed Row="2" Column="0" TextColor="#000" TextAlignment="Center" />
		<Image Row="2" Column="2" Color="#000" File="Assets/shower.png" Height="20" Alignment="Center" />
	</Grid>
</StackPanel>
</Each>
6

Apologies on my delayed response as I have been traveling with limited access to the Internet.

I made the proposed changes as shown below without any difference as I still cannot display the values on my UX:

this.onParameterChanged(function(param) {
debug_log("Fetching Property ID: " + param);
getDetails(param);
});

function getDetails(id) {
try {
var data = det.query(“SELECT * FROM propertiz WHERE prop_id = ?”, id);
prop.value = JSON.parse(data);
console.log(prop.value);
}
catch (e) {
//console.log(“Unable to retrieve details from DB\n” + e);
console.log("\n" + e);
}
}

module.exports = {
prop: prop,
back: function() {
router.goBack();
},
search: function() {
router.push(“search”);
}
}

I’m not sure where I could be going wrong. So I’ll tear down my code and start afresh to see if there is something amiss.

7

Return value from query is not a JSON string.

8

Bjørn-Olav Strand wrote:

Return value from query is not a JSON string.

Isn’t that why I then populate it into my Observable variable using this line:

prop.value = JSON.parse(data);

Or what is the best way to handle it?

9 
prop.replaceAll(data);
10

Bjørn-Olav Strand wrote:

prop.replaceAll(data);

Thanks Bjørn-Olav Strand. This is now working