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

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 this goes in.. - I would prefer something like: define(DB_DATAOBJECT_PGSQL_NEXTVAL_QUERY," 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 = '%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 "); then use $__DB->getRow(sprintf(DB_DATAOBJECT_PGSQL_NEXTVAL_QUERY,$tablename.....); rather than flood the method with too much data.. Regards Alan > { > 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 -- PEAR Development Mailing List (http://pear.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php