php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #56378 PRAGMA table_info() results mangled
Submitted: 2005-04-21 16:25 UTC Modified: 2005-04-22 09:24 UTC
From: tom at supertom dot com Assigned:
Status: Closed Package: PDO_SQLITE (PECL)
PHP Version: 5.0.3 OS: Suse 7.1
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: tom at supertom dot com
New email:
PHP Version: OS:

 

 [2005-04-21 16:25 UTC] tom at supertom dot com
Description:
------------
I believe I have found a bug

PDO - 0.3
PDO_SQLITE - 0.3

If I retrieve the results of PRAGMA table_info('user_policy') with fetchAll, the type column in the array that is returned is mangled.  It appears to me that spaces are sucked out.

Notice the user_policy_no data type below, and the output of PHP's print_r on the array that is returned.  "INTEGER UNSIGNED" is turned into "INTEGERUNSIGNED"

Here is my SQL:

CREATE TABLE user_policy (
  user_policy_no INTEGER UNSIGNED NOT NULL ,
  name VARCHAR(80) NOT NULL,
  manager_zone_access_level CHAR(1) NULL,
  manager_active_yn CHAR(1) NOT NULL DEFAULT '1',
  manager_gui_access_level_lkup CHAR(1) NULL,
  active_yn CHAR(1) NULL,
  creation_dte DATETIME NULL,
  last_modified_dte DATETIME NULL,
  PRIMARY KEY(user_policy_no)
)

Here is a dump of the array:
    [0] => Array
        (
            [cid] => 0
            [0] => 0
            [name] => user_policy_no
            [1] => user_policy_no
            [type] => INTEGERUNSIGNED
            [2] => INTEGERUNSIGNED
            [notnull] => 99
            [3] => 99
            [dflt_value] =>
            [4] =>
            [pk] => 1
            [5] => 1
        )

Reproduce code:
---------------
CREATE TABLE user_policy (
  user_policy_no INTEGER UNSIGNED NOT NULL ,
  name VARCHAR(80) NOT NULL,
  manager_zone_access_level CHAR(1) NULL,
  manager_active_yn CHAR(1) NOT NULL DEFAULT '1',
  manager_gui_access_level_lkup CHAR(1) NULL,
  active_yn CHAR(1) NULL,
  creation_dte DATETIME NULL,
  last_modified_dte DATETIME NULL,

  PRIMARY KEY(user_policy_no)
)

$field_result = $db->prepare("PRAGMA table_info('user_policy')");
$field_result->execute();
print_r($field_result->fetchAll());


Expected result:
----------------
Array
(
    [0] => Array
        (
            [cid] => 0
            [0] => 0
            [name] => user_policy_no
            [1] => user_policy_no
            [type] => INTEGERUNSIGNED
            [2] => INTEGERUNSIGNED
            [notnull] => 99
            [3] => 99
            [dflt_value] =>
            [4] =>
            [pk] => 1
            [5] => 1
        )

    [1] => Array
        (
            [cid] => 1
            [0] => 1
            [name] => name
            [1] => name
            [type] => VARCHAR(80)
            [2] => VARCHAR(80)
            [notnull] => 99
            [3] => 99
            [dflt_value] =>
            [4] =>
            [pk] => 0
            [5] => 0
        )

    [2] => Array
        (
            [cid] => 2
            [0] => 2
            [name] => manager_zone_access_level
            [1] => manager_zone_access_level
            [type] => CHAR(1)
            [2] => CHAR(1)
            [notnull] => 0
            [3] => 0
            [dflt_value] =>
            [4] =>
            [pk] => 0
            [5] => 0
        )

    [3] => Array
        (
            [cid] => 3
            [0] => 3
            [name] => manager_active_yn
            [1] => manager_active_yn
            [type] => CHAR(1)
            [2] => CHAR(1)
            [notnull] => 99
            [3] => 99
            [dflt_value] => 1
            [4] => 1
            [pk] => 0
            [5] => 0
        )

    [4] => Array
        (
            [cid] => 4
            [0] => 4
            [name] => manager_gui_access_level_lkup
            [1] => manager_gui_access_level_lkup
            [type] => CHAR(1)
            [2] => CHAR(1)
            [notnull] => 0
            [3] => 0
            [dflt_value] =>
            [4] =>
            [pk] => 0
            [5] => 0
        )

    [5] => Array
        (
            [cid] => 5
            [0] => 5
            [name] => active_yn
            [1] => active_yn
            [type] => CHAR(1)
            [2] => CHAR(1)
            [notnull] => 0
            [3] => 0
            [dflt_value] =>
            [4] =>
            [pk] => 0
            [5] => 0
        )

    [6] => Array
        (
            [cid] => 6
            [0] => 6
            [name] => creation_dte
            [1] => creation_dte
            [type] => DATETIME
            [2] => DATETIME
            [notnull] => 0
            [3] => 0
            [dflt_value] =>
            [4] =>
            [pk] => 0
            [5] => 0
        )

    [7] => Array
        (
            [cid] => 7
            [0] => 7
            [name] => last_modified_dte
            [1] => last_modified_dte
            [type] => DATETIME
            [2] => DATETIME
            [notnull] => 0
            [3] => 0
            [dflt_value] =>
            [4] =>
            [pk] => 0
            [5] => 0
        )

)



Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-04-21 17:31 UTC] wez@php.net
Did you try this using the sqlite3 command line tool?
What does it show for that column?
 [2005-04-22 07:35 UTC] tom at supertom dot com
Yes, sorry I didn't post that info previously.  The sqlite3 command line tool shows the data type correctly.  "INTEGER UNSIGNED"

I will do more testing on it today and report my findings.
 [2005-04-22 09:24 UTC] tom at supertom dot com
I am mistaken!  The sqlite3 client DOES display the same problem!  I'll report this to the SQLite folks.  Sorry for the confusion, but hopefully this will help others in the future.

Thanks,

Tom
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Dec 26 11:01:30 2024 UTC