QSQLITE driver issue

[b1]

G'Day,

I am trying to write a simple app using Sqlite. (normally use MySql). My problem is that it creates the file "nksalvo.db", says that it is open, but the "create" commands fail saying the driver is not loaded. The "table 1 creation" message return the driver name as QSQLITE so it does appear to be loaded.

I am using Qt 4.5.0 statically compiled, on windows ( at the moment - Linux next )

I am guessing the error is somewhere else, but where?

Any pointers would much appreciated...thanks B1.

int MainWindow::openDatabase()
{
	if (dbopen == true )
	{
		return 0;
	}
	else
	{
		// Create connection to database server
		nksalvoDB = QSqlDatabase::addDatabase( "QSQLITE" );
		nksalvoDB.setDatabaseName( cwdir+"/nksalvo.db" );
		if ( !nksalvoDB.open() )
		{	
			QMessageBox::critical( this, "NKSalvo Database Error",
			"Could not connect to the database!\n"
			"\n"
			"Reported error:\n"
			+nksalvoDB.lastError().driverText()+"\n"
			+nksalvoDB.lastError().databaseText()+"\n"
			+nksalvoDB.databaseName()+"\n");
			dbopen = false;
			return 1;
		}
		createtableqry.prepare( "CREATE TABLE IF NOT EXISTS serialdata (`port` varchar(10) default NULL,"
								"`speed` varchar(6) default NULL,`databits` varchar(4) default NULL,"
								"`parity` varchar(5) default NULL, `stopbits` varchar(4) default NULL"
								"`id` smallint(2) NOT NULL, PRIMARY KEY  (`id`)" );
		createtableqry.exec();
		if (!createtableqry.isActive() )
		{
			QMessageBox::critical(this, "Salvo Error", "Table 1 Creation Failed! \n"
				                  "\n"+createtableqry.lastError().text()+"\n"+nksalvoDB.driverName() );
			dbopen = false;
			return 1;
		}
		createtableqry.prepare( "CREATE TABLE IF NOT EXIST salvodata (butlabel string, butcolour string, row1 string,"
								"row2 string, row3 string, row4 string, row5 string,"
								"row6 string, row7 string, row8 string, row9 string,"
								"row10 string, id int NOT NULL, PRIMARY KEY  (`id`))");
		createtableqry.exec();
		if (!createtableqry.isActive() )
		{
			QMessageBox::critical(this, "Salvo Error", "Table 2 Creation Failed! \n"
				                  "\n"+createtableqry.lastError().text()+"\n"+nksalvoDB.driverName());
			dbopen = false;
			return 1;
		}
		dbopen = true;
		return 0;
	}
}

int MainWindow::openDatabase()
{
	if (dbopen == true )
	{
		return 0;
	}
	else
	{
		// Create connection to database server
		nksalvoDB = QSqlDatabase::addDatabase( "QSQLITE" );
		nksalvoDB.setDatabaseName( cwdir+"/nksalvo.db" );
		if ( !nksalvoDB.open() )
		{	
			QMessageBox::critical( this, "NKSalvo Database Error",
			"Could not connect to the database!\n"
			"\n"
			"Reported error:\n"
			+nksalvoDB.lastError().driverText()+"\n"
			+nksalvoDB.lastError().databaseText()+"\n"
			+nksalvoDB.databaseName()+"\n");
			dbopen = false;
			return 1;
		}
		createtableqry.prepare( "CREATE TABLE IF NOT EXISTS serialdata (`port` varchar(10) default NULL,"
								"`speed` varchar(6) default NULL,`databits` varchar(4) default NULL,"
								"`parity` varchar(5) default NULL, `stopbits` varchar(4) default NULL"
								"`id` smallint(2) NOT NULL, PRIMARY KEY  (`id`)" );
		createtableqry.exec();
		if (!createtableqry.isActive() )
		{
			QMessageBox::critical(this, "Salvo Error", "Table 1 Creation Failed! \n"
				                  "\n"+createtableqry.lastError().text()+"\n"+nksalvoDB.driverName() );
			dbopen = false;
			return 1;
		}
		createtableqry.prepare( "CREATE TABLE IF NOT EXIST salvodata (butlabel string, butcolour string, row1 string,"
								"row2 string, row3 string, row4 string, row5 string,"
								"row6 string, row7 string, row8 string, row9 string,"
								"row10 string, id int NOT NULL, PRIMARY KEY  (`id`))");
		createtableqry.exec();
		if (!createtableqry.isActive() )
		{
			QMessageBox::critical(this, "Salvo Error", "Table 2 Creation Failed! \n"
				                  "\n"+createtableqry.lastError().text()+"\n"+nksalvoDB.driverName());
			dbopen = false;
			return 1;
		}
		dbopen = true;
		return 0;
	}
}

