On Mon, June 8, 2009 5:41 pm, Stephen Russell wrote:
> On Mon, Jun 8, 2009 at 4:02 PM, MB Software Solutions General
> Account<[email protected]> wrote:
>
>> We've got a vendor here at my day job where they are having a problem
>> generating unique keys for new records in a table. They claim to be
>> locking the record, incrementing the counter, updating the underlying
>> keys table, then unlocking the record. This is a DotNet program,
>> though, working alongside of our VFP9 app. They say they're using the
>> VFP OLEDB
>> provider only, and for some reason, the NextID values in the keys lookup
>> table are getting reset to 0. This is NOT a problem with our app as
>> it's running in hundreds of centers across the world, but now that their
>> app is in the mix, there's a problem (yet they're claiming that it's not
>> a problem with their app!!!!! LOL!).
>>
>> My question is this: would they run into problems with their approach
>> (using only VFP OLEDB) if they did NOT all deploy the multi-threaded
>> runtime? Perhaps they don't need runtimes if they're only using the VFP
>> OLEDB from their DotNet app?????
>>
> -------------------------------------------------------------
>
>
> I do not think that they can issue a LOCK unless they pass back a
> transactional SQL Statement.
>
> Find out if they are stating their code within a trans.
Here's the code he's using from his DotNet app. Can you see anything
wrong with it? Many thanks in advance!!!!
------------------------------------------------------------------------
This is the code currently deployed in prod.
private int _generateIdentity(OleDbConnection conn, string table, string
idfield)
{
int id = -1;
OleDbCommand cmd = new OleDbCommand();
OleDbDataReader reader = null;
try
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn; // Set command Connection property
cmd.CommandType = CommandType.Text; // Set command
CommandType property
// Execscript('USE ConnectionString Path\Idtable.dbf
shared' +CHR(13) + CHR(10)+ 'FLOCK("IDTABLE")')
// e.g. Execscript(USE C:\SYM\SYM_NEW_DATA\IDTABLE.DBF +
CHR(13) + CHR(10) + FLOCK("IDTABLE"))
// Open the table in shared mode and use FLOCK to lock
the entire table
cmd.CommandText = "Execscript('USE " +
_connectionString.Split('=', ';')[3] + "\\IDTABLE.DBF
SHARED' + CHR(13)+ CHR(10)+ 'FLOCK(\''IDTABLE\'')')";
cmd.ExecuteNonQuery(); // Execute the query
// Retrieve the lastid value for the corresponding table
cmd.CommandText = "SELECT " + DBFields.idtable.lastid + "
FROM " + DBTables.IDTABLE + " WHERE " +
DBFields.idtable.table + " ='" + table.ToUpper() + "' AND
" + DBFields.idtable.idfield + "='" + idfield.ToUpper() +
"'";
reader = cmd.ExecuteReader(); // Execute the query
if (reader.Read()) // If data available
id =
Convert.ToInt32(reader[DBFields.idtable.lastid].ToString());
// Assign the retrieved value to a variable
reader.Close(); // Close the reader object
if (id != -1)
id++; // Increment the value of the last id field
// Update IDTABLE with the incremented lastid value for
the corresponding table
cmd.CommandText = "UPDATE idtable SET " +
DBFields.idtable.lastid + " = " + id + " WHERE " +
DBFields.idtable.table + " = '" + table + "' AND " +
DBFields.idtable.idfield + " ='" + idfield + "'";
cmd.ExecuteNonQuery(); // Execute query
}
catch (Exception x)
{
_errorCode = -1;
_message = x.Message;
}
finally
{
cmd.CommandText = "Execscript('UNLOCK IN IDTABLE')"; //
Unlock IDTABLE
cmd.ExecuteNonQuery(); // Execute the query
// Dispose all used objects
cmd.Dispose();
}
return id; // Return the updated lastid
}
------------------------------------------------------------------------
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
