Can anyone point to an example on the web or elsewhere of calling a stored
procedure from Visual C++ (ideally from an MFC perspective, but anything
would help)

If it makes a difference, it is a just a select procedure that takes
parameters for what to query and returns a table. Alternately, an example of
simply calling a query would help too. I have done all of this with Access
databases, but though the API's are similar, the same tricks do not seem to
work with SQL.

Thanks much in advance.

Hi

I am not sure about off the shelf examples but this walks you through it!

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore/html/_core_recordset.3a_.declaring_a_class_for_a_predefined_query_.28.odbc.29.asp

You may also want to read:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore98/html/_core_recordset.3a_.parameterizing_a_recordset_.28.odbc.29.asp

John

[quoted text, click to view]

"HumanJHawkins" <JHawkins@HumanitiesSoftware.Com> a écrit dans le message de
news: w049c.3025$lt2.1689@newsread1.news.pas.earthlink.net...
[quoted text, click to view]

Founded in the package dblib...

/***********************************************************************
Copyright (c) 2000, Microsoft Corporation
All Rights Reserved.
***********************************************************************/
/* ** The example uses the following stored procedure,
** named "rpctest", which it assumes is located in the
** user's default database. Before running this example,
** you must create "rpctest" in your default database.
**
** create procedure rpctest
** (@param1 int out,
** @param2 int out,
** @param3 int out,
** @param4 int)
** as
** begin
** select "rpctest is running."
** select @param1 = 11
** select @param2 = 22
** select @param3 = 33
** select @param1
**
** return 123
** end
**
*/

// This sample uses mixed mode security, other than Windows NT
Authentication,
// to establish connections. To use Windows NT Authentication, please use
// DBSETLSECURE to set the secure connection flag.
// Make the necessary changes to the hard-coded values, such as server
// name, user name and password.

#if defined(DBNTWIN32)
#include <windows.h>
#endif

#include <stdio.h>
#include <stdlib.h>
#include <sqlfront.h>
#include <sqldb.h>
#define FMTSTR "%-8.8s %-8.8s %-8.8s %-8.8s\n"
#define FMTSTR1 "%-8.8s %-8.8s %8.8ld %8.8ld\n"

/* Forward declarations of the error handler and message handler routines.
*/
int err_handler(DBPROCESS*, int, int, int, char*, char*);
int msg_handler(DBPROCESS*, DBINT, int, int, char*, char*, char*,
DBUSMALLINT);

