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
Contributor
Legoless
Posts: 15
Registered: ‎11-16-2012
My Device: None
My Carrier: Mobitel

SqlDataAccess insert problem

Hello,

 

I am trying for 2 hours to solve this problem. I am building a basic application, which uses SQLite database using SqlDataAccess wrapper. This is my database creation code, which gets executed only if db/database.db file does not exist.

 

// Create an SqlDataAccess object
SqlDataAccess sda("db/database.db");

sda.execute("CREATE TABLE Profiles (profile_id INTEGER PRIMARY KEY, profile_name TEXT, is_sel INTEGER, max_number INTEGER, number_to_generate INTEGER);");
sda.execute("INSERT INTO Profiles (profile_name, is_sel, max_number, number_to_generate) VALUES ('Test', 1, 39, 7);");

sda.disconnect();

 Otherwise I select the data in table and display it in QML. The select code:

SqlDataAccess sda("db/database.db");

QVariant list = sda.execute("SELECT * FROM Profiles;");

But I never get to this point. SELECT always returns empty QVariant, which is normal because data is never inserted. I tried inserting data immediately before SELECT, but also nothing. The INSERT clause is valid and does not display any error (using DataAccessError) and is executed successfully. The schema is created successfully, but the data is never inserted.

 

According to this example it should work: https://developer.blackberry.com/cascades/documentation/device_platform/data_access/working_with_sql...

 

What am I doing wrong?

Please use plain text.
Developer
rcebryk
Posts: 84
Registered: ‎12-01-2012
My Device: 9790
My Carrier: Orange

Re: SqlDataAccess insert problem

I think I had similar problem, try without a semicolon at the end:

just: "INSERT INTO table (col,col2,col3) VALUES (3,4,5)"

 

Another thing, before continuing it is good to check if previous operation was successfull:

if(sda.hasError())

qDebug()<<sda.error().errorMessage();

else

{//continue

 

}

Lublin BlackBerry Developer Group


---
If I helped you please mark my response as solution or "Like" it.
Please use plain text.
Contributor
Legoless
Posts: 15
Registered: ‎11-16-2012
My Device: None
My Carrier: Mobitel

Re: SqlDataAccess insert problem

Thank you for your  reply. I have tried all queries with and without semicolon, does not make any difference. And I also tested each query with sda.hasError() function, like you said, but there are no errors at all. Just the data is not saved.

 

Thanks.

Please use plain text.
Developer
rcebryk
Posts: 84
Registered: ‎12-01-2012
My Device: 9790
My Carrier: Orange

Re: SqlDataAccess insert problem

[ Edited ]

Are you sure that the result of SELECT query is correct? Did you cast it to QVariantList? Maybe check

qDebug()<<list.value<QVariantList>().size();

Lublin BlackBerry Developer Group


---
If I helped you please mark my response as solution or "Like" it.
Please use plain text.
Contributor
Legoless
Posts: 15
Registered: ‎11-16-2012
My Device: None
My Carrier: Mobitel

Re: SqlDataAccess insert problem

I have tested the SELECT query many times, even in online SQL validators. The select statement is not the problem, because the INSERT is not performed. I can also check this by filesize, because the database file does not change size after creating tables. I cannot imagine that 5 selects do not add a single byte to the database.

Please use plain text.
Developer
rcebryk
Posts: 84
Registered: ‎12-01-2012
My Device: 9790
My Carrier: Orange

Re: SqlDataAccess insert problem

But CREATE works correctly? Did you check if the table was correctly created? I used INSERT many times and it worked. Did you check with "SELECT * FROM sqlite_master;" ?
Lublin BlackBerry Developer Group


---
If I helped you please mark my response as solution or "Like" it.
Please use plain text.
Contributor
Legoless
Posts: 15
Registered: ‎11-16-2012
My Device: None
My Carrier: Mobitel

Re: SqlDataAccess insert problem

The create should work, because the filesize is correctly changed when query is executed. There is no error in query, which I checked with debug. I will try the select clause you gave me.
Please use plain text.
Regular Contributor
Q-T
Posts: 94
Registered: ‎12-02-2011
My Device: non-Blackberry user
My Carrier: none

Re: SqlDataAccess insert problem

For debugging purposes scp file over to your Linux (may work with windows) machine and download apt-get with ubuntu) sqlite3 This way you can verify that your tables are there, schema is as you think it is, and see what records are there.

 

sqlite3 database.db

 

.tables      // will show tables

.schema <tablename> // show you the schema

select * from <tablename>; // note semicolin fo rthis command only

 

 

Please use plain text.