hi there,
I'm currently converting a MySQL application to PostgreSQL, and I
hacked this app to support PEAR::Db .
after some good coding days, I've found a problem: mysql_insert_id on
pear::db .
I've those two piece of code, the first is part of a class:
---------
function connect($layer, $host,$db_port, $db_name,$user, $pass) {
$dsn = "$layer://$user:$pass@$ho st:$db_port/$db_name";
$this->db_connect = DB::Connect($ds n);
return $this->db_connect;
}
function genId($sequence , $onDemand='true ') {
return $this->db_connect->nextId($sequen ce);
}
function insert_id() {
$result = $this->db_connect->nextId();
return $result;
}
---------
please note, those are the functions before my hacks:
---------
// query should be rewritten to include this; || this is not a
comments of mine
function genId($sequence ) {
return 0;
}
function insert_id() {
$result = mysql_insert_id ($this->db_connect);
return $result;
}
and here we go with code:
---------
function store() {
global $db;
if ( !$this->isCleaned() ) {
if ( !$this->cleanVars() ) {
return false;
}
}
foreach ( $this->cleanVars as $k=>$v ) {
$$k = $v;
}
if ( empty($uid) ) {
$uid = $db->genId(persone_ uid_seq"); //<<<<< this is the problem
$sql = "INSERT INTO persone
(identificativo ,nome_persona,a cronimo,passwor d,level,theme,l ast_login)
VALUES
($uid,'$uname', '$name','$pass' ,'$level','$the me',0)";
} else {
$sql ="UPDATE persone SET acronimo='$unam e', nome_persona='$ name',
password='$pass ', theme='$theme' WHERE identificativo= $uid";
}
if ( !$result = $db->query($sql) ) {
$this->setErrors("Cou ld not store data in database.");
//pretty useless because of PEAR::db error handling
return false;
}
if ( empty($uid) ) {
$uid = $db->insert_id(); //<<<<maybe this?
}
return $uid;
}
---------
my problem is that I had to define two identical functions to make the
app working, the second is that in database the id generated starts
from zero.
I've an alredy populated database, so these values cannot be inserted,
I need generated IDs that start from a point, in mysql those functions
works without problems.
Things I don't understand:
have PEAR::db something that could replace the above function? in the
source code I've seen something similar to createsequence but I'm not
sure how to use it.
second, what is $uid = $db->genId(persone_ uid_seq"); ? persone is a
table of mines, but persone_uid_seq not.
surely I'm missing something important with the sequences, can you
please tell me what's wrong? thanks.
kain
I'm currently converting a MySQL application to PostgreSQL, and I
hacked this app to support PEAR::Db .
after some good coding days, I've found a problem: mysql_insert_id on
pear::db .
I've those two piece of code, the first is part of a class:
---------
function connect($layer, $host,$db_port, $db_name,$user, $pass) {
$dsn = "$layer://$user:$pass@$ho st:$db_port/$db_name";
$this->db_connect = DB::Connect($ds n);
return $this->db_connect;
}
function genId($sequence , $onDemand='true ') {
return $this->db_connect->nextId($sequen ce);
}
function insert_id() {
$result = $this->db_connect->nextId();
return $result;
}
---------
please note, those are the functions before my hacks:
---------
// query should be rewritten to include this; || this is not a
comments of mine
function genId($sequence ) {
return 0;
}
function insert_id() {
$result = mysql_insert_id ($this->db_connect);
return $result;
}
and here we go with code:
---------
function store() {
global $db;
if ( !$this->isCleaned() ) {
if ( !$this->cleanVars() ) {
return false;
}
}
foreach ( $this->cleanVars as $k=>$v ) {
$$k = $v;
}
if ( empty($uid) ) {
$uid = $db->genId(persone_ uid_seq"); //<<<<< this is the problem
$sql = "INSERT INTO persone
(identificativo ,nome_persona,a cronimo,passwor d,level,theme,l ast_login)
VALUES
($uid,'$uname', '$name','$pass' ,'$level','$the me',0)";
} else {
$sql ="UPDATE persone SET acronimo='$unam e', nome_persona='$ name',
password='$pass ', theme='$theme' WHERE identificativo= $uid";
}
if ( !$result = $db->query($sql) ) {
$this->setErrors("Cou ld not store data in database.");
//pretty useless because of PEAR::db error handling
return false;
}
if ( empty($uid) ) {
$uid = $db->insert_id(); //<<<<maybe this?
}
return $uid;
}
---------
my problem is that I had to define two identical functions to make the
app working, the second is that in database the id generated starts
from zero.
I've an alredy populated database, so these values cannot be inserted,
I need generated IDs that start from a point, in mysql those functions
works without problems.
Things I don't understand:
have PEAR::db something that could replace the above function? in the
source code I've seen something similar to createsequence but I'm not
sure how to use it.
second, what is $uid = $db->genId(persone_ uid_seq"); ? persone is a
table of mines, but persone_uid_seq not.
surely I'm missing something important with the sequences, can you
please tell me what's wrong? thanks.
kain