Unable to work with database

[Atomic_Sheep]

Hi Guys,

Not sure where I'm going wrong but here's my code:

[CODE/] //Create a database (TO DO, close the database)
QString strServerName = "LOCALHOSE\\SQLITE";
QString strDBName = "test.db";

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); //EDIT in final to incorporate multiple connections, will need a tracking variable that will track how many connections are opened and create a new unique one.
/*
db.setConnectOptions();
QString dsn = QString("DRIVER={SQL Native Client}; SERVER=%1;DATABASE=%2;Trusted_Connection=Yes;").ar g(strServerName).arg(strDBName);
db.setDatabaseName(dsn);
*/
db.setDatabaseName(strDBName);

bool dbOpen = db.open("test.db", NULL);
if(dbOpen == TRUE)
{
qDebug() << "Database open";
QSqlQuery query;
query.exec("SELECT Row FROM tblField WHERE Page = one");
qDebug() << query.lastError().text(); //if error executing SQL statement occurs, get error


}
if(dbOpen == FALSE)
{
qDebug() << "Last error " << db.lastError().text();
}

//Button cNav;

db.close();
db.removeDatabase("test.db");[\CODE]

Basically I get the output of:

Database open
"no such table: tblField Unable to execute statement"

Not sure why, I've got the database in the folder where the .exe is, in fact I've got in about 10 different folders just to be sure. I know that if it can't find a database, it'll create a new blank one, which I think is what is happening, question is, why?

[anda_skoa]

Have you verified with an external tool that the database actually has a table called tblField?

Also make sure your preprocessor macros are defined correctly or better even use C++ boolean literals true and false.

Cheers,
_

[ChrisW67]

This code will be looking for a file called "test.db" in the current working directory of the executing process. This is not necessarily in the same place as the executable or "in about 10 different folders just to be sure." For test purposes put a correct test.db file in a known location and specify an absolute path to it.

Even if tblfield did exist this:

query.exec("SELECT Row FROM tblField WHERE Page = one");

query.exec("SELECT Row FROM tblField WHERE Page = one");

To copy to clipboard, switch view to plain text mode 

is probably not valid SQL unless there is a column actually called one.

db.removeDatabase("test.db");

db.removeDatabase("test.db");

To copy to clipboard, switch view to plain text mode 

makes no sense, you never created a database connection called "test.db" with addDatabase().

[Atomic_Sheep]

I've been looking into how to add an existing database into a project, however I've not been able to figure out exactly how to do it and it's not really clearly documented in the tutorial. Here is what I presently have in the .pro file:

LIBS += C:\Users\Administrator\Desktop\sqlite3.dll
 
    addFiles.sources = test.db
    addFiles.path = .

LIBS += C:\Users\Administrator\Desktop\sqlite3.dll

    addFiles.sources = test.db
    addFiles.path = .

To copy to clipboard, switch view to plain text mode 

How do I specify an absolute path? Is this done in

db.setDatabaseName("testdb");

db.setDatabaseName("testdb");

To copy to clipboard, switch view to plain text mode 

?

I can't find any obvious function in:

http://qt-project.org/doc/qt-4.8/qsqldatabase.html

That allows one to specify the path of the .db file.

[ChrisW67]

Connecting to a database has been absolutely done to death in these forums.

Here is what I presently have in the .pro file

I have no idea where you got this from. Remove the lines you gave us and ensure that you do have:

QT += sql

QT += sql

To copy to clipboard, switch view to plain text mode 

How do I specify an absolute path?

For Sqlite:

db.setDatabaseName("/some/full/path/to/the/test.db");

db.setDatabaseName("/some/full/path/to/the/test.db");

To copy to clipboard, switch view to plain text mode 

[Atomic_Sheep]

Thanks Chris, you were right and I didn't even process what you wrote about my query being questionable at best, looks like it's all been working all along. Many thanks and silly me .

[Atomic_Sheep]

QString strDBName = "D:\path to .db file where the .exe is data.db";
db.setDatabaseName(strDBName);
bool dbOpen = db.open(strDBName, NULL);

QString strDBName = "D:\path to .db file where the .exe is data.db";
db.setDatabaseName(strDBName);
bool dbOpen = db.open(strDBName, NULL);

To copy to clipboard, switch view to plain text mode 

query.exec("SELECT Column1, Column2, Column3, Column4, Column5, Column6, FROM tblTable WHERE (Column4='EMPTY')"); //
        qDebug() << query.lastError().text(); //if error executing SQL statement occurs, get error
 
        //Selected rows based on condition
        while(query.next())
        {
	    qDebug() << "inside while loop";
            int iRow = query.value(0).toInt();
            int iColumn = query.value(1).toInt();
            qDebug() << "Row = " << iRow << "Column = " << iColumn;
        }

query.exec("SELECT Column1, Column2, Column3, Column4, Column5, Column6, FROM tblTable WHERE (Column4='EMPTY')"); //
        qDebug() << query.lastError().text(); //if error executing SQL statement occurs, get error

        //Selected rows based on condition
        while(query.next())
        {
	    qDebug() << "inside while loop";
            int iRow = query.value(0).toInt();
            int iColumn = query.value(1).toInt();
            qDebug() << "Row = " << iRow << "Column = " << iColumn;
        }

To copy to clipboard, switch view to plain text mode 

I've got this code, and unfortunately it's not producing any results. The first qDebug with lastError(), prints " ". According to the documentation (Returns error information about the last error (if any) that occurred with this query.), not sure if the empty brackets indeed correspond to the fact that no error occured or whether we're supposed to get no output at all if no error occured but I'm assuming that no error occured.

The second qDebug statement inside the 'while' loop nevergets executed so not sure why the program never enters the 'while' loop.

I've tried PRAGMA:

query.exec("PRAGMA database_list");
        //qDebug() << query.lastError().text(); //if error executing SQL statement occurs, get error
 
        //List of currently available databases
        while(query.next())
        {
            int iDatabaseConnection = query.value(2).toInt();
            QString sDatabaseSecondColumn = query.value(2).toString();
            QString sDatabaseFileName = query.value(2).toString();
            qDebug() << "Database connection " << iDatabaseConnection << "Database file name = " << sDatabaseFileName << "Second column = " << sDatabaseSecondColumn;
        }

query.exec("PRAGMA database_list");
        //qDebug() << query.lastError().text(); //if error executing SQL statement occurs, get error

        //List of currently available databases
        while(query.next())
        {
            int iDatabaseConnection = query.value(2).toInt();
            QString sDatabaseSecondColumn = query.value(2).toString();
            QString sDatabaseFileName = query.value(2).toString();
            qDebug() << "Database connection " << iDatabaseConnection << "Database file name = " << sDatabaseFileName << "Second column = " << sDatabaseSecondColumn;
        }

To copy to clipboard, switch view to plain text mode 

and the result was:

Database connection 0 Database file name = "D:\path to .db file where the .exe is data.db" Second column = "D:\path to .db file where the .exe is data.db"

So the PRAGMA query enters the 'while' loop but the other does not.

[Atomic_Sheep]

I edited the code a litte:

qDebug() << "Database sequence " << iDatabaseSequence << "Database name = " << sDatabaseName << "File associated = " << sDatabaseFileName;

qDebug() << "Database sequence " << iDatabaseSequence << "Database name = " << sDatabaseName << "File associated = " << sDatabaseFileName;

To copy to clipboard, switch view to plain text mode 

Now it prints out:

Database sequence 0 Database name = "main" File associated = "D:\path to .db file where the .exe is data.db"

[ChrisW67]

Your query as posted is not valid SQL and should be returning an error.

If the query was corrected in the obvious way and returns no rows, i.e. the while() loop is never entered, that would be because there are no rows in that table with 'EMPTY' in column4. This is correct behaviour.

[Atomic_Sheep]

Turns out, everything is working fine, I think my database is corrupt (or I stuffed up access to sqlite conversion), if I search for WHERE column4 = 'SOMETHING', it works, and indeed, if I just pull all the data that it can without the where statement, it only pulls the the SOMETHINGs, so not quite sure where the database got corrupted but at least it pretty much solved.