Welcome!

Welcome to the official BlackBerry Support Community Forums.

This is your resource to discuss support topics with your peers, and learn from each other.

inside custom component

Native Development

Reply
Developer
Bluenoser
Posts: 267
Registered: ‎04-04-2012
My Device: BlackBerry Z30/Z10/Z10LE/Q10/PlayBook

Download JSON fle and save to SQL

What I would like to do is to download a JSON file from a webserver and then save the data directly to a SQL file on the device. Any suggestions or example of how to do this?

 

Thanks

Please use plain text.
Trusted Contributor
Brennan12325
Posts: 204
Registered: ‎05-15-2012
My Device: None
My Carrier: Telus

Re: Download JSON fle and save to SQL

I'm not sure what you mean by "directly". Certainly you can parse the json into an object, then run some insert statements on a DB. Is this a one time operation? Does it happen every time the app runs?

----------------------
Check out my app, Alien Flow for reddit

And of course, like my post if you found it helpful or informative!
Please use plain text.
Developer
Bluenoser
Posts: 267
Registered: ‎04-04-2012
My Device: BlackBerry Z30/Z10/Z10LE/Q10/PlayBook

Re: Download JSON fle and save to SQL

@Brennan12325,

 

  I guess I shouldn't use the word "directly". As you said, load the JSON and then insert the data into SQL. This will be done before the list using the SQL data is displayed. The JSON data will only have new information since the last JSON to SQL update.

 

 

Please use plain text.
Trusted Contributor
Brennan12325
Posts: 204
Registered: ‎05-15-2012
My Device: None
My Carrier: Telus

Re: Download JSON fle and save to SQL

Before I write a code example of how to do this, just to be clear, is a C++ solution ok? I'm not sure how (or if) this could be done in pure QML, and it's definitely not something I would want to attempt :smileytongue:

----------------------
Check out my app, Alien Flow for reddit

And of course, like my post if you found it helpful or informative!
Please use plain text.
Developer
Bluenoser
Posts: 267
Registered: ‎04-04-2012
My Device: BlackBerry Z30/Z10/Z10LE/Q10/PlayBook

Re: Download JSON fle and save to SQL

Yes. That is fine. Thanks

Please use plain text.
Trusted Contributor
Brennan12325
Posts: 204
Registered: ‎05-15-2012
My Device: None
My Carrier: Telus

Re: Download JSON fle and save to SQL

[ Edited ]
// PARSE.cpp

DatabaseAccessManager dbManager;

//This is a reply handler for a network request. Depending on how you do your request
// your QNetworkReply might be a parameter.
void PostCommentsPageHandler::commentsRequestReplyHandler() {
	QNetworkReply *reply = qobject_cast<QNetworkReply *>(sender());
	if (!reply->error()) {
		JsonDataAccess jda;
		QVariant variant = jda.loadFromBuffer(response);
		QVariantList jsonTree = variant.value<QVariantList>();
		
		parseJson(jsonTree);
	}
}

void parseJson(QVariantList jsonTree)
{
	//Here's where you have to use your own JSON format. For instance
	// this JSON has a list of elements "children". ie.
	//
	// "children": [
	//	{
	//	} ]
	QVariantList treeList = tree.toMap()["children"].toList();
	
	Q_FOREACH(QVariant childComment, treeList)
	{
		//Again, move through the JSOn based on your element names.
		if(childComment.toMap()["data"].toMap()["body"].toString() != "")
		{
			QVariantMap data = childComment.toMap()["data"].toMap();
			// Now that we have our data, we should insert it into a DB.
			dbManager.AddData(data);
		}
	}
}

//DATABASE.hpp
SqlDataAccess* your_db;

DatabaseAccessManager();
void AddData(QVariantMap toAdd);

// DATABASE.cpp
DatabaseAccessManager::DatabaseAccessManager() 
{
	//Create the db if it doesn't exist.
	if(!QFile::exists(home.absoluteFilePath("YourData.db")))
	{
		QFile file(home.absoluteFilePath("YourData.db"));

		// Open the file that was created
		if (file.open(QIODevice::ReadWrite)) {
			// Create an SqlDataAccess object
			your_db = new SqlDataAccess(home.absoluteFilePath("YourData.db"));

			// Create a table called Employee in the database file
			your_db->execute("CREATE TABLE YourData( DATA_ID VARCHAR(6) PRIMARY KEY, MORE_DATA INT);");
		}
	}
	else
	{
		//Just set the DB if it exists.
		your_db = new SqlDataAccess(home.absoluteFilePath("YourData.db"));
	}
}

void DatabaseAccessManager::AddData(QVariantMap toAdd)
{
	QString addQuery;
	QTextStream(&addQuery) << "INSERT INTO YourData (DATA_ID, MORE_DATA) ";
	QTextStream(&addQuery) << "VALUES ('" << toAdd["DATA_ID"].toInt() << "', '" << toAdd["MORE_DATA"].toInt() << "');";
	your_db->execute(addQuery);
}

 

