SQLite make problems

[raphaelf]

Hello everybody,
QT 4.1.3
SQlite 3.0
OS: WINXP

I am able to connect to my sqlite DB and i am also able to show all values on my listView..
If i try to insert a new row i get thie ERROR: "database is locked unable to fetch row"..
I think this database has a problem, but i need this database, there are all data i need
I am also not able to change values on my QTableView
I can doble click a row on my listview, i can write something there but the values desaper from the listiew
if i delete everything from this database and try to insert a new row or change a value directly on the listview it workssss!!!!
Have somebody a idea what is wrong?

Thanks forwarding

[patrik08]

Hello everybody,
QT 4.1.3
if i delete everything from this database and try to insert a new row or change a value directly on the listview it workssss!!!!
Have somebody a idea what is wrong?

Thanks forwarding

If you show the model source piece from this table people here can help... to solve problem...

If your DB can open on http://sqlitebrowser.sourceforge.net/ or http://sourceforge.net/projects/qtexcel-xslt/ sqlite2 or 3 the db is not broken.... readonly?

[wysota]

Maybe you locked the database (or row) from within your application somewhere not knowing about it? Are you inside of a transaction? Did you run any query that may have rendered the database read-only? Do you have write permissions for the file containing the database?

[raphaelf]

hi everibody,

I can say that i have permissions and i tryed follow:

My Database has 257 rows..
If i delete some rows directly with sqlite3.exe and start my app again, so i am able to insert some rows, but after inserting some rows i get this ERROR again.

BUT I AM ABLE TO INSERT NEW ROWS direct with sqlite3.exe, but why not via QT

Have somebody a idea??

[wysota]

Do you have free space on the disk where the database is located?

[raphaelf]

Hi wysota,

Yes a have some GBs free space..

Have something to do with this ?:
http://www.sqlite.org/lockingv3.html

I am searching a solution since 4 weeks

[wysota]

Could you show us your code used to manipulate the database?

[raphaelf]

Of course:

Have you a idea?

// Connect to the SQLITE Database
bool MainWindow::connectToDB()
{
	//Um PostgreSQL zu vervenden muss der QPSQL Treiber QPSQL verwendet werden.
	QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("LernIT.db");
}
 
// QSQLTableModel to show and manipulate data on the sqlite database
void MainWindow::showWords()
{
	ui.description_le->setDisabled(true);
	setWhite();
	ui.l1_le->clear();
	ui.l2_le->clear();
	ui.plural_le->clear();
	ui.description_le->clear();
 
	QSqlTableModel *model = new QSqlTableModel;
        model->setTable("words_tbl");
        model->setEditStrategy(QSqlTableModel::OnFieldChange);
        model->select();
      	//model->removeColumn(0); // don't show the ID
       	model->setHeaderData(1, Qt::Horizontal, tr("Deutsch"));
       	model->setHeaderData(2, Qt::Horizontal, tr("Portuguiesisch"));
 
        ui.tableView->setModel(model);
       	ui.tableView->setColumnHidden(0, true);
       	ui.tableView->sortByColumn(1);
        ui.tableView->show();  
}
 
 
// I am using this Script to insert new rows to the database:
QSqlQuery insert;
		insert.prepare("insert into words_tbl (language1, language2, plural, description) values ('" 
				+ ui.l1_le->text() + "', '" + ui.l2_le->text() + "','" + ui.plural_le->text() + "', '" + ui.description_le->text() + "')");
		if( ! insert.exec() ) {
    	QMessageBox::information(this,"LernIT",insert.lastError().text());
    	return;}
 
//To select some data i am using this script:
QString random;
	QSqlQuery count("SELECT words_id FROM words_tbl WHERE rowid>=random() % (SELECT max(words_id)+1 FROM words_tbl) LIMIT 1;");
 
	while(count.next())
	{
		random = count.value(0).toString();
	}

// Connect to the SQLITE Database
bool MainWindow::connectToDB()
{
	//Um PostgreSQL zu vervenden muss der QPSQL Treiber QPSQL verwendet werden.
	QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("LernIT.db");
}

// QSQLTableModel to show and manipulate data on the sqlite database
void MainWindow::showWords()
{
	ui.description_le->setDisabled(true);
	setWhite();
	ui.l1_le->clear();
	ui.l2_le->clear();
	ui.plural_le->clear();
	ui.description_le->clear();
	
	QSqlTableModel *model = new QSqlTableModel;
        model->setTable("words_tbl");
        model->setEditStrategy(QSqlTableModel::OnFieldChange);
        model->select();
      	//model->removeColumn(0); // don't show the ID
       	model->setHeaderData(1, Qt::Horizontal, tr("Deutsch"));
       	model->setHeaderData(2, Qt::Horizontal, tr("Portuguiesisch"));

        ui.tableView->setModel(model);
       	ui.tableView->setColumnHidden(0, true);
       	ui.tableView->sortByColumn(1);
        ui.tableView->show();  
}


