Calling Stored Procedure ??

Kim -- t9521601@munsu.ulsan.ac.kr
Wednesday, May 15, 1996

Environment : Visual C++ 2.0 ,
              WinNT 3.5 , 
              SQL Server 4.21,
              ODBC 2.0

I want call "Stored Procedure" of SQL server database in my program.
Stored Procedure is following in my database.
  ---------------------------------------------
  CREATE PROC SHIPPARAM (@SHIP CHAR(5)) AS
  SELECT SHIP,MIS,ABLK FROM ABLK
  WHERE ABLK.SHIP = @SHIP
  ORDER BY ABLK.ABLK
  ---------------------------------------------
then  
   SHIPPARAM H903
   
This procedure's result is following.
 SHIP  MIS   ABLK
------------------
 H903  1010  A110S
 H903  1020  A120P
 H903  1020  A120S

I want this result in my program.
my test program code is following. 

class SPset : public CRecordset
{
public:
	SPset(CDatabase* pDatabase = NULL);
	DECLARE_DYNAMIC(SPset)

// Field/Param Data
	
	//{{AFX_FIELD(SPset, CRecordset)
	CString	m_SHIP;
	long	m_MIS;
	CString	m_ABLK;
	//}}AFX_FIELD
	CString m_paramship;


	//{{AFX_VIRTUAL(SPset)
	public:
	virtual CString GetDefaultConnect();    // Default connection string
	virtual CString GetDefaultSQL();    // Default SQL for Recordset
	virtual void DoFieldExchange(CFieldExchange* pFX);  // RFX support
	//}}AFX_VIRTUAL
};

///////////////////////////////////////////////////////////
// spset.cpp : implementation file
//

#include "stdafx.h"
#include "testSP.h"
#include "spset.h"


IMPLEMENT_DYNAMIC(SPset, CRecordset)

SPset::SPset(CDatabase* pdb)
	: CRecordset(pdb)
{
	//{{AFX_FIELD_INIT(SPset)
	m_SHIP = _T("");
	m_MIS = 0;
	m_ABLK = _T("");
	m_nFields = 3;
	//}}AFX_FIELD_INIT
	m_nParams = 1;
	m_paramship = _T("");
}


CString SPset::GetDefaultConnect()
{
        // this is my datasource.
	return _T("ODBC;DSN=sql server data;");
}

CString SPset::GetDefaultSQL()
{
        //this is my default database table
	return _T("dbo.ABLK");
}

void SPset::DoFieldExchange(CFieldExchange* pFX)
{
	
	//{{AFX_FIELD_MAP(SPset)
	pFX->SetFieldType(CFieldExchange::outputColumn);
	RFX_Text(pFX, "SHIP", m_SHIP);
	RFX_Long(pFX, "MIS", m_MIS);
	RFX_Text(pFX, "ABLK", m_ABLK);
	//}}AFX_FIELD_MAP
	pFX->SetFieldType(CFieldExchange::param);
	RFX_Text(pFX,"@SHIP",m_paramship);
}

///////////////////////////////////////////////////////////
// testSdoc.cpp : implementation of the CtestSPDoc class
//

#include "stdafx.h"
#include "testSP.h"

#include "testSdoc.h"
#include "spset.h"


/////////////////////////////////////////////////////////////////////////////
// CtestSPDoc

IMPLEMENT_DYNCREATE(CtestSPDoc, CDocument)

BEGIN_MESSAGE_MAP(CtestSPDoc, CDocument)
	//{{AFX_MSG_MAP(CtestSPDoc)
		
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CtestSPDoc construction/destruction

CtestSPDoc::CtestSPDoc()
{
	// TODO: add one-time construction code here

}

CtestSPDoc::~CtestSPDoc()
{
}

BOOL CtestSPDoc::OnNewDocument()
{
	if (!CDocument::OnNewDocument())
		return FALSE;

	SPset db(NULL);
	CString strSQL = "{CALL SHIPPARAM (?)}";
	db.m_paramship = "H903";
	if(db.Open(CRecordset::snapshot,strSQL))
	db.MoveFirst();
	// TODO: add reinitialization code here
	// (SDI documents will reuse this document)

	return TRUE;
}

/////////////////////////////////////////////////////////////////////////////
// CtestSPDoc serialization

void CtestSPDoc::Serialize(CArchive& ar)
{
	if (ar.IsStoring())
	{
		// TODO: add storing code here
	}
	else
	{
		// TODO: add loading code here
	}
}

When running this program,get follwing error message.
 : Implicit conversion from datatype 'real' to 'char'
   is not allowed. Use the CONVERT function to run this query.

please. tell me the way.

                        Seon Geun Kim. in University of Ulsan.

---

Mike Blaszczak -- mikeblas@msn.com
Friday, May 17, 1996

----------
From: 	owner-mfc-l@netcom.com on behalf of Seon Geun, Kim
Sent: 	Tuesday, May 14, 1996 19:26

> Environment : Visual C++ 2.0 ,
>              WinNT 3.5 , 
>              SQL Server 4.21,
>              ODBC 2.0

> I want call "Stored Procedure" of SQL server database in my program.
> Stored Procedure is following in my database.

>  CREATE PROC SHIPPARAM (@SHIP CHAR(5)) AS

 > I want this result in my program.
 > my test program code is following. 

> CString SPset::GetDefaultSQL()
> {
>        //this is my default database table
>	return _T("dbo.ABLK");
> }


 > When running this program,get follwing error message.
 >  : Implicit conversion from datatype 'real' to 'char'
 >  is not allowed. Use the CONVERT function to run this query.

You've changed all the parameters in the DoFieldExchange(), but your 
GetDefaultSql() function just returns the name of a table. The columns you're 
binding don't match the columns from the table.  Since you said you want to 
execute the stored procedure, why are you returning a SQL Statement that 
retrieves all columns from a table?  You should edit your GetDefaultSql() 
function to return an ODBC command that will execute the stored procedure.

.B ekiM
TCHAR sz[] = _T("These words are my own.");

---

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