QODBC and stored procedures

[xgoan]

Hi all,

I'm connecting with QODBC to a SQL Server Database; I have a stored procedure that returns a value
How can I get this value?

Thanks

[lyuts]

This is the simpliest way, I think.

//declare you QSqlQuery
QString qstr = QString("SELECT your_stored_proc(<input params>);");
 
 
if ( !query.exec(qstr) ) {
// handle this
}
 
// if nothing has been returned
if ( !query.first() ) {
// handle this
}
 
//let us assume that your stored proc returns integer
int var = query.value(0).toInt();

//declare you QSqlQuery
QString qstr = QString("SELECT your_stored_proc(<input params>);");


if ( !query.exec(qstr) ) {
// handle this
}

// if nothing has been returned
if ( !query.first() ) {
// handle this
}

//let us assume that your stored proc returns integer
int var = query.value(0).toInt();

To copy to clipboard, switch view to plain text mode 

[xgoan]

With this query:

QString stmt="SELECT [Login](:result, :type, :username, :password);";

QString stmt="SELECT [Login](:result, :type, :username, :password);";

To copy to clipboard, switch view to plain text mode 

I get this error:

[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '@P1'. [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared. QODBC3: Unable to execute statement"

[lyuts]

Did you bind your paramter values?
Could you show your code?

[xgoan]

This is my code

ConnectDialog::LoginResult ConnectDialog::login()
{
    QSqlQuery q;
    QString stmt="SELECT [Login](:result, :type, :username, :password);";
    QByteArray md5;
 
    q.setForwardOnly(true);
    md5=QCryptographicHash::hash(password().toLatin1(),
        QCryptographicHash::Md5);
    q.prepare(stmt);
    q.bindValue(":result", QVariant(0), QSql::InOut);
    q.bindValue(":type", QVariant(0), QSql::InOut);
    q.bindValue(":username", userName());
    q.bindValue(":password", md5);
    if(!q.exec())
    {
        qCritical()<<q.lastError().text();
    }
    else if(!q.first())
    {
        qCritical()<<q.lastError().text();
    }
    qDebug()<<q.record();

ConnectDialog::LoginResult ConnectDialog::login()
{
    QSqlQuery q;
    QString stmt="SELECT [Login](:result, :type, :username, :password);";
    QByteArray md5;

    q.setForwardOnly(true);
    md5=QCryptographicHash::hash(password().toLatin1(),
        QCryptographicHash::Md5);
    q.prepare(stmt);
    q.bindValue(":result", QVariant(0), QSql::InOut);
    q.bindValue(":type", QVariant(0), QSql::InOut);
    q.bindValue(":username", userName());
    q.bindValue(":password", md5);
    if(!q.exec())
    {
        qCritical()<<q.lastError().text();
    }
    else if(!q.first())
    {
        qCritical()<<q.lastError().text();
    }
    qDebug()<<q.record();

To copy to clipboard, switch view to plain text mode 

[lyuts]

I don't have such an opportunity to compile code, but nevertheless... don't you need to put username and password in quotes(or double-quotes)?
I'm using PostgreSQL and it requires quotes around text/varchar/char params.

[xgoan]

No, because is a prepared query, QSqlQuery escape it.