Calling Stored Procedures from CRecordset

Pinker Michael -- PinkerM@logica.com
Friday, September 06, 1996


Hi

Environment: Windows 95, MSVC 4.0

I am trying to call a store procedure from my app which has one output 
parameter
and no returned result set.

The MFC documentation says use CDatabase::ExecuteSQL for stored procedures
that don't return result set but i need to return parameter from the stored 
proc, so I
used a CRecordset instead. This works fine - the parameter is passed and
returned fine - apart from a message box "No columns were bound prior to 
calling SQLExtendedFetch".
I've set the number of fields to 0 but it still attempts to make a call to 
extended fetch.
I've looked at the help and MSDN but can't find anything.

I could get rid of the message box by returning a dummy result set but i 
would like to
do it without.

Any ideas/articles??


cheers


mike

---

John Ferguson -- johnf@uvsg.com
Saturday, September 07, 1996

I have looked at this recently. One of the great things about the MFC
classes, is the well thought
out design that allows you to fall back to the SDK level.

In my application, I use the CDatbase class for my connection, and
CRecordset classes for almost all of my data retrieval. But, like you, I
did run into a couple of situations that the MFC database classes don't
yet support.  Catching a return value from a STP, for example; or the
one you gave.

solution...
Use open CDatabase object's data members, in native odbc2.0 sdk calls.
It works great.

I've also used this technique to return a single row from a table,
without having to set up a recordset class.

hope this helps
johnf

>I am trying to call a store procedure from my app which has one output
>parameter
>and no returned result set.
>
>The MFC documentation says use CDatabase::ExecuteSQL for stored procedures
>that don't return result set but i need to return parameter from the stored
>proc, so I
>used a CRecordset instead. This works fine - the parameter is passed and
>returned fine - apart from a message box "No columns were bound prior to
>calling SQLExtendedFetch".
>I've set the number of fields to 0 but it still attempts to make a call to
>extended fetch.
>I've looked at the help and MSDN but can't find anything.
>
>I could get rid of the message box by returning a dummy result set but i
>would like to
>do it without.
>
>Any ideas/articles??
>
>
>cheers
>
>
>mike
>

---

Dan Kirby -- dkirby@accessone.com
Saturday, September 07, 1996

[Mini-digest: 2 responses]

Remember that if you are using the cursor library, you must have at least
one bound column.  That is what can cause the "No columns were bound prior
to calling SQLExtendedFetch" message. Also remember that using
CRecordset::Open() expects that you will have a returned resutlset. If you
won't have a returned resultset, use the ExecuteSQL.  In VC++ 4.2, there is
now a virtual function of CDatabase called BindParameters which gets passed
an HSTMT,  you can call SQLBindParameters in there if you would like to
have a return values/parameters in the SQL statement you are about to
perform.

-dan


----------
> From: Pinker Michael 
> To: 'smtp:mfc-l@netcom.com'
> Subject: Calling Stored Procedures from CRecordset
> Date: Friday, September 06, 1996 10:27 AM
> 
> 
> Hi
> 
> Environment: Windows 95, MSVC 4.0
> 
> I am trying to call a store procedure from my app which has one output 
> parameter
> and no returned result set.
> 
> The MFC documentation says use CDatabase::ExecuteSQL for stored
procedures
> that don't return result set but i need to return parameter from the
stored 
> proc, so I
> used a CRecordset instead. This works fine - the parameter is passed and
> returned fine - apart from a message box "No columns were bound prior to 
> calling SQLExtendedFetch".
> I've set the number of fields to 0 but it still attempts to make a call
to 
> extended fetch.
> I've looked at the help and MSDN but can't find anything.
> 
> I could get rid of the message box by returning a dummy result set but i 
> would like to
> do it without.
> 
> Any ideas/articles??
> 
> 
> cheers
> 
> 
> mike
-----From: Mike Geldens 

G'day Mike,

Here's how I do it:

In SQL Server...
/****** Object:  Stored Procedure dbo.GetScoreCount    Script Date: 10/07/96
20:40:17 ******/
CREATE PROCEDURE dbo.GetScoreCount
AS
    begin
	RETURN (SELECT COUNT(*) from Scores)
    end



In MSVC...

long CRallyScoreDoc::SQLGetScoreCount()
{
    RETCODE rc ;
    HDBC   hdbc ;
    HSTMT  hstmt ;
    long   lRowCount = 0 ;
	
    if ( !SQLCheckDBOpen())      // helper fn to ensure valid DB connection
	return 0 ;

    hdbc = m_dbRally.m_hdbc ;
	
    rc = ::SQLAllocStmt( hdbc, &hstmt ) ;
    switch ( rc )
    {
	case SQL_INVALID_HANDLE:
	case SQL_ERROR :
	    SQLErrorHandler( hdbc, hstmt ) ;
	    break ;
	case SQL_SUCCESS :
	case SQL_SUCCESS_WITH_INFO :
	{
            SWORD  sRowCount;
            SDWORD cbRowCount ;

            ::SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_SLONG,
SQL_INTEGER, 0, 0, &sRowCount, 0, &cbRowCount) ;
            rc = ::SQLExecDirect(hstmt, (unsigned char *)"{?=call
GetScoreCount}",SQL_NTS) ;

            if ( rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO )
                SQLErrorHandler( hdbc, hstmt ) ;
            else
                lRowCount = sRowCount ;

            rc = ::SQLFreeStmt(hstmt, SQL_DROP)	;
            break ;
        }
        default:            // ??
            break ;
	}
    return lRowCount ;
}

Hope this helps

Regards,


Mike
Mike Geldens               mgeldens@thehub.com.au
 cwinds Software - custom software for engineers

---

| Вернуться в корень Архива |