Re: RES: [PEAR-DEV] DataObjects insert() for pgsql

Thanks - can you explain this.. - normally tables dont have a '.' in them.. ????

	        $dummy = explode('.',$this->__table);
	        $schema_name = $dummy[0];
	        $table_name  = $dummy[1];

Regards

Alan Rezende Rodrigo wrote:

The sql bellow returns the sequence name of a collumn! But the sequence name must be parsed! If you want the next value, you must use $__DB->nextId($sequence_name).

  	        SELECT
  	                adef.adsrc  as sequence
  	        FROM
  	                pg_catalog.pg_attribute a LEFT JOIN	

pg_catalog.pg_attrdef adef

  	                ON a.attrelid=adef.adrelid
  	                AND a.attnum=adef.adnum
  	        WHERE
  	                        a.atthasdef = 't' AND
  	                        a.attname = '%s' AND
  	                a.attrelid = (SELECT oid FROM

pg_catalog.pg_class

				    WHERE relname='%s'
          	                AND relnamespace = (SELECT oid FROM

pg_catalog.pg_namespace WHERE

                  	        nspname = '%s'))
  	                AND a.attnum > 0 AND NOT a.attisdropped

-----Mensagem original----- De: Alan Knowles [mailto:alan@akbkhome.com] Enviada em: terça-feira, 7 de outubro de 2003 05:15 Para: Rezende Rodrigo Cc: pear-dev@lists.php.net; Matsushita Wagner Assunto: Re: [PEAR-DEV] DataObjects insert() for pgsql I presume this uses a table defined like. create table xxx ( id int default next_val(some_seqence),......); could you try attaching as a .txt file, so it doesnt get messed up.. and just try and explain how exploding a tablename, would give you a schema/tablename.. ?

       {
           if (!($seq = @$options['sequence_'. $this->__table])) {

	        $dummy = explode('.',$this->__table);
	        $schema_name = $dummy[0];
	        $table_name  = $dummy[1];

	        $sql_seq = "
	        SELECT
	                a.attname as attname, adef.adsrc  as sequence
	        FROM
	                pg_catalog.pg_attribute a LEFT JOIN

pg_catalog.pg_attrdef adef

	                ON a.attrelid=adef.adrelid
	                AND a.attnum=adef.adnum
	        WHERE
	                        a.atthasdef = 't' AND
	                        a.attname = '$key' AND
	                a.attrelid = (SELECT oid FROM pg_catalog.pg_class

WHERE relname='$table_name'

       	                AND relnamespace = (SELECT oid FROM

pg_catalog.pg_namespace WHERE

               	        nspname = '$schema_name'))
	                AND a.attnum > 0 AND NOT a.attisdropped
	        ;
       	";
	        $r = $__DB->getRow($sql_seq,DB_FETCHMODE_ASSOC);
	        $attrname_seq = $r['attname'];	
	        $sequence_name = explode('\'',$r['sequence']);

$sequence_name = (isset($sequence_name[1]))?$sequence_name[1]:'';

               $seq = $sequence_name;
           }
           $this->$key = $__DB->nextId($seq);
       }

       foreach($items as $k => $v) {
           if (!isset($this->$k) || $this->$k == '' || $this->$k ===

'null') {

               continue;
           }

           if ($leftq) {
               $leftq  .= ', ';
               $rightq .= ', ';
           }
           $leftq .= "$k ";

           if (strtolower($this->$k) === 'null') {
               $rightq .= " NULL ";
               continue;
           }
           if ($v & DB_DATAOBJECT_STR) {
               $rightq .= $__DB->quote($this->$k) . " ";
               continue;
           }

           if (is_numeric($this->$k)) {
               $rightq .=" {$this->$k} ";
               continue;
           }
           // at present we only cast to integers
           // - V2 may store additional data about float/int
           $rightq .= ' ' . intval($this->$k) . ' ';

       }
       if ($leftq) {
           $r = $this->_query("INSERT INTO {$this->__table} ($leftq)

VALUES

($rightq) ");

           if (PEAR::isError($r)) {
               DB_DataObject::raiseError($r);
               return false;
           }
           if ($r < 1) {
               DB_DataObject::raiseError('No Data Affected By

insert',DB_DATAOBJECT_ERROR_NOAFFECTEDROWS);

               return false;
           }

           if ($key &&
               ($items[$key] & DB_DATAOBJECT_INT) &&
               ($dbtype == 'mysql') &&
               (@$options['ignore_sequence_keys'] != 'ALL') &&
                   (
                       !@$options['ignore_sequence_keys'] ||
                       (is_array(@$options['ignore_sequence_keys']) &&

in_array($this->__table,@$options['ignore_sequence_keys']))

                   )
               )
           {
               $this->$key =

mysql_insert_id($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->co

nnection);

           }

           $this->_clear_cache();
           if ($key) {
               return $this->$key;
           }
           return true;
       }
       DB_DataObject::raiseError("insert: No Data specifed for query",

DB_DATAOBJECT_ERROR_NODATA);

       return false;

}

-- Can you help out? Need Consulting Services or Know of a Job? http://www.akbkhome.com