Querying the value of a variable in SQL Server from C ++ (MFC)

[Yany]

Hi,

I have an issue in C++ (MFC) with SQL Server that doesn't work.

When I query this: (SQL)

select int_field from my_table

With this code:

<
int myClass::getSQLValue()
{
CRecordset rs(&myDB);
rs.Open(CRecordset::snapshot,"select int_field from my_table");

CDBVariant var;
rs.GetFieldValue((short)0,var);

rs.Close();

return var.m_lVal;
}
>

It works fine.


But,

Nevertheless, I need to query a value of a variable
like...


declare @var as int=4;
select @var


so, changing the code above...

<
int myClass::getSQLValue()
{
CRecordset rs(&myDB);
rs.Open(CRecordset::snapshot,"declare @var as int=4; select @var");

CDBVariant var;
rs.GetFieldValue((short)0,var);

rs.Close();
return var.m_lVal;
}
>

This throws a runtime error message at line:

rs.Open(... saying: Debug Assertion Failed!


If someone can give me a better way to do this, I'll really appreciate it.

[SIK]

rs.Open(CRecordset::snapshot,"declare @var as int=4; select @var");

I have not seen or have any experience with passing injected sql this way.

Usually, such sql is created in a sql stored procedure and we then execute the stored procedure from our c++ code.

Does your sample work if you change to a stored procedure?

[Yany]

Thank you SIK.

Yes, I just tried with stored procedure...

create procedure SP_Test1 as
declare @var as int=4;select @var

then, in SQL Query editor:

EXECUTE SP_Test1

it works fine.


but, in C++ code, same way!

rs.Open(CRecordset::snapshot,"EXECUTE SP_Test1");


Throws : Debug Assertion Failed!