Weird problem with PDO/ODBC and Access

[Costas Menico]

I have an Access database and a table. One of the columns was a memo
field. If I execute a 'select * from table', all the rows would be
retrieved but the columns after the memo column had null data.

If I moved the memo field to the end and executed the same SQL the data
was retrieved propery.

Also if I specified the column name by itself (E.g. select phone from
table' ), the data was retrieved.

Has anyone seen this kind of problem with any other databases where a
memo field must be the last one?

[David Quinton]

Re: Weird problem with PDO/ODBC and Access

On 13 Aug 2006 12:35:17 -0700, "Costas Menico" <cmenico@gmail. com>
wrote:

>Has anyone seen this kind of problem with any other databases where a
>memo field must be the last one?

Could it be something to do with the length?
<http://www.php.net/manual/en/function.odbc-longreadlen.php refers.
--
Locate your Mobile phone: <http://www.bizorg.co.u k/news.html>
Great gifts: <http://www.ThisBritain .com/ASOS_popup.html >

[ImOk]

Re: Weird problem with PDO/ODBC and Access

It seems to happen with a short table too.
I am using PHP5.1 under Windows XP of course.

I made a small table and added 2 rows with info in all columns and here
is the layout:

LastName - text,
notes - memo,
addr - text,
zip - text

Here is the code. It fails in $cursor and $cursor1 but works when notes
is last in $cursor2.

<?php
$tbl='tblNotes' ;

$user='';
$pass='';
$conn = "DSN=mailing;Dr iver=Microsoft Access Driver";
try {
$dbh = new PDO("odbc:$conn ", $user, $pass);

// prints nulls after NOTES
$cursor=$dbh->query("SELEC T * from $tbl order by LastName");
foreach ($cursor as $row) {
$row=array_chan ge_key_case($ro w, CASE_UPPER);
echo $row['LASTNAME'] . '|';
echo $row['NOTES'] . '|';
echo $row['ADDR'] . '|';
echo $row['ZIP'] . '|';
echo "\n";
}
echo "\n";

// Also prints nulls after NOTES
$cursor1=$dbh->query("SELEC T LastName, notes, addr, zip from $tbl
order by LastName");
foreach ($cursor1 as $row) {
$row=array_chan ge_key_case($ro w, CASE_UPPER);
echo $row['LASTNAME'] . '|';
echo $row['NOTES'] . '|';
echo $row['ADDR'] . '|';
echo $row['ZIP'] . '|';
echo "\n";
}
echo "\n";

// prints all columns correctly because I moved notes to be last
$cursor2=$dbh->query("SELEC T LastName, addr, zip, notes from $tbl
order by LastName");
foreach ($cursor2 as $row) {
$row=array_chan ge_key_case($ro w, CASE_UPPER);
echo $row['LASTNAME'] . '|';
echo $row['NOTES'] . '|';
echo $row['ADDR'] . '|';
echo $row['ZIP'] . '|';
echo "\n";
}

} catch (PDOException $e) {

echo $e->getMessage() ;

}
$dbh = null;
?>


Result when you run this:
$cursor
doe|mm|||
smith|xx|||

$cursor1
doe|mm|||
smith|xx|||

$cursor2
doe|mm|120 main street|10006|
smith|xx|320 bway|10007|

[Chung Leong]

Re: Weird problem with PDO/ODBC and Access


Costas Menico wrote:

I have an Access database and a table. One of the columns was a memo
field. If I execute a 'select * from table', all the rows would be
retrieved but the columns after the memo column had null data.
>
If I moved the memo field to the end and executed the same SQL the data
was retrieved propery.
>
Also if I specified the column name by itself (E.g. select phone from
table' ), the data was retrieved.
>
Has anyone seen this kind of problem with any other databases where a
memo field must be the last one?

That's a known limitation of ODBC I think. Once the driver goes into
blob retrieval mode, it can't retrieve fixed width columns anymore.