How to get output data from sql store procedure ?

Mucahit

Hi all,

I have a problem about to get output data from sql store procedure. I want to see deger variable is 45 because in procedure it gives 45 but i see deger variable is still 5. I tried all posibility that i now but i couldnt succeed. If you help me i will be apprisuaded.

My Code:

        QSqlDatabase db;
    
    
    
        db=QSqlDatabase::addDatabase("QODBC","myAwsomDatabase");
    
    
    
        db.setDatabaseName("DRIVER={FreeTDS};SERVER=serverip;PORT=port;DATABASE=database;UID=database;PWD=database");

if(db.open())
{

qry.setForwardOnly(true);
int pers_num=424;
QString tarih="2020-09-25";
int deger=5;

qry.prepare("exec bant_pers_list ?,?,?");
qry.bindValue(0,pers_num);
qry.bindValue(1,tarih);
qry.bindValue(2,deger,QSql::Out);

if(qry.exec())
{
while(qry.next())
{
qDebug()<<qry.boundValue(2);
}
}
}

Stored Procedure:

ALTER PROCEDURE [dbo].[bant_pers_list]
@pers_num bigint,@tarih smalldatetime,@deger int output
as
SET NOCOUNT ON

set @deger=45
SELECT top 1 * FROM dbo.bant_no

return

JonB

@Mucahit
I might be wrong about this :) but I have a theory....

I'm not sure the output parameter gets bound during fetching of the result set (inside your while loop), only on conclusion. Try the qDebug()<<qry.boundValue(2); after the while loop?

Mucahit

Thanks for your theory but it didnt work :) I tried to get it came out on different lines (before while,inside of while and after while) but still got value that 5 .

JonB

@Mucahit
Hmmm. Shame!

Then since the code looks OK to me, though I don't know the ins & outs of ODBC, I would start by declaring a simpler stored proc with just a single parameter, the int output, and see how that went?

BTW, is exec right across ODBC for call a stored proc? I kinda thought it was call for ODBC? Have a read of maybe https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-how-to/running-stored-procedures-call-stored-procedures?view=sql-server-ver15 ?

P.S.
I'd like to see your qry.executedQuery() after you have executed the query. I want to see if it has implemented the OUTPUT....

P.P.S.
Lookie here:
https://doc.qt.io/qt-5/qsqlquery.html#approaches-to-binding-values

I think you're supposed to do this via an example there:

    QSqlQuery query;
    query.prepare("CALL AsciiToInt(?, ?)");
    query.bindValue(0, "A");
    query.bindValue(1, 0, QSql::Out);
    query.exec();
    int i = query.boundValue(1).toInt(); // i is 65

See that it's CALL like I thought?

Mucahit

When i change exec to call it gives this error:

QODBCResult::exec: Unable to execute statement: "[FreeTDS][SQL Server]Incorrect syntax near '424'."

I am sure when i use exec with prepare it works because i print with qDebug() it gives me result. And when i take normal select data with stored procedure it gives me result.Why i cant run this "call bant_pers_list ?,?,?".
qDebug()<<qry.executedQuery()
="exec bant_pers_list ?,?,?"

Mucahit

I managed to run the call command but qry.next() didnt work and i cant get output from data on store proc. Could there be a problem with the driver FreeTDS ? I tried this example but it didnt work, i am getting i=0 :

QSqlQuery query;
query.prepare("CALL AsciiToInt(?, ?)");
query.bindValue(0, "A");
query.bindValue(1, 0, QSql::Out);
query.exec();
int i = query.boundValue(1).toInt(); // i is 65

JonB

@Mucahit said in How to get output data from sql store procedure ?:

Could there be a problem with the driver FreeTDS

That is exactly what I was thinking about. Is there any documentation on this driver, any mention of how/whether it supports OUTPUT parameters, or whether it supports them when operating on an MS SQL Server database rather than some other database like MySQL? Are you able to test code against some other driver than this one?

Mucahit

I am already using MSSQL 17 server. I found this sources, please read.

1-) https://lists.ibiblio.org/pipermail/freetds/2014q1/028857.html
2-)https://www.freetds.org/userguide/ChoosingTdsProtocol.html
3-)https://raspberrypi.stackexchange.com/questions/108329/cant-get-the-msodbcsql-package-for-debian-10
4-) https://demirten.gitbooks.io/linux-sistem-programlama/content/misc/sqlserver.html

Also i work on raspberry bi card, for this I need to install msodbc17 package but it does not support arm based. I changed the freetds version to 4.2 but it still didn't work .What do you think i should do ? (i am working raspberry pi card os raspbian and qt is running this card but I'm connecting to a server on another Windows machine.)