May 17, 2011

wenks21 wenks21
Lab Rat
17 posts

I can’t Insert a new data to SQLITE

Page  
1

I tried to insert new data to the database. here’s my code:

  1. #include "mainwindow.h"
  2. #include <QtSql/QSqlDatabase>
  3. #include <QtDebug>
  4. #include <QtGui/QApplication>
  5. #include <QtSql>
  6.  
  7. int main(int argc, char *argv[])
  8. {
  9.     QApplication app(argc, argv);
  10.  
  11.     //MainWindow mainWindow;
  12.     //mainWindow.setOrientation(MainWindow::ScreenOrientationAuto);
  13.     //mainWindow.showExpanded();
  14.  
  15.     QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
  16.     db.setDatabaseName("C:\sqlite3\sample1.db");
  17.  
  18.     if(!db.open())
  19.     {
  20.         qFatal("Failed to connect!");
  21.     }
  22.  
  23.     else
  24.  
  25.     qDebug("Connected.");
  26.  
  27.     QSqlQuery qry;
  28.  
  29.     qry.prepare("insert into sample1.db (firstname, lastname) values ('John','Dean')");
  30.     if (!qry.exec())
  31.  
  32.         qDebug() << qry.lastError();
  33.  
  34.     else
  35.       qDebug("Inserted");
  36.  
  37.  
  38.     db.close();
  39.     return 0;
  40.  
  41.     return app.exec();
  42.  
  43. }

It has no errors but when I look into my sqlite using SELECT statement to see if it works, nothings happened the data that I want to add is not there.

thanks in advance!

20 replies

May 17, 2011

leon.anavi leon.anavi
Area 51 Engineer
1216 posts

Hi,

You should create table in the database and then to insert values into the table. Test your queries with SQlite command line tool such as sqlite3.exe before adding them to the source code.

Cheers,
Leon

 Signature 

http://anavi.org/

May 17, 2011

Andre Andre
Robot Herder
6393 posts

I think your database name is invalid. Remember, a backslash is used for escaping in C++ strings… It is good practice to only use backslashes for the display of paths on platforms where a backslash is the path separator (you do that translation using QDir::toNativeSeparators). In your own code, use forward slashes only.

May 17, 2011

leon.anavi leon.anavi
Area 51 Engineer
1216 posts

Hi again,

I just want to post several useful links which will solve the issue:
CREATE TABLE [sqlite.org]
Examples:
Creating an SQLite database in Qt [wiki.forum.nokia.com]
Creating a database table in Qt [wiki.forum.nokia.com]
Inserting a row into a database in Qt [wiki.forum.nokia.com]

Follow the examples in the order I have posted them, merge them into a single application and you will solve the issues.

 Signature 

http://anavi.org/

May 17, 2011

wenks21 wenks21
Lab Rat
17 posts

thanks to both of you! now I modified the code. but still nothing happened.

here’s the code:

  1. #include "mainwindow.h"
  2. #include <QtSql/QSqlDatabase>
  3. #include <QtDebug>
  4. #include <QtGui/QApplication>
  5. #include <QtSql>
  6.  
  7. int main(int argc, char *argv[])
  8. {
  9.     QApplication app(argc, argv);
  10.  
  11.     //MainWindow mainWindow;
  12.     //mainWindow.setOrientation(MainWindow::ScreenOrientationAuto);
  13.     //mainWindow.showExpanded();
  14.  
  15.     QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
  16.     db.setDatabaseName("C:/sqlite3/sample1.db");
  17.  
  18.     if(!db.open())
  19.     {
  20.         qFatal("Failed to connect!");
  21.     }
  22.  
  23.     else
  24.  
  25.     qDebug("Connected.");
  26.  
  27.     QSqlQuery qry;
  28.  
  29.     qry.prepare( "CREATE TABLE IF NOT EXISTS names (id INTEGER UNIQUE PRIMARY KEY, firstname VARCHAR(30), lastname VARCHAR(30))" );
  30.       if( !qry.exec() )
  31.         qDebug() << qry.lastError();
  32.       else
  33.         qDebug() << "Table created!";
  34.  
  35.     qry.prepare("insert into names (id, firstname, lastname) values (1, 'John','Dean')");
  36.     if (!qry.exec())
  37.  
  38.         qDebug() << qry.lastError();
  39.  
  40.     else
  41.       qDebug("Inserted");
  42.  
  43.  
  44.     db.close();
  45.     return 0;
  46.  
  47.     return app.exec();
  48.  
  49. }