Here's what I would guage to be the difficult portion of your problem. Parsing the JSON from the QNetworkReply finished SLOT and adding it to the DB. You should be able to find code for setting up the DB as a DataModel somewhere else. If you have any questions let me know.

 

There are obviously many syntax errors, but this is the jist of it. I have architected this is such a way as to have your DatabaseAccessManager in its own class, but that isn't necessary.

----------------------
Check out my app, Alien Flow for reddit

And of course, like my post if you found it helpful or informative!
Please use plain text.
Developer
Bluenoser
Posts: 267
Registered: ‎04-04-2012
My Device: BlackBerry Z30/Z10/Z10LE/Q10/PlayBook

Re: Download JSON fle and save to SQL

Hi,

 

  For clariification.

 

void parseJson(QVariantList jsonTree)
{
	//Here's where you have to use your own JSON format. For instance
	// this JSON has a list of elements "children". ie.
	//
	// "children": [
	//	{
	//	} ]
	QVariantList treeList = tree.toMap()["children"].toList();

tree is really jsonTree?

 

void parseJson(QVariantList jsonTree)
{
	//Here's where you have to use your own JSON format. For instance
	// this JSON has a list of elements "children". ie.
	//
	// "children": [
	//	{
	//	} ]
	QVariantList treeList = jsonTree.toMap()["children"].toList();

 

Please use plain text.
Developer
simon_hain
Posts: 16,113
Registered: ‎07-29-2008
My Device: Z10 LE, Z30, Passport
My Carrier: O2 Germany

Re: Download JSON fle and save to SQL

i would suggest to use QtConcurrent::run for processing like this.
While networking per se is async, the response processing runs on the requesting thread and can lead to unresponsive UI.
(The UI renders fine, it just does not react)
----------------------------------------------------------
feel free to press the like button on the right side to thank the user that helped you.
please mark posts as solved if you found a solution.
@SimonHain on twitter
Please use plain text.
Developer
Bluenoser
Posts: 267
Registered: ‎04-04-2012
My Device: BlackBerry Z30/Z10/Z10LE/Q10/PlayBook

Re: Download JSON fle and save to SQL

I am trying to go through the parseJson procedure. If I have a JSON file with the following details.

 

[
    {
        "firstName" : "Mike",
        "lastName" : "Chepesky",
        "employeeNumber" : 01840192
    },
    {
        "firstName" : "Westlee",
        "lastName" : "Barichak",
        "employeeNumber" : 47901927
    },
    {
        "firstName" : "Ian",
        "lastName" : "Dundas",
        "employeeNumber" : 29472012
    }
]

 would I just leave the .topMap() empty as the array is the file itself?

 

Also when looking at where the data is being added, does the AddData procedure add each element (firstName, lastName, employeeNumber as individual SQL calls or as a single call with all three?

Please use plain text.
Developer
Bluenoser
Posts: 267
Registered: ‎04-04-2012
My Device: BlackBerry Z30/Z10/Z10LE/Q10/PlayBook

Re: Download JSON fle and save to SQL

Here is what I have so far. Does this look right?

 

void EmployeeListModel::requestFinished() {
	int requestError = m_reply->error();
	if (requestError == QNetworkReply::NoError) {
		log("Network Reply reports No Error");
		int available = m_reply->bytesAvailable();
		if (available > 0) {
			QString bytesAvailable = QString::number(available);
			log("Reply has " + bytesAvailable + " bytes");

			const QByteArray response(m_reply->readAll());

			bb::data&colon;:JsonDataAccess jda;
			QVariantList jsonList = jda.loadFromBuffer(response).toList();
			Q_FOREACH(QVariant jsonElement, jsonList) {

			}
		}
	}
	else {
		QString replyError = QString::number(requestError);
		log("Network Reply reports Error " + replyError);
	}
	m_reply->deleteLater();
}

 The Q_FOREACH loop will find the three list elements, but how do I get all the data pairs. I have been looking through examples and all I can find is extracting certain elements. I want to extract all elements.

 

so

 

[
    {
        "firstName" : "Mike",
        "lastName" : "Chepesky",
        "employeeNumber" : 01840192
    },
    {
        "firstName" : "Westlee",
        "lastName" : "Barichak",
        "employeeNumber" : 47901927
    },
    {
        "firstName" : "Ian",
        "lastName" : "Dundas",
        "employeeNumber" : 29472012
    }
]

 ends up as

 

"Mike", "Chepesky", 01840192
"Westlee", "Barichak", 47901927
"Ian", "Dundas", 29472012

 So I can then push it into the sql.



 

 

 

 

 

Please use plain text.