Just glancing through this code, I see these two things to have concerns about: There is no validation for the FLOCK took place. The Select may not return a value, and yet they still run the update putting in a -1
HTH, Tracy -----Original Message----- From: MB Software Solutions General Account Sent: Wednesday, June 10, 2009 11:45 AM 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/000801c9e9e4$24e766d0$6eb634...@com ** 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.
