Home » Database » DB » Bug #4220

[2005-04-25 13:44 UTC] kwinter at sitrack dot com

Description:
------------
DB package version 1.7.6
ifx.php file adds to support ids 7.x

1- 'tabname' is a char(18) field in IDS 7.x, hence a 'trim()' is needed. Not needed in IDS 9.x due to a varchar column type

2- get the next free id in a table

3- recognize ifx datatypes, primary keys and auto increment fields in .ini file

4- unquote 'current' keyword for use in datetime fields

Reproduce code:
---------------
//-1- on line 664 -
return 'SELECT trim(tabname) FROM systables WHERE tabid >= 100';

//-2- add lines -
function nextId($seq_name){
$_pk = array_shift($this->getPKFieldsNames($seq_name));
$_next = array_shift(ifx_fetch_row(@ifx_query("SELECT max(".$_pk.")+1 FROM ".$seq_name, $this->connection), "NEXT"));
return abs($_next);
}

// returns an array containing the names of the fields that are primary keys.
function getPKFieldsNames($p_result) {
$strFields = $this->getPKFieldsIDs($p_result);
$id = @ifx_query("select colname from syscolumns,systables where systables.tabid=syscolumns.tabid and systables.tabname='".$p_result."' and colno in (".$strFields.")",$this->connection);
$arrFields=array();
if ($id){
$row = ifx_fetch_row ($id, "NEXT");
while (is_array($row)){
for(reset($row); $fieldName=key($row); next($row)) {
$fieldValue=trim($row[$fieldName]);
$arrFields[$fieldValue]=$fieldValue;
}
$row = ifx_fetch_row ($id, "NEXT");
}
}
return $arrFields;
}

// returns a comma-separated list containing the IDs of the fields that are primary keys.
function getPKFieldsIDs($p_result){
$_fields = "";
$id = @ifx_query("SELECT part1, part2, part3, part4, part5, part6, part7,part8, part9, part10, part11, part12, part13, part14, part15,part16 FROM sysconstraints, sysindexes,systables WHERE sysconstraints.tabid = systables.tabid AND sysconstraints.idxname = sysindexes.idxname AND sysconstraints.tabid = sysindexes.tabid AND sysconstraints.constrtype='P' AND systables.tabname='".$p_result."'",$this->connection);

$row = ifx_fetch_row ($id, "NEXT");
while (is_array($row))
{
for(reset($row); $fieldname=key($row); next($row))
{
if ($row[$fieldname]!=0) $_fields .= $row[$fieldname].",";
}
$row = ifx_fetch_row ($id, "NEXT");
}
ifx_free_result ($id);
$_fields = substr($_fields,0,-1);
return $_fields;
}

//-3- add near line 603
$pkFlds = $this->getPKFieldsNames($result);

//-3- add near line 630 recognize ifx primary keys fields
if(!empty($pkFlds[$key])) {
$res[$i]['flags'] .= ' primary_key';
}
// recognize ifx datatypes and auto increment fields
switch(strtoupper($props[0])) {
case 'SQLSERIAL':
$res[$i]['flags'] .= ' auto_increment';
case 'SQLSMINT':
case 'SQLINT':
case 'SQLDECIMAL':
$res[$i]['type'] = 'INT';
break;
case 'SQLSMFLOAT';
case 'SQLFLOAT':
case 'SQLINTERVAL':
$res[$i]['type'] = 'FLOAT';
break;
case 'SQLDATE':
$res[$i]['type'] = 'DATE';
break;
case 'SQLDTIME':
$res[$i]['type'] = 'DATETIME';
break;
case 'SQLVCHAR':
case 'SQLNCHAR':
case 'SQLNVCHAR':
case 'SQLTEXT':
case 'SQLCHAR':
$res[$i]['type'] = 'VARCHAR';
break;
}

//-4- add near line 246 -
$query = preg_replace('/\'current\'/i','current',$query);

[2005-05-16 14:02 UTC] kwinter at sitrack dot com

