Stored Procs, Packages, Views...Pah!
-
Warning: The Following is a Rant. From time to time I get into the religious 'To Stored Proc or not To Stored Proc' argument. Let me confess right now. As much as I appreciate all the pro's of Stored Procs, I still prefer to not use them when it can be avoided. It may be a technically inferior solution but I still prefer to create functions and subs in my VB or C# code that perform the equivalent task. I have finally decided that the reason for my bias is that the greatest Code Horrors I deal with now are badly written Packages and Stored Procs etc. It's a bloody nightmare. And to top it all, I regularly have to deal with the fact that the features needed to debug aren't installed, or they're running some cut down version of a DBMS. So I'm debugging this rats maze of code using techniques that I haven't used since I wrote Basic on my Sinclair Spectrum. Plug in Values. Run. Check Log File. Nothing Happened. Plug in Values. Run. Check Log File. Something unexpected happened. repeat to fade. Right now I'm trawling through the PL/SQL code of a very large globally known company. Honestly I've decided that what I'm looking at is a cast off from that team of monkeys that are working on the complete works of Shakespeare. I also regularly have to deal with Databases that contain hundreds of functions, procs and views where nobody knows if they are needed anymore but everybody is terrified to modify or remove any of them. I see companies who spawn a new copy every time a proc needs to be changed (just in case) and use that. I see companies who have no Gold version of their DB. When they need a DB either for test or for a new Production site, they just copy an existing production site. As for version control. It seems DB Objects live in some abstract zone, like International waters that aren't covered by the treaties that cover version control. This is no way to live. Thanks for letting me get that off my chest. Now back to the rats maze. -Richard
Hit any user to continue.
-
Warning: The Following is a Rant. From time to time I get into the religious 'To Stored Proc or not To Stored Proc' argument. Let me confess right now. As much as I appreciate all the pro's of Stored Procs, I still prefer to not use them when it can be avoided. It may be a technically inferior solution but I still prefer to create functions and subs in my VB or C# code that perform the equivalent task. I have finally decided that the reason for my bias is that the greatest Code Horrors I deal with now are badly written Packages and Stored Procs etc. It's a bloody nightmare. And to top it all, I regularly have to deal with the fact that the features needed to debug aren't installed, or they're running some cut down version of a DBMS. So I'm debugging this rats maze of code using techniques that I haven't used since I wrote Basic on my Sinclair Spectrum. Plug in Values. Run. Check Log File. Nothing Happened. Plug in Values. Run. Check Log File. Something unexpected happened. repeat to fade. Right now I'm trawling through the PL/SQL code of a very large globally known company. Honestly I've decided that what I'm looking at is a cast off from that team of monkeys that are working on the complete works of Shakespeare. I also regularly have to deal with Databases that contain hundreds of functions, procs and views where nobody knows if they are needed anymore but everybody is terrified to modify or remove any of them. I see companies who spawn a new copy every time a proc needs to be changed (just in case) and use that. I see companies who have no Gold version of their DB. When they need a DB either for test or for a new Production site, they just copy an existing production site. As for version control. It seems DB Objects live in some abstract zone, like International waters that aren't covered by the treaties that cover version control. This is no way to live. Thanks for letting me get that off my chest. Now back to the rats maze. -Richard
Hit any user to continue.
Richard A. Dalton wrote:
I have finally decided that the reason for my bias is that the greatest Code Horrors I deal with now are badly written Packages and Stored Procs etc.
But THIS is the problem, not really the 'concepts' of stored procedures or packages. The same can be said about regular code and writing shared libraries and using objects. Poorly written anything is junk. You can’t blame the tool or the concept really. Blame the user of the concept/tool = YES Blame the reviewer of the designer = YES Blame the reviewer of the implementation = YES You can use the best tools on the best platform using the best agreed upon methodologies and still write code that is ugly.
-
Richard A. Dalton wrote:
I have finally decided that the reason for my bias is that the greatest Code Horrors I deal with now are badly written Packages and Stored Procs etc.
But THIS is the problem, not really the 'concepts' of stored procedures or packages. The same can be said about regular code and writing shared libraries and using objects. Poorly written anything is junk. You can’t blame the tool or the concept really. Blame the user of the concept/tool = YES Blame the reviewer of the designer = YES Blame the reviewer of the implementation = YES You can use the best tools on the best platform using the best agreed upon methodologies and still write code that is ugly.
Ray Cassick wrote:
You can use the best tools on the best platform using the best agreed upon methodologies and still write code that is ugly.
:thumbsup:
-
Richard A. Dalton wrote:
I have finally decided that the reason for my bias is that the greatest Code Horrors I deal with now are badly written Packages and Stored Procs etc.
But THIS is the problem, not really the 'concepts' of stored procedures or packages. The same can be said about regular code and writing shared libraries and using objects. Poorly written anything is junk. You can’t blame the tool or the concept really. Blame the user of the concept/tool = YES Blame the reviewer of the designer = YES Blame the reviewer of the implementation = YES You can use the best tools on the best platform using the best agreed upon methodologies and still write code that is ugly.
I agree 100%, there is too many drawbacks on using adhock queries in the client application that most of the times prevents scaling an application. what if you have a table holding customer records, and you want to move the address columns to a new table to introduce multiple addresses for a customer. how are you going to due this when you have a web app, thick client, analysis cubes, reporting that will all require change and how do you manage deploying this change out? by preventing adhock or table direct queries all one would have to due is modify a few views and procs. secondly how can you ensure that all the adhock queries in all applications that are consuming this database are all using indexes, common business logic and formulas. and how would you trace down a poorly written adhock query that is table scanning, how do you go about fixing it and deploying it without impacting other user applications. my developers may only select from a view or call a proc from any application period.... look on the bright side, you have an opportunity to catalog all objects and send out impact statements to understand who is using what and also have an opportunity to find synergies between your development teams to come to a common process
-
Richard A. Dalton wrote:
I have finally decided that the reason for my bias is that the greatest Code Horrors I deal with now are badly written Packages and Stored Procs etc.
But THIS is the problem, not really the 'concepts' of stored procedures or packages. The same can be said about regular code and writing shared libraries and using objects. Poorly written anything is junk. You can’t blame the tool or the concept really. Blame the user of the concept/tool = YES Blame the reviewer of the designer = YES Blame the reviewer of the implementation = YES You can use the best tools on the best platform using the best agreed upon methodologies and still write code that is ugly.
You are echoing exactly the point I made about VB. It's the developers not the tools that are the problem. Yes. You can write good, clear PL/SQL (ish). Yes. All of the problems I have mentioned are the fault of humans, not the software, not the language. BUT....... The level of expertise generally for PL/SQL or T/SQL or indeed databases in general seems so poor that I consider them toxic pieces of technology. Right now I'm working on Code that comes from...ORACLE. Surely to goodness if any company should care about turning out a decent stab at good PL/SQL it should be ORACLE. It's a fricken mess. I can't stress enough just how bad this code is. I've been in this business for over 14 years and this is genuinely the worst code of any kind that I have ever worked with. I will go so far as to say that it is impossible to work with. Now. As bad as the code can be in VB or ASP.Net or C++ or Whatever, at least I have decent relatively mature and solid debugging tools. At least by and large source code tends to be under source control. At lest by and large there tend not to be multiple different versions of the same functions (ish). Also, the nature of PL/SQL and T/SQL etc are that they are In my opinion fundamentally ugly languages. Verbose and difficult to work with when doing anything but the most basic tasks. The Development Tools seem geared towards light admin rather than heavy development. Perhaps, with the right set of tools, life could be easier, but far from the right set of tools, most companies I visit don't even have a basic set of tools. They set up the cheapest simplest configuration that gets them moving. Need Debugging? Screw you Jack...create some log files. So we have a number of problems coming together in a perfect storm. 1. Developers DB skills beyond the absolute basics are poor. Attitude seems to be...DB? Pff How hard can that be? It's just SQL right? 2. Most who can write PL/SQL or T/SQL have no concept of what quality code is. Indeed I don't think they see their PL/SQL as code, and they don't feel obliged to observe ANY rules of craftsmanship. 3. Development and Debugging tools are poor at best. 4. Most clients seem confused about how to integrate DB development into their overall development process. And seem reluctant to heed advice on how to do so. 5. There can and often is a turf war between developers and DBA's that make a difficult situation worse. Overall...my feeling is...it's not wort
-
You are echoing exactly the point I made about VB. It's the developers not the tools that are the problem. Yes. You can write good, clear PL/SQL (ish). Yes. All of the problems I have mentioned are the fault of humans, not the software, not the language. BUT....... The level of expertise generally for PL/SQL or T/SQL or indeed databases in general seems so poor that I consider them toxic pieces of technology. Right now I'm working on Code that comes from...ORACLE. Surely to goodness if any company should care about turning out a decent stab at good PL/SQL it should be ORACLE. It's a fricken mess. I can't stress enough just how bad this code is. I've been in this business for over 14 years and this is genuinely the worst code of any kind that I have ever worked with. I will go so far as to say that it is impossible to work with. Now. As bad as the code can be in VB or ASP.Net or C++ or Whatever, at least I have decent relatively mature and solid debugging tools. At least by and large source code tends to be under source control. At lest by and large there tend not to be multiple different versions of the same functions (ish). Also, the nature of PL/SQL and T/SQL etc are that they are In my opinion fundamentally ugly languages. Verbose and difficult to work with when doing anything but the most basic tasks. The Development Tools seem geared towards light admin rather than heavy development. Perhaps, with the right set of tools, life could be easier, but far from the right set of tools, most companies I visit don't even have a basic set of tools. They set up the cheapest simplest configuration that gets them moving. Need Debugging? Screw you Jack...create some log files. So we have a number of problems coming together in a perfect storm. 1. Developers DB skills beyond the absolute basics are poor. Attitude seems to be...DB? Pff How hard can that be? It's just SQL right? 2. Most who can write PL/SQL or T/SQL have no concept of what quality code is. Indeed I don't think they see their PL/SQL as code, and they don't feel obliged to observe ANY rules of craftsmanship. 3. Development and Debugging tools are poor at best. 4. Most clients seem confused about how to integrate DB development into their overall development process. And seem reluctant to heed advice on how to do so. 5. There can and often is a turf war between developers and DBA's that make a difficult situation worse. Overall...my feeling is...it's not wort
Richard A. Dalton wrote:
Treat the DB as a data store only *unless* the client has the infrastructure and the expertise to support using the DB for more.
Agreed. Or unless, you control the DB as well.
Richard A. Dalton wrote:
Screw you Jack...create some log files.
Well, at least you're not named Jack. :-D
-
Richard A. Dalton wrote:
Treat the DB as a data store only *unless* the client has the infrastructure and the expertise to support using the DB for more.
Agreed. Or unless, you control the DB as well.
Richard A. Dalton wrote:
Screw you Jack...create some log files.
Well, at least you're not named Jack. :-D
Andrew Rissing wrote:
Agreed. Or unless, you control the DB as well.
I think this small modification of my original point answers the points raised by PoweredByOtgc above. Yes. There are types of applications and applications of a certain scale where it's very hard to get by without using the additional powers of the DB Server. but... If you are building one of those applications you damn well better be in an environment that can handle it and know what you are doing. Using the old 80/20 rule. 80% of apps can probably get by with using the DB as a pure dumb data store, and in my opinion that should probably be the default approach. The DB is too important. It's the foundation of all your systems. It's too important to be screwed around with by code monkeys who assume that because they can write an SQL query and know what an index is they can write Stored Procs, Packages and all manner of things. I'll say it right now, hand on heart, I've been writing PL/SQL and T/SQL for years and in my opinion even I shouldn't be. I don't believe I am skilled enough. It's a specialised area that needs a hell of a lot more respect and perhaps a sprinkling of fear. Nobody assumes that because you can use Javascript you must therefore be able to write enterprise apps in Java. Yet that seems to be the assumption when considering SQL Queries and PL/SQL. Part of the problem is the notion that YOU MUST USE STORED PROCS. It drives inexperienced codes down a road they shouldn't be on. I think far from encouraging programmers to work with stored procs by default. The default approach should be to work with AdHoc queries entirely in your language of choice. When you advance to a level where you can work effectively in PL/SQL and T/SQL you would probably be working in that 20% or less of apps that really need that. And that PL/SQL T/SQL track should be a clearly defined path that you follow, not something you fell into. Bottom line folks...can we pleeease get a bit of software craftsmanship in the DB. Anyhoo, thanks for listening. It's been a help. -Rd
Hit any user to continue.
-
You are echoing exactly the point I made about VB. It's the developers not the tools that are the problem. Yes. You can write good, clear PL/SQL (ish). Yes. All of the problems I have mentioned are the fault of humans, not the software, not the language. BUT....... The level of expertise generally for PL/SQL or T/SQL or indeed databases in general seems so poor that I consider them toxic pieces of technology. Right now I'm working on Code that comes from...ORACLE. Surely to goodness if any company should care about turning out a decent stab at good PL/SQL it should be ORACLE. It's a fricken mess. I can't stress enough just how bad this code is. I've been in this business for over 14 years and this is genuinely the worst code of any kind that I have ever worked with. I will go so far as to say that it is impossible to work with. Now. As bad as the code can be in VB or ASP.Net or C++ or Whatever, at least I have decent relatively mature and solid debugging tools. At least by and large source code tends to be under source control. At lest by and large there tend not to be multiple different versions of the same functions (ish). Also, the nature of PL/SQL and T/SQL etc are that they are In my opinion fundamentally ugly languages. Verbose and difficult to work with when doing anything but the most basic tasks. The Development Tools seem geared towards light admin rather than heavy development. Perhaps, with the right set of tools, life could be easier, but far from the right set of tools, most companies I visit don't even have a basic set of tools. They set up the cheapest simplest configuration that gets them moving. Need Debugging? Screw you Jack...create some log files. So we have a number of problems coming together in a perfect storm. 1. Developers DB skills beyond the absolute basics are poor. Attitude seems to be...DB? Pff How hard can that be? It's just SQL right? 2. Most who can write PL/SQL or T/SQL have no concept of what quality code is. Indeed I don't think they see their PL/SQL as code, and they don't feel obliged to observe ANY rules of craftsmanship. 3. Development and Debugging tools are poor at best. 4. Most clients seem confused about how to integrate DB development into their overall development process. And seem reluctant to heed advice on how to do so. 5. There can and often is a turf war between developers and DBA's that make a difficult situation worse. Overall...my feeling is...it's not wort
1. Developers DB skills beyond the absolute basics are poor. Attitude seems to be...DB? Pff How hard can that be? It's just SQL right? That's why a development team should hire GOOD developers. Anyone that say this is IMHO NOT a 'good developer'. 2. Most who can write PL/SQL or T/SQL have no concept of what quality code is. Indeed I don't think they see their PL/SQL as code, and they don't feel obliged to observe ANY rules of craftsmanship. That's why you should hire GOOD DBAs. And by Good I don't mean 'can write a query with joins and that does not use Select * all over the place. I mean a real DBA. I would agree that many don not feel they are writing code, but add to that the fact that ANY company that has ANY change management or review process SHOULD be running these resources through a code review process. 3. Development and Debugging tools are poor at best. I don't know if I agree here. I have seen some pretty good tools. I am getting partial to Toad lately, and the SQL Enterprise manager does some decent performance monitoring metrics. The large gap I see when things like web services must run multiple queries against disparate Dbs from different vendors and do aggregation before returning result sets, but that is just something inherent in using multiple platforms like I run up against all th times (IE: Mixing data from DB2, SQL and Oracle SPs). 4. Most clients seem confused about how to integrate DB development into their overall development process. And seem reluctant to heed advice on how to do so. Again, people problem that can be solved (IE: The idiots need to be fired) and not really a technology issue. 5. There can and often is a turf war between developers and DBA's that make a difficult situation worse. I hear that :) but again, people problem. I seem to remember hearing a discussion VERY similar to this way way back in the days when OOP was 'invented' and people started to discuss the n-tier model and how UI side developers were never going to 'get' how their design impacts the overall use of the app so code jockeys should just be allowed to write it all. :) Man, what comes around goes around :) But I DO understand it... been knee deep in it... lived through it...
-
Warning: The Following is a Rant. From time to time I get into the religious 'To Stored Proc or not To Stored Proc' argument. Let me confess right now. As much as I appreciate all the pro's of Stored Procs, I still prefer to not use them when it can be avoided. It may be a technically inferior solution but I still prefer to create functions and subs in my VB or C# code that perform the equivalent task. I have finally decided that the reason for my bias is that the greatest Code Horrors I deal with now are badly written Packages and Stored Procs etc. It's a bloody nightmare. And to top it all, I regularly have to deal with the fact that the features needed to debug aren't installed, or they're running some cut down version of a DBMS. So I'm debugging this rats maze of code using techniques that I haven't used since I wrote Basic on my Sinclair Spectrum. Plug in Values. Run. Check Log File. Nothing Happened. Plug in Values. Run. Check Log File. Something unexpected happened. repeat to fade. Right now I'm trawling through the PL/SQL code of a very large globally known company. Honestly I've decided that what I'm looking at is a cast off from that team of monkeys that are working on the complete works of Shakespeare. I also regularly have to deal with Databases that contain hundreds of functions, procs and views where nobody knows if they are needed anymore but everybody is terrified to modify or remove any of them. I see companies who spawn a new copy every time a proc needs to be changed (just in case) and use that. I see companies who have no Gold version of their DB. When they need a DB either for test or for a new Production site, they just copy an existing production site. As for version control. It seems DB Objects live in some abstract zone, like International waters that aren't covered by the treaties that cover version control. This is no way to live. Thanks for letting me get that off my chest. Now back to the rats maze. -Richard
Hit any user to continue.
Yeah, I pretty much agree and haven't used stored procs hardly anywhere. I also try to use very generic SQL when ever possible to avoid problem swapping out databases.
CQ de W5ALT
Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software
-
Warning: The Following is a Rant. From time to time I get into the religious 'To Stored Proc or not To Stored Proc' argument. Let me confess right now. As much as I appreciate all the pro's of Stored Procs, I still prefer to not use them when it can be avoided. It may be a technically inferior solution but I still prefer to create functions and subs in my VB or C# code that perform the equivalent task. I have finally decided that the reason for my bias is that the greatest Code Horrors I deal with now are badly written Packages and Stored Procs etc. It's a bloody nightmare. And to top it all, I regularly have to deal with the fact that the features needed to debug aren't installed, or they're running some cut down version of a DBMS. So I'm debugging this rats maze of code using techniques that I haven't used since I wrote Basic on my Sinclair Spectrum. Plug in Values. Run. Check Log File. Nothing Happened. Plug in Values. Run. Check Log File. Something unexpected happened. repeat to fade. Right now I'm trawling through the PL/SQL code of a very large globally known company. Honestly I've decided that what I'm looking at is a cast off from that team of monkeys that are working on the complete works of Shakespeare. I also regularly have to deal with Databases that contain hundreds of functions, procs and views where nobody knows if they are needed anymore but everybody is terrified to modify or remove any of them. I see companies who spawn a new copy every time a proc needs to be changed (just in case) and use that. I see companies who have no Gold version of their DB. When they need a DB either for test or for a new Production site, they just copy an existing production site. As for version control. It seems DB Objects live in some abstract zone, like International waters that aren't covered by the treaties that cover version control. This is no way to live. Thanks for letting me get that off my chest. Now back to the rats maze. -Richard
Hit any user to continue.
Except when it comes to security - isolating code that changes the database into stored procedures that accept parameters helps guard against some of the most common security violations - SQL injection attacks, and far too few coders seem to appreciate the effort required to adequately ensure that SQL statements build and submitted to the database are adequately cleansed to prevent this. This scenario comes to mind: Little Bobby Tables (XKCD)
-
Except when it comes to security - isolating code that changes the database into stored procedures that accept parameters helps guard against some of the most common security violations - SQL injection attacks, and far too few coders seem to appreciate the effort required to adequately ensure that SQL statements build and submitted to the database are adequately cleansed to prevent this. This scenario comes to mind: Little Bobby Tables (XKCD)
Rob Grainger wrote:
SQL injection attacks ... far too few coders seem to appreciate the effort required to adequately ensure that SQL statements ... adequately cleansed to prevent this.
It's not that hard, is it? Provided you know what the string delimter and escape characters are, there's no problem. For example, in SQL Server, escape all string parameter values by doubling up any single quote character (thus escaping them), and wrap the whole string in a pair of single quote characters (thus defining the limits of the string to the parser), and you're sorted.
-
Rob Grainger wrote:
SQL injection attacks ... far too few coders seem to appreciate the effort required to adequately ensure that SQL statements ... adequately cleansed to prevent this.
It's not that hard, is it? Provided you know what the string delimter and escape characters are, there's no problem. For example, in SQL Server, escape all string parameter values by doubling up any single quote character (thus escaping them), and wrap the whole string in a pair of single quote characters (thus defining the limits of the string to the parser), and you're sorted.
...or just use Parameters instead - it makes the code easier to read as a bonus...
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
-
...or just use Parameters instead - it makes the code easier to read as a bonus...
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
Could you supply example code of how to use parameters with an IN clause, where the number of items in the in clause is not known until runtime? I've never seen a solution to that one...
-
Could you supply example code of how to use parameters with an IN clause, where the number of items in the in clause is not known until runtime? I've never seen a solution to that one...
Not without thinking about it for a while! But if you are doing things like that, you should know what you are doing anyway rather than just suck-it-and-see which most seem to try. :laugh:
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
-
1. Developers DB skills beyond the absolute basics are poor. Attitude seems to be...DB? Pff How hard can that be? It's just SQL right? That's why a development team should hire GOOD developers. Anyone that say this is IMHO NOT a 'good developer'. 2. Most who can write PL/SQL or T/SQL have no concept of what quality code is. Indeed I don't think they see their PL/SQL as code, and they don't feel obliged to observe ANY rules of craftsmanship. That's why you should hire GOOD DBAs. And by Good I don't mean 'can write a query with joins and that does not use Select * all over the place. I mean a real DBA. I would agree that many don not feel they are writing code, but add to that the fact that ANY company that has ANY change management or review process SHOULD be running these resources through a code review process. 3. Development and Debugging tools are poor at best. I don't know if I agree here. I have seen some pretty good tools. I am getting partial to Toad lately, and the SQL Enterprise manager does some decent performance monitoring metrics. The large gap I see when things like web services must run multiple queries against disparate Dbs from different vendors and do aggregation before returning result sets, but that is just something inherent in using multiple platforms like I run up against all th times (IE: Mixing data from DB2, SQL and Oracle SPs). 4. Most clients seem confused about how to integrate DB development into their overall development process. And seem reluctant to heed advice on how to do so. Again, people problem that can be solved (IE: The idiots need to be fired) and not really a technology issue. 5. There can and often is a turf war between developers and DBA's that make a difficult situation worse. I hear that :) but again, people problem. I seem to remember hearing a discussion VERY similar to this way way back in the days when OOP was 'invented' and people started to discuss the n-tier model and how UI side developers were never going to 'get' how their design impacts the overall use of the app so code jockeys should just be allowed to write it all. :) Man, what comes around goes around :) But I DO understand it... been knee deep in it... lived through it...
Ray Cassick wrote:
That's why a development team should hire GOOD developers.
Yes. Of course. As I've said, no matter what the tool ultimately it comes down to the people. But I'm coming from the perspective of 14 years of being brought into existing projects, or starting new projects in existing teams. I don't have the option (generally) of getting the team and the infrastructure the way I like. I am seriously considering throwing my hat at it and I have spoken to a few companies about joining them full time, but I'll only do it if I know I'm joining somewhere that is doing software properly (or at least seriously trying to hit that ideal). I've reached the point where I'll take the pay cut if I have to in order to work with good guys and gals. In the meantime I'm faced with debugging our profession as best I can. Boy, as a profesion we sure do suck. Right now I'm faced with the problems that aren't even the fault of the developers surrounding me. There is nobody to fire because the code that's screwing me up now comes from a Vendor, and no, dropping the vendor isn't an option either. And Yes, even if my notion of keeping developers away from the DB for as long as possible were to take hold in this company it wouldn't fix the problem of Vendors shipping bad DB Code. But it would be a start. We need to make a start. The Packages and Procs and Views that surround a Database are an API. They need to be given the same respect as any API. They are perceived as something less than an API. Something more pliable than an API. I so often hear the excuse that you should put logic into stored procs because Stored Procs are easier to change than code. THEY ARE NOT. They are exactly as difficult to change as code. You have the same difficulties of keeping backward compatibility of interfaces. I would contend that the "belief" that the DB is easier to change than code is a big part of the problem. I'm not saying you should't use Procs packages etc. I'm just saying that it shouldn't be the no-brainer people seem to want it to be. Particularly when you or your team are no-brainers. -Rd
Hit any user to continue.
-
Not without thinking about it for a while! But if you are doing things like that, you should know what you are doing anyway rather than just suck-it-and-see which most seem to try. :laugh:
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
You might be thinking for a while. As far as I'm aware, it's not possible.
-
Warning: The Following is a Rant. From time to time I get into the religious 'To Stored Proc or not To Stored Proc' argument. Let me confess right now. As much as I appreciate all the pro's of Stored Procs, I still prefer to not use them when it can be avoided. It may be a technically inferior solution but I still prefer to create functions and subs in my VB or C# code that perform the equivalent task. I have finally decided that the reason for my bias is that the greatest Code Horrors I deal with now are badly written Packages and Stored Procs etc. It's a bloody nightmare. And to top it all, I regularly have to deal with the fact that the features needed to debug aren't installed, or they're running some cut down version of a DBMS. So I'm debugging this rats maze of code using techniques that I haven't used since I wrote Basic on my Sinclair Spectrum. Plug in Values. Run. Check Log File. Nothing Happened. Plug in Values. Run. Check Log File. Something unexpected happened. repeat to fade. Right now I'm trawling through the PL/SQL code of a very large globally known company. Honestly I've decided that what I'm looking at is a cast off from that team of monkeys that are working on the complete works of Shakespeare. I also regularly have to deal with Databases that contain hundreds of functions, procs and views where nobody knows if they are needed anymore but everybody is terrified to modify or remove any of them. I see companies who spawn a new copy every time a proc needs to be changed (just in case) and use that. I see companies who have no Gold version of their DB. When they need a DB either for test or for a new Production site, they just copy an existing production site. As for version control. It seems DB Objects live in some abstract zone, like International waters that aren't covered by the treaties that cover version control. This is no way to live. Thanks for letting me get that off my chest. Now back to the rats maze. -Richard
Hit any user to continue.
Sql debug is always a problem no matter you use stored proc or straight sql statement in code. I see several advantages of using stored proc. It has faster execution. It is safe (against sql injection). Data/business logic can be modified in a stored proc alone without re-compliling the main application as long as the returned data columns are the same.
TOMZ_KV
-
Richard A. Dalton wrote:
I have finally decided that the reason for my bias is that the greatest Code Horrors I deal with now are badly written Packages and Stored Procs etc.
But THIS is the problem, not really the 'concepts' of stored procedures or packages. The same can be said about regular code and writing shared libraries and using objects. Poorly written anything is junk. You can’t blame the tool or the concept really. Blame the user of the concept/tool = YES Blame the reviewer of the designer = YES Blame the reviewer of the implementation = YES You can use the best tools on the best platform using the best agreed upon methodologies and still write code that is ugly.
It's not the tool that's used, it's the tool that uses it!
==================================== Transvestites - Roberts in Disguise! ====================================
-
Could you supply example code of how to use parameters with an IN clause, where the number of items in the in clause is not known until runtime? I've never seen a solution to that one...
-
You can't use parameters with an IN clause. You would have to use something similar to "WHERE ((col1 = @parm1) OR (col1 = @parm2)) ..." which is easy enough to build at runtime.
True, but it rules out stored procedures...
