Re: Return value from a stored procedure in ADO - C / C++ / MFC Discussion Boards

Assuming your stored proc looks something like this:

create procedure my_proc( @param1 int )
as
begin
   return 1
end

here's how you'd code it up with ADO:

#define ADO_TESTHR(x)	{	if FAILED(x) _com_issue_error(x);	};
 
_ConnectionPtr pConn;
_CommandPtr pCmd;
_variant_t vtValue;
 
try
{   
    ADO_TESTHR( pConn.CreateInstance( "ADODB.Connection" ) );
    ADO_TESTHR( pCmd.CreateInstance( "ADODB.Command" ) );
 
    .. open your connection  pConn->Open something
 
    pCmd->ActiveConnection = pConn;
    pCmd->CommandText = "dbo.my_cool_proc";   // The name of your proc
    pCmd->CommandType = adCmdStoredProc;
 
    // Add the proc parameters
    pCmd->Parameters->Append( pCmd->CreateParameter( "param1", adInteger, adParamInput, sizeof(int) ) );
     
    // Add the return parameter.  This MUST be the last parameter appended
    pCmd->Parameters->Append( pCmd->CreateParameter( "return", adInteger, adParamReturnValue, sizeof(int) ) );
 
    // Set the parameter value  
    pCmd->Parameters->Item["param1"]->Value = 1234;
 
    pCmd->Execute( NULL, NULL, adCmdStoredProc );
 
    // Check out the return value
    vtValue = pCmd->Parameters->Item["return"]->Value;
    if( vtValue.vt == VT_I4 )
    {
        TRACE( "%lu\n", static_cast< long >( vtValue ) );
    }		
  
}
catch( _com_error &ce )
{
}
 
try
{
   if( pConn )
   {
      if( pConn->State != adStateClosed )
      {
          pConn-Close();
      }
   }
}
catch( ... )
{
}

Ty

"The significant problems we face cannot be solved at the same level of thinking we were at when we created them." -Albert Einstein

Thank yo for the answer.
Please find here my sample code with the stored procedure.
With my code I can't retrieve the output value.

Best Regards

void CStoreProcedureDlg::OnWriteData()
{
//Initialize the connection with the Database RTLSQL1 ARCHIVES_NUM
//COM initialization
CoInitialize (NULL);
_ConnectionPtr m_pConn2("ADODB.Connection");
_ParameterPtr pParam2,pParam3;

try
{
//Check if I can create a instance for a connection into a db
HRESULT hr = m_pConn2.CreateInstance (__uuidof (Connection));

if (FAILED (hr))
{
// Automat_LOG_WITH_TIME(_T("DB : Can't create intance of Connection"));
}
else
{
// Automat_LOG_WITH_TIME(_T("DB : I can create intance of Connection"));
}

CString strFileName = "JA980630";
//Check if I can Open a session in the ARCHIVES_TVI
if (FAILED (m_pConn2->Open("provider=sqloledb.1;database=ARCHIVES_TVI;server=1.1.1.1;uid=xxx;pwd=xxx;network=dbmssocn","","",adConnectUnspecified)))

{
// Automat_LOG_WITH_TIME(_T("DB : Can't open datasource ARCHIVES_TVI"));
// Automat_Send_SMS(_T("DB : Can't open datasource ARCHIVES_TVI"),_T(""));

}
else
{
//Automat_LOG_WITH_TIME(_T("DB : I can open datasource ARCHIVES_TVI"));
CString sT;
sT.Format("%d",1);
_variant_t varLiReturn;
int liReturn = -99;

_CommandPtr pCmd2("ADODB.Command");
pCmd2->ActiveConnection = m_pConn2;
//Store procedure for the DATABASE ARCHIVES_NUM
pCmd2->CommandText = "StartFileNumV3";

pParam2 = pCmd2->CreateParameter ( _bstr_t ("FileName"), adVarChar,
adParamInput, strFileName.GetLength (), (_bstr_t) strFileName);
pCmd2->Parameters->Append ( pParam2);

pParam2 = pCmd2->CreateParameter ( _bstr_t ("AutomatNr"), adInteger,
adParamInput, sizeof(int), _variant_t (sT));

pCmd2->Parameters->Append ( pParam2);

// pCmd2->Parameters->Item[_variant_t((long)1)]->Direction = adParamReturnValue;
pParam2 = pCmd2->CreateParameter(_T("Status"),adInteger,adParamOutput,sizeof(int),varLiReturn);
pCmd2->Parameters->Append ( pParam2);

//pParam2->Value = vtroyal;
_RecordsetPtr pRecordset;
pRecordset.CreateInstance(__uuidof(Recordset));
pRecordset = pCmd2->Execute(NULL, NULL, adCmdStoredProc);
varLiReturn = pCmd2->Parameters->Item[_T("Status")]->Value;

Sleep(100); //ms
//Automat_LOG_WITH_TIME(_T("DB : The Store Procedure have been execute with success (StartFileNum)"));

//Close the DATABASE
if ( (m_pConn2->State & adStateOpen) == adStateOpen)
{
m_pConn2->Close();
// Automat_LOG_WITH_TIME(_T("DB : I can close datasource RTLSQL1 ARCHIVES_TVI"));
}
else
{
// Automat_LOG_WITH_TIME(_T("DB : I can't close datasource RTLSQL1 ARCHIVES_TVI"));
}
}
}
catch ( _com_error &e )
{
_bstr_t bstrSource (e.Source());
_bstr_t bstrDescription (e.Description());
CString sErrorDB;
sErrorDB.Format("DB : ERROR !!!!! ARCHIVES_TVI\r\nException thrown for classes generated by #import\r\nCode = %08lx\r\nCode meaning = %s\r\nSource = %s\r\nDescription = %s\r\n", e.Error (), e.ErrorMessage (), (LPCTSTR) bstrSource, (LPCTSTR) bstrDescription);
// Automat_LOG_WITH_TIME(sErrorDB);
MessageBox (sErrorDB, bstrDescription,MB_OK);
}
catch (...)
{
TRACE ( "*** Unhandled Exception ***" );
}

Sleep(100); //ms
CoUninitialize();
}

