I maintain a legacy application that runs on PHP/5.2.6 under Windows Server 2003 and connects to a remote Oracle 10g server through ODBC. I'm writing a new feature that needs a CLOB column so it can store arbitrarily large texts but I can't find the way to manipulate it via good old ODBC functions.
For writing, I've tried this approach:
<?php
$sql = 'INSERT INTO FOO (FOO_ID, FOO_CLOB) VALUES (?, ?)';
$stmt = odbc_prepare($conn, $sql);
$res = odbc_execute($stmt, array(1, 'Very large string'));
?>
But as soon as the string is larger than 4,000 bytes:
SQL error: [Microsoft][ODBC driver for Oracle]Error en el parámetro 2: Datos truncados, SQL state 01004 in SQLExecute
... which roughly means "Truncated data". I have a similar problem when reading: it works fine until I receive more than 4,000 bytes.
Is it possible to manipulate a CLOB column with more than 4,000 bytes using PHP ODBC functions?
(My alternative is to store stuff in files and only save the file path on DB but I'd like to avoid that if possible.)
Update: Following Justin Cave's advice, I've dumped Microsoft's ODBC Driver in favour of Oracle's and my code inserts large strings without changes. My question now is how to read the CLOB column. Reading it as-is:
$sql = 'SELECT FOO_ID, FOO_CLOB FROM FOO';
$res = odbc_exec($conn, $sql);
while($row = odbc_fetch_array($res)){
// ...
}
... triggers this when running odbc_fetch_array():
ORA-01861: literal does not match format string
