SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 ?retrieve large data from mssql stored proc in PHP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

prabhatkr
Starting Member

India
6 Posts

Posted - 08/10/2008 :  03:55:52  Show Profile  Reply with Quote
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

Edited by - prabhatkr on 08/10/2008 04:53:36

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/10/2008 :  04:45:50  Show Profile  Reply with Quote
are you asking about returning xml value in appln?
Go to Top of Page

prabhatkr
Starting Member

India
6 Posts

Posted - 08/10/2008 :  04:57:53  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/10/2008 :  05:42:44  Show Profile  Reply with Quote
try using SQLTEXT type
Go to Top of Page

prabhatkr
Starting Member

India
6 Posts

Posted - 08/10/2008 :  06:01:57  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000