Extended Stored Procedure help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Extended Stored Procedure help

Author

Topic

dknibbs
Starting Member

4 Posts

Posted - 2003-06-26 : 05:33:52

I have a problem with an extended stored procedure in 2000 that works fine in 7.0.

The results of running both are below:
Thanks in advance
David

7.0
FileN,FileDte,FileSze
Rates20030623204637.txt,Mon Jun 23 20:46:37 2003,3675.0
Rates20030624204628.txt,Tue Jun 24 20:46:28 2003,3667.0
Rates20030625204633.txt,Wed Jun 25 20:46:33 2003,3671.0

2000
FileN,FileDte,FileSze
Rates20030620204632.txt ,Fri Jun 20 20:46:32 2003 ,3681.0
Rates20030621204632.txt ,Sat Jun 21 20:46:32 2003 ,3910.0
Rates20030622204632.txt ,Sun Jun 22 20:46:32 2003 ,3910.0
Rates20030623204637.txt ,Mon Jun 23 20:46:37 2003 ,3675.0
Rates20030624204628.txt ,Tue Jun 24 20:46:28 2003 ,3667.0

As you can see, the second one is padded with spaces. I can put an rtrim around it but there are quite a few and I'd like to fix it at source.

The code for the ESP is below:

#include <stdafx.h>
#include <stdio.h>
#include <io.h>
#include <time.h>
#include <Srv.h>

#define XP_NOERROR 0
#define XP_ERROR 1
#define MAXCOLNAME 100
#define MAXNAME 100
#define MAXTEXT 255

#define SRV_MAXERROR 20000
#define XP_PROCLIST_ERROR SRV_MAXERROR + 1
#define CMDSHELL_ERROR SRV_MAXERROR + 2
#define DISKLIST_ERROR SRV_MAXERROR + 3
#define SCAN_XBASE_ERROR SRV_MAXERROR + 4
#define TRACE_ERROR SRV_MAXERROR + 6

#ifdef __cplusplus
extern "C" {
#endif

RETCODE __declspec(dllexport) xp_SystemFile(SRV_PROC *srvproc);

#ifdef __cplusplus
}
#endif

class CDbString
{
public:
CDbString(SRV_PROC *srvproc, int paramNo)
{
int paramlength = srv_paramlen(srvproc, paramNo);
str = new DBCHAR[paramlength + 1];
if (str)
{
srv_bmove(srv_paramdata(srvproc, paramNo), str, paramlength);
str[paramlength] = '\0';
}
}

~CDbString()
{
if (str)
{
delete str;
}
}

operator char *()
{
if (str == 0)
throw "Could not allocate memory";
return str;
}

operator const char *() const
{
if (str == 0)
throw "Could not allocate memory";
return str;
}

private:
char *str;
};

RETCODE __declspec(dllexport) xp_SystemFile(SRV_PROC *srvproc)
{

struct _finddata_t c_file;
long hFile;

DBSMALLINT i = 0;
DBCHAR colname[MAXCOLNAME];
DBCHAR spFileName[MAXTEXT];
DBCHAR spFileTime[MAXTEXT];
DBCHAR spFileSize[MAXTEXT];

int paramnum;

// Check number of parameters
//
if ((paramnum = srv_rpcparams(srvproc)) != 1) {
// Send error message and return
//
srv_sendmsg(srvproc, SRV_MSG_ERROR, CMDSHELL_ERROR, SRV_INFO, (DBTINYINT)0,
NULL, 0, 0, "Error executing extended stored procedure: Invalid # of Parameters, Expecting FileName",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL mu(st complete the
// result set of an Extended Stored Procedure.
//
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}

/* Find first file in current directory */
if( (hFile = _findfirst( CDbString(srvproc, 1), &c_file )) == -1L )
srv_sendmsg(srvproc,SRV_MSG_INFO,0,(DBTINYINT)0,(DBTINYINT)0,NULL,0,0,"No files found!",SRV_NULLTERM);
//printf( "No *.c files in current directory!\n" );
else
{
//Set up the column names
wsprintf(colname, "FileName");
srv_describe(srvproc, 1, colname, SRV_NULLTERM, SRVCHAR, MAXNAME, SRVCHAR, 0, NULL);

wsprintf(colname, "Time");
srv_describe(srvproc, 2, colname, SRV_NULLTERM, SRVCHAR, MAXNAME, SRVCHAR, 0, NULL);

wsprintf(colname, "Size");
srv_describe(srvproc, 3, colname, SRV_NULLTERM, SRVCHAR, MAXTEXT, SRVCHAR, 0, NULL);

wsprintf(spFileName, "%-12s",c_file.name);
wsprintf(spFileTime, "%.24s",ctime( &( c_file.time_write ) ));
wsprintf(spFileSize, "%9ld",c_file.size);

srv_setcoldata(srvproc, 1, spFileName);
srv_setcollen(srvproc, 1, strlen(spFileName));

srv_setcoldata(srvproc, 2, spFileTime);
srv_setcollen(srvproc, 2, strlen(spFileTime));

srv_setcoldata(srvproc, 3, spFileSize);
srv_setcollen(srvproc, 3, strlen(spFileSize));

// Send the entire row
srv_sendrow(srvproc);

/* Find the rest of the files */
while( _findnext( hFile, &c_file ) == 0 )
{
wsprintf(spFileName, "%-12s",c_file.name);
wsprintf(spFileTime, "%.24s",ctime(&( c_file.time_write )));
wsprintf(spFileSize, "%9ld",c_file.size);

srv_setcoldata(srvproc, 1, spFileName);
srv_setcollen(srvproc, 1, strlen(spFileName));

srv_setcoldata(srvproc, 2, spFileTime);
srv_setcollen(srvproc, 2, strlen(spFileTime));

srv_setcoldata(srvproc, 3, spFileSize);
srv_setcollen(srvproc, 3, strlen(spFileSize));

// Send the entire row
srv_sendrow(srvproc);

}

// Now return the number of rows processed
srv_senddone(srvproc, SRV_DONE_MORE | SRV_DONE_COUNT, (DBUSMALLINT)0, (DBINT)i);

_findclose( hFile );
}
return XP_NOERROR ;
}

dknibbs
Starting Member

4 Posts

Posted - 2003-06-26 : 09:35:29

Sorry it's not clear for this but the results of the 2000 one are padded with white space so I need to use rtrim everywhere when I call it or fix the xp.

David

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-06-26 : 11:31:43

Maybe just subtract "1" from Column Lengths?

srv_setcoldata(srvproc, 1, spFileName);
srv_setcollen(srvproc, 1, strlen(spFileName) - 1);

srv_setcoldata(srvproc, 2, spFileTime);
srv_setcollen(srvproc, 2, strlen(spFileTime) - 1);

srv_setcoldata(srvproc, 3, spFileSize);
srv_setcollen(srvproc, 3, strlen(spFileSize) - 1);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources