I have an Access database and a table. One of the columns is a memo
type. If I execute a 'select * from table', all the rows are
retrieved but the columns after the memo column have null data.
If I move the memo column to the end in the structure and execute the same SQL the data
is retrieved propery.
Also if I specify the column name by itself (E.g. select phone from
table' ), the data is also retrieved.
Has anyone seen this kind of problem with any other databases where a
memo makes all the following columns null in the row cursor?
Thanks
Weird problem with PDO/ODBC and MS Access
Moderator: General Moderators
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Thanks for responding.Everah wrote:What happens when you select all the field names by name (longer version of 'SELECT *')?
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
Code: Select all
<?php
$tbl='tblNotes';
$user='';
$pass='';
$conn = "DSN=mailing;Driver=Microsoft Access Driver";
try {
$dbh = new PDO("odbc:$conn", $user, $pass);
// prints nulls after NOTES
$cursor=$dbh->query("SELECT * from $tbl order by LastName");
foreach ($cursor as $row) {
$row=array_change_key_case($row, 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("SELECT LastName, notes, addr, zip from $tbl order by LastName");
foreach ($cursor1 as $row) {
$row=array_change_key_case($row, 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("SELECT LastName, addr, zip, notes from $tbl order by LastName");
foreach ($cursor2 as $row) {
$row=array_change_key_case($row, CASE_UPPER);
echo $row['LASTNAME'] . '|';
echo $row['NOTES'] . '|';
echo $row['ADDR'] . '|';
echo $row['ZIP'] . '|';
echo "\n";
}
} catch (PDOException $e) {
echo $e->getMessage();
}
$dbh = null;
?>$cursor
doe|mm|||
smith|xx|||
$cursor1
doe|mm|||
smith|xx|||
$cursor2
doe|mm|120 main street|10006|
smith|xx|320 bway|10007|
I did another interesting test by adding a NOTES2 memo column. It seems all memo columns print always no matter where they are in the fields list of the select statement. I guess this is good for a workaround unless you have a table with many fields which must be listed in the select. PIA.Everah wrote:What happens when you select all the field names by name (longer version of 'SELECT *')?
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
I suppose you could report to the PHP bug tracker.