extended stored proc trouble with srv_paramsetoutput

extended stored proc trouble with srv_paramsetoutput

Post by Aide » Wed, 04 Sep 2002 23:07:47



Hi,

I have written an extended stored proc: xp_OnDoSomething which can be called
from SQL:




My ex. stored proc is doing the necessary to generate a correct value for
Res, but I can't get the result into the output parameter - the important
parts of the code are:

PBYTE pUnencrypted=0;
DWORD dwUnencryptedLength=0;
...do something to populate pUnencrypted and get its length in
dwUnencryptedLength

long lRes = atol(reinterpret_cast<char*>(pUnencrypted)); // error checking
ommitted
//lRes  is the result I want to return, so I know it is correct here.

srv_describe(pSrvProc, 1, "Res", SRV_NULLTERM, SRV_TDS_INT4,
             dwUnencryptedLength, SRV_TDS_INT4 , dwUnencryptedLength,
&lRes);

srv_sendrow(pSrvProc) // error checking ommitted

// Set the output parameter
// THIS CALL FAILS EVERY TIME
if (FAIL == srv_paramsetoutput(pSrvProc, 1, pUnencrypted,
dwUnencryptedLength, FALSE))
{
        printError (pSrvProc, "srv_paramsetoutput failed...");
        return (XP_ERROR);

Quote:}

srv_senddone(pSrvProc, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 1);

If I comment out the srv_paramsetoutput call I see the correct value being

Any help gratefully received

Thanks,

Aiden

 
 
 

extended stored proc trouble with srv_paramsetoutput

Post by Gert E.R. Draper » Wed, 04 Sep 2002 23:49:56


I don't understand what you are trying to achieve, do you want an output
parameter or a result set?

An output parameter is simple, use something like this
BOOL CXpParameter::SetParam(INT nParam, LONG lLong)
{
#ifdef _DEBUG
   // In debug mode, make sure this really is an output parameter
   // and it is an integer column
   //
   assert(IsOutputParam(nParam));
   assert(IsInteger(nParam));
#endif

   RETCODE rc = srv_paramsetoutput(m_srvproc, nParam, (BYTE*)&lLong,
sizeof(LONG), FALSE);
   return (SUCCEED == rc);

Quote:}

If you want a result set you need to use srv_setcollen before each
srv_sendrow, after you use srv_describe to layout the structure per column
once.


Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright ? SQLDev.Net 1991-2002 All rights reserved.


> Hi,

> I have written an extended stored proc: xp_OnDoSomething which can be
called
> from SQL:




> My ex. stored proc is doing the necessary to generate a correct value for
> Res, but I can't get the result into the output parameter - the important
> parts of the code are:

> PBYTE pUnencrypted=0;
> DWORD dwUnencryptedLength=0;
> ...do something to populate pUnencrypted and get its length in
> dwUnencryptedLength

> long lRes = atol(reinterpret_cast<char*>(pUnencrypted)); // error checking
> ommitted
> //lRes  is the result I want to return, so I know it is correct here.

> srv_describe(pSrvProc, 1, "Res", SRV_NULLTERM, SRV_TDS_INT4,
>              dwUnencryptedLength, SRV_TDS_INT4 , dwUnencryptedLength,
> &lRes);

> srv_sendrow(pSrvProc) // error checking ommitted

> // Set the output parameter
> // THIS CALL FAILS EVERY TIME
> if (FAIL == srv_paramsetoutput(pSrvProc, 1, pUnencrypted,
> dwUnencryptedLength, FALSE))
> {
>         printError (pSrvProc, "srv_paramsetoutput failed...");
>         return (XP_ERROR);
> }
> srv_senddone(pSrvProc, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 1);

> If I comment out the srv_paramsetoutput call I see the correct value being

> Any help gratefully received

> Thanks,

> Aiden


 
 
 

extended stored proc trouble with srv_paramsetoutput

Post by Aide » Thu, 05 Sep 2002 00:03:27


You're quite right. That's me sorted - thanks very much.

Aiden



> I don't understand what you are trying to achieve, do you want an output
> parameter or a result set?

> An output parameter is simple, use something like this
> BOOL CXpParameter::SetParam(INT nParam, LONG lLong)
> {
> #ifdef _DEBUG
>    // In debug mode, make sure this really is an output parameter
>    // and it is an integer column
>    //
>    assert(IsOutputParam(nParam));
>    assert(IsInteger(nParam));
> #endif

>    RETCODE rc = srv_paramsetoutput(m_srvproc, nParam, (BYTE*)&lLong,
> sizeof(LONG), FALSE);
>    return (SUCCEED == rc);
> }

> If you want a result set you need to use srv_setcollen before each
> srv_sendrow, after you use srv_describe to layout the structure per column
> once.


> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2002 All rights reserved.



> > Hi,

> > I have written an extended stored proc: xp_OnDoSomething which can be
> called
> > from SQL:




> > My ex. stored proc is doing the necessary to generate a correct value
for
> > Res, but I can't get the result into the output parameter - the
important
> > parts of the code are:

> > PBYTE pUnencrypted=0;
> > DWORD dwUnencryptedLength=0;
> > ...do something to populate pUnencrypted and get its length in
> > dwUnencryptedLength

> > long lRes = atol(reinterpret_cast<char*>(pUnencrypted)); // error
checking
> > ommitted
> > //lRes  is the result I want to return, so I know it is correct here.

> > srv_describe(pSrvProc, 1, "Res", SRV_NULLTERM, SRV_TDS_INT4,
> >              dwUnencryptedLength, SRV_TDS_INT4 , dwUnencryptedLength,
> > &lRes);

> > srv_sendrow(pSrvProc) // error checking ommitted

> > // Set the output parameter
> > // THIS CALL FAILS EVERY TIME
> > if (FAIL == srv_paramsetoutput(pSrvProc, 1, pUnencrypted,
> > dwUnencryptedLength, FALSE))
> > {
> >         printError (pSrvProc, "srv_paramsetoutput failed...");
> >         return (XP_ERROR);
> > }
> > srv_senddone(pSrvProc, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 1);

> > If I comment out the srv_paramsetoutput call I see the correct value
being

> > Any help gratefully received

> > Thanks,

> > Aiden

 
 
 

1. Newbie: Trouble calling an extended stored proc

Hello

I've just created an extended stored procedure in a dll and added it to the
master database by selecting 'New Extended Stored Procedure'
My xp_func is now listed in the Extended Stored Proc list of the master
database.  Both Guest & public have EXECUTE permission for xp_myfunc.

My problem:  I wish to call the xp from an insertion trigger in another db.
I attempt to set the trigger which contains EXECUTE xp_myfunc, and Query
Analyser returns:

"Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'xp_myfunc'. The stored procedure will still
be created."

I assume that it cannot find the xp because it exists in another db.  I
tried using 'USE master' but with no luck, it says it is invalid to use this
in a Trigger or stored proc.

Can anyone help please.

Thanks

DP
Using SQLServer

2. MS SQL*Server DBAs Needed-U.S.-(Recruiter)

3. Extended SP's-srv_paramsetoutput -

4. Wait Type

5. Calling stored proc from extended stored procedure

6. Is it possible to prioritize clients for a MySQL server?

7. Extended Stored Procedure Trouble - Please Help!

8. solving xp_sendmail !! this is a nightmare ( followup )

9. Extended stored proc and parameter

10. Writing extended stored proc (xp_) for SQL 6.5

11. Executing an extended stored proc on a cluster

12. Extended stored proc. - sendmail

13. Extended Stored Proc: Get Value from WebService