limit on string length returned from stored proc

Lightspeed 4

Is there a limit on length of string that can be returned from a stored proc?

I have a stored proc that returns a large string value (actually xml blob) but it's being truncated at around the 2k mark.

Is this a lightspeed limitation? Can I configure a larger max length?

Thanks,

Barry

LightSpeed doesn't do anything to truncate strings, but some databases may require database-specific parameter types to handle very long strings. What database are you using? Is the large string a scalar return value, an out-parameter or a member of an entity?

I'm operating over a legacy DB which is out of my control. Can't modify the stored proc unfortunately. Goes something like this:

select 
    *
from
    [user]
For
    Xml Path('User'), Root('UserDetails')

Which returns a large XML blob:

<UserDetails>
  <User>
    <FirstName>Barry</FirstName>
    <Surname>Keenan</Surname>

    ...

  </User>
</UserDetails>

It looks like there's an underlying issue in ADO.NET where SqlCommand.ExecuteScalar() truncates XML data and you have to use ExecuteXmlReader() instead.

Since you're calling a sproc and (as far as I can tell) returning a scalar string rather than entities, your best bet may be to drop down to ADO.NET rather than trying to get LightSpeed to execute the query for you. You can do this by constructing a command using LightSpeedContext.DataProviderObjectFactory.CreateCommand, then calling PrepareCommand to associate it with the current unit of work's connection and transaction. Then you can call ExecuteXmlReader() on the command:

SqlCommand cmd = (SqlCommand)(context.DataProviderObjectFactory.CreateCommand());
cmd.CommandText = "GetUsersAsXml";
cmd.CommandType = CommandType.StoredProcedure;

_unitOfWork.PrepareCommand(cmd);

XmlReader reader = cmd.ExecuteXmlReader();

You can now work on the XmlReader directly, or load it into a XmlDocument or XDocument, or save it out to a TextWriter to get the XML text.

If I've misunderstood and you are trying to load the result into an entity rather than a scalar, let me know (maybe with some more info about how you want it to work) and we'll see if there's anything we can do to address this.