Weird problem with PDO/ODBC and MS Access

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
imok
Forum Newbie
Posts: 6
Joined: Tue Jun 13, 2006 12:41 pm

Weird problem with PDO/ODBC and MS Access

Post by imok »

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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

What happens when you select all the field names by name (longer version of 'SELECT *')?
imok
Forum Newbie
Posts: 6
Joined: Tue Jun 13, 2006 12:41 pm

Post by imok »

Everah wrote:What happens when you select all the field names by name (longer version of 'SELECT *')?
Thanks for responding.
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;
?>
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|
imok
Forum Newbie
Posts: 6
Joined: Tue Jun 13, 2006 12:41 pm

Post by imok »

Everah wrote:What happens when you select all the field names by name (longer version of 'SELECT *')?
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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

That is certainly an interesting problem. I have never encountered that before. Perhaps it may be a glitch in PDO's handling of that type of query with Access.
imok
Forum Newbie
Posts: 6
Joined: Tue Jun 13, 2006 12:41 pm

Post by imok »

Everah wrote:That is certainly an interesting problem. I have never encountered that before. Perhaps it may be a glitch in PDO's handling of that type of query with Access.
Who do I report problems to? Maybe it can be fixed in a future release.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I suppose you could report to the PHP bug tracker.
Post Reply