Professional Web Applications Themes

Advice Needed : Nasty Problem PHP/MS SQL Server and Varchar fields > 255 in Length - PHP Development

I am currently working on a PHP based website that needs to be able to draw from Oracle, MS SQL Server, MySQL and given time and demand other RDBMS. I took a lot of time and care creating a flexible and solid wrapper and am deep into coding. The only problem is a noticed VARCHAR fields being drawn from SQL Server 2000 are being truncated to 255 characters. I searched around php.net and found the following : Note to Win32 Users: Due to a limitation in the underlying API used by PHP (MS DbLib C API), the length of VARCHAR ...

  1. #1

    Default Advice Needed : Nasty Problem PHP/MS SQL Server and Varchar fields > 255 in Length


    I am currently working on a PHP based website that needs to be able to draw
    from Oracle, MS SQL Server, MySQL and given time and demand other RDBMS. I
    took a lot of time and care creating a flexible and solid wrapper and am
    deep into coding. The only problem is a noticed VARCHAR fields being drawn
    from SQL Server 2000 are being truncated to 255 characters.

    I searched around php.net and found the following :

    Note to Win32 Users: Due to a limitation in the underlying API used by PHP
    (MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
    need to store more data, use a TEXT field instead.
    (http://www.php.net/manual/en/function.mssql-field-length.php)

    The only problem with this advice is Text fields seem to be limited to 16
    characters in length, and I am having similar results in terms of truncation
    with other character based fields that can store more than 255 characters.

    I am using PHP 4.3.3 running on IIS using the php_mssql.dll extensions and
    the functions referenced here http://www.php.net/manual/en/ref.mssql.php.
    What are my options here? Has anybody worked around this or am I missing
    something obvious?

    James


    James Guest

  2. #2

    Default Re: Advice Needed : Nasty Problem PHP/MS SQL Server and Varchar fields > 255 in Length

    No, text fields can handle text string up to 2-Gig. The 16 bytes refers to
    the space used up by the string pointer inside the record. The problem with
    text/ntext is that they're slow, and you can't use them in ORDER BY or GROUP
    BY.

    The superlame way of retrieving more than 255 chars from a MSSQL varchar is
    to retrieve the field in multiple sections, using the SUBSTR() function,
    then concatenate them in PHP:

    SELECT SUBSTR(msg, 1, 255) AS msg_1, SUBSTR(msg, 256, 255) AS msg_2, ....

    $msg = $row['msg_1'] . $row['msg_2'] . $row['msg_3'] ...

    Uzytkownik "James" <com>
    napisal w wiadomosci news:40285e29$tds.net... 
    draw 

    drawn 
    truncation 


    Chung Guest

  3. #3

    Default Re: Advice Needed : Nasty Problem PHP/MS SQL Server and Varchar fields > 255 in Length

    James (com) writes: 

    As pointed out by Chung Leong, there is room for 2GB of data in text.
    The 16 bytes you see is just a pointer.

    However, text is fairly bersome and not really easy to use. I don't
    know anything about PHP, but it's apparent that PHP uses DB-Library to
    access SQL Server. And while I think this is a very nice API, Microsoft
    does not think so, and has not developed DB-Library since the release of
    SQL 6.5, which was seven years ago. The next version of SQL Server, slated
    for release this year, will accept connections from DB-Library, but will
    not come with files needed for development. You may not even get the DB-
    Library run-time DLL:s, but have to find them elsewhere.

    Thus, there are all reasons to look into alternative means of connections
    for PHP to MS SQL Server.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  4. Moderated Post

    Default Re: Advice Needed : Nasty Problem PHP/MS SQL Server and Varchar fields > 255 in Length

    Removed by Administrator
    James Guest
    Moderated Post

  5. #5

    Default Re: Advice Needed : Nasty Problem PHP/MS SQL Server and Varchar fields > 255 in Length

    Hi James!
    On Mon, 9 Feb 2004 22:39:20 -0600, "James"
    <com> wrote:
     

    If you are working with FreeTDS, the limitations is there. Use the
    TDSVER environment variable and set it to 7.0 or 2000 (check the last
    on www.freetds.org). SQL Server 6.5 had this limitation, but others
    don't have it. I use happily 8k varchars on SQL 7.0

    HTH, Jochen
    --
    Jochen Daum - Cabletalk Group Ltd.
    PHP DB Edit Toolkit -- PHP scripts for building
    database editing interfaces.
    http://sourceforge.net/projects/phpdbedittk/
    Jochen Guest

  6. #6

    Default Re: Advice Needed : Nasty Problem PHP/MS SQL Server and Varchar fields > 255 in Length

    Hi JAmes!
    On Tue, 10 Feb 2004 07:42:49 -0600, "James"
    <com> wrote:
     [/ref]
    >PHP 
    >>
    >> As pointed out by Chung Leong, there is room for 2GB of data in text.
    >> The 16 bytes you see is just a pointer.
    >>
    >> Thus, there are all reasons to look into alternative means of connections
    >> for PHP to MS SQL Server.
    >>[/ref]
    >
    >I believe the Linux PHP builds use that alternate means and the Warning
    >(given only on one page and not the main PHP MS SQL Server driver page as it
    >should have been) -- I'll be testing that later on today.
    >[/ref]
    The other thing you can do is:

    select cast(field as text) as field from table

    to get the complete content.


    HTH, Jochen
    --
    Jochen Daum - Cabletalk Group Ltd.
    PHP DB Edit Toolkit -- PHP scripts for building
    database editing interfaces.
    http://sourceforge.net/projects/phpdbedittk/
    Jochen Guest

  7. Moderated Post

    Default Re: Advice Needed : Nasty Problem PHP/MS SQL Server and Varchar fields > 255 in Length

    Removed by Administrator
    Erland Guest
    Moderated Post

  8. #8

    Default Re: Advice Needed : Nasty Problem PHP/MS SQL Server and Varchar fields > 255 in Length

    Holy crap. Why didn't I think of that...

    Uzytkownik "Jochen Daum" <co.nz> napisal w wiadomosci
    news:com... [/ref][/ref]
    by [/ref][/ref]
    you [/ref][/ref]
    to [/ref][/ref]
    connections 
    > >
    > >I believe the Linux PHP builds use that alternate means and the Warning
    > >(given only on one page and not the main PHP MS SQL Server driver page as[/ref][/ref]
    it 
    > The other thing you can do is:
    >
    > select cast(field as text) as field from table
    >
    > to get the complete content.
    >
    >
    > HTH, Jochen
    > --
    > Jochen Daum - Cabletalk Group Ltd.
    > PHP DB Edit Toolkit -- PHP scripts for building
    > database editing interfaces.
    > http://sourceforge.net/projects/phpdbedittk/[/ref]


    Chung Guest

Similar Threads

  1. Varchar Minimum Character Length
    By Katash in forum MySQL
    Replies: 1
    Last Post: April 5th, 03:32 PM
  2. Increasing the length of a varchar domain
    By Rich Doughty in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: January 31st, 12:26 PM
  3. Having a Problem Printing From InDesign. Advice Needed.
    By jamnau@adobeforums.com in forum Adobe Indesign Windows
    Replies: 3
    Last Post: July 19th, 03:31 AM
  4. #26197 [NEW]: varchar fields are not working correct
    By christian dot muench at euphamed dot de in forum PHP Development
    Replies: 0
    Last Post: November 11th, 08:13 AM
  5. Serious ibook problem - Advice needed
    By Ryan Newman in forum Mac Portable
    Replies: 3
    Last Post: September 5th, 02:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139