php-pdo backend MYSQL syntax errors

[GoogleCodeExporter]

What steps will reproduce the problem?
1. click save/load
2. choose php-pdo backend
3. click import from DB
4. Or load the php-dbo/index.php directly with ?action=import&database=northwind

What is the expected output? What do you see instead?
Steps 1-3:
Expected output would be the er diagram.  Instead an error message is presented 
in the input/output textarea:
Server response: Internal Server Error

Step 4:
Expected output would be the database diagram presented in xml data.  Instead a 
mysql syntax error is thrown:

Problem 1:
42000:1064
You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'FROM `TABLES`
                WHERE
                    `TABLE_SCHEMA` = 'coreda' at line 4
                SELECT
                    `TABLE_NAME` as `name`,
                    `TABLE_COMMENT` as `comment`,
                FROM `TABLES`
                WHERE
                    `TABLE_SCHEMA` = ?

C:\wamp\www\wwwsqldesigner-2.7\backend\php-pdo\index.php:167 
BackendPhpPdo::getException
C:\wamp\www\wwwsqldesigner-2.7\backend\php-pdo\LayerMYSQL.php:76 
BackendPhpPdo->execute
user_func:0 LayerMYSQL::Tables
C:\wamp\www\wwwsqldesigner-2.7\backend\php-pdo\index.php:153 
call_user_func_array
C:\wamp\www\wwwsqldesigner-2.7\backend\php-pdo\index.php:279 
BackendPhpPdo->getLayer
C:\wamp\www\wwwsqldesigner-2.7\backend\php-pdo\index.php:375 
BackendPhpPdo::buildXML
C:\wamp\www\wwwsqldesigner-2.7\backend\php-pdo\index.php:399 
BackendPhpPdo::actionImport
C:\wamp\www\wwwsqldesigner-2.7\backend\php-pdo\index.php:415 
BackendPhpPdo::controler

Problem 2:
42000:1064
You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near '`NON_UNIQUE` as 
`non_unique`
                FROM `STATISTICS`
                W' at line 6
                SELECT
                    `TABLE_NAME` as `table`,
                    `INDEX_NAME` as `name`,
                    `INDEX_TYPE` as `type`,
                    `COLUMN_NAME` as `column`
                    `NON_UNIQUE` as `non_unique`
                FROM `STATISTICS`
                WHERE
                    `TABLE_SCHEMA` = ?
                ORDER BY `SEQ_IN_INDEX`

C:\wamp\www\wwwsqldesigner-2.7\backend\php-pdo\index.php:167 
BackendPhpPdo::getException
C:\wamp\www\wwwsqldesigner-2.7\backend\php-pdo\LayerMYSQL.php:159 
BackendPhpPdo->execute
user_func:0 LayerMYSQL::Keys
C:\wamp\www\wwwsqldesigner-2.7\backend\php-pdo\index.php:153 
call_user_func_array
C:\wamp\www\wwwsqldesigner-2.7\backend\php-pdo\index.php:303 
BackendPhpPdo->getLayer
C:\wamp\www\wwwsqldesigner-2.7\backend\php-pdo\index.php:375 
BackendPhpPdo::buildXML
C:\wamp\www\wwwsqldesigner-2.7\backend\php-pdo\index.php:399 
BackendPhpPdo::actionImport
C:\wamp\www\wwwsqldesigner-2.7\backend\php-pdo\index.php:415 
BackendPhpPdo::controler

What version of the product are you using? On what operating system?
Version 2.7
Windows 7
WAMP stack

Please provide any additional information below.
LayerMYSQL.php has two typos causing these issues:

Problem 1:
public static function Tables() {
...
line 71:  '.self::protect('TABLE_COMMENT').' as '.self::protect('comment').', 
<-- remove trailing comma
...
}

Fix 1:
public static function Tables() {
...
$req = '
SELECT
'.self::protect('TABLE_NAME').' as '.self::protect('name').',
'.self::protect('TABLE_COMMENT').' as '.self::protect('comment').'
FROM '.self::protect('TABLES').'
WHERE
'.self::protect('TABLE_SCHEMA').' = ?
';
...
}

Problem 2:
public static function Keys($table) {
...
line 152: '.self::protect('COLUMN_NAME').' as '.self::protect('column').' <-- 
Add trailing comma
...
}

Fix 2:
public static function Keys($table) {
...
$req = '
SELECT
'.self::protect('TABLE_NAME').' as '.self::protect('table').',
'.self::protect('INDEX_NAME').' as '.self::protect('name').',
'.self::protect('INDEX_TYPE').' as '.self::protect('type').',
'.self::protect('COLUMN_NAME').' as '.self::protect('column').',
'.self::protect('NON_UNIQUE').' as '.self::protect('non_unique').'
FROM '.self::protect('STATISTICS').'
WHERE
'.self::protect('TABLE_SCHEMA').' = ?
ORDER BY '.self::protect('SEQ_IN_INDEX').'
';
...
}

Making these 2 changes fixed the issue for me.

Great tool!
Thanks!

Original issue reported on code.google.com by jeremy.s...@gmail.com on 29 Oct 2013 at 2:30

[GoogleCodeExporter]

Original comment by ondrej.zara on 31 Oct 2013 at 5:03

• Changed state: Accepted
• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

Shall be fixed in 
http://code.google.com/p/wwwsqldesigner/source/detail?r=6706800e25899a71c86796f5
f48bbf5262035f21. Can you please confirm?

Original comment by ondrej.zara on 31 Oct 2013 at 5:21

• Changed state: Fixed
• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

Looks good!  Thanks for fixing this so quickly!

Original comment by jeremy.s...@gmail.com on 4 Nov 2013 at 5:04

• Added labels: ****
• Removed labels: ****