245a246,247
> // unquote 'current' keyword for use in datetime fields.
> $query = preg_replace('/\'current\'/i', 'current', $query);
603c605,608
<
---
>
> // get primary key Fields
> $pkFlds = $this->getPKFieldsNames($result);
>
629a635,668
>
> // recognize ifx primary keys fields
> if (!empty($pkFlds[$key])) {
> $res[$i]['flags'] .= ' primary_key';
> }
> // recognize ifx datatypes and auto increment fields
> switch (strtoupper($props[0])) {
> case 'SQLSERIAL': // serial 4
> $res[$i]['flags'] .= ' auto_increment';
> case 'SQLSMINT': // smallint 2
> case 'SQLINT': // integer 4
> case 'SQLDECIMAL': // decimal 2048
> $res[$i]['type'] = 'INT';
> break;
> case 'SQLSMFLOAT'; // smallfloat 4
> case 'SQLFLOAT': // float 8
> case 'SQLINTERVAL': // interval 1024
> $res[$i]['type'] = 'FLOAT';
> break;
> case 'SQLDATE': // date 4
> $res[$i]['type'] = 'DATE';
> break;
> case 'SQLDTIME': // datetime 3594
> $res[$i]['type'] = 'DATETIME';
> break;
> case 'SQLVCHAR': //
> case 'SQLNCHAR': // nchar 5
> case 'SQLNVCHAR': // nvarchar 8
> case 'SQLTEXT': // text 56
> case 'SQLCHAR': // char 10 o character 1
> $res[$i]['type'] = 'VARCHAR';
> break;
> }
>
664c703,704
< return 'SELECT tabname FROM systables WHERE tabid >= 100';
---
> // 'tabname' is a char(18) field in IDS 7.x, hence a 'trim()' is needed. Not needed in IDS 9.x due to a varchar column type
> return 'SELECT trim(tabname) FROM systables WHERE tabid >= 100';
671a712,777
> /**
> * Returns an array containing the names of the fields that are primary keys.
> *
> * @param string $p_result A string containing the name of a table
> * @return array an associative array with the fields' names
> * @see tableInfo()
> */
> function getPKFieldsNames($p_result)
> {
> $strFields = $this->getPKFieldsIDs($p_result);
>
> $id = @ifx_query("select colname from syscolumns,systables where systables.tabid=syscolumns.tabid and systables.tabname='".$p_result."' and colno in (".$strFields.")", $this->connection);
> $arrFields=array();
> if ($id){
> $row = ifx_fetch_row($id, "NEXT");
> while (is_array($row)) {
> for (reset($row); $fieldName = key($row); next($row)) {
> $fieldValue = trim($row[$fieldName]);
> $arrFields[$fieldValue] = $fieldValue;
> }
> $row = ifx_fetch_row($id, "NEXT");
> }
> }
> return $arrFields;
> }
>
> /**
> * Returns a comma-separated list containing the IDs of the fields that are primary keys.
> *
> * @param string $p_result A string containing the name of a table
> * @return string a comma-separated list containing the IDs of the fields that are primary keys
> * @see getPKFieldsNames()
> */
> function getPKFieldsIDs($p_result)
> {
> $_fields = "";
> $id = @ifx_query("SELECT part1, part2, part3, part4, part5, part6, part7,part8, part9, part10, part11, part12, part13, part14, part15,part16 FROM sysconstraints, sysindexes,systables WHERE sysconstraints.tabid = systables.tabid AND sysconstraints.idxname = sysindexes.idxname AND sysconstraints.tabid = sysindexes.tabid AND sysconstraints.constrtype='P' AND systables.tabname='".$p_result."'", $this->connection);
>
> $row = ifx_fetch_row($id, "NEXT");
> while (is_array($row)) {
> for (reset($row); $fieldname=key($row); next($row)) {
> if ($row[$fieldname]!=0) $_fields .= $row[$fieldname].",";
> }
> $row = ifx_fetch_row($id, "NEXT");
> }
> ifx_free_result($id);
> $_fields = substr($_fields, 0, -1);
> return $_fields;
> }
>
> /**
> * Returns the next free id in a table
> *
> * @param string $seq_name A string containing the name of a table
> *
> * @internal
> * @see DB_common::nextID()
> * @access public
> */
> function nextId($seq_name)
> {
> $_pk = array_shift($this->getPKFieldsNames($seq_name));
> $_next = array_shift(ifx_fetch_row(@ifx_query("SELECT max(".$_pk.")+1 FROM ".$seq_name, $this->connection), "NEXT"));
> return abs($_next);
> }
>

[2005-05-17 21:17 UTC] kwinter at sitrack dot com