To copy to clipboard, switch view to plain text mode 

[Lykurg]

• What does

  qWarning() << QSqlDatabase::drivers();

  qWarning() << QSqlDatabase::drivers();

  To copy to clipboard, switch view to plain text mode 
  tell you?
• Use exec("CREATE...") there is no need to use prepare.

From the docs:

Returns true if the query is active. An active QSqlQuery is one that has been exec()'d successfully but not yet finished with.

So maybe the create statement was successful but already finished, because a normal CREATE don't need much time. Have you checked your created database file, I guess the table is created there...


EDIT: and, ähh, your sql statements aren't valid!!!

[b1]

Thanks for the quick response Lykurg,

It gives ("QMYSQL3", "QMYSQL", "QSQLITE")

And thanks for the tip on exec( Create....)

The SQL statements are taken from similar ones I use under MySQL. Granted the second statement is probably wrong, but I can't see whats wrong with the first statement.

B1.

[Lykurg]

Granted the second statement is probably wrong, but I can't see whats wrong with the first statement.

CREATE TABLE IF NOT EXISTS serialdata (`port` varchar(10) default NULL, `speed` varchar(6) default NULL,`databits` varchar(4) default NULL, `parity` varchar(5) default NULL, `stopbits` varchar(4) default NULL `id` smallint(2) NOT NULL, PRIMARY KEY (`id`)

should be

CREATE TABLE IF NOT EXISTS serialdata (`port` varchar(10) default NULL, `speed` varchar(6) default NULL,`databits` varchar(4) default NULL, `parity` varchar(5) default NULL, `stopbits` varchar(4) default NULL, `id` smallint(2) NOT NULL, PRIMARY KEY (`id`))

Note the last ")" and the comma in front of "`id` smallint(2)".

[b1]

D'OH!! Thank you Lykurg,

I think I need glasses!! Thank you for pointing out my typos. I have now corrected them but I still have the same error.
I added the qWarning << nksalvoDB.open() as below

createtableqry.exec( "CREATE TABLE IF NOT EXISTS serialdata (`port` varchar(10) default NULL,"
								"`speed` varchar(6) default NULL,`databits` varchar(4) default NULL,"
								"`parity` varchar(5) default NULL, `stopbits` varchar(4) default NULL,"
								"`id` smallint(2) NOT NULL, PRIMARY KEY  (`id`) )" );
 
		if (!createtableqry.isActive() )
		{
			QMessageBox::critical(this, "Salvo Error", "Table 1 Creation Failed! \n"
				                  "\n"+createtableqry.lastError().text()+"\n"+nksalvoDB.driverName() );
 
                dbopen = false;
qWarning() << QSqlDatabase::drivers();
qWarning() << nksalvoDB.open();
                return 1

createtableqry.exec( "CREATE TABLE IF NOT EXISTS serialdata (`port` varchar(10) default NULL,"
								"`speed` varchar(6) default NULL,`databits` varchar(4) default NULL,"
								"`parity` varchar(5) default NULL, `stopbits` varchar(4) default NULL,"
								"`id` smallint(2) NOT NULL, PRIMARY KEY  (`id`) )" );
												
		if (!createtableqry.isActive() )
		{
			QMessageBox::critical(this, "Salvo Error", "Table 1 Creation Failed! \n"
				                  "\n"+createtableqry.lastError().text()+"\n"+nksalvoDB.driverName() );

                dbopen = false;
qWarning() << QSqlDatabase::drivers();
qWarning() << nksalvoDB.open();
                return 1

To copy to clipboard, switch view to plain text mode 

and it returns true!

So I am guessing its an SQL syntax problem then...but I can't see it!

Thank you again...B1.

[Lykurg]

Ok, your code works fine on my side. So next guess createtableqry points the the wrong database connection. Where do you initialize it? Use for testing a

createtableqry = QSqlQuery(nksalvoDB);

createtableqry = QSqlQuery(nksalvoDB);

To copy to clipboard, switch view to plain text mode 

right bevor you use it.

And it's no good idea to have a QSqlQuery as a member variable, can easily cause errors on exit the application like "Closing xxx while database xx still in use" or something like that.

[b1]

Hooray!!!

Thank you so much, Lykurg. That did the trick. I will re visit how I initialise the database query.

Thanks again, I do appreciate it.
A very happy B1.