duplicate recordset

[sepehr]

there is some data on my database which is updated rarely like name of cities so I want to download them into a local database on client computer rendering avoidance of overhead on my database server,now what is the best way to retrieve a mysql table records and paste those records to a sqlite database table?
I have done the following but it's VERY slow,takes 5 seconds to just copy 30 records

QSqlDatabase citiesDB = QSqlDatabase::addDatabase("QMYSQL","CitiesConnection");
     QSqlDatabase localDB = QSqlDatabase::addDatabase("QSQLITE","localDB");        
 
localDB.setDatabaseName(singletonConfig::Instance()->configHash().value("kavoshFolder")+"/localCitiesDB");
     localDB.open();
     citiesDB.setHostName("192.168.1.2");
     citiesDB.setDatabaseName("Kavosh8");
     citiesDB.setUserName("root");
     citiesDB.setPassword("secret"); 
	 citiesDB.open();		
	 QSqlQuery mainDbQuery("select * from province",citiesDB);
	 QSqlQuery localDbProQuery("CREATE TABLE `province` (province_id int(11) NOT NULL\
	                          ,province varchar(100)\
	                         ,PRIMARY KEY (province_id))",localDB);	 
	 localDbProQuery.exec();
	 mainDbQuery.exec();	 
	 mainDbQuery.first();
	 QVariantList provinceID,province;	 
 
	   do
	    {	      
	      provinceID<<mainDbQuery.value(0);      
	      province<<mainDbQuery.value(1).toString();	     	      	      	     	      
 
	    }
	   while(mainDbQuery.next() );	 
	 localDbProQuery.prepare("insert into province values(?,?)");
	 localDbProQuery.addBindValue(provinceID);  
	 localDbProQuery.addBindValue(province);  
	 qDebug()<<localDbProQuery.execBatch();
 
	 provinceID.clear();
	 QVariantList cityID,city,type;
	 mainDbQuery.prepare("select * from city");
	 mainDbQuery.exec();
	 mainDbQuery.first();
	 int i=0;
	 do
	    {	      
	      qDebug()<<mainDbQuery.value(0).toInt();
	      cityID<<mainDbQuery.value(0);
	      provinceID<<mainDbQuery.value(1);      
	      city<<mainDbQuery.value(2).toString();	     	      	      	     	      
	      type<<mainDbQuery.value(3);	         
	    }
	   while(mainDbQuery.next() );	 
	   QSqlQuery localDbCityQuery("CREATE TABLE `city` ( \
                                 `city_ID` int(11) NOT NULL, \
                                 `province_ID` int(11) NOT NULL,\
   				   			     `city` varchar(100) NOT NULL,\
  							     `type` varchar(3) DEFAULT '0',\
  							     PRIMARY KEY (`city_ID`))",localDB);
 
	 localDbCityQuery.exec();
	 localDbCityQuery.prepare("insert into city values(?,?,?,?)");	 
	 localDbCityQuery.addBindValue(cityID);  
	 localDbCityQuery.addBindValue(provinceID);  
	 localDbCityQuery.addBindValue(city);  
	 localDbCityQuery.addBindValue(type);  
	 localDbCityQuery.execBatch();

QSqlDatabase citiesDB = QSqlDatabase::addDatabase("QMYSQL","CitiesConnection");
     QSqlDatabase localDB = QSqlDatabase::addDatabase("QSQLITE","localDB");        

localDB.setDatabaseName(singletonConfig::Instance()->configHash().value("kavoshFolder")+"/localCitiesDB");
     localDB.open();
     citiesDB.setHostName("192.168.1.2");
     citiesDB.setDatabaseName("Kavosh8");
     citiesDB.setUserName("root");
     citiesDB.setPassword("secret"); 
	 citiesDB.open();		
	 QSqlQuery mainDbQuery("select * from province",citiesDB);
	 QSqlQuery localDbProQuery("CREATE TABLE `province` (province_id int(11) NOT NULL\
	                          ,province varchar(100)\
	                         ,PRIMARY KEY (province_id))",localDB);	 
	 localDbProQuery.exec();
	 mainDbQuery.exec();	 
	 mainDbQuery.first();
	 QVariantList provinceID,province;	 
	   
	   do
	    {	      
	      provinceID<<mainDbQuery.value(0);      
	      province<<mainDbQuery.value(1).toString();	     	      	      	     	      
	 
	    }
	   while(mainDbQuery.next() );	 
	 localDbProQuery.prepare("insert into province values(?,?)");
	 localDbProQuery.addBindValue(provinceID);  
	 localDbProQuery.addBindValue(province);  
	 qDebug()<<localDbProQuery.execBatch();
	 
	 provinceID.clear();
	 QVariantList cityID,city,type;
	 mainDbQuery.prepare("select * from city");
	 mainDbQuery.exec();
	 mainDbQuery.first();
	 int i=0;
	 do
	    {	      
	      qDebug()<<mainDbQuery.value(0).toInt();
	      cityID<<mainDbQuery.value(0);
	      provinceID<<mainDbQuery.value(1);      
	      city<<mainDbQuery.value(2).toString();	     	      	      	     	      
	      type<<mainDbQuery.value(3);	         
	    }
	   while(mainDbQuery.next() );	 
	   QSqlQuery localDbCityQuery("CREATE TABLE `city` ( \
                                 `city_ID` int(11) NOT NULL, \
                                 `province_ID` int(11) NOT NULL,\
   				   			     `city` varchar(100) NOT NULL,\
  							     `type` varchar(3) DEFAULT '0',\
  							     PRIMARY KEY (`city_ID`))",localDB);
  							     	 
	 localDbCityQuery.exec();
	 localDbCityQuery.prepare("insert into city values(?,?,?,?)");	 
	 localDbCityQuery.addBindValue(cityID);  
	 localDbCityQuery.addBindValue(provinceID);  
	 localDbCityQuery.addBindValue(city);  
	 localDbCityQuery.addBindValue(type);  
	 localDbCityQuery.execBatch();

To copy to clipboard, switch view to plain text mode 

[caduel]

see
http://www.qtcentre.org/forum/f-gene...lite-4180.html

i.e.: use a transaction; read up on bulk inserts; if nothing helps: write a csv file and import that

HTH

[sepehr]

I wrapped up the insert statement in
localDB.transaction ();
//batch insert statement
localDB.commit () ;
it works just fine now!