PEAR Bug Search Results

MDB_QueryTool: Bug 17290 [Open] MySQL AUTO_INCREMENT problems

fludimir at gmail dot com — 2010-04-04T04:35:18+00:00

MDB_QueryTool Bug
Reported by fludimir
2010-04-04T04:35:18+00:00
PHP: 5.2.12 OS: Windows XP SP2 Package Version: 1.2.2

Description:
------------
Cannot insert data in mysql table with auto_increment primary key. 
MDB2 version 2.4.1

Test script:
---------------
mysql> describe news;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| date     | date         | YES  |     | NULL    |                |
| title    | varchar(255) | YES  |     | NULL    |                |
| announce | text         | YES  |     | NULL    |                |
| text     | text         | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


class news extends MDB_QueryTool
{
    var $table = 'news';
    var $primaryCol = 'id';

}

$news = new news($mdb2);

$data = array(
    'date' =>       '2010-04-05',
    'title' =>      'test',
    'announce'  =>  'test',
    'text' =>       'test'
);

$id = $news->save($data);

Expected result:
----------------
Valid insertion and return inserted id

Actual result:
--------------
MDB_QueryTool 1.2.2 (current stable):
MDB2 Error: unknown error _doQuery: [Error message: Could not execute statement]
[Last executed query: INSERT INTO news (`date`, `title`, `announce`, `text`, `id`) VALUES ('2010-04-05', 'test', 'test', 'test', 'NULL')]
[Native code: 1366]
[Native message: Incorrect integer value: 'NULL' for column 'id' at row 1]

MDB_QueryTool 1.2.0 (tried a lot of earlier versions to find some working):
Valid insert and return inserted id, but instead of using MySQL AUTO_INCREMENT feature there was created news_seq sequence table, and insert query builded with col `id` and some value from sequence for this. Don't know how for another RDBMS, but as for MySQL you don't need to specify auto_increment column at all. Valid query is "INSERT INTO news (`date`, `title`, `announce`, `text`) VALUES ('2010-04-05', 'test', 'test', 'test')" .
I also tried version from cvs (Query.php,v 1.84 2008/07/14) and took the same result as with 1.2.0 - insertion works, but by using sequences instead of Auto_Increment

MDB_QueryTool: Bug 16524 [Open] Query.add() returns 'NULL' i.o inserted id for MDB2+autoincrement

gonz000 at hotmail dot com — 2009-08-15T03:49:16+00:00

MDB_QueryTool Bug
Reported by marvin_x
2009-08-15T03:37:52+00:00
PHP: 5.3.0 OS: Windows XP SP3 Package Version: 1.2.2

Description:
------------
Query->add() does not return the last inserted id, if we use MDB2 AND if we have an autoincrement supporting database / table. Instead add() returns 'NULL' (even with succesful insertion)
The add() function is supposed to figure out an id and return that id.  For MDB this works, since the id is figured out before the query. For MDB2 this is different.
The MDB2_Extended.beforeID() function is called, which , if auto-increment is supported, returns $id == 'NULL' to be put into the query, which works fine on execution.
After the execution nothing happens, and the id remains 'NULL' . A suggested fix is included in the files. It calls MDB2's AfterId function.

MDB_QueryTool: Bug 15463 [Open] Broken SELECT

michael at stellarent dot com — 2008-12-30T22:25:32+00:00

MDB_QueryTool Bug
Reported by mpgjunky
2008-12-30T22:25:32+00:00
PHP: 5.1.6 OS: Fedora 6 Package Version: 1.2.2

