Problems with QVariants in Stored Procedures

NicolasKsi

Hi everyone!
Im working with QSqlQuery on a Microsoft server, trying to run some Stored Procedures wich outputs some data.
The documentation claims that, if you are using a QVariant to retrieve data from a QSqlQuery, you should first allocate space.

from the docs...
"If paramType is QSql::Out or QSql::InOut, the placeholder will be overwritten with data from the database after the exec() call. In this case, sufficient space must be pre-allocated to store the result into."

I have read the QVariant Documentation, and i havent found exactly how to "pre-allocate" the space. Ive managed to do it in a way that is extremely untidy, so I would like to know how to do it correctly. Lets begin with some examples.

The stored procedure recieves 2 strings, and returns the Id of a table.

//Query is of type QSqlQuery and is previously initialized
QVariant VariantAux;
Query.prepare("{CALL GetId(:Name,:Sirname,:Id)}");
Query.bindValue(":Name","John",QSql::In);
Query.bindValue(":SirName","Doe",QSql::In);
Query.bindValue(":Id",VariantAux,Qsql::Out);
if(!Query.exec())
     return -1;
int IdFromTable=Query.boundValue(":Id").toInt();

With this case, the program will just crash afther the Query.exec(). It makes sense though, because VariantAux hasnt been initialized. Now lets Check this code

//Query is of type QSqlQuery and is previously initialized
QVariant VariantAux=0;
Query.prepare("{CALL GetId(:Name,:Sirname,:Id)}");
Query.bindValue(":Name","John",QSql::In);
Query.bindValue(":SirName","Doe",QSql::In);
Query.bindValue(":Id",VariantAux,Qsql::Out);
if(!Query.exec())
     return -1;
int IdFromTable=Query.boundValue(":Id").toInt();

Now, ive said that the VariantAux value is 0, now it understands that an int is supposed to be placed on the QVariant. This code works, but NOT ALWAYS.
If the id of the table is a big number, the program CRASHES. My guess? Qt is assigning space to the QVariant as if it was going to recieve an int, but it could recieve a higher number... In this case it crashes.
Lets check this final code

//Query is of type QSqlQuery and is previously initialized
QVariant VariantAux=4294967296;
Query.prepare("{CALL GetId(:Name,:Sirname,:Id)}");
Query.bindValue(":Name","John",QSql::In);
Query.bindValue(":SirName","Doe",QSql::In);
Query.bindValue(":Id",VariantAux,Qsql::Out);
if(!Query.exec())
     return -1;
int IdFromTable=Query.boundValue(":Id").toInt();

This code so far has worked perfectly, By assigning 4294967296 to the QVariant, Qt makes sure there will be enough space for the result.
The code works, but im not complety satisfied.

QVariant VariantAux=4294967296;

At least from my point of view, that way of assinging space is awful. Is there a better way to do this?
Can I specify the type of variable the QVariant will hold? For example, char, int, unsigned long int, etc...

Maybe my query usage is wrong... Is there another way to retrieve data from StoredProcedures without using QVariants?

NOTE: Almost all of the examples ive found on the internet are like this

query.prepare("{CALL fp_test(?,?,?)}");

Im interested on using placeholder instead of the "?" notation, that I find quite confusing (If you alter the order of the binds, the query wont work, while with placeholder it always works)

Thanks in advance

SGaist

Hi,

You could construct a QVariant using e.g. std::numeric_limits<T>::max().

kshegunov

Well, the painless way is to just use the appropriate type to implicitly construct the variant. E.g:

int id = 0; //< It's a dummy

query.prepare("{CALL GetId(:Name, :Sirname, :Id)}");
query.bindValue(":Name", "John", QSql::In);
query.bindValue(":SirName", "Doe", QSql::In);
query.bindValue(":Id", id, QSql::Out); //< Just use the dummy here
if(!query.exec())
     return -1;

bool ok;
id = query.boundValue(":Id").toInt(&ok);

VRonin

from here http://doc.qt.io/qt-5/qsqlquery.html#approaches-to-binding-values looks like a constant is good enough. I might be wrong but I always used it with constant and never run into problems.

Query.bindValue(":Id",0,Qsql::Out);

kshegunov

Yes, just like with a dummy variable. The idea is to pass a valid (in the sense of type) QVariant.