void main()
{
LOGINREC *login;
DBPROCESS *dbproc;

int i;
int numrets;
DBINT param1 = 1;
DBINT param2 = 2;
DBINT param3 = 3;
DBINT param4 = 4;
RETCODE return_code;

/* Initialize private DB Library structures. */
dbinit();


/* Install the user-supplied error-handling and message-handling
* routines. They are defined at the bottom of this source file.
*/

dbmsghandle((DBMSGHANDLE_PROC)msg_handler);
dberrhandle((DBERRHANDLE_PROC)err_handler);

/* Allocate and initialize the LOGINREC structure to be used
* to open a connection to SQL Server.
*/

login = dblogin( );
DBSETLUSER(login, "user");
DBSETLPWD(login, "my_passwd");
DBSETLAPP(login, "rpcexample");
DBSETLVERSION(login, DBVER60);
// To use secure, or trusted, connection, uncomment the following line.
// DBSETLSECURE (login);

dbproc = dbopen(login, (char *)"my_server");

/* Make the rpc. */
if (dbrpcinit(dbproc, "rpctest", (DBSMALLINT)0) == FAIL)
{
printf("dbrpcinit failed.\n");
dbexit();
exit(ERREXIT);
}
if (dbrpcparam(dbproc, "@param1", (BYTE)DBRPCRETURN, SQLINT4,
-1, -1, (BYTE *)&param1)
== FAIL)
{
printf("dbrpcparam failed.\n");
dbexit();
exit(ERREXIT);
}

if (dbrpcparam(dbproc, "@param2", (BYTE)DBRPCRETURN, SQLINT4,
-1, -1, (BYTE *)&param2)
== FAIL)
{
printf("dbrpcparam failed.\n");
dbexit();
exit(ERREXIT);
}

if (dbrpcparam(dbproc, "@param3", (BYTE)DBRPCRETURN, SQLINT4,
-1, -1, (BYTE *)&param3)
== FAIL)
{
printf("dbrpcparam failed.\n");
dbexit();
exit(ERREXIT);
}

if (dbrpcparam(dbproc, "@param4", (BYTE)NULL, SQLINT4,
-1, -1, (BYTE *)&param4)
== FAIL)
{
printf("dbrpcparam failed.\n");
dbexit();
exit(ERREXIT);
}

if (dbrpcsend(dbproc) == FAIL)
{
printf("dbrpcsend failed.\n");
dbexit();
exit(ERREXIT);
}

if (dbsqlok(dbproc) == FAIL)
{
printf("dbsqlok failed.\n");
dbexit();
exit(ERREXIT);
}
while ((return_code = dbresults(dbproc)) != NO_MORE_RESULTS)
{
if (return_code == FAIL)
{
printf("dbresults failed.\n");
dbexit();
exit(ERREXIT);
}

/* Print any rows that may have been returned. */
dbprrow(dbproc);

/* Examine any return parameters that may have arrived. */

numrets = dbnumrets(dbproc);
printf("%d return values received.\n\n", numrets);

if (numrets != 0)
{
printf
(FMTSTR, "Name", "Type", "Length", "Value");
printf
(FMTSTR,
"------------", "------------",
"------------", "------------");

for (i = 1; i <= numrets; i++)
{
printf
(FMTSTR1, dbretname(dbproc, i),
dbprtype(dbrettype(dbproc, i)), dbretlen(dbproc, i),
*((DBINT *)(dbretdata(dbproc, i))));
}

}
if (dbhasretstat(dbproc))
printf("Return status = %ld\n", dbretstatus(dbproc));
else
printf("No return status for this command.\n");
}

dbexit();
}
int err_handler(dbproc, severity, dberr, oserr, dberrstr, oserrstr)
DBPROCESS *dbproc;
int severity;
int dberr;
int oserr;
char *dberrstr;
char *oserrstr;
{
if (dberrstr != NULL) printf("DB-Library error:\n\t%s\n", dberrstr);

if (oserr != DBNOERR)
printf("Operating-system error:\n\t%s\n", oserrstr);
if ((dbproc == NULL) || (DBDEAD(dbproc)))
return(INT_EXIT);
else
{

return(INT_CANCEL);
}
}

int msg_handler(dbproc, msgno, msgstate, severity, msgtext,
srvname, procname, line)
DBPROCESS *dbproc;
DBINT msgno;
int msgstate;
int severity;
char *msgtext;
char *srvname;
char *procname;
DBUSMALLINT line;

{
printf ("Msg %ld, Level %d, State %d\n",
msgno, severity, msgstate);

if (srvname != NULL)
printf ("Server '%s', ", srvname);
if (procname != NULL)
printf ("Procedure '%s', ", procname);
if (line != 0)
printf ("Line %d", line);

printf("\n\t%s\n", msgtext);

return(0);
}

--
Emmanuel

[posted and mailed]

elecoest (elecoest@wanadoo.fr) writes:
[quoted text, click to view]

No, no. no!

DB-Library is a deprecated interface. No, that is not my opinion. I like
it, but Microsoft does not agree with me, and they have not made any
development to DB-Library for the last seven years or so. With DB-Library
you don't get full supports for all datatypes in SQL Server.

Furthermore, next version of SQL Server will not ship with any files
to support DB-Library. It will still accept connection from DB-Library
components.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

HumanJHawkins (JHawkins@HumanitiesSoftware.Com) writes:
[quoted text, click to view]

Samples for both ODBC and SQLOLEDB available here:

http://www.microsoft.com/downloads/details.aspx?familyid=7824BA50-3E29-45CF-8C02-5597C014A707&displaylang=en

The text to go with the samples are in Books Online.

There are further samples the directory 80/Tools/DevTools/Samples of
your SQL Server installation.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

[quoted text, click to view]

So, I would like to develop a native access to a mssql server (through a
dll). What is the best way ? ADO ? ODBC ?

[quoted text, click to view]

Ouf ...

Emmanuel

elecoest (elecoest@wanadoo.fr) writes:
[quoted text, click to view]

Unless you have high requirements for performance, I say .Net. ADO .Net
has a much cleaner interface than any of the client libraries. I have
heard people saying that managed code may not be as fast as unmanaged
code.

Of course, if your DLL is to be used by unmanaged code, using .Net for
its implmentation would require you do write a COM-interop, and that may
not be that simple.

For implementation in unmanaged code, the choices would be ODBC, ADO
or direct access to SQLOLEDB depending on what you do. From what the
small sample I have from newsgroups, is that ODBC seems to be the interface
that most C++ programmers use.



--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at