Problem with QSqlQuery and the .arg of a QString[SOLVED]

[jano_alex_es]

Hello,

The next code works perfectly:



//Note: CSQLUsers::AL_PUPIL is "1", an intenger

QSqlQuery data(QString("SELECT id FROM users WHERE iaccesslevel=%1 AND sname = 'Alex' ") .arg(CSQLUsers::AL_PUPIL), *pointer_my_database->GetDBPtr());

bool test1 = data.first();
bool test2 = data.isValid();
bool test3 = data.isSelect();


But, when I try to use the arguments of QString (something that works with integers or other sort of variables)



//Note: CSQLUsers::AL_PUPIL is "1", an intenger

QString name = 'Alex';
QSqlQuery data(QString("SELECT id FROM users WHERE iaccesslevel=%1 AND sname = %2 ") .arg(CSQLUsers::AL_PUPIL) .arg(name), *pointer_my_database->GetDBPtr());

bool test1 = data.first();
bool test2 = data.isValid();
bool test3 = data.isSelect();


the three booleans are false... why? the argument should be converted to QString properly and call the SQL :S

thanks!

[wysota]

It is but it is not quoted so the select statement is invalid. You should use QSqlQuery::prepare and QSqlQuery::bindValue instead of substituting arguments directly.

[munna]

The single quote around the name is missing in the second code block. Try this



//Note: CSQLUsers::AL_PUPIL is "1", an intenger

QString name = 'Alex';
QSqlQuery data(QString("SELECT id FROM users WHERE iaccesslevel=%1 AND sname = '%2' ") .arg(CSQLUsers::AL_PUPIL) .arg(name), *pointer_my_database->GetDBPtr());

bool test1 = data.first();
bool test2 = data.isValid();
bool test3 = data.isSelect();

[Lesiok]

Try something like this (You must write %2 in apostrophes)

QString name = 'Alex';
QSqlQuery data(QString("SELECT id FROM users WHERE iaccesslevel=%1 AND sname = '%2' ") .arg(CSQLUsers::AL_PUPIL) .arg(name), *pointer_my_database->GetDBPtr());

or this

QString name = 'Alex';
QSqlQuery data(QString("SELECT id FROM users WHERE iaccesslevel=%1 AND sname = :sname ") .arg(CSQLUsers::AL_PUPIL), *pointer_my_database->GetDBPtr());
data.bindValue(":sname",name);

[jano_alex_es]

thanks, but it's not working :S

If I use the '%2' solution,

data.first(); == false
data.isValid() == false
data.isSelect(); == true

but if I use the data.bindValue, the three are set to false :S

when I use "name = 'Alex'" the three are set to true, so in my opinion the problem is not in the database

[jano_alex_es]

On the other hand, the next code:



QString name = 'Alex';
QSqlQuery data(*pointer_my_database->GetDBPtr());
data.prepare(QString("SELECT id FROM users WHERE iaccesslevel=:iaccesslevel AND sname = :sname"));

data.bindValue(":sname", name);
data.bindValue(":iaccesslevel", CSQLUsers::AL_PUPIL);
data.exec();

bool test1 = data.first();
bool test2 = data.isValid();
bool test3 = data.isSelect();


gives me false, false and true again :S

[Lesiok]

Why You have


QString name = 'Alex';

not


QString name = "Alex";

???

[jano_alex_es]

Because I was testing, testing, testing and testing, and I though "maybe the problem is in the double-mark, SQL uses simple so let's try". The compiler said nothing, I though it was the same.

Thanks, definitly your solutions were right and the last problem was that.

[wysota]

The compiler said nothing, I though it was the same.

I see someone is using a lousy... eee.... Microsoft compiler. :)

[jano_alex_es]

what can I say, the company chooses the compiler :P