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

[caduel]

see
http://www.qtcentre.org/forum/f-general-programming-9/t-bulk-insert-into-sqlite-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!;)