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

Re: Sql Error

[ Edited ]

Nopes. Just one. I guess I didn't understand the implications of the add Database.

 

QSql just seems more straight forward from what I read in the forum no? As I am not really familiar with c++, i just wanted to choose the easiest way to do things, so I just picked one and followed the tutorial from there.

 

Is this additional database the one causing the problem?

 

I am guessing I am trying to follow too many different ways to do this thing at the same time so I am just trying to pick one path and just follow it down. 

 

Is my understanding of this whole structure correct:

 

1) copy database to data folder --> I am hoping up to this point my code is working and I've managed to copy it?

  •   the database is now at data/data.db  (correct?)

 

2) access the database

  • should I use SqlDataAccess at this point? or should I use QsqlQuery? Are they the equivalent of each other?
  • where shoudl I be declaring this? inside an individual function or at the top in something like iniDatabase?

 

3) open the database?

  • Is this the same as 'Accessing' the database?

4) execute

  • do I have to open/access the database every single time I want to execute a query? 

5) get back result

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

Re: Sql Error

In my opinion after nearly 25 years of SQL development in a corporate/government environment, SqlDataAccess is the easiest SQLite method available on BB10. It neatly wraps up all the DataSource and SqlConnection stuff so you don't have to worry about it yourself. Unless you have very sophisticated needs that SqlDataAccess islolates from you, I would always recommend using SqlDataAccess if you can.

 

In answer to your numbered questions...

 

  1. Yes, and no. You are correctly copying the database to your app's "data" path, but then you are not connecting to it properly. More details below...
  2. As I stated above, I would strongly suggest you use SqlDataAccess.
  3. I would say that "open" and "access" are not equivalent. Technically, you normally only open the database once, but you access it as many times as you need to do queries and updates.
  4. Although I have seen even experienced developers who write online tutorials do it that way, in fact it is not advisable to open and close the database repeatedly, especially if there is a chance more that one thread could be accessing the database at the same time. Buried somewhere in the BlackBerry SQL documentation there is a very strong recommendation that you open a database only once, at application startup, and then whenever you need to query or update the tables you should use this single instance.

Let's say you access a database in one of your slots, which are asynchronous by nature. This means that more than one slot, running concurrently, could try to update or query the database at the same time. If you let each slot keep its own connection to the database, table and row locking is unlikely to work properly. Also, I have seen people doing it the "wrong" way report that the database engine throws lots of error messages about trying to connect to an already open database, even though they thought they closed it properly the last time they accessed it. Can you do it? Sometimes, but much better to use a single connection which can properly manage locking and won't complain every time your app tries to reconnect.

 

As for question one above, the problem is that while you are correctly copying the skeleton database to the app data directory, you are then not using that location properly when opening the database. I suggest you use the debugger to take a look at what newFileName actually holds, and you should understand why you can't open "data/" + newFileName. Drop the "data/" + part and it should work fine.

 

Forgive me if I'm wrong, but based on the SQL and C++ code you have posted it seems to me that you are unfamiliar with SQL, C++, and Cascades, and yet are trying to use all three together without understanding how any of them work. I have a feeling your SQL table layouts are not likely to be optimal either. Do you know when and how to add indexes or foreign keys to provide fast lookups and data integrity? On top of that, when things aren't working you tend to want to make your code more complicated before you understand why your simpler code doesn't work. Perhaps you should build something even simpler at first to learn C++ and Cascades before throwing SQL and data access into the mix.

 

That said, the code I suggested several posts ago that uses SqlDataAccess should work with little or no modification, and is probably the simplest solution to what I understand you are trying to do. If it doesn't work for you, just let us know what is happening and one of us will supply the necessary tweaks.


unbreakable wrote:

Nopes. Just one. I guess I didn't understand the implications of the add Database.

 

QSql just seems more straight forward from what I read in the forum no? As I am not really familiar with c++, i just wanted to choose the easiest way to do things, so I just picked one and followed the tutorial from there.

 

Is this additional database the one causing the problem?

 

I am guessing I am trying to follow too many different ways to do this thing at the same time so I am just trying to pick one path and just follow it down. 

 

Is my understanding of this whole structure correct:

 

1) copy database to data folder --> I am hoping up to this point my code is working and I've managed to copy it?

  •   the database is now at data/data.db  (correct?)

 

2) access the database

  • should I use SqlDataAccess at this point? or should I use QsqlQuery? Are they the equivalent of each other?
  • where shoudl I be declaring this? inside an individual function or at the top in something like iniDatabase?

 

3) open the database?

  • Is this the same as 'Accessing' the database?

4) execute

  • do I have to open/access the database every single time I want to execute a query? 

5) get back result






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

Thank you for your thorough reply greenmr. Apologies if my questions do sound insanely noob sometimes ... I am trying to do this as a kind of hobby, the last computer lesson I took must have been in, secondary school or something. Am in a totally different, if not altogether opposing, industry.

 

It was easier for me to learn sqlite and sql statements online etc. At the very least I can now construct the query statements with just some reference. Cascades - the qml part I am more or less alright with too. I have been 'practising' for a few months now and I think, I am slowly able to manage the other aspects of cascades. 

 

But it was harder to find reading materials on SqlDatabase especially in the context of Cascades. The tutorials are ambiguous (and thus all my confusion). It would be really helpful if you could point me to some site which can explain them clearly. The BB site's ones are too vague.

 

In which case, I will follow your recommendation to use SqlDataAccess. Thanks!

 

So I guess I should ensure my code works like:

- copy database

- open database

- access when i need to

- access when i need to

- close when I end my programme

 

Is this correct?

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

Re: Sql Error

Yes, that about sums it up. I would just change the list a little like this...

 

  • Copy database IF IT ISN'T ALREADY THERE!
  • Open/Connect to database.
  • Access database.
  • Access database.
  • Access database.
  • Close database when app shuts down by deleting the SqlDataAccess.

What I am saying in that last entry is that the database connection is automatically closed just by deleting the SqlDataAccess. The easiest way to do this is to simply set your main application UI class as the parent of the SQlDataAccess. This way it will be deleted automatically when your app shuts down.

 


unbreakable wrote:

Thank you for your thorough reply greenmr. Apologies if my questions do sound insanely noob sometimes ... I am trying to do this as a kind of hobby, the last computer lesson I took must have been in, secondary school or something. Am in a totally different, if not altogether opposing, industry.

 

It was easier for me to learn sqlite and sql statements online etc. At the very least I can now construct the query statements with just some reference. Cascades - the qml part I am more or less alright with too. I have been 'practising' for a few months now and I think, I am slowly able to manage the other aspects of cascades. 

 

But it was harder to find reading materials on SqlDatabase especially in the context of Cascades. The tutorials are ambiguous (and thus all my confusion). It would be really helpful if you could point me to some site which can explain them clearly. The BB site's ones are too vague.

 

In which case, I will follow your recommendation to use SqlDataAccess. Thanks!

 

So I guess I should ensure my code works like:

- copy database

- open database

- access when i need to

- access when i need to

- close when I end my programme

 

Is this correct?






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

Thank you for your reply.

 

I understand what you mean about keeping the database open. But trying to execute my code always comes up with the error:

 

QSqlDatabasePrivate::addDatabase: duplicate connection name...

 

I pass my queries through:

 

void dbHelper::executeQuery (const QString query){
	SqlDataAccess sda (mdbFilewithPath);
	sda.execute(query);
	if (sda.hasError()){
		DataAccessError err = sda.error();
		qWarning() << "SQL Error: " << err.errorType() <<err.errorMessage();
	}

}

 

 

Should I add another database connection or something like the way they did in the Quotes app? 

 

I would assume I need a kind of simultaneous access to the database? For displaying the data and also for updating/inserting. Can I do both in SqlDatabase?

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

Re: Sql Error

Ok, you still don't quite understand me I guess. In your executeQuery function the first thing you do each time is create a new SqlDataAccess and connect it to the database. You do NOT want to do this, and this is why you are getting that error message. You need to create the SqlDataAccess in the dbHelper class constructor, not in the executeQuery function. What you are doing is creating multiple SqlDataAccess objects that each make their own connection and they are not disconnecting properly before you create the next one.

 

Do you remember I told you in an earlier post that I had seen even supposedly experienced developers create tutorials where a new connection was created for each query? Do you remember I said this was wrong and that it generates "duplicate connection" warnings after every query? Well this is exactly what you are doing in your code.

 

Do this instead so you only get a single SqlDataAccess you can use for as many queries as you need:

 

dbHelper::dbHelper() {
    this->sda = new SqlDataAccess();
}

dbHelper::~dbHelper() {
    delete this->sda;
}

void dbHelper::executeQuery (const QString query) {
    this->sda->execute(query);
    if (this->sda->hasError()){
	DataAccessError err = sda.error();
	qWarning() << "SQL Error: " << err.errorType() <<err.errorMessage();
    }
}

 Do you understand how this code works?


unbreakable wrote:

Thank you for your reply.

 

I understand what you mean about keeping the database open. But trying to execute my code always comes up with the error:

 

QSqlDatabasePrivate::addDatabase: duplicate connection name...

 

I pass my queries through:

 

void dbHelper::executeQuery (const QString query){
	SqlDataAccess sda (mdbFilewithPath);
	sda.execute(query);
	if (sda.hasError()){
		DataAccessError err = sda.error();
		qWarning() << "SQL Error: " << err.errorType() <<err.errorMessage();
	}

}

 

 

Should I add another database connection or something like the way they did in the Quotes app? 

 

I would assume I need a kind of simultaneous access to the database? For displaying the data and also for updating/inserting. Can I do both in SqlDatabase?






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

[ Edited ]

Thank you for your patience greenmr! 

 

I have did as you suggested. Moving the SqlDataAccess up to the main class, added the deletion etc.

 

However, my app now crashes in the simulator with no warning/error message in the console the moment I try to execute the function. Also, this->sda = new SqlDataAccess(); have a warning: 

Multiple markers at this line
- candidates are:
- no matching function for call to 
'bb::data&colon;:SqlDataAccess::SqlDataAccess()'

 


In my header file I have declared SqlDataAccess* sda; is this correct?

 

Sorry for all these noob questions! 

 

Also, does the fact that I already have a DataAccess in my QML populating my listview with the data affect any of the connections here?