Description:
------------
I get a broken select when I use left joins and multiple tables. The code is show below:

    $ticket = new Ticket($dsn, null, 2);
    $ticket->useResult('object');
    $ticket->setSelect("client_tickets.*, users_table.*,prices.*, displaytext, count(wp.user_id) as wnp, count(pp.user_id) as
 pnp");
    $ticket->addLeftJoin("users_table", "users_table.userid = client_tickets.userid");
    $ticket->addLeftJoin("prices", "users_table.acc_type = prices.acc_type");
    $ticket->addLeftJoin("payment_worldpay as wp", "users_table.userid = wp.user_id");
    $ticket->addLeftJoin("paypal_payments as pp", "users_table.userid = pp.user_id");
    $ticket->setGroup("userid");
    $trec = $ticket->get($id)->fetchRow();

See actual result for sql statement.

There are in fact two problems (if I am not wrong):

1. Spaces in front of 'table.*'
2. Column prefixing statement in Query.php line 2097, _buildSelect() function (which also affects returned result as object).

I fixed the problems by:

1. Add: $tables = array_map('trim', $tables) after the tables have been extracted into $tables.
2. Modify line 2097 from:
$cols[$aTable][] = $aTable. '.' .$colName . ' AS '. $this->_quoteIdentifier('_'. $this->getTableShortName($aTable) .'_'. $colName);

TO 
$cols[$aTable][] = $aTable. '.' .$colName . ' AS '. $this->_quoteIdentifier($this->getTableShortName($aTable) .'_'. $colName);

I am not sure if the problems I experienced are a result of me not using the class properly or not. If this is the case please let me know.

Cheers,
Michael.

Expected result:
----------------
A proper SQL statement, even if column names are being prefixed with table name as show below:

SELECT client_tickets.`id` AS `id`,client_tickets.`id_cat` AS `id_cat`,client_tickets.`description` AS `description`,client_tickets.`status` AS `status`,client_tickets.`userid` AS `userid`,client_tickets.`adate` AS `adate`,client_tickets.`t_status` AS `t_status`,users_table.`id_users_table` AS `users_id_users_table`,users_table.`name` AS `users_name`,users_table.`address` AS `users_address`,users_table.`town` AS `users_town`,users_table.`country` AS `users_country`,users_table.`email` AS `users_email`,users_table.`zipcode` AS `users_zipcode`,users_table.`userid` AS `users_userid`,users_table.`pwd` AS `users_pwd`,users_table.`sex` AS `users_sex`,users_table.`birth_date` AS `users_birth_date`,users_table.`acc_type` AS `users_acc_type`,users_table.`status` AS `users_status`,users_table.`creation_date` AS `users_creation_date`,users_table.`hash` AS `users_hash`,users_table.`recurr` AS `users_recurr`,users_table.`referral` AS `users_referral`,users_table.`activated` AS `users_activated`,users_table.`expiry` AS `users_expiry`,users_table.`friend` AS `users_friend`,users_table.`comments` AS `users_comments`,users_table.`dutch_id` AS `users_dutch_id`,users_table.`remote_addr` AS `users_remote_addr`,users_table.`period` AS `users_period`,prices.`id` AS `prices_id`,prices.`acc_type` AS `prices_acc_type`,prices.`price` AS `prices_price`,prices.`d_price` AS `prices_d_price`,prices.`currency` AS `prices_currency`,prices.`enabled` AS `prices_enabled`,prices.`display` AS `prices_display`,prices.`displaytext` AS `prices_displaytext`,prices.`displayto` AS `prices_displayto`,prices.`type` AS `prices_type`,prices.`ns_type` AS `prices_ns_type`,prices.`days` AS `prices_days`,`displaytext`,count(wp.user_id) AS `wnp`,count(pp.user_id) AS `pnp` FROM `client_tickets` LEFT JOIN users_table ON users_table.userid = client_tickets.userid LEFT JOIN prices ON users_table.acc_type = prices.acc_type LEFT JOIN payment_worldpay as wp ON users_table.userid = wp.user_id LEFT JOIN paypal_payments as pp ON users_table.userid = pp.user_id GROUP BY client_tickets.userid


Actual result:
--------------
SELECT client_tickets.`id` AS `id`,client_tickets.`id_cat` AS `id_cat`,client_tickets.`description` AS `description`,client_tickets.`status` AS `status`,client_tickets.`userid` AS `userid`,client_tickets.`adate` AS `adate`,client_tickets.`t_status` AS `t_status`,users_table.`id_users_table` AS `_table_id_users_table`,users_table.`name` AS `_table_name`,users_table.`address` AS `_table_address`,users_table.`town` AS `_table_town`,users_table.`country` AS `_table_country`,users_table.`email` AS `_table_email`,users_table.`zipcode` AS `_table_zipcode`,users_table.`userid` AS `_table_userid`,users_table.`pwd` AS `_table_pwd`,users_table.`sex` AS `_table_sex`,users_table.`birth_date` AS `_table_birth_date`,users_table.`acc_type` AS `_table_acc_type`,users_table.`status` AS `_table_status`,users_table.`creation_date` AS `_table_creation_date`,users_table.`hash` AS `_table_hash`,users_table.`recurr` AS `_table_recurr`,users_table.`referral` AS `_table_referral`,users_table.`activated` AS `_table_activated`,users_table.`expiry` AS `_table_expiry`,users_table.`friend` AS `_table_friend`,users_table.`comments` AS `_table_comments`,users_table.`dutch_id` AS `_table_dutch_id`,users_table.`remote_addr` AS `_table_remote_addr`,users_table.`period` AS `_table_period`,prices.`id` AS `_prices_id`,prices.`acc_type` AS `_prices_acc_type`,prices.`price` AS `_prices_price`,prices.`d_price` AS `_prices_d_price`,prices.`currency` AS `_prices_currency`,prices.`enabled` AS `_prices_enabled`,prices.`display` AS `_prices_display`,prices.`displaytext` AS `_prices_displaytext`,prices.`displayto` AS `_prices_displayto`,prices.`type` AS `_prices_type`,prices.`ns_type` AS `_prices_ns_type`,prices.`days` AS `_prices_days`,`displaytext`,count(wp.user_id) AS `wnp`,count(pp.user_id) AS `pnp` FROM `client_tickets` LEFT JOIN users_table ON users_table.userid = client_tickets.userid LEFT JOIN prices ON users_table.acc_type = prices.acc_type LEFT JOIN payment_worldpay as wp ON users_table.userid = wp.user_id LEFT JOIN paypal_payments as pp ON users_table.userid = pp.user_id GROUP BY client_tickets.userid

MDB_QueryTool: Bug 14542 [Open] Problem with join if field is underscore separated

adomas dot rimeika at gmail dot com — 2008-08-22T10:49:31+00:00

MDB_QueryTool Bug
Reported by gumigutas
2008-08-22T10:49:31+00:00
PHP: 5.2.5 OS: Linux Package Version: 1.2.2

Description:
------------
$this->addJoin($this->tableA, "{$this->table}.account_trans_id={$this->tableA}.id");

field "account_trans_id" converts into "accounttrans.id"

MDB_QueryTool: Feature/Change Request 7642 [Open] Data type abstraction layer does not work not fetched results

christian_rocher at hotmail dot com — 2006-05-16T17:44:09+00:00

MDB_QueryTool Feature/Change Request
Reported by christian_rocher@...
2006-05-16T17:14:37+00:00
PHP: 4.3.10 OS: Windows XP SP2 Package Version: 

Description:
------------
Hi,

I use MDB2-2.0.1 with MDB2_Driver_mysql-1.0.1, MDB2_Driver_pgsql-1.0.0 and MDB_QueryTool-1.1.1 to connect to my databases (MySQL and Posgres).

The Data type abstraction layer works fine. But fetched records (QueryTool->getAll()) returns data in the database format. For example: a boolean is 0 or 1 for mysql and "f" or "t" for posgres.



Test script:
---------------
$this->QueryTool->reset();
$this->QueryTool->AddOrder( $this->QueryTool->primaryCol);
$this->QueryTool->getAll();


Expected result:
----------------
the bolean values are converted to 0 or 1

Actual result:
--------------
a boolean result is 0 or 1 for mysql and "f" or "t" for posgres.