@@ -243,6 +243,8 @@
*/
function simpleQuery($query)
{
+ // unquote 'current' keyword for use in datetime fields.
+ $query = preg_replace('/\'current\'/i', 'current', $query);
$ismanip = DB::isManip($query);
$this->last_query = $query;
$this->affected = null;
@@ -600,6 +602,8 @@

$flds = @ifx_fieldproperties($id);
$count = @ifx_num_fields($id);
+ // get primary key Fields
+ $pkFlds = $this->getPKFieldsNames($result);

if (count($flds) != $count) {
return $this->raiseError("can't distinguish duplicate field names");
@@ -627,6 +631,40 @@
'len' => $props[1],
'flags' => $props[4] == 'N' ? 'not_null' : '',
);
+
+ // recognize ifx primary keys fields
+ if (in_array($key, $pkFlds)) {
+ $res[$i]['flags'] .= ' primary_key';
+ }
+ // recognize ifx datatypes and auto increment fields
+ switch (strtoupper($props[0])) {
+ case 'SQLSERIAL': // serial 4
+ $res[$i]['flags'] .= ' auto_increment';
+ case 'SQLSMINT': // smallint 2
+ case 'SQLINT': // integer 4
+ case 'SQLDECIMAL': // decimal 2048
+ $res[$i]['type'] = 'INT';
+ break;
+ case 'SQLSMFLOAT'; // smallfloat 4
+ case 'SQLFLOAT': // float 8
+ case 'SQLINTERVAL': // interval 1024
+ $res[$i]['type'] = 'FLOAT';
+ break;
+ case 'SQLDATE': // date 4
+ $res[$i]['type'] = 'DATE';
+ break;
+ case 'SQLDTIME': // datetime 3594
+ $res[$i]['type'] = 'DATETIME';
+ break;
+ case 'SQLVCHAR':
+ case 'SQLNCHAR': // nchar 5
+ case 'SQLNVCHAR': // nvarchar 8
+ case 'SQLTEXT': // text 56
+ case 'SQLCHAR': // char 10 o character 1
+ $res[$i]['type'] = 'VARCHAR';
+ break;
+ }
+
if ($mode & DB_TABLEINFO_ORDER) {
$res['order'][$res[$i]['name']] = $i;
}
@@ -661,7 +699,9 @@
{
switch ($type) {
case 'tables':
- return 'SELECT tabname FROM systables WHERE tabid >= 100';
+ // 'tabname' is a char(18) field in IDS 7.x, hence a 'trim()' is
+ // needed. Not needed in IDS 9.x due to a varchar column type.
+ return 'SELECT trim(tabname) FROM systables WHERE tabid >= 100';
default:
return null;
}
@@ -669,6 +709,65 @@

// }}}

+ /**
+ * Returns an array containing the names of the fields that are primary keys.
+ *
+ * @param string $tblName A string containing the name of a table
+ * @return array $pkRtn An aray with the primary key fields' names
+ * @see tableInfo()
+ */
+ function getPKFieldsNames($tblName)
+ {
+ $pkRtn = array();
+ $pkQuery = "SELECT syscolumns.colname "
+ . "FROM sysconstraints, sysindexes, systables, syscolumns "
+ . "WHERE sysconstraints.constrtype = 'P' "
+ . "AND sysconstraints.tabid = systables.tabid "
+ . "AND sysindexes.tabid = systables.tabid "
+ . "AND sysindexes.idxname = sysconstraints.idxname "
+ . "AND syscolumns.tabid = systables.tabid "
+ . "AND syscolumns.colno in (sysindexes.part1, sysindexes.part2,"
+ . " sysindexes.part3, sysindexes.part4,"
+ . " sysindexes.part5, sysindexes.part6,"
+ . " sysindexes.part7, sysindexes.part8,"
+ . " sysindexes.part9, sysindexes.part10,"
+ . " sysindexes.part11, sysindexes.part12,"
+ . " sysindexes.part13, sysindexes.part14,"
+ . " sysindexes.part15, sysindexes.part16)"
+ . " AND systables.tabname = '$tblName' ";
+
+ $pkResult = @ifx_query($pkQuery, $this->connection);
+ if ($pkResult) {
+ while ($pkArr = @ifx_fetch_row($pkResult, 'NEXT')) {
+ $pkRtn[] = trim(array_shift($pkArr));
+ }
+ }
+ return $pkRtn;
+ }
+
+ /**
+ * Returns the next free id in a table
+ *
+ * @param string $tblName A string containing the name of a table
+ * @see DB_common::nextID()
+ * @access public
+ */
+ function nextId($tblName)
+ {
+ $pkFlds = $this->getPKFieldsNames($tblName);
+ if (sizeof($pkFlds) == 1) {
+ $nQuery = 'SELECT max(' . $pkFlds[0] . ') + 1 FROM ' . $tblName;
+ $nResult = @ifx_query($nQuery, $this->connection);
+
+ if ($nResult) {
+ if ($nArr = @ifx_fetch_row($nResult, 'NEXT')) {
+ return abs(array_shift($nArr));
+ }
+ }
+ }
+ return $this->ifxRaiseError(DB_ERROR_NOT_CAPABLE);
+ }
+
}

/*

[2005-07-01 07:29 UTC] arnaud dot didry at gmail dot com

fabulous ! it works !!!
that was exactly what i was trying to do !

thanks a lot !