May 6, 2002 at 7:47 am
I have written a Visual C++ program that accepts one or more strings, creates an Oracle clob and then calls an Oracle stored procedure to enqueue the clob into an Oracle Advanced Queue. Now someone at work wants me to turn this program into an Extended Stored Procedure. I am not sure if this is even possible. Does anyone have any suggestions on how this might be done?
Thanks,
Tom
May 6, 2002 at 8:20 am
It is possible. I think there is some sample code and headers that get installed if you choose the sample code option when you install SQL. The other option is to make it as a COM DLL that you can call using sp_oacreate and then create a wrapper standard proc around that.
Andy
May 6, 2002 at 7:26 pm
In Visual C++ when you create a new project you have an option call SQL Extended Stored Procedure or something to that effect that will create you a sample dll that you can alter to build yours. It has a specific set a calls you need to use. Then it is nothing more than adding the information for it in SQL Server.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 15, 2002 at 8:03 am
I upgraded to Visual Studio 6.0 Enterprise Edition and installed service pack 5 so I could run the Extended Stored Procedure wizard. I configured the project created by the wizard and got it to connect to the machine where SQL Server 7.0 is installed. The program the wizard created run and wrote it's output to the Visual Studio result window. Everything looked like it was setup correctly so I rewrote the proc.cpp program to make calls to my Oracle program that adds XML to an Advanced Queue. This new program compiles and links without error. I used the Visual Studio environment to register the Extended Stored Procedure and it looks like the registration works. I can see the procedure name in the data view window after I do the registration. When I try to call the procedure (from Visual Studio or the SQL Server Query Analyzer) I get the following error:
Cannot load the DLL xp_AQenq.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
I suspect that the procedure did not get registered properly but I can't find an explanation of the error or any thing that indicates what the problem is so I am not sure. Does anyone have any suggestions?
Thanks.
Tom
May 15, 2002 at 9:40 am
Have you tried regsvr32 -u to unregister and then regsvr32 to reregister? If so I am not sure, can you send me you code for you dll so I can take a closer look at it. (It should not affect it if you have to change usernames and passwords if any in it.)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 16, 2002 at 1:38 pm
-----------------------------------------------------------------------------------------------------------------
The error message I get is:
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL xp_AQenq.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
-------------------------------------------------------------------------------------------------------------------
I used the following sql to register the procedure (run from within Visual C++).
USE master
GO
EXEC sp_addextendedproc 'xp_AQenq', 'xp_AQenq.dll'
I have tried several registration methods but not the one you mention, I don't have direct access to the server.
--------------------------------------------------------------------------------------------------------------------
The program I am trying to register is as follows (I link this program with an Oracle obj file that defines the called routines in this code.)
#include <stdio.h>
#include <crtdbg.h>
#include "stdafx.h"
#include "UTProCenq.h"
#define RETCODEint
int statOUT;
#ifdef __cplusplus
extern "C" {
#endif
RETCODE __declspec(dllexport) xp_AQenq(char *strIN);
#ifdef __cplusplus
}
#endif
RETCODE __declspec(dllexport) xp_AQenq(char *strIN)
{
char connectUid[256];
/* The Oracle username, password and database to be used to connect
* to the Oracle Database. Hardcode to SQL Server version.
*/
#ifdef _DEBUG
_RPT0(_CRT_WARN, "Entering xp_AQenq.\n");
#endif
strcpy(connectUid,"scott/tiger");
/* Connect to Oracle Metabase server*/
statOUT= DBconnect(connectUid);if (statOUT!= EX_SUCCESS ) {
#ifdef _DEBUG
_RPT1(_CRT_WARN, "ProCenq main DBconnect failed %s. \n", connectUid);
#endif
strcpy(strIN, "ProCenq main DBconnect failed ");
strcat(strIN, connectUid);
return(statOUT);
}
/* Last buffer of data*/
statOUT= AppendToClob(strlen(strIN), 'y', strIN);
if (statOUT!= EX_SUCCESS ) {
#ifdef _DEBUG
_RPT0(_CRT_WARN, "ProCenq main ApendToClob Failed.\n");
#endif
strcpy (strIN,"ProCenq main ApendToClob Failed.");
enqRollback();
DBdisconnect();
return(statOUT);
}
/* Parse XML data in clob. */
statOUT= xmlParse();
if (statOUT!= EX_SUCCESS ) {
#ifdef _DEBUG
_RPT0(_CRT_WARN, "ProCenq XML parse of clob Failed.\n");
#endif
strcpy(strIN,"ProCenq XML parse of clob Failed.");
enqRollback();
DBdisconnect();
return(statOUT);
}
/* Enqueue Clob just finished. If enqueue is successful then
* commit the queue entry be for continuing. Also remove the
* update transaction for the originating database so they stay
* in sync.
*/
statOUT= enqueue();
if (statOUT== EX_SUCCESS ) {
CommitIt();
}else{
#ifdef _DEBUG
_RPT0(_CRT_WARN, "ProCenq enqueue of clob Failed.\n");
#endif
strcpy(strIN,"ProCenq enqueue of clob Failed.");
enqRollback();
DBdisconnect();
return(statOUT);
}
/* Free Oracle variables and close connection to the
* Oracle Metabase server after each call/
*/
DBdisconnect();
return statOUT;
}
-------------------------------------------------------------------------------------------------------
May 22, 2002 at 12:32 pm
Just wanted to update this post to say that I finally got the Extended Stored Procedure working.
Thanks for the help
Tom
May 23, 2002 at 5:12 am
What did you change? Sorry but I got busy and lost the post, but had looked and did not see anything right off.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 24, 2002 at 8:12 am
The Oracle path had not been set properly before the SQL Server was started so it could not find an oracle dll called by my program. Once this was fixed the program worked. I now have a Visual C++ project that contains the Extended Stored Procedure and the Oracle dll that it calls. It also contains several SQL programs that register, unregister and test (call) the Extended Stored Procedure. The output from the Extended Stored Procedure call is displayed in the Visual C++ result window. I also added a post-processing step that copies the new dll file to the mssql7/binn directory on the appropriate sever so that everything can be done from inside Visual C++.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply