Strange behavior QSqlQuery and "Driver Not Loaded Error"

VladimirPivovar

Hi!

@
db = QSqlDatabase::addDatabase("QMYSQL","Connection-To-MySQLServer-5.5");

if (db.isOpen())
{
  static QSqlQuery queryJAN; 
  queryJAN = QSqlQuery(db);

   queryStringJAN = "INSERT INTO january;
    queryStringJAN = queryStringJAN + " (answer,dateOfCall,timeOfCall,phoneNumberA,phoneNumberB,callDuration,offset) VALUES ";
    queryStringJAN.append( QString( "(?,?,?,?,?,?,?)," ).repeated( 1000) );
    queryStringJAN.chop( 1 );

    if( !queryJAN.prepare( queryStringJAN ) )
    {
      qDebug() << "Unable to prepare query:" << query.lastError();
      return FALSE;
    }

}

@

qDebug() returns:

"Unable to prepare query: QSqlError(-1, "Driver not loaded", "Driver not loaded")"

Why can not prepare statement?

ChrisW67

The error message is consistent with the QMYSQL database plugin not being built and available in your system... but it is not consistent with your code. If that is your actual code then execution should never reach line 13 because you have not opened the database (even if the driver were available).

Insert:
@
qDebug() << QSqlDatabase::drivers();
@

at line 2 and check that QMYSQL is listed.

Assuming this is not your actual code, and execution has reached line 13 then it seems unlikely that you will be able to prepare a query with 7000 bind variables. Even if it is possible I would suggest this is not the right way to insert 1000 rows of 7 values each.

lgeyer

You will have to build the MySQL plugin, see "here":http://doc-snapshot.qt-project.org/4.8/sql-driver.html#building-the-plugins-manually.

I'm with ChrisW67 that using thousands of bound variables is not what you want to do.

In addition, you create the query <code>static</code>, which means that it will never go out of scope, and thus dangles as soon as the database connection is closed.

VladimirPivovar

My program reads the binary files and determines in which month they have been modified:

@

void getInfoFromCurrentAMAFile(CHAR szT[256])
{

QFileInfo InfoAboutCurrentFile&#40;szT&#41;;

if (InfoAboutCurrentFile.exists())
{
    dtm.setDate(InfoAboutCurrentFile.lastModified().addDays(-1).date());
    fileCreatedAt = dtm.toString("yyyy");
    //dtmM.setDate(InfoAboutCurrentFile.lastModified().addDays(-1).date());
    fileCreatedAtMonth = dtm.toString("M").toInt();

}

}

@

I have a function,which prepares a query depending on the month:

@

static QSqlDatabase db;
static QSqlQuery query;

BOOL PrepareQueryStringMainPack()
{
if (db.isOpen())
{

switch (fileCreatedAtMonth)
{

    case 1: queryStringMainPackage = "INSERT INTO january_ama"; break;
    case 2: queryStringMainPackage = "INSERT INTO february_ama"; break;
    case 3: queryStringMainPackage = "INSERT INTO march_ama"; break;
    case 4: queryStringMainPackage = "INSERT INTO april_ama"; break;
    case 5: queryStringMainPackage = "INSERT INTO may_ama"; break;
    case 6: queryStringMainPackage = "INSERT INTO june_ama"; break;
    case 7: queryStringMainPackage = "INSERT INTO july_ama"; break;
    case 8: queryStringMainPackage = "INSERT INTO august_ama"; break;
    case 9: queryStringMainPackage = "INSERT INTO september_ama"; break;
    case 10: queryStringMainPackage = "INSERT INTO october_ama"; break;
    case 11: queryStringMainPackage = "INSERT INTO november_ama"; break;
    case 12: queryStringMainPackage = "INSERT INTO december_ama"; break;

}

queryStringMainPackage = queryStringMainPackage + " (answer,dateOfCall,timeOfCall,phoneNumberA,phoneNumberB,callDuration,offset) VALUES ";

}

if( !query.prepare( queryStringMainPackage ) )
{
   qDebug() << "Unable to prepare query:" << query.lastError();
   return FALSE;
}

}

BOOL BindValuesMainPack()
{
query.addBindValue(answer);
query.addBindValue(dateForMySQL);
query.addBindValue(timeForMySQL);
query.addBindValue(phoneNumberA);
query.addBindValue(phoneNumberB);
query.addBindValue(callDuration);
query.addBindValue(offset);
}
@

When the program generates a new request (it was read a file created in another month) - after the formation of a query to insert 1000 records an error

