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());
 
		}
 
  	}
}

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());
			
		}
	  	
  	}
}

To copy to clipboard, switch view to plain text mode 

[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");

QSqlDatabase db2 = QSqlDatabase::addDatabase("QSQLITE", "something");

To copy to clipboard, switch view to plain text mode 

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 );

QSqlQuery insert( db2 );

To copy to clipboard, switch view to plain text mode 

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 );
...

insert.prepare("insert into words_tbl (language1, language2, plural, description) values ( :lang1, :lang2, :plural, :desc )" );
insert.bindValue( ":lang1", language1 );
...

To copy to clipboard, switch view to plain text mode 

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