Active Record Select Truncating Text

[eluser]atomicx6637[/eluser]
I have a text field in a MSSQL database that is a varchar(5000). When the user inserts the data using activerecord via the webpage it inserts the data just fine, but when I'm querying the database the field data is getting truncated to about 253 chars.

I run the query in Microsoft Studio and I see all the data.

Any ideas?

Thanks
Troy

[eluser]DynamiteN[/eluser]
could be that im just remembering wrong but i think varchar only allow up to 255 ?
tried to use text instead that would be able to hold up to 5000 :/ ... GL

[eluser]flaky[/eluser]
@DynamiteN

In MSSQL varchar can go up to 8000, but if you want more you can use MAX value.
In MySQL you can use bigger values than 255 but depending on the value it will be converted to TEXT, MEDIUMTEXT etc.

The maximum storage size for VARCHAR(MAX) is 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes). The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. Since each character in a VARCHAR data type uses one byte, the maximum length for a VARCHAR(MAX) data type is 2,147,483,645.

The maximum storage size for NVARCHAR(MAX) is also 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes). The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. Since each Unicode character in an NVARCHAR data type uses two bytes, the maximum length for an NVARCHAR(MAX) data type is 1,073,741,822.

The maximum storage size for VARBINARY(MAX) is the same as the maximum storage size for VARCHAR(MAX) and NVARCHAR(MAX), which is 2^31-1 (2,147,483,647 bytes or 2GB - 1 bytes). The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length.

[eluser]atomicx6637[/eluser]
The storing of the varchar(5000) isn't a problem it is when I try to retrieve it from the database it is only bringing back 253 characters even through there is 600+ chars in the db.

[eluser]flaky[/eluser]
Can you put the code here

[eluser]atomicx6637[/eluser]

Code:

function loadById($applicationId)
    {
        $this->db->select('applicationId, name, softwareVersion, description, priorityId, applicationTypeId,
            businessUnitId, departmentId, developmentLanguage, version, notes, itUnitId, supportTypeId');
        $this->db->where('applicationId', $applicationId);
        $query = $this->db->get('Applications');    

        $app_info = new ApplicationInfo;
        foreach ($query->result() as $row)
        {
            $app_info->version = $row->version;
            $app_info->applicationId = $row->applicationId;
            $app_info->name = $row->name;
            $app_info->softwareVersion = $row->softwareVersion;
            $app_info->description = $row->description;
            $app_info->priorityId = $row->priorityId;
            $app_info->applicationTypeId = $row->applicationTypeId;
            $app_info->businessUnitId = $row->businessUnitId;
            $app_info->departmentId = $row->departmentId;
            $app_info->developmentLanguage = $row->developmentLanguage;
            $app_info->notes = $row->notes;
            $app_info->version = $row->version;
            $app_info->itUnitId = $row->itUnitId;
            $app_info->supportTypeId = $row->supportTypeId;
            
            print '$app_info->notes: ' . $row->notes . '<BR>';
        }
        return $app_info;
    }

[eluser]atomicx6637[/eluser]
I changed the type in the database from varchar(4000) to text and it is working now. I ran across this in the mssql_query docs.

http://php.net/manual/en/function.mssql-query.php

NOTE that the DB library that is internally driving these functions will NOT return more than 256 characters for data types varchar are char. After SQL Server 7 they allowed for lengths of up to 8000, but never updated the library. This drove me crazy for a day (all other connectivity worked from Java and C#, just not PHP). So, switch your data type to text if you wish to have lengths more than 256 characters.

I believe this is not techinically considered a bug; I checked the bug reports section and all reports on this issue were closed with a message indicating this.

[eluser]becky.resler[/eluser]
I just ran across this issue myself. I'm glad I found this thread so I didn't end up ripping all my hair out.

I just wanted to mention, you don't have to change the data type on the columns. You can cast the column as text when doing a select and the data won't get cut off.

For example, if I have a table structured as such:

Code:

CREATE TABLE dbo.mytable
(
    id          int IDENTITY(1,1) PRIMARY KEY NOT NULL,
    title       varchar(100) NOT NULL,
    description varchar(500) NULL
)

I can cast the description column and not lose any data.

Code:

$this->db->select("id, title, CAST(description AS text) AS description", true)->from("mytable");

I did this in my current project and it worked like a charm.