is there something in the code that i have to replace?

May 17, 2011

leon.anavi leon.anavi
Area 51 Engineer
1216 posts
wenks21 wrote:
is there something in the code that i have to replace?

Have you tested your SQL statements only using the command line tool? Are they OK?

Best regards,
Leon

 Signature 

http://anavi.org/

May 17, 2011

wenks21 wenks21
Lab Rat
17 posts

when i tried to test it in the command prompt in the directory of the sqlite to see if it works it said that “Error: no such table names”.

May 17, 2011

Andre Andre
Robot Herder
6393 posts

How far did you get? What is the output your program generated?

May 17, 2011

wenks21 wenks21
Lab Rat
17 posts

It only connects to the database.

May 17, 2011

Andre Andre
Robot Herder
6393 posts

wenks21 wrote:
It only connects to the database.

So… after the “Connected.” output, you get nothing else? Your code suggests that you should either get an error or an OK for both the queries you try to execute. If you get an error, please show it.

Edit:
I think you are messing up the constructors of your QSqlQuery object. Try to use the constructor that takes a QSqlDatabase argument.

May 17, 2011

wenks21 wenks21
Lab Rat
17 posts

yes exactly. after the ‘Connected’ output nothing else happened.

how to do the constructor that you are saying? any samples?

thanks a lot!

May 17, 2011

Andre Andre
Robot Herder
6393 posts

You might try the QSqlQuery documentation to learn about the different constructors for that class…

If you get no other output, you might try to set a breakpoint at the place where you last get output, and follow the program execution statement by statement by running in debug mode (F5 in Qt Creator).

May 17, 2011

wenks21 wenks21
Lab Rat
17 posts

ok thanks for the info!

June 28, 2011

aureliocano aureliocano
Lab Rat
6 posts

Hi wenks21,

Did you resolve your problem?
I am in the same point… :(

June 29, 2011

solareclectic solareclectic
Lab Rat
9 posts

You need to open the actual file before opening the database within, like so….

  1.     QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
  2.     QFile newdbfile(QDir::toNativeSeparators(QDir::homePath()+"/test.sqlt"));
  3.     db.setDatabaseName(newdbfile.fileName());
  4.  
  5.     if (newdbfile.open(QIODevice::ReadWrite))
  6.     {
  7.        if(!db.open())
  8.        {
  9.            qFatal("Failed to connect!");
  10.        }
  11.        else
  12.        qDebug("Connected.");
  13.     }

notice, that I wrapped your db.open() with a newdbfile.open().
also, this little dance I did sure makes it easy to move around ‘tween machines and platforms

  1.     QFile newdbfile(QDir::toNativeSeparators(QDir::homePath()+"/test.sqlt"));
  2.     db.setDatabaseName(newdbfile.fileName());

I built and ran this code inserted into your code. It works.

June 29, 2011

Lukas Geyer Lukas Geyer
Lab Rat
2074 posts
solareclectic wrote:
You need to open the actual file before opening the database within, like so….

You do not need to open the file. If the code above does not work it is most likely that you

  • have no permission to write to the C:/sqlite3 directory
  • have no directory C:/sqlite3, which prevents the QSQLITE driver from creating the file (this might be the reason why opening – and thus creating – the file solves your problem)

Page  
1

  ‹‹ Set display precision different from internal precision in QDoubleSpinBox      [Qt Bug reported] App crashes on QDesktopServices::openUrl(QUrl("file:///C://folder)); if folder is empty ››

You must log in to post a reply. Not a member yet? Register here!