Import PostgreSQL Values to SQLITE

[raphaelf]

Hi everybody,

I am trying to make a function that import data from a table from PostgreSQL and insert the data on a sqlite3 database.

But i dont know if my idea will work, can somebody help me:


bool MainWindow::connectToDB()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
db.setHostName("localhost");
db.setDatabaseName("lernit");
db.setUserName("admin");
db.setPassword("admin");

if(!db.open())
{
QMessageBox::information(this,"LenrIT",db.lastError().text());
}
else
{
QSqlQuery select("SELECT language1, language2, plural, description FROM words_tbl");
while(select.next())
{
QString language1 = select.value(0).toString();
QString language2 = select.value(1).toString();
QString plural = select.value(2).toString();
QString description = select.value(3).toString();

QSqlDatabase db2 = QSqlDatabase::addDatabase("QSQLITE");
db2.setDatabaseName("LernIT.db");
if(!db2.open())
QMessageBox::information(this,"LenrIT",db.lastError().text());

QSqlQuery insert;
insert.prepare("insert into words_tbl (language1, language2, plural, description) values ('"
+ language1 + "', '" + language2 + "', '" + plural + "', '" + description + "')");
if( ! insert.exec() )
QMessageBox::information(this,"LernIT",insert.lastError().text());


QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
db.setHostName("localhost");
db.setDatabaseName("lernit");
db.setUserName("admin");
db.setPassword("admin");
if(!db.open())
QMessageBox::information(this,"LenrIT",db.lastError().text());

}

}
}

[zlatko]

You dont know if it will work ? Run it ;)

Why you create second handler to posgre database ?

[jacek]

QSqlDatabase db2 = QSqlDatabase::addDatabase("QSQLITE");
It should be:
QSqlDatabase db2 = QSqlDatabase::addDatabase("QSQLITE", "something");
This way you will create a second connection, without closing the first one, so you won't have to connect to the database every time you want to insert something.


QSqlQuery insert;
This should be:
QSqlQuery insert( db2 );
because you want to use that other connection for this statement.


insert.prepare("insert into words_tbl (language1, language2, plural, description) values ('"
+ language1 + "', '" + language2 + "', '" + plural + "', '" + description + "')");
This is dangerous, better use bindValue():

insert.prepare("insert into words_tbl (language1, language2, plural, description) values ( :lang1, :lang2, :plural, :desc )" );
insert.bindValue( ":lang1", language1 );
...
This way you can also optimise your code a bit by invoking insert.prepare() only once.

Your code should look like this:
open connection to PostgreSQL
open connection to SQLite
prepare the insert statement
prepare the select statement
execute the select statement
for each selected row:
bind values to the insert statement
execute the insert statement