[asdbabil]

the code below dynamically creates sequences and tables successfully but it's unable to link the pk field to a sequence when it's created. I tried the nextval('$seq') but it didn't work. I spent hours searching the web for a solution and I couldn't get no where. (im using postgresql) Help me please.

if($_REQUEST['fieldid'] == ''){
 if ($seq = $adb->pquery("CREATE SEQUENCE cust_".$columnName."_seq INCREMENT BY 1 MINVALUE 1000 MAXVALUE 999999999999999999"))
{
$log->debug("********seq: $seq created***");
} else{
$log->debug("********seq: $seq failed***");
}
$qur ="CREATE TABLE cust_".$columnName."(
".$columnName."id integer NOT NULL,
".$columnName." varchar(200) NOT NULL,
presence integer NOT NULL default '1',
picklist_valueid integer NOT NULL default '0',
PRIMARY KEY (".$columnName."id))";
$adb->pquery($qur, array());
}

This post has been edited by asdbabil: 15 July 2011 - 03:31 PM

[JackOfAllTrades]

Try

$newTable = "cust_$columnName";
$newSequence = "$newTable_seq";
if ($adb->pquery(
        "CREATE SEQUENCE $newSequence INCREMENT BY 1 MINVALUE 1000 MAXVALUE 999999999999999999"))
{
    $log->debug("********seq: $newSequence created***");
    $qur ="CREATE TABLE $newTable({$columnName}id integer NOT NULL DEFAULT nextval($newSequence), $columnName varchar(200) NOT NULL, presence integer NOT NULL default '1', picklist_valueid integer NOT NULL default '0', PRIMARY KEY ({$columnName}id))";
} else {
    $log->debug("********seq: $newSequence creation failed***");
}

or simply

$qur = "CREATE TABLE cust_{$columnName}({$columnName}id serial, $columnName varchar(200) NOT NULL, presence integer NOT NULL default '1', picklist_valueid integer NOT NULL default '0', PRIMARY KEY ({$columnName}id))";

because using the serial datatype buys you the sequence creation and attachment. For your reading pleasure.

[asdbabil]

You have no idea how helpful that was!
Thank you

This post has been edited by asdbabil: 18 July 2011 - 04:04 PM