@
if( !query.exec() )
{
qDebug() << "Unable to exec query:" << query.lastError();
}
@

Instead of 1,000 entries to be inserted into the database query contains only two entries

I think that creating a new query affects the performance of command execute query

The most interesting thing is that this command is executed without error:

@
if( !query.prepare( queryStringMainPackage ) )
{
qDebug() << "Unable to prepare query:" << query.lastError();
return FALSE;
}
@

lgeyer

Be aware that the database may choose to delay preparing a query until it is executed the first time. In this case, preparing a syntactically wrong query succeeds, but every consecutive exec() will fail.

Your query actually is syntactically wrong and it does not contain any placeholders which could be replaced by addBindValue().

Again, is there any specific reason all your variables are static?

VladimirPivovar

"Again, is there any specific reason all your variables are static?"
I want to make these static variables are available throughout the project.

"Your query actually is syntactically wrong and it does not contain any placeholders which could be replaced by addBindValue()"

Well, as I then create and perform new query based on changing conditions?
Here in more detail please

Why does the formation of the first query, I was able to insert 500000000 rows with inserts 1000 rows and the second formation - when the first insert 1000 rows linking the commands could not work out properly?

It was after when the formation of a new query (changed monthly) command is no longer run
@
if( !query.exec() )
{
qDebug() << "Unable to exec query:" << query.lastError();
}
@

lgeyer

The effect of static depends on the scope:

at file scope (as shown in your example) the visiblity and accessibility of the variable is actually limited (to the file it was declared in), not extended
at class scope, the variable is shared by all instances of the class (no effect on visibility, accessibility depends on access specifier)
at function scope, the variable will retain its value between function calls (no effect on visibility or accessibility)

A design rule of thumb is that variable should always be restricted to the most limited scope possible (which also implies that there should be no variable at global scope); instead just pass the variable around as needed (keep in mind that QSqlQuery is implicitly shared) or let it be a private member, accessible through a getter (encapsulation).

Given that <code>queryStringMainPackage</code> is also static (or a member of the class) this affects your second question as well, as consecutive calls to <code>PrepareQueryStringMainPackage()</code> result in an invalid query string (as the text is appended over and over again, <code>queryStringMainPackage = queryStringMainPackage + ...</code>).

The next call to exec() has to fail, because it would have to execute an invalid query. The prepare() does work because the acutal preparation is most probably delayed to the call to exec().

In addition, your query does not contain any "placeholders":http://qt-project.org/doc/qt-4.8/qsqlquery.html#approaches-to-binding-values (at least not the code you've shown), so <code>BindValuesMainPack()</code> has to fail as well.

VladimirPivovar

Data Binding I was doing the right:

Binding values using positional placeholders (version 2):
QSqlQuery query;
query.prepare("INSERT INTO person (id, forename, surname) "
"VALUES (?, ?, ?)");
query.addBindValue(1001);
query.addBindValue("Bart");
query.addBindValue("Simpson");
query.exec();

String queryStringMainPackage I resed as necessary

My algorithm is:

I initialize a request for a particular month
prepare a request to insert 1000 records
link the data
If the number of records was in 1000 - execute a request to insert

Error appears when inserting after the formation of a query to insert, even for another month...
If i insert records in one table - it all works, when, a new request for another month, and inserts the first of thousands of records - error

Please give me the working source code as it should be
Because I do not know what to do already

lgeyer

I'm quite sorry, but I'm having a hard time understanding you; you will find a sketch attached how such a task could be done in principle and it's up to you to fit it to your concrete requirement.
@
bool importFile(const QString &fileName)
{
QFileInfo fileInfo(fileName);

if(fileInfo.exists(&#41; == false&#41;
    return false;

QSqlQuery query;
query.prepare("INSERT INTO " +
              QLocale(QLocale::English&#41;.toString(fileInfo.lastModified().addDays(-1),
                                                 "MMMM").toLower() +
              "_ama (answer, ..., offset) VALUES (?, ..., ?)");
do
{
    readNextRecordFromFile&#40;&#41;;

    query.addBindValue(readNextValueFromRecord(&#41;&#41;;
    ...
    query.addBindValue(readNextValueFromRecord(&#41;&#41;;
} 
while ((query.exec&#40;&#41; == true&#41; && (lastRecordRead(&#41; == false)&#41;;

return ((query.isActive() == true) && (lastRecordRead(&#41; == true));

}
@
Brain to terminal. Exemplary.

And make sure you've had a good read on C++, design principles, Qt and the Qt SQL module.