On Wed, Jun 10, 2009 at 10:44 AM, MB Software Solutions General Account<[email protected]> wrote: > 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 > } > ------------------------------------------------------------------------ ------------------------
hahahahaha You should be worried about what they are doing. He is using Fox like syntax from SQL. How wrong is that? I wonder why I found a different backend so long ago. Takes 3 operations just to get a key. 1) Wrong !!! cmd.ExecuteNonQuery() 2) He needs to run ALL that code in one pass instead of multiple ones. 3) He should return the value in an ExecScaler instead of that reader crap. id =cmd.ExecuteScalar( ); What is totally scary is that this is hard coded and not OOP where you pass to your DA layer the code to run and receive the value. Or in other words these people need a code review by someone outside of their group. That catch and finally statement are not correct either. Why would you need to unlock after you unlocked and didn't have a problem? HELLO! These guys/gals need some direction in C# outside of interacting how they work with VFP. String example of execute scaler here <http://tinyurl.com/ms6dhc> -- Stephen Russell Sr. Production Systems Programmer Web and Windows Development Independent Contractor Memphis TN 901.246-0159 _______________________________________________ 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.
