QSqlQuery problem

[MarkoSan]

Hi, I have the code:

QString CMerchandizeBrowser::getMerchandizeName(QString strPicPath)
{
    Q_ASSERT(strPicPath.size()>0);    // assertion test
    // use query
/*
    QSqlQuery useQuery("USE merchandize;");
    qDebug() << useQuery.lastError();    // debug
*/
    // query string;
    QString queryString("SELECT * FROM `eros`.`merchandize` WHERE PicPath=\"%1\";");
    queryString=queryString.arg(strPicPath);
    qDebug() << "queryString:" << queryString;    // debug
    QSqlQuery query(queryString);    // executes sql query
    //Q_ASSERT(query.value(iMerchandizeFieldNAME).toString().size()>0);    // assertion test
    qDebug() << query.lastError();    // debug: shows error
    qDebug() << query.value(iMerchandizeFieldNAME).toString();    // debug
    return(query.value(iMerchandizeFieldNAME).toString());    // returns merchandize name
}

QString CMerchandizeBrowser::getMerchandizeName(QString strPicPath)
{
    Q_ASSERT(strPicPath.size()>0);    // assertion test
    // use query
/*
    QSqlQuery useQuery("USE merchandize;");
    qDebug() << useQuery.lastError();    // debug
*/
    // query string;
    QString queryString("SELECT * FROM `eros`.`merchandize` WHERE PicPath=\"%1\";");
    queryString=queryString.arg(strPicPath);
    qDebug() << "queryString:" << queryString;    // debug
    QSqlQuery query(queryString);    // executes sql query
    //Q_ASSERT(query.value(iMerchandizeFieldNAME).toString().size()>0);    // assertion test
    qDebug() << query.lastError();    // debug: shows error
    qDebug() << query.value(iMerchandizeFieldNAME).toString();    // debug
    return(query.value(iMerchandizeFieldNAME).toString());    // returns merchandize name
}

To copy to clipboard, switch view to plain text mode 

and after qDebug() << query.lastError() I get:

warning: QSqlError(-1, "", "")

warning: QSqlError(-1, "", "")

To copy to clipboard, switch view to plain text mode 

The connection to database is ok, I've double checked. What is wrong, please help!

[marcel]

I don't think you need ";" appended to the query string. I think it is added automatically.
Remove it and see if there's any difference.

[MarkoSan]

Same result. And in both cases, I also get following warning in debugging process:

QSqlQuery::value: not positioned on a valid record

QSqlQuery::value: not positioned on a valid record

To copy to clipboard, switch view to plain text mode 

[marcel]

Everything is OK with the query, so the problem must be somewhere else. Double check you database connection... What happens if you pass the database as the second parameter to QSqlQuery.

[MarkoSan]

God damn, upon seinding paramaters to database, as 3rd paramater I've sent hostname instead of database name and normally, database was not activated. Marcel, thanks!!!!

But on the other hand, I have one more question. I have another function that retreives data from another table and it has been working perfectly before I've corrected this glitch with database paramaters. How is that possible?

[marcel]

I don't know... Magic?
Maybe that database was the default schema for the user you connected with...

[MarkoSan]

I will dig into this, it'a possible nasty bug, and let me report what I've discovered.

[jacek]

warning: QSqlError(-1, "", "")

This actually means that there is no error.

QSqlQuery::value: not positioned on a valid record

You don't invoke QSqlQuery::next() before value().

Also better use bindValue() to avoid SQL injection:

QSqlQuery q;
q.prepare( "SELECT * FROM `eros`.`merchandize` WHERE PicPath= :path" );
q.bindValue( ":path", strPicPath );
if( q.exec() ) {
   while( q.next() ) {
      // ...
   }
}
else {
   // error
}

QSqlQuery q;
q.prepare( "SELECT * FROM `eros`.`merchandize` WHERE PicPath= :path" );
q.bindValue( ":path", strPicPath );
if( q.exec() ) {
   while( q.next() ) {
      // ...
   }
}
else {
   // error
}

To copy to clipboard, switch view to plain text mode 

[brokensword]

I have another big problem with QSqlQuery.
I'm binding values using bindValue() function. Inside exec() function driver()->formatValue() function is called for each binded value, so each '\' becames '\\'.
Is theer any way to avoid such behaviour?

(I have standart_conforming_strings = on in my postgres.conf so I don't need escaping '\' characters)

[sabeesh]

Hi,
Try to use this style

GetQry = ( char *) malloc ( ( 50 * sizeof ( char ) ) + 1 );

strcpy(GetQry, "select * from TableList where id > ");
TmpQry = ( char * ) realloc ( GetQry, sizeof( char ) * ( strlen(GetQry ) + strlen(TID) + 1 ) );

GetQry = TmpQry;
strcat(GetQry, TID);

and execute that string GetQry

[jacek]

GetQry = ( char *) malloc ( ( 50 * sizeof ( char ) ) + 1 );

strcpy(GetQry, "select * from TableList where id > ");
TmpQry = ( char * ) realloc ( GetQry, sizeof( char ) * ( strlen(GetQry ) + strlen(TID) + 1 ) );

GetQry = TmpQry;
strcat(GetQry, TID);

Don't even dare to write such horrible things in a Qt-based code.

[jacek]

I have another big problem with QSqlQuery.
I'm binding values using bindValue() function. Inside exec() function driver()->formatValue() function is called for each binded value, so each '\' becames '\\'.
Is theer any way to avoid such behaviour?

(I have standart_conforming_strings = on in my postgres.conf so I don't need escaping '\' characters)

I think you should contact the Trolls as this is something that should be fixed in Qt code before standard_conforming_strings becomes "on" by default.