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
Posts: 74
Registered: ‎12-17-2009
My Device: Z30
My Carrier: O2

Sql Error

I am trying to connect to a sqlite database to insert/update records. But I am getting back such an error! I am able to access the data itself via DataSource/ListView etc.

 

SqlDataAccess error:  "unable to open new database file "asset:///Rules.db"" . QSqlError::type= "QSqlError::ConnectionError" , QSqlError::number= -1 , databaseText= "unable to open database file" , driverText= "Error opening database" 

 

And my C++ code is:

#include "dbHelper.h"
#include <bb/data/SqlDataAccess>
#include <bb/data/DataAccessError>
#include <bb/system/SystemDialog>

#include <QtSql/QtSql>
#include <QDebug>

using namespace bb::data;
using namespace bb::system;

const QString DB_PATH = "asset:///Rules.db";

dbHelper::dbHelper() {
	initDatabase();
}

void dbHelper::initDatabase(){
	QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
	database.setDatabaseName(DB_PATH);
}

void dbHelper::bookmark(const QString &rule_id)
{
//	bool success = false;

	SqlDataAccess *sqlda = new SqlDataAccess(DB_PATH);
	QVariantMap rule;
	rule["id"] = rule_id;

	sqlda->execute("INSERT INTO user (ruleid, bookmark) VALUES (:id, 1)", rule);

//Disabled for now
//	if (!sqlda->hasError())
//			{success = true;}
//			return success;
}

 For now I am just using a dummy button to call on the class via a _sqlproperty.bookmark(ruleid) where _sqlproperty was declared in the main.cpp already. That part seems okay as the console log seems to suggest that it manages to get to the stage where it is trying to access the sqlite?

 

I am not sure what I am missing on, I have followed mostly the Database CRUD example, although I did not drop/create the tables as they did as it was not necessary.

 

Thanks in advance!

Developer
Posts: 1,158
Registered: ‎03-20-2013
My Device: Red LE Developer Z10
My Carrier: Fido

Re: Sql Error

There are a couple of issues with your approach.

 

  1. The argument to QSqlDatabase::setDatabaseName() is a  QString, not a QUrl. It is QUrl that resolves URIs like "asset:///".
  2. While I could show you how to solve issue #1, more importantly, while you can write pretty much anywhere in your apps path tree during development, when you export for release your asset path is read-only, so you will not be able to update the database at all if you leave it there.

You are going to have to move your database file to your data directory if you want to be able to make any changes to it at runtime. You can have QSqlDatabase build your database from scratch on-the-fly if it doesn't exist when you start your app, but you may prefer to have a skeleton database bundled with your app and copy it to the data path if it isn't already there, so I'll show you how to do that.

 

First, you need to copy your skeleton database from assets:/// to your data path:

 

// ---Check if your database file exists in the data directory
QFile dbFile( QDir::homePath() + "/Rules.db" );
if ( !dbFile.exists() ) {
	// ---No it doesn't, get the application root path
	QString appRoot( QDir::homePath() );
	appRoot.chop( 4 );

	// ---Copy initial version from assets folder
	QFile::copy( appRoot + "app/native/assets/Rules.db", QDir::homePath() + "/Rules.db" );
}

Now you can open the database with QSqlDatabase:

 

QSqlDatabase database = QSqlDatabase::addDatabase( "QSQLITE" );
database.setDatabaseName( QDir::homePath() + "/Rules.db" );

You cannot hardcode the path to your assets and data directories since you won't know them at runtime, part of the root pathname for your app is generated by the BB10 OS when your app is installed.

 


unbreakable wrote:

I am trying to connect to a sqlite database to insert/update records. But I am getting back such an error! I am able to access the data itself via DataSource/ListView etc.

 

SqlDataAccess error:  "unable to open new database file "asset:///Rules.db"" . QSqlError::type= "QSqlError::ConnectionError" , QSqlError::number= -1 , databaseText= "unable to open database file" , driverText= "Error opening database" 

 

And my C++ code is:

#include "dbHelper.h"
#include <bb/data/SqlDataAccess>
#include <bb/data/DataAccessError>
#include <bb/system/SystemDialog>

#include <QtSql/QtSql>
#include <QDebug>

using namespace bb::data;
using namespace bb::system;

const QString DB_PATH = "asset:///Rules.db";

dbHelper::dbHelper() {
	initDatabase();
}

void dbHelper::initDatabase(){
	QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
	database.setDatabaseName(DB_PATH);
}

void dbHelper::bookmark(const QString &rule_id)
{
//	bool success = false;

	SqlDataAccess *sqlda = new SqlDataAccess(DB_PATH);
	QVariantMap rule;
	rule["id"] = rule_id;

	sqlda->execute("INSERT INTO user (ruleid, bookmark) VALUES (:id, 1)", rule);

//Disabled for now
//	if (!sqlda->hasError())
//			{success = true;}
//			return success;
}

 For now I am just using a dummy button to call on the class via a _sqlproperty.bookmark(ruleid) where _sqlproperty was declared in the main.cpp already. That part seems okay as the console log seems to suggest that it manages to get to the stage where it is trying to access the sqlite?

 

I am not sure what I am missing on, I have followed mostly the Database CRUD example, although I did not drop/create the tables as they did as it was not necessary.

 

Thanks in advance!






Developer of Built for BlackBerry certified multiFEED RSS/Atom feed reader and aggregator.  multiFEED Icon

Play nice: Clicking Like Button on posts that helped you not only encourages others to continue sharing their experience, but also improves your own rating on this board. Also, don't forget to accept a post if it solves your problem or answers your question.
Developer
Posts: 74
Registered: ‎12-17-2009
My Device: Z30
My Carrier: O2

Re: Sql Error

Thanks so much! Going to try it now.

Just a quick question, if, in the future, I would like to update my sql, but there are still the 'user' settings that I would like to save, will this allow me to do so?

I just want to understand -does that mean that the sql file I keep inside the asset is just a skeleton that will be copied over to the data folder, and if there are any changes made to this copied sql file, it will be saved in that new copied database and not the 'original' one I would have loaded onto the assets folder?
Developer
Posts: 6,152
Registered: ‎07-05-2012
My Device: Playbook, Dev Alpha C, Z10 LE, Z30
My Carrier: Orange

Re: Sql Error

That's correct, basically do a check for th existance of the database file in the data folder each time and don't copy it across (from assets) if one already exists therefore it will be persistant across updates.


If you've been helped click on Like Button, if you've been saved buy the app. Smiley Happy

Developer of stokLocker, Sympatico and Super Sentences.
Developer
Posts: 74
Registered: ‎12-17-2009
My Device: Z30
My Carrier: O2

Re: Sql Error

Thanks!

I have tried doing that, but now I am not sure how to execute my query?

I have put the code above in my ::inidatabase();

And then I called it again in my ::function()

 

	QSqlDatabase database = QSqlDatabase::addDatabase( "QSQLITE" );
	database.setDatabaseName( QDir::homePath() + "/Rules.db" );

	QVariantMap rule;
	rule["id"] = rule_id;

	database->execute("INSERT INTO user (ruleid, favourite) VALUES (:id, 1)", rule);

 but in the last line it is marking it as an error: base operand of '->' has non-pointer type 'QSqlDatabase'

Developer
Posts: 6,152
Registered: ‎07-05-2012
My Device: Playbook, Dev Alpha C, Z10 LE, Z30
My Carrier: Orange

Re: Sql Error

Because you haven't defined it as as a pointer object use the '.' operand if you are defining this way.

 

Take a look here for the difference in defining on the heap or stack...

 

http://www.learncpp.com/cpp-tutorial/79-the-stack-and-the-heap/


If you've been helped click on Like Button, if you've been saved buy the app. Smiley Happy

Developer of stokLocker, Sympatico and Super Sentences.
Developer
Posts: 1,158
Registered: ‎03-20-2013
My Device: Red LE Developer Z10
My Carrier: Fido

Re: Sql Error

[ Edited ]

Yes. The one in your assets folder is forever unwritable and so will always remain just the skeleton that you packaged with the application. There is no way around this since BB10 does not allow an app to write to its own assets directory. Once you start updating the copy of the database, that is where all your updates will remain. It can be confusing since BB10 allows apps running with a debug token to violate this rule and and write pretty much anywhere in the application's directory tree, including "assets:///". but when you sign your app for release, BB10 will start enforcing path permissions and your app will break.

 

I learned this the hard way, but luckily before I made a new release of my app available for sale. I had come up with a nifty trick to map a symbolic link to one of two almost identical directories in my assets folder. At run time my app would set up the symbolic link so that I could then get different versions of various files by the same name, depending on where the link pointed. Very much like the Theme Support that was added with BB10.1. Everything worked fine during development, but when I exported for release the symbolic link stopped working since it is actually a hidden file that is stored where the link is accessed from, in this case, my assets folder. The app couldn't save the link due to the stronger permission restriction.

 

I have a strict policy that I strongly suggest ALL developers adhere to... once you have signed your app, but before you upload it to BB World for release, sideload the signed app onto your device and test all new features. This will catch anything that you might have missed due to the relaxed permission restrictions BB10 gives when an app is running against a debug token. You can also upload it to BB World and then do a sandbox download, but it can take up to 48 hours for your uploaded app to be available for sandboxed download, whereas sideloading your signed APK can be done immediately.

 

What this means regarding the first part of your question is that if you need to change the database structure in a later app release, you must do it against the COPY of the database using DDL statments. You obviously can't just copy your new skeleton database over the copy since that would wipe out any user settings that had been changed from the skeleton defaults.


unbreakable wrote:
Thanks so much! Going to try it now.

Just a quick question, if, in the future, I would like to update my sql, but there are still the 'user' settings that I would like to save, will this allow me to do so?

I just want to understand -does that mean that the sql file I keep inside the asset is just a skeleton that will be copied over to the data folder, and if there are any changes made to this copied sql file, it will be saved in that new copied database and not the 'original' one I would have loaded onto the assets folder?





Developer of Built for BlackBerry certified multiFEED RSS/Atom feed reader and aggregator.  multiFEED Icon

Play nice: Clicking Like Button on posts that helped you not only encourages others to continue sharing their experience, but also improves your own rating on this board. Also, don't forget to accept a post if it solves your problem or answers your question.
Highlighted
Developer
Posts: 1,158
Registered: ‎03-20-2013
My Device: Red LE Developer Z10
My Carrier: Fido

Re: Sql Error

Yep, what he said.

 

 

Usually Momentics will catch this error for you as you are typing, but if you change the variable declaration from a pointer to an instance after the fact you will only notice your mistake when you try to build. This can also happen if you copy and paste code from somewhere else since the IDE syntax checker only works when you are actually typing code in.

 


BBSJdev wrote:

Because you haven't defined it as as a pointer object use the '.' operand if you are defining this way.

 

Take a look here for the difference in defining on the heap or stack...

 

http://www.learncpp.com/cpp-tutorial/79-the-stack-and-the-heap/






Developer of Built for BlackBerry certified multiFEED RSS/Atom feed reader and aggregator.  multiFEED Icon

Play nice: Clicking Like Button on posts that helped you not only encourages others to continue sharing their experience, but also improves your own rating on this board. Also, don't forget to accept a post if it solves your problem or answers your question.
Developer
Posts: 74
Registered: ‎12-17-2009
My Device: Z30
My Carrier: O2

Re: Sql Error

Thanks guys! Will try to read up more on this matter! Starting to feel as if I am getting myself into a deeper end of the pool than I expected!

Developer
Posts: 1,158
Registered: ‎03-20-2013
My Device: Red LE Developer Z10
My Carrier: Fido

Re: Sql Error

No worries. Lots of experienced and helpful developers here more that willing to answer your questions.

 

 


unbreakable wrote:

Thanks guys! Will try to read up more on this matter! Starting to feel as if I am getting myself into a deeper end of the pool than I expected!






Developer of Built for BlackBerry certified multiFEED RSS/Atom feed reader and aggregator.  multiFEED Icon

Play nice: Clicking Like Button on posts that helped you not only encourages others to continue sharing their experience, but also improves your own rating on this board. Also, don't forget to accept a post if it solves your problem or answers your question.