__________________________________________________________________________________

The stored procedure :

CREATE PROCEDURE StartFileNumV3
(
@FileName varchar(8), @AutomatNr int, @Status int OUTPUT
)
AS
declare @Now datetime
declare @Nbr int
set @Nbr = (SELECT COUNT(*) FROM Num_Files WHERE (File_Name = @FileName))

if @Nbr = 1
begin
set @Nbr = (SELECT COUNT(*) FROM Num_Files WHERE (File_Name = @FileName) and (SetNumReady is not null) and (DateSetNumReady is not null))
if @Nbr = 1
begin
set @Now = GetDate()
UPDATE Num_Files SET Num_Go = @Now, AutomatNr = @AutomatNr WHERE File_Name = @FileName
SET @Status = 1
end
else
begin
/*La référence du fichier est présente dans la table, mais celle-ci ne doit pas se trouver dans la BétaCart ...*/
SET @Status = 2
end
end
else
begin
/*La référence du fichier n'est pas présente dans la table ...*/
SET @Status = 0
end

SELECT @Status
GO

youssef

Ahh, your procedure has an output parameter, not a return value. That's a little different. What happens with this line of code:

varLiReturn = pCmd2->Parameters->Item[_T("Status")]->Value;

Does varLiReturn contain the right value, or do you get something incorrect? Does it throw a COM error at that point, or ? Everything looks okay just from a quick glance.

Ty

"The significant problems we face cannot be solved at the same level of thinking we were at when we created them." -Albert Einstein

Is that I must to force a ReturnValue in my stored procedure for resolve my problem ?

Best Regards

youssef

No, you can definitely use output parameters in ADO. I do them all the time. What exactly is going wrong when you execute your code? It looks okay from looking at it, but it will help to know what is happening. Does it throw a _com_error, or is the value just wrong?

Ty

"The significant problems we face cannot be solved at the same level of thinking we were at when we created them." -Albert Einstein

youssef read here, you have an explanation how get the return value....

Regards

Carlos Antollini.
Sonork ID 100.10529 cantollini

Many thanks Carlos

Best Regards

youssef

When I execute my sample, now I have an IDispatch Error #3079 : error converting data char to int.

Please find the source code for checking where is the problem.
Other thing, I would like to know if I can specifie the NAME for each INPUT for the STORED PROCEDURE.

Best Regards

