Querying MS Access MDB File from PHP over PDO

[da.eXecutoR]

Hey Access community!

I'm a php developer from switzerland and I'm currently running into a strange problem with an old access database.

The creator of the access database has choosen some column names which brings me now in troubles.

I've got names like "Verkauf-Durch", "Auftrags-Nr.", "Name der Firma" and so on.

Now when I'm trying this query in which way ever:

$sql = $db->con->query("SELECT Verkauf-Durch FROM T_Auftrag");
$sql = $db->con->query("SELECT \"Verkauf-Durch\" FROM T_Auftrag");
$sql = $db->con->query("SELECT [Verkauf-Durch] FROM T_Auftrag");
$sql = $db->con->query("SELECT `Verkauf-Durch` FROM T_Auftrag");

I'm always gettin some error messages. Mostly the look like:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[07002]: COUNT field incorrect: -3010 [Microsoft][ODBC Microsoft Access Driver] 1 Parameter wurden erwartet, aber es wurden zu wenig Parameter übergeben. (SQLExecute[-3010] at ext\pdo_odbc\odbc_stmt.c:254)' in C:\Websites\Systemmanagement\Website\comitis\system\services\nortonantivirus.php:34 Stack trace: #0 C:\Websites\Systemmanagement\Website\comitis\system\services\nortonantivirus.php(34): PDO->query('SELECT "Verkauf...') #1 {main} thrown in C:\Websites\Systemmanagement\Website\comitis\system\services\nortonantivirus.php on line 34

or...

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[07002]: COUNT field incorrect: -3010 [Microsoft][ODBC Microsoft Access Driver] 2 Parameter wurden erwartet, aber es wurden zu wenig Parameter übergeben. (SQLExecute[-3010] at ext\pdo_odbc\odbc_stmt.c:254)' in C:\Websites\Systemmanagement\Website\comitis\system\services\nortonantivirus.php:34 Stack trace: #0 C:\Websites\Systemmanagement\Website\comitis\system\services\nortonantivirus.php(34): PDO->query('SELECT Verkauf-...') #1 {main} thrown in C:\Websites\Systemmanagement\Website\comitis\system\services\nortonantivirus.php on line 34

...or even...

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: -1002 [Microsoft][ODBC Microsoft Access Driver] Unzulässiges Einklammern des Namens '[T_Auftrag.Verkauf-Durch]'. (SQLExecute[-1002] at ext\pdo_odbc\odbc_stmt.c:254)' in C:\Websites\Systemmanagement\Website\comitis\system\services\nortonantivirus.php:34 Stack trace: #0 C:\Websites\Systemmanagement\Website\comitis\system\services\nortonantivirus.php(34): PDO->query('SELECT [T_Auftr...') #1 {main} thrown in C:\Websites\Systemmanagement\Website\comitis\system\services\nortonantivirus.php on line 34

So please folks, can somebody help me outa here?

If I'm querying a column with a "normal" name like "Versandart" it works like a charm!

$sql = $db->con->query("SELECT Versandart FROM T_Auftrag");

Greetings

eXe

 

[spikepl]

I don't know how the syntax should be when going from PHP to Access (via ODBC, probably), for column names containing blanks or other nono-characters. In Access they should just be enclosed by square brackets. You could from PHP try to get the offending column names from T-Auftrag, to see what they look like from PHP.

http://forums.digitalpoint.com/showthread.php?t=932077

http://www.shotdev.com/php/php-access/php-access-odbc-get-field-name/

 

[da.eXecutoR]

@spikepl: you're a lifesaver!

I've found out that the column name is not what you see when you look into the table from access 2000:

So i tried all the time "Auftrags-Nr." instead of "Auftrags-Nr" and "Verkauf-Durch" instead of "Verkauft durch".

Thats really annoying!

Thank you 100 times! Your idea opened my eyes for this!

Wish you a nice and pleasant day!

Greetings from switzerland.

 

[spikepl]

Weeeee, glad it worked

 

[da.eXecutoR]

Funny thing, now even this works:

$sql = $db->con->query("Select kI.Anrede,
                              kI.[Name der Firma],
                              kI.Vorname,
                              kI.Nachname,
                              kI.[E-Mail],
                              aP.[Auftrags-Position],
                              aP.[Artikel-Nr],
                              aP.Positionstext,
                              aP.[Auftrags-Nr],
                              aI.[Auftrags-Nr],
                              aI.[Kunden-Nummer],
                              aI.Lieferdatum
                            FROM
                              [T_Kunden] AS kI
                            LEFT JOIN
                              ([T_Auftrag] AS aI
                               LEFT JOIN
                               [T_Auftragspositionen] AS aP
                               ON
                               aP.[Auftrags-Nr] = aI.[Auftrags-Nr]
                              )
                            ON
                              kI.[Kunden-Code] = aI.[Kunden-Nummer]
                            WHERE
                              aI.Lieferdatum = CDbl(#2011-05-05 00:00:00#)");

Thank you very much again Bra!