Home » Database » MDB2 » Bug #9851

[2007-01-17 05:15 UTC] corey at corlogic dot com

Description:
------------
Using MDB2 to connect to pgsql 8.1.5:

Attempting to perform the sql operations

"create temporary table mytable
as <complicated query>"

worked under pg_query, but fails in mdb2

"insert into select " should also work, but does not.

Test script:
---------------
using psql, do the following (pgsql ver 8.1.5)

z=# create table src (x int, y int, z int);
CREATE TABLE
z=# insert into src values (1,2,3);
INSERT 0 1
z=# insert into src values (4,5,6);
INSERT 0 1
z=# insert into src values (7,8,9);
INSERT 0 1
z=# create temporary table mytemp as select * from src where x > 1;
SELECT
z=# select * from mytemp;
x | y | z
---+---+---
4 | 5 | 6
7 | 8 | 9
(2 rows)

z=# \q

<?

include_once('inc/UserTemplate.php');

$conn = connect($config);

// this worked in pg_query
$stmt = $conn->prepare('
create temporary table mytemp
as select *
from src
where x > 1');

if (PEAR::isError($stmt)) {
print 'one: ' . $stmt->getMessage(). '<br>';
} else {

$res = $stmt->execute();

if (PEAR::isError($res)) {
print 'two: ' . $res->getMessage(). '<br>';
}
}

// this should be considered an insert, but MDB2 seems to think
// it's a select

$stmt2 = $conn->prepare('
insert into src(x,y,z)
select x*10, y*10, z*10
from src'
);

if (PEAR::isError($stmt2)) {
print 'three: ' . $stmt2->getMessage(). '<br>';
} else {

$res2 = $stmt2->execute();

if (PEAR::isError($res2)) {
print 'four: ' . $res2->getMessage(). '<br>';
} else {
print 'result: ' . $res2; // should be a rowcount, is an object
}
}
?>

Expected result:
----------------
The script should create the global temporary table, and the insert into select statement should be treated as DML, not as a select.

Actual result:
--------------
create temp table command fails outright.

insert into select seems to ignore the INSERT into portion.