// I am using this Script to insert new rows to the database:
QSqlQuery insert;
		insert.prepare("insert into words_tbl (language1, language2, plural, description) values ('" 
				+ ui.l1_le->text() + "', '" + ui.l2_le->text() + "','" + ui.plural_le->text() + "', '" + ui.description_le->text() + "')");
		if( ! insert.exec() ) {
    	QMessageBox::information(this,"LernIT",insert.lastError().text());
    	return;}

//To select some data i am using this script:
QString random;
	QSqlQuery count("SELECT words_id FROM words_tbl WHERE rowid>=random() % (SELECT max(words_id)+1 FROM words_tbl) LIMIT 1;");
	
	while(count.next())
	{
		random = count.value(0).toString();
	}

To copy to clipboard, switch view to plain text mode 

[wysota]

What's the point of using "prepare" here if you don't make any bindings afterwards?

[jacek]

Read this: http://lists.trolltech.com/qt-intere...ad00099-0.html

[raphaelf]

Hi everybody,
I found out that if i dont call this part, i am able to insert how much rows with qt i want:

void MainWindow::showWords()
{
 
	ui.description_le->setDisabled(true);
	setWhite();
	ui.l1_le->clear();
	ui.l2_le->clear();
	ui.plural_le->clear();
	ui.description_le->clear();
 
	QSqlTableModel *model = new QSqlTableModel;
        model->setTable("words_tbl");
        model->setEditStrategy(QSqlTableModel::OnFieldChange);
        model->select();
      	//model->removeColumn(0); // don't show the ID
       	model->setHeaderData(1, Qt::Horizontal, tr("Deutsch"));
       	model->setHeaderData(2, Qt::Horizontal, tr("Portuguiesisch"));
 
        ui.tableView->setModel(model);
       	ui.tableView->setColumnHidden(0, true);
       	ui.tableView->sortByColumn(1);
        ui.tableView->show();  
 
}

void MainWindow::showWords()
{
	
	ui.description_le->setDisabled(true);
	setWhite();
	ui.l1_le->clear();
	ui.l2_le->clear();
	ui.plural_le->clear();
	ui.description_le->clear();
	
	QSqlTableModel *model = new QSqlTableModel;
        model->setTable("words_tbl");
        model->setEditStrategy(QSqlTableModel::OnFieldChange);
        model->select();
      	//model->removeColumn(0); // don't show the ID
       	model->setHeaderData(1, Qt::Horizontal, tr("Deutsch"));
       	model->setHeaderData(2, Qt::Horizontal, tr("Portuguiesisch"));

        ui.tableView->setModel(model);
       	ui.tableView->setColumnHidden(0, true);
       	ui.tableView->sortByColumn(1);
        ui.tableView->show();  
        
}

To copy to clipboard, switch view to plain text mode 

So the QSQLTableModel makes problem.. and i am not able to update values on it..

Have somebody now a idea?

[raphaelf]

Could be that QSQLTableModel is not working corect with sqlite3?
Because witch ms sql and postgre it works perfect

[wysota]

The problem is with SQLite, not with Qt.

[raphaelf]

hi wysota, ok i see..

So i will have to change to postgree again

Is there no solution for work with sqltablemodel and sqlite3?

[wysota]

Update to a newer version.

[raphaelf]

Hi wysota i have downloaded and tryed sqlite 3.3.17 (newest version)
and i am also not able to change my values on the qsqltablemodel

[jacek]

So the QSQLTableModel makes problem.. and i am not able to update values on it..

Have somebody now a idea?

Maybe this will help: http://trolltech.com/developer/task-...ntry&id=128671

[raphaelf]

Hi Jacek,

I have insert two new lines, is that correct, because i have same problem:

QSqlTableModel *model = new QSqlTableModel;
        model->setTable("words_tbl");
        model->setEditStrategy(QSqlTableModel::OnFieldChange);
        model->select();
       //Should this line solve the problem??
        while (model->canFetchMore()){
        model->fetchMore();}
      	//model->removeColumn(0); // don't show the ID
       	model->setHeaderData(1, Qt::Horizontal, tr("Deutsch"));
       	model->setHeaderData(2, Qt::Horizontal, tr("Portuguiesisch"));
 
        ui.tableView->setModel(model);
       	ui.tableView->setColumnHidden(0, true);
       	ui.tableView->sortByColumn(1);
        ui.tableView->show();

QSqlTableModel *model = new QSqlTableModel;
        model->setTable("words_tbl");
        model->setEditStrategy(QSqlTableModel::OnFieldChange);
        model->select();
       //Should this line solve the problem??
        while (model->canFetchMore()){
        model->fetchMore();}
      	//model->removeColumn(0); // don't show the ID
       	model->setHeaderData(1, Qt::Horizontal, tr("Deutsch"));
       	model->setHeaderData(2, Qt::Horizontal, tr("Portuguiesisch"));

        ui.tableView->setModel(model);
       	ui.tableView->setColumnHidden(0, true);
       	ui.tableView->sortByColumn(1);
        ui.tableView->show();

