PHP: Getting Text to Display from ODBC Database

If you have been frustrated by trying to output your long text data from a Microsoft SQL database via an ODBC connection you are not alone. The default setting for the odbc.defaultlrl (how odbc handles larger fields i.e. TEXT, IMAGE, NTEXT in MS SQL) is 4096 bytes, far to small, considering TEXT and IMAGE can store up to 2,147,483,647 bytes and NTEXT stores Unicode data up to 1,073,741,823 bytes.

To solve this problem you have to increase the size of the odbc.defaultlrl setting in the php.ini file. Located about half way down in the Module Settings section. You will see the chunk of code listed for ODBC. Change the odbc.defaultlrl = 4096 to a number great enough for need. I changed it to 60000 because I needed to work with documents that were approximately 4000 words in length. Here is the sample code from the php.ini file:

[ODBC]

;odbc.default_db = Not yet implemented

;odbc.default_user = Not yet implemented

;odbc.default_pw = Not yet implemented

; Allow or prevent persistent links.

odbc.allow_persistent = On

; Check that a connection is still valid before reuse.

odbc.check_persistent = On

; Maximum number of persistent links. -1 means no limit.

odbc.max_persistent = -1

; Maximum number of links (persistent + non-persistent). -1 means no limit.

odbc.max_links = -1

; Handling of LONG fields. Returns number of bytes to variables. 0 means

; passthru.

odbc.defaultlrl = 60000

; Handling of binary data. 0 means passthru, 1 return as is, 2 convert to char.

; See the documentation on odbc_binmode and odbc_longreadlen for an explanation

; of uodbc.defaultlrl and uodbc.defaultbinmode

odbc.defaultbinmode = 1