Bug #9204

Sequences don't work with MSSQL

Details

Submitted	2006-10-31 14:20 UTC
From	alberto dot morselli at cup2000 dot it
Assigned	davidc
Status	Closed
Package	MDB2_Driver_mssql
PHP Version	5.1.6
OS	Windows XP
Roadmaps	(Not assigned)

Comments

[2006-10-31 14:20 UTC] alberto dot morselli at cup2000 dot it

Description:
------------
With MSSQLServer 2000 SP4 installed on Windows Server 2003 SP1 the nextId method doesn't work properly.

The first time it's called it creates the table 'mytable_seq' with one column named 'sequence'. The only record in the table is sequence = 2.

The second time it's called it adds a record sequence = 0 in table 'mytable_seq'.

The third time it's calles id doesn't do anything. No error is returned.

Method getServerVersion returns:
[major] => 8
[minor] => 00
[patch] => 2039
[extra] =>
[native] => Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Test script:
---------------
<?php
require_once 'MDB2.php';
$dsn = "mssql://myuser:mypwd@localhost/mydb";
$db =& MDB2::connect($dsn);
$id = $db->nextId('mytable');
echo "<pre>";
print_r($id);
echo "</pre>\n";
?>

Expected result:
----------------
First time
<pre>1</pre>

Second time
<pre>2</pre>

Third time
<pre>3</pre>

Actual result:
--------------
First time
<pre>1</pre>

Second time
<pre>0</pre>

Third time
<pre></pre>

[2006-10-31 14:35 UTC] alberto dot morselli at cup2000 dot it

It seems that everytime nextId is called it tries to write a record into table 'mytable_seq' with sequence=0.
This insert fails because sequence is primary key and there is already a row with sequence=0.

[2006-12-28 16:54 UTC] alberto dot morselli at cup2000 dot it

I need to make sequences work as quickly as possible.
So I decided to modify code by myself, even if I'm not a good coder.
I commented every changes I made like that:
Added lines: //++Alberto
Removed lines: //--Alberto

In MDB2/Driver/mssql.php I modified functions _checkSequence and nextID

function _checkSequence($seq_name)
{
$query = "SELECT * FROM $seq_name";
$tableExists =& $this->_doQuery($query, true);
if (PEAR::isError($tableExists)) {
if ($tableExists->getCode() == MDB2_ERROR_NOSUCHTABLE) {
return false;
}
//return $tableExists;
return false;
}

// return true; //--Alberto
return mssql_result($tableExists, 0, 0); //++Alberto

//With this change function returns the last (and only) value stored into the sequence field

}

// if ($this->_checkSequence($sequence_name)) { //--Alberto
$seq_val = $this->_checkSequence($sequence_name); //++Alberto
if ($seq_val) { //++Alberto

//With this change I store the result to reuse it

$query = "SET IDENTITY_INSERT $sequence_name ON ".

// "INSERT INTO $sequence_name ($seqcol_name) VALUES (0)"; //--Alberto
"INSERT INTO $sequence_name ($seqcol_name) VALUES (" . ($seq_val + 1). ")"; //++Alberto

//Here was the failure: value 0 can be insert only once.
//With this change I insert the value read from the sequence plus 1

} else {
$query = "INSERT INTO $sequence_name ($seqcol_name) VALUES (0)";
}
$result =& $this->_doQuery($query, true);
$this->popExpect();
if (PEAR::isError($result)) {
if ($ondemand && !$this->_checkSequence($sequence_name)) {
$this->loadModule('Manager', null, true);
// Since we are creating the sequence on demand
// we know the first id = 1 so initialize the
// sequence at 2

// $result = $this->manager->createSequence($seq_name, 2); //--Alberto
$result = $this->manager->createSequence($seq_name); //++Alberto

//I want to start sequence from 1, not from 2

if (PEAR::isError($result)) {
return $this->raiseError($result, null, null,
'nextID: on demand sequence '.$seq_name.' could not be created');
} else {
// First ID of a newly created sequence is 1
return 1;
}
}
return $result;
}
$value = $this->lastInsertID($sequence_name);
if (is_numeric($value)) {
$query = "DELETE FROM $sequence_name WHERE $seqcol_name < $value";
$result =& $this->_doQuery($query, true);
if (PEAR::isError($result)) {
$this->warnings[] = 'nextID: could not delete previous sequence table values from '.$seq_name;
}
}
return $value;
}

In MDB2/Driver/Manager/mssql.php I've modified function createSequence

function createSequence($seq_name, $start = 1)
{
$db =& $this->getDBInstance();
if (PEAR::isError($db)) {
return $db;
}

$sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
$seqcol_name = $db->quoteIdentifier($db->options['seqcol_name'], true);
$query = "CREATE TABLE $sequence_name ($seqcol_name " .
"INT PRIMARY KEY CLUSTERED IDENTITY($start,1) NOT NULL)";

$res = $db->exec($query);
if (PEAR::isError($res)) {
return $res;
}

if ($start == 1) {
// return MDB2_OK; //--Alberto Function MUST insert a value (need by changes made in _checkSequence)
}

$query = "SET IDENTITY_INSERT $sequence_name ON ".
"INSERT INTO $sequence_name ($seqcol_name) VALUES ($start)";
$res = $db->exec($query);

if (!PEAR::isError($res)) {
return MDB2_OK;
}

$result = $db->exec("DROP TABLE $sequence_name");
if (PEAR::isError($result)) {
return $db->raiseError($result, null, null,
'createSequence: could not drop inconsistent sequence table');
}

return $db->raiseError($res, null, null,
'createSequence: could not create sequence table');
}

Perhaps it's not the best way to correct the problem, but with this changes sequences seem to work (at least with my machine configuration) and I haven't found collateral effects yet.

[2007-01-19 13:34 UTC] alberto dot morselli at cup2000 dot it

Hi David,
I saw you have corrected sequence emulation with the changes I suggested you.

Unfortunately yesterday I noticed this solution should have problems if many client call nextId function at the same time.
So I ask a MSSQL expert for some seggestions, and I found a better and much much easier way to do the same.

In attachment you can find the new solution, based on mdb2_driver_mssql-1.1.2
The only modified file is MDB2/mssql.php: basing on revision 1.152, I only changed instruction with the INSERT in nextID function (you can see //++Alberto comment).
MDB2/Manager/mssql.php doesn't need any changes, so the working release is 1.82

Thank you
Alberto Morselli

[2007-01-19 15:41 UTC] alberto dot morselli at cup2000 dot it

I forgot to reopen the bug...