?retrieve large data from mssql stored proc in PHP

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 2005 Forums

Transact-SQL (2005)

?retrieve large data from mssql stored proc in PHP

Author

Topic

prabhatkr
Starting Member

6 Posts

Posted - 2008-08-10 : 03:55:52

I have a table with columns(uid &xmlfile). I have a stored procedure xquery to retrieve a part of xmlfile.
The output parameter is nvarchar(max) type. But my file size is more than 255 chars( approx 2000 chars ).
How could I make this possible? Please help.

ALTER PROCEDURE dbo.getsome
        (
        @room varchar(80),
        @lm int,
        @usid int,
        @retxml nvarchar(max) OUTPUT
        )
 
AS
 
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_PADDING ON
 
 
        SET @retxml =convert(nvarchar(max),( SELECT 
        ((chat.query('(/sroot[1]/message[@id > sql:variable("@lm")])')))  
             FROM mdb WHERE url=@room))
             
        
        RETURN
 
 
///////////////////php code below////////////////
global $$link;
                                $l="";
                                  $stmt=mssql_init("getchat",$$link ); $i=1;
                                mssql_bind($stmt, "@room",    $url,    SQLVARCHAR,    FALSE);
                                mssql_bind($stmt, "@usid",   $usid,    SQLINT4,    FALSE);
                                mssql_bind($stmt, "@lm",  $last,  SQLINT4, FALSE);
                                mssql_bind($stmt, "@retxml", $l,  SQLVARCHAR, TRUE, FALSE,400); 
                                
                                $result = mssql_execute($stmt);

pk from Asia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-10 : 04:45:50

are you asking about returning xml value in appln?

prabhatkr
Starting Member

6 Posts

Posted - 2008-08-10 : 04:57:53

yes Visakh. i want to return a portion of xml file as xml from stored proc.
I am usinf php. the output of sp could be sqlvarchar with max size of 255( as far as i know). So if return is more than 255, it is trimmed and xml fails.

Could you please help me?

pk from Asia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-10 : 05:42:44

try using SQLTEXT type

prabhatkr
Starting Member

6 Posts

Posted - 2008-08-10 : 06:01:57

i replaced 2nd last line of php as
mssql_bind($stmt, "@retxml", $l, SQLTEXT, TRUE);

But the error comes as it is depricated.

pk from Asia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources