Problem saving text to DB

6 Apr 2006 10:07 AM

Griff

Hi

I have a table that has a field of type TEXT [text, length=16, allow nulls]

I have a stored procedure that receives a parameter of type text and simply
inserts this data [insert myTable (myField) value (myTextData)]

Now the whole thing works fine, except when the data (which happens to be
XML) is over ~ 1000 characters.

The TEXT datatype can obviously hold more data than 1000 characters, so just
wondering why it's failing.

I'm using ASP (classic) connecting to a remote SQL server using ADO. Just
wondering whether it's a limit in ADO?

Thanks for any suggestions

Griff

6 Apr 2006 10:17 AM

ML

How did you discover the limit to character lenght? Are all the parameters
(variables) declared to support lengths of up to 2GB of data (or whatever
you've decided the maximum should be)?

ML

---
http://milambda.blogspot.com/

6 Apr 2006 11:46 AM

Griff

Hi ML

In the ASP code, I simply have the following code:
--------------
   Set obj_recordset = CreateObject("ADODB.Recordset")
       With obj_recordset
        ' Set RS's parameters
        .ActiveConnection = obj_connection
        .CursorLocation = 3 'adUseClient
        .CursorType = 3 'adOpenStatic
        .LockType = 4 'adLockBatchOptimistic

        ' Query the database
        .Open str_sql_query

        'make the recordset disconnected to free resources
        .ActiveConnection = Nothing
   End With
--------------

The str_sql_query is passed into this function and is simply of the form:
str_sql_query = "myStoredProcName 'string', int, int, 'text'

In this case, 'text' = '<xml>.............</xml>'

As for the length....

Well, the stored procedure ran without creating any errors and all the input
parameters seemed to do in okay, except for the XML document.

I wondered whether it might be a dodgy character, but it worked if I split
the TEXT in half and tried posting the first 500 characters. I incremented
this by 100 characters with each test and it keep working until about the
1000 character mark.

I investigated the data in this region and there are no "dodgy" characters
that I could see.....so assumed that it's purely a length issue.

Griff

Show quoteHide quote

"ML" <M*@discussions.microsoft.com> wrote in message
news:AE849E57-E07E-4D0E-BC43-C40AE6FE81F8@microsoft.com...
> How did you discover the limit to character lenght? Are all the parameters
> (variables) declared to support lengths of up to 2GB of data (or whatever
> you've decided the maximum should be)?
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

7 Apr 2006 10:05 AM

Daniel Crichton

Griff wrote on Thu, 6 Apr 2006 12:46:20 +0100:

Show quoteHide quote

> Hi ML
>
> In the ASP code, I simply have the following code:
> --------------
>    Set obj_recordset = CreateObject("ADODB.Recordset")
>        With obj_recordset
>         ' Set RS's parameters
>         .ActiveConnection = obj_connection
>         .CursorLocation = 3 'adUseClient
>         .CursorType = 3 'adOpenStatic
>         .LockType = 4 'adLockBatchOptimistic
>
>         ' Query the database
>         .Open str_sql_query
>
>         'make the recordset disconnected to free resources
>         .ActiveConnection = Nothing
>    End With
> --------------
>
> The str_sql_query is passed into this function and is simply of the form:
> str_sql_query = "myStoredProcName 'string', int, int, 'text'
>
> In this case, 'text' = '<xml>.............</xml>'
>
> As for the length....
>
> Well, the stored procedure ran without creating any errors and all the
> input parameters seemed to do in okay, except for the XML document.
>
> I wondered whether it might be a dodgy character, but it worked if I split
> the TEXT in half and tried posting the first 500 characters. I
> incremented this by 100 characters with each test and it keep working
> until about the 1000 character mark.
>
> I investigated the data in this region and there are no "dodgy" characters
> that I could see.....so assumed that it's purely a length issue.

Have you tried using a command object? eg.:

Set obj_command = Server.CreateObject("ADODB.Command")
With obj_command
    .ActiveConnection = obj_connection
    .CommandText = "myStoredProcName"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh    'not great for performance, should use
CreateParameter here to create each in turn
    .Parameters(0).Value = "string"
    .Parameters(1).Value = int
    .Parameters(2).Value = int
    .Parameters(3).Value = "<xml> ....</xml>"
    .Execute
End With

This also adds additional protection in case of SQL injection attacks - for
instance, if your XML string included something like

'; DELETE FROM Table ; SELECT '

in it, then the SQL might still be valid and yet you'd end up with Table
being empty :\

Dan