void CADO_SP_CARLOSDlg::OnButton1()
{
//Sample with Connection string for SQL Server

CADODatabase pAdoDb;
CString strConnection = _T("");

//strConnection = _T("Provider=MSDASQL;PersistSecurityInfo=False;"
// "Trusted_Connection=Yes;"
// "Data Source=Access Sql Server;catalog=sampledb");

strConnection = _T("provider=sqloledb.1;database=ARCHIVES_TVI;server=132.160.148.48;uid=Automat1;pwd=boulet");

pAdoDb.SetConnectionString(strConnection);

if(pAdoDb.Open())
{
m_editStatus = _T("OK");
}
else
{
m_editStatus = _T("KO");
}
UpdateData(FALSE);

//CADORecordset prs(&pAdoDb);

//Input Value
CADOParameter pParamIn_AutomatNr(CADORecordset::typeInteger, sizeof(int),CADOParameter::paramInput,_T("AutomatNr"));
CADOParameter pParamIn_FileName(CADORecordset::typeChar, sizeof(char) * 8, CADOParameter::paramInput,_T("FileName"));
//Output Value
CADOParameter pParamOutStatus(CADORecordset::typeInteger, sizeof(int), CADOParameter::paramOutput,_T("Status"));

//Set Datas to the Stored Procedure
pParamIn_AutomatNr.SetValue(1);
CString strFileName= "JK950213";
pParamIn_FileName.SetValue((_bstr_t) strFileName);

CADOCommand pCmd(&pAdoDb, "StartFileNumV3");

pCmd.AddParameter(&pParamIn_AutomatNr);
pCmd.AddParameter(&pParamIn_FileName);
pCmd.AddParameter(&pParamOutStatus);

CADORecordset pRs(&pAdoDb);

if(pRs.Execute(&pCmd))
{
CString str = _T("");
int nVal=0;
pParamOutStatus.GetValue(nVal);
}

}

the Stored Procedure :

CREATE PROCEDURE StartFileNumV3
(
@FileName varchar(8), @AutomatNr int, @Status int OUTPUT
)
AS
declare @Now datetime
declare @Nbr int
set @Nbr = (SELECT COUNT(*) FROM Num_Files WHERE (File_Name = @FileName))

if @Nbr = 1
begin
set @Nbr = (SELECT COUNT(*) FROM Num_Files WHERE (File_Name = @FileName) and (SetNumReady is not null) and (DateSetNumReady is not null))
if @Nbr = 1
begin
set @Now = GetDate()
UPDATE Num_Files SET Num_Go = @Now, AutomatNr = @AutomatNr WHERE File_Name = @FileName
SET @Status = 1
end
else
begin
/*La référence du fichier est présente dans la table, mais celle-ci ne doit pas se trouver dans la BétaCart ...*/
SET @Status = 2
end
end
else
begin
/*La référence du fichier n'est pas présente dans la table ...*/
SET @Status = 0
end

SELECT @Status
GO

youssef

When I Insert the records into the database which contain files using the following code:-

CSplit splt;
HGLOBAL hGlobal;
int m_r;
tst.Open(CRecordset::dynamic);
tst.AddNew();
tst.m_Split_ID ="128888211111";
CString m_strMid;
m_r = f.GetLength();
hGlobal = GlobalAlloc(GPTR,m_r);
tst.m_Split_Eng_Text.m_dwDataLength = m_r;
tst.m_Split_Eng_Text.m_hData = GlobalLock(hGlobal);
f.ReadHuge(tst.m_Split_Eng_Text.m_hData,m_r);
tst.SetFieldDirty(&tst.m_Split_Eng_Text);
tst.SetFieldNull(&tst.m_Split_Eng_Text,FALSE);
tst.Update();
f.Close();
tst.Close();

But When I repeat the above lines of code
It is giving me error as
"Out of Memory"
Can anyone suggest the solution for it

Samir Sood

GlobalAlloc is fine, but when u finish with the allocated memory u should use GlobalFree

Papa
Murex Co.

while (TRUE)
Papa.WillLove ( Bebe ) ;

I have used the GlobalFree function but to no avail

Samir Sood

I need help writing a C++ source code for a two dimensional array [4}[4]that prompts the user to enter the numbers. The program should include a class which include public and private identifiers. The program must read the data and calculate the sum of each row, the sum of each column, the sum of main diagonal and print the results.

Somebody, please, please help me! Cry | :((

is this some assignment?
if so, try it yourself first.

i could help you, if you post your code

Thomas

modified 29-Aug-18 21:01pm.

I have worked on this code for two days. I made several changes but can't seem to find the right combination. This is my first programming class. What is wrong with this one?

//This is an interactive program that will
//prompt the user to enter 16 real numbers,
//one at a time. The numbers will be stored
//into a two dimensional array. This program
//will calculate the sum of each row, sum of
//each column, the sum of diagonal and print the results.

#include <iostream.h>

class twod

{
public:
int sumr;
int a[4][4];
int read (int a[4][4]);
int sor (int a[4][4]);
int soc (int a[4][4]);
int somd (int a[4][4]);

private:
void print(int sumr, int sumc, int d);

// call the functions

read(int a);
sumr = sor(int a[4][4]);
sumc = soc(a);
d = somd(a);
print (int sumr, int sumc, int d);
return 0;

};

int twod::read(int a[4][4])
{

int i, j;
{
for (i=0; i<=3; ++i)
{
for (j=0; j<=3; ++j)
{
cout <<"Enter an integer: " <<endl;
cin="">> a[i][j];
}
}
}
return (a[0]);
}
int twod::sor(int a[4][4])
{
int sumr = 0;
int i;
for (i=0; i<=3; ++i)
{
for (j=0; j<=3; ++j)

int sumr = sumr +a[i][i];
return (sumr);
}
}

int twod::soc(int a[4][4])
{
int i, j;
int sumc =0;

for (i=0; i<=3; ++i)
{
for (j=0; j<=3; ++j)

sumc =a[j][j];
sumc = sumc[j] + a[i][j];
}
return (sumc);
}

int twod::somd(somd (int a[4][4])
{
int d = 0;
int i;

for (i=0; i<=3; ++i)
d = d + a[i][i];
return (d);
}

void int twod::print(int sumr, int sumc, int d)
{
cout <<"The sum of rows is : " << sumr <

We will not do your homework for you. Start on the code yourself, and if you get stuck, post your questions and the code you have so far.

--Mike--
Just released - RightClick-Encrypt v1.4 - Adds fast & easy file encryption to Explorer
My really out-of-date homepage
Sonork-100.19012 Acid_Helm

Since you do not know the size of the array at compile time, use dynamic allocation. Remember to programmably delete the individual arrays after you are done.

Kuphryn

I am designing a system that needs a large file and i am using memory mapped files.

My file can grow above 4GB and i do not want to map the whole file to memory at one point.

But, the application is multithreaded and multiple threads may want access to different parts of the file. What is the overhead, if there are a lot of Map and UnMap calls? I mean, i know it will be slower than it not being there Smile | :)

. But, have anyone experience as to the nature of overhead that this will cause.

Thomas

modified 29-Aug-18 21:01pm.

The overhead includes performance loss and possible synchronization problems. The program could suffer a performance loss since more than one thread could be reallocation larger virtual memory.

As the threads access the mapped files, you will need to implement a synchronization algorithm. Depending on the number of processes the program creates, you will need a CRITICAL_SECTION or a mutex.

Can you implement a solution such that the program will allocate memory before creating the threads? Thus, you will not have to allocate more virtual memory as often.

Kuphryn

Why do you need to Map and UnMap? Can't each thread Map the file, and then you synchronize access across the threads?

Regardless of whether memory mapped files are used or not you still need to handle thread synchronization, so I don't see what that has to do with it.

I thought MMF couldn't go beyond 2G, but could well be wrong on this.

What is in this 4G file???

BTW I haven't used MMF, but I am very interested in this technique and keep reading all I can find about it. I'll be very interested in your experience. Doesn't seem to me that many people are using MMF and it looks so simple and elegant, and fast.

Neville Franks, Author of ED for Windows. www.getsoft.com

The data is historic market data for 5 years. Depending on the number of symbols that will be there, this could be a large file. Now, the mapping will be done only on certain parts of the file, depending on the user query. There will be a small part of the file, that will be updated from real-time market data. I can maintain that part of the file always in memory and do thread sync and stuff. Actually I already have code that has a limit of 2 GB as you said (the largest that can be mapped now). But, if I change that to mapping parts of the file, then there could potentially be a number of map/unmap going on. Hence before I went ahead with this, I just wanted to know whether anyone here has idea about the overhead involved in this.

My logic was: Even if a map existed, if the data has not been queried before, it would not be in memory. So, the overhead is the actual creation of the map. But, when I unmap (the case being the data requested is not in the current map), I assume that the OS unloads the memory related to that part (although I am not sure - it could be delayed too.)

I was not planning to map and unmap. The process would be to maintain a map until there is a query that requires me to map another part.

Thomas

modified 29-Aug-18 21:01pm.

I don't know precisely what happens when you map/unmap but my guess is that for map nothing would happen, and for unmap any writes would be delayed. Why not write some test code and then profile it.

This is some info on MMF I've been collecting.

--------------------------------------------------------------------------------
Title: Tuesday, 20 August 2002 9:34:05 PM C/C++ Users Journal Web Site
Text: The Standard Librarian: File-Based Containers
Matt Austern
Looking for a way to create a file-based container? You might need to look outside
the Standard C++ library, and memory mapping may be the answer.
Comment: STL style container using MMF. Unix implementation.
From: http://www.cuj.com/experts/1907/austern.htm?topic=experts

--------------------------------------------------------------------------------
Title: Tuesday, 20 August 2002 9:30:39 PM Managing Memory-Mapped Files in Win32
Text: Managing Memory-Mapped Files in Win32
Comment: Good MS article on MMF
From:
http://msdn.microsoft.com/library/en-us/dngenlib/html/msdn_manamemo.asp?frame=true

File Mapping:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/base/file_mapping.asp
--------------------------------------------------------------------------------
Title: Sunday, 18 August 2002 7:40:45 PM SourceForge.net: Project Info -
Common C++ Libraries
Text: The Common C++ project has been merged with APE, and the combined project
now uses the Common C++ name. Common C++ offers a highly portable application
framework for threading, sockets, realtime network streaming, persistance, and file
access.
Comment: Includes MMF code
From: http://sourceforge.net/projects/cplusplus/

--------------------------------------------------------------------------------
Title: Sunday, 14 July 2002 9:10:26 AM Reliquary Class Library
Text: FileMaps use the operating system's caching mechanism to map a file to a
section of virtual memory. Applications access the memory, while the operating
system maintains the file.
Comment: Part of Reliquary Library
From:
http://www.fidnet.com/~bbreeden/reliquary/classlib/filemap.htm#FileMap_class

--------------------------------------------------------------------------------
Title: Sunday, 7 July 2002 9:36:54 AM System Abstraction Layer
Text: Mapped on memory file provides direct access to the file data using
virtual memory mechanism. That is the most efficient way to access file, because it
requires no context switching and data copying. This class provides all the methods
from os_file class and has one additional method get_mmap_addr()
Comment:
From: http://www.garret.ru/~knizhnik/SAL/ReadMe.htm#mmap_file

--------------------------------------------------------------------------------

Neville Franks, Author of ED for Windows. www.getsoft.com

I did a sample profiling program:

The results:

Scenario a: [map - update - unmap] in a loop - 60 us per iteration
Scenario b: map - [update in a loop] - unmap - 40 ns per update

If the update is done on two memory locations in the view more than 4 KB apart, the time in the first case goes up to 80 us.

To confirm this, I added a FlushViewofFile in the scenario b loop and the time was 11 ms!.

Now I have to draw my conclusions.

- mapping and unmapping are not that bad, if they are inevitable.
- if you can map the whole file to memory, then do it. it gives best performance
- Limit the number of FlushViewofFile calls to what is absolutely necessary.

Thomas

modified 29-Aug-18 21:01pm.

Hi Thomas, Thanks for that - very interesting, and good to boot. My main interest in MMF is in database code (BTree etc.) where instead of managing my own cache (which I do now), get rid of that and use MMF. This greatly simplifies my code and lets the OS cache look after caching instead of me. It also skips having to move data back and forth between my app<->cache<->disk file, which should also help improve performance, even if just by a small amount.

The main issue is whose cache provides optimal performance. I've heard and read that for some large database/file systems using your own caching can actually hinder performance. All very interesting stuff indeed.

Neville Franks, Author of ED for Windows. www.getsoft.com

Hi All,
I need to pass a parameter to a method which takes the value as LPCWSTR.But the data is in the simple character string char[10].How do I convert the data type by type casting.
Thanks
Abhishek.

Learning is a never ending process of Life.

Simple typecasting won't work. You can use:

USES_CONVERSION;

CallSomeMethodThatAcceptsWideString( A2W( pszSomeCharArray ) );

Wenn ist das Nunstück git und Slotermeyer? Ja! Beierhund das oder die Flipperwaldt gersput!