To copy to clipboard, switch view to plain text mode 

[jacek]

I have insert two new lines, is that correct, because i have same problem:

If you have the same problem as the one discussed on qt-interest, then it means you can't have more than one query object at once.

Try executing that loop before you create QSqlQuery and create only one instance of that class.

[raphaelf]

Hi ..

I am using before insert a new row like that

void MainWindow::insert()
{
	QSqlTableModel *model = new QSqlTableModel;
        model->setTable("words_tbl");
        while (model->canFetchMore()){
        model->fetchMore();}
        model->setEditStrategy(QSqlTableModel::OnFieldChange);
        model->select();
 
      	//model->removeColumn(0); // don't show the ID
       	model->setHeaderData(1, Qt::Horizontal, tr("Deutsch"));
       	model->setHeaderData(2, Qt::Horizontal, tr("Portuguiesisch"));
 
        ui.tableView->setModel(model);
       	ui.tableView->setColumnHidden(0, true);
       	ui.tableView->sortByColumn(1);
       	ui.tableView->show();
 
	if ((ui.l1_le->text() == "")||(ui.l2_le->text() == ""))
	{
	QMessageBox::information(this,"LernIT","Please fill field Deutsch and Portuguiesisch");
	return;}
 
 
	if (ui.switch_cb->currentIndex() == 0)
	{
		/*
		QSqlQuery insert;
		insert.prepare("insert into words_tbl (language1, language2, plural, description) values ( :lang1, :lang2, :plural, :desc );" );
		insert.bindValue( ":lang1", ui.l1_le->text() );
		insert.bindValue( ":lang2", ui.l2_le->text() );
		insert.bindValue( ":plural", ui.plural_le->text() );
		insert.bindValue( ":desc", ui.description_le->text() );
		if( ! insert.exec() ) {
    	QMessageBox::information(this,"LernIT",insert.lastError().text());
    	ui.l1_le->setText("");
    	ui.l2_le->setText("");
    	ui.plural_le->setText("");
    	ui.description_le->setText("");
    	ui.l1_le->setFocus();
    	return;}
    	*/
    	QSqlRecord rec = model->record();
    	rec.setValue("language1", ui.l1_le->text());    
    	rec.setValue("language2", ui.l2_le->text());
    	rec.setValue("plural", ui.plural_le->text());
    	rec.setValue("description", ui.description_le->text());
    	model->insertRecord(-1, rec);
.
.
.

void MainWindow::insert()
{
	QSqlTableModel *model = new QSqlTableModel;
        model->setTable("words_tbl");
        while (model->canFetchMore()){
        model->fetchMore();}
        model->setEditStrategy(QSqlTableModel::OnFieldChange);
        model->select();
        
      	//model->removeColumn(0); // don't show the ID
       	model->setHeaderData(1, Qt::Horizontal, tr("Deutsch"));
       	model->setHeaderData(2, Qt::Horizontal, tr("Portuguiesisch"));

        ui.tableView->setModel(model);
       	ui.tableView->setColumnHidden(0, true);
       	ui.tableView->sortByColumn(1);
       	ui.tableView->show();
	
	if ((ui.l1_le->text() == "")||(ui.l2_le->text() == ""))
	{
	QMessageBox::information(this,"LernIT","Please fill field Deutsch and Portuguiesisch");
	return;}
	

	if (ui.switch_cb->currentIndex() == 0)
	{
		/*
		QSqlQuery insert;
		insert.prepare("insert into words_tbl (language1, language2, plural, description) values ( :lang1, :lang2, :plural, :desc );" );
		insert.bindValue( ":lang1", ui.l1_le->text() );
		insert.bindValue( ":lang2", ui.l2_le->text() );
		insert.bindValue( ":plural", ui.plural_le->text() );
		insert.bindValue( ":desc", ui.description_le->text() );
		if( ! insert.exec() ) {
    	QMessageBox::information(this,"LernIT",insert.lastError().text());
    	ui.l1_le->setText("");
    	ui.l2_le->setText("");
    	ui.plural_le->setText("");
    	ui.description_le->setText("");
    	ui.l1_le->setFocus();
    	return;}
    	*/
    	QSqlRecord rec = model->record();
    	rec.setValue("language1", ui.l1_le->text());    
    	rec.setValue("language2", ui.l2_le->text());
    	rec.setValue("plural", ui.plural_le->text());
    	rec.setValue("description", ui.description_le->text());
    	model->insertRecord(-1, rec);
.
.
.

To copy to clipboard, switch view to plain text mode 

Should i post my .cpp file?

Please help me