Postgresql Driver Failure

Gerald.Rager

Hi,

am working with Datatables editor and the php framework
with mysql everything works perfect

now i have a new project and have to use postgresql
but if i want to do a insert i became a syntax error

       SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "as" LINE 1: ...ol_cid, col_name ) VALUES ( $1, $2 )    
       RETURNING as dt_pkey ^

i take a look in the code of the Postgres Driver (query.php)
i think the failure is in line

        $row = $pkRes->fetch();

        $sql .= ' RETURNING '.$row['attname'].' as dt_pkey';

because the col ($row['attname']) is missing in the sql

protected function _prepare( $sql )
{
    // Add a RETURNING command to postgres insert queries so we can get the
    // pkey value from the query reliably
    if ( $this->_type === 'insert' ) {
        $table = explode( ' as ', $this->_table[0] );

        // Get the pkey field name
        $pkRes = $this->_dbcon->prepare( 
            "SELECT
                pg_attribute.attname, 
                format_type(pg_attribute.atttypid, pg_attribute.atttypmod) 
            FROM pg_index, pg_class, pg_attribute 
            WHERE 
                pg_class.oid = '{$table[0]}'::regclass AND
                indrelid = pg_class.oid AND
                pg_attribute.attrelid = pg_class.oid AND 
                pg_attribute.attnum = any(pg_index.indkey)
                AND indisprimary"
        );
        $pkRes->execute();
        $row = $pkRes->fetch();

        $sql .= ' RETURNING '.$row['attname'].' as dt_pkey';
    }

    // Prep a PDO statement
    $this->_stmt = $this->_dbcon->prepare( $sql );

    // bind values
    for ( $i=0 ; $i<count($this->_bindings) ; $i++ ) {
        $binding = $this->_bindings[$i];

        $this->_stmt->bindValue(
            $binding['name'],
            $binding['value'],
            $binding['type'] ? $binding['type'] : \PDO::PARAM_STR
        );
    }
}

allan

Hi,

The attname parameter should be coming from pg_attribute.attname in the select query. Could you confirm that you have a primary key column in the table you are working with? A missing primary key would probably cause this issue.

Thanks,
Allan

Gerald.Rager

Hi allan,

okay that is possible because i use the postgres oid so i dont define a primary key
mhm but i can´t define oid as primary key

allan

Yes, that would probably do it. So when you create the Editor instance are you doing something like Editor::inst( $db, 'myTable', 'oid' )? That isn't something I had considered before to be honest - I think it would require a little modification of the Editor Postgres driver to use:

$sql .= ' RETURNING oid as dt_pkey';

You might want to make that conditional for tables where there is a primary key, so that is used, rather than the oid.

Allan

Gerald.Rager

okay thank you allan