| Groups | Blog | Home |
sql server programming : T-SQL and Record Locking
I'm trying to use SQL to provide services via a server to client relationship - potentially with in an n-tier structure. As I see it I have two options. 1) Create a program which builds SQL statements via passed parameters and use either statements or prepared statements to execute the SQL command required. This is okay but it's not ideal from a performance perspective. 2) Create stored procedures which excute SQL statements via passed parameters. I prefer option 2) but as I understand it any returned result set is not updatable as an SQL cursor cannot be left open to forefill any further SQL routines - by the way I will be using ODBC so as I understand it I cannot bind to the T-SQL. This might be acceptable if I could maintain locked records seperately, if not then I go back to option 1). Can anyone help me ? Is it possible to create T-SQL and have ODBC result sets be updateable ? It is possible to directly maintain locked records ? If so would this also apply to Jet ?
The last thing you want to do in an n-tier app is to hold cursors
open. The whole idea is that you use stateless methods that hold no resources either on the SQLS or in your middle tier objects. To this end, you need one set of stored procedures to select the data, and another set of parameterized stored procedures that map to your middle-tier method calls (insert, update, delete). This gives you clean lines of separation between the client, the MT and the server, and at no time is the client ever in direct communication with the server. For security reasons as well as performance reasons you never want to build dynamic SQL and pass it from the client. If I was you, I'd invest in a good book or two on n-tier architecture before jumping in. None of this would apply to Jet because it isn't a server -- it's only a desktop database. However, the basic idea of fetching only needed data and not holding locks is always a good idea no matter what your back end is. -- Mary MCW Technologies http://www.mcwtech.com On Sun, 21 Dec 2003 22:41:05 -0800, "Gary" <Gary.Pott@NTLworld.com> [quoted text, click to view] wrote:
>Hi, > >I'm trying to use SQL to provide services via a server to client relationship - potentially with in an n-tier structure. > >As I see it I have two options. >1) Create a program which builds SQL statements via passed parameters and use either statements or prepared statements to execute the SQL command required. >This is okay but it's not ideal from a performance perspective. >2) Create stored procedures which excute SQL statements via passed parameters. > >I prefer option 2) but as I understand it any returned result set is not updatable as an SQL cursor cannot be left open to forefill any further SQL routines - by the way I will be using ODBC so as I understand it I cannot bind to the T-SQL. > >This might be acceptable if I could maintain locked records seperately, if not then I go back to option 1). > >Can anyone help me ? > >Is it possible to create T-SQL and have ODBC result sets be updateable ? >It is possible to directly maintain locked records ? If so would this also apply to Jet ? > >Thanks, good look with your SQL programming and Merry Christmas - Happy New Year !
Hi Mary, The specific problem I'm having is that if I create T-SQL procedures to run an update I cannot be sure that the request I'm going to make will succeed because I cannot create a cursor with update enabled in T-SQL and then process the result set via ODBC - with the driver I am using I cannot even pass back a cursor via the stored procedure. Even if this transaction is a short term one - in which case I am forced into using a prepared statement rather than a stored procedure. In this case I have an object server which handles database table, index and field layouts amongst other things - these are passed to requesting applications and hence save database meta data look ups. The server also handles new definitions added to the database and hence needs an updatable cursor. Perhaps, having done a load of research into the topic I have answered my own question - it's not possible with the Microsoft driver I am using with Java. I only tried this question as a last resort. The answer you have given though is applicable to Client side requests and I do see what you are saying. Personally, I'm used to locking all records required until they are used etc. I'm currently in two minds about how and if I really need some sort of record locking policy - if I do I shouldn't really move away from record locking provided via the SQL server. If I do move away from handling locks via the SQL server then this implies all transactions have to go through my policy BUT this will exlude any SQL server side updates - which is a loss I would not like to make. In THIS context (Which is outside of my original question) it would be perfect to be able to maintain the SQL server list of client side locks directly in SQL server and hence forget about any further handling of locks (Becuase they would be handled by default) - but I doubt if this facility is in SQL server, if it is I would be very happy. My aim with Jet has always been to allow it to be used - I don't want to force someone into buying SQL Server unless they have a high transaction rate. Jet should in theory be able to handle most of my needs but only time will tell on this score. Perhaps I will drop using any Microsoft products and try others instead as the support for them is mostly tied in with Microsoft programming which is a route I don't want to go down due to lack of support for other environments - Midrange and Mainframe architecture. *** Sent via Developersdex http://www.developersdex.com ***
You should investigate using stored procedures if you are using SQL
Server as your back end. If you are worried about record locking and transactional integrity, you can implement all updates inside of explicit transactions, and send back success/failure information in output parameters. Properly constructed stored procedures can be executed and processed by any modern programming language. If your programming language does not support processing output parameters, then you are using outdated technology. Crafting a scalable Jet back-end is going to take an entirely different approach since the two products are designed for different environments (server and desktop), to say nothing of mainframe, which is another beast entirely. A "one size fits all" solution that will work in every enviornment simply doesn't exist in the real world. -- Mary MCW Technologies http://www.mcwtech.com On Fri, 26 Dec 2003 06:00:27 -0800, Gary Pott <gary.pott@ntlworld.com> [quoted text, click to view] wrote:
> >Hi Mary, > >The specific problem I'm having is that if I create T-SQL procedures to >run an update I cannot be sure that the request I'm going to make will >succeed because I cannot create a cursor with update enabled in T-SQL >and then process the result set via ODBC - with the driver I am using I >cannot even pass back a cursor via the stored procedure. Even if this >transaction is a short term one - in which case I am forced into using a >prepared statement rather than a stored procedure. In this case I have >an object server which handles database table, index and field layouts >amongst other things - these are passed to requesting applications and >hence save database meta data look ups. The server also handles new >definitions added to the database and hence needs an updatable cursor. > >Perhaps, having done a load of research into the topic I have answered >my own question - it's not possible with the Microsoft driver I am using >with Java. I only tried this question as a last resort. > >The answer you have given though is applicable to Client side requests >and I do see what you are saying. Personally, I'm used to locking all >records required until they are used etc. I'm currently in two minds >about how and if I really need some sort of record locking policy - if I >do I shouldn't really move away from record locking provided via the SQL >server. If I do move away from handling locks via the SQL server then >this implies all transactions have to go through my policy BUT this will >exlude any SQL server side updates - which is a loss I would not like to >make. In THIS context (Which is outside of my original question) it >would be perfect to be able to maintain the SQL server list of client >side locks directly in SQL server and hence forget about any further >handling of locks (Becuase they would be handled by default) - but I >doubt if this facility is in SQL server, if it is I would be very happy. > >My aim with Jet has always been to allow it to be used - I don't want to >force someone into buying SQL Server unless they have a high transaction >rate. Jet should in theory be able to handle most of my needs but only >time will tell on this score. > >Perhaps I will drop using any Microsoft products and try others instead >as the support for them is mostly tied in with Microsoft programming >which is a route I don't want to go down due to lack of support for >other environments - Midrange and Mainframe architecture. > >*** Sent via Developersdex http://www.developersdex.com *** >Don't just participate in USENET...get rewarded for it!
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |