Thursday, November 5, 2009
Back to Basics: Parameterizing Inline SQL
Things have been a bit slow with any interesting project work and I don't want this blog to fall
even more stagnant . So I am going to be writing a new series called "Back to Basics" containing best practices and some basic code references aimed at newer coders.
There are some situations in which you cannot use stored procedures in order to handle all of your data access. Some cases it is just because of the architecture where you are at, other times it is because you are building the SQL at run time (this still can be done in both P/L or T-SQL but in this case we are going to write it inline).
But no matter what the circumstance, it is still good practice to parameterize your statements. This can help with style as well as helping to protect against
SQL Injection (Wikipedia).
We will take a normal inline SQL statement and convert it to a parameterized statement. We will start with this.
public void Save() {
string sql = "INSERT INTO People (First_Name, Last_Name, Age) VALUES (\"" + this.FirstName + "\", \"" + this.LastName +
"\", \"" + this.Age + "\");";
using (OracleConnection conn = new OracleConnection("connstring"))
using (OracleCommand comm = new OracleCommand(conn, sql)) {
comm.CommandType = CommandType.Text;
comm.ExecuteNonQuery();
}
}This will work as is, and technically is correct. However, the readability goes out the window. With all the escape characters and everything in that statement it becomes hard to read very quickly. This is also a very small insert statement. Imagine a larger table with 15-20 rows. The statment would become huge and debugging would become hard if you mistyped something, so along with the readability, scaleability is bad as well.
So lets take that statement and parameterize it. This adds to the number of lines of code, but in this case it is well worth it. We take out all of the items in the VALUES clause and replace them with identifiers, much like you would if you were writing a full stored procedure. Then you can add the parameters to the command object. With this you can specify data types and sizes to even further restrict the data entered to help make sure no bad data gets through. This also allows you to significantly shorten your SQL statement, and allows you to add another value much easier than in the previous example.
public void Save() {
string sql = "INSERT INTO People (First_Name, Last_Name, Age) VALUES (:FirstName, :LastName, :Age);";
using (OracleConnection conn = new OracleConnection("connstring"))
using (OracleCommand comm = new OracleCommand(conn, sql)) {
comm.CommandType = CommandType.Text;
comm.Parameters.Add(":FirstName", OracleType.VarChar, 50).Value = this.FirstName;
comm.Parameters.Add(":LastName", OracleType.VarChar, 50).Value = this.LastName;
comm.Parameters.Add(":Age", OracleType.Int32).Value = this.Age;
comm.ExecuteNonQuery();
}
}This will work for both Oracle and MS SQL, you would just have to change from and Oracle connection and command to a SQL one. Also I think the preferred syntax when using MS SQL would be to use the @ symbol rather than the colon.
Labels: .NET, Back to Basics, SQL
posted by Tom Becker at
|
Friday, June 26, 2009
Recursive Functions in SQL 2000
Recently I was asked for a list of all the history associated with a row in a database. In this case when a row in the table was updated, we did an insert with the updates and updated the old row with a pointer to the new row. We needed to do this so we could show history when needed without having to create a history table.
So since I didn't know how many ancestors a row could have, I decided to use recursion. And for those of you who never had a programming class with me know that I really don't like recursion, it confuses me. But it had to be done. So below is the recursive function that can be called to get a list of all ancestors of a given row:
CREATE FUNCTION [dbo].[udf_GetAncestors]
(@CurrID int)
RETURNS varchar(50) AS
BEGIN
DECLARE
@ancestorIDs varchar(50),
@NextID INT
SELECT @NextID = ItemID
FROM Items
WHERE RevisedItemID= @CurrID;
SET @ancestorIDs = Convert(varchar, @CurrID);
IF @NextID IS NOT NULL BEGIN
SET @ancestorIDs = '''' + @ancestorIDs + ''', ' + dbo.udf_GetAncestors(@NextID);
END
Return @ancestorIDs
END
Labels: SQL
posted by Tom Becker at
|
Wednesday, July 16, 2008
Transactions in VB.Net and Oracle
This is going to be kinda a two part post. Each talking about an approach to the same problem. But first some background.
I am creating a questionnaire that is being saved to the database in a mutli-step process. 4 steps to be exact. If any one of those steps fail I want to be able to roll back to the beginning so I don't have a stub of a questionnaire floating around. I need that process to be
atomic.
I originally wrote it so that the 4 steps were independent of each other so that it could be as flexible as possible. And then the problem of the stubs came up. So I had to make it atomic. So since I already had four functions and 4 stored procedures in Oracle I created a transaction in .Net and just added it to my command objects (Part 1 of this article), but that didn't work for me. So then I went to combining all 4 procedures into one and using Oracle transactions (Part 2).
Part 1: .Net Transactions
.Net transactions aren't actually that hard, In my case I was using the
OracleTransaction class but there is a SQLTransaction class that works the same way for those of you using MS-SQL. Essentially you tie your transaction to your connection and your command object. You must use the same connection for each command object, but that is pretty much your only limitation. The most common implementation that I've seen is just passing the connection and the transaction to all the functions that needed it and that is what I did as well. Code examples below:
Private Sub Save()
Dim conn As New OracleConnection("ConnString")
Dim trans As OracleTransaction
conn.Open()
Try
trans = conn.BeginTransaction
saveThingOne(conn, trans)
saveThingTwo(conn, trans)
trans.Commit()
Catch ex As Exception
trans.Rollback()
End Try
End Sub
Private Sub saveThingOne(ByVal conn As OracleConnection, ByVal trans As OracleTransaction)
Using comm As New OracleCommand("Save_Thing_One_Stored_Procedure", conn, trans)
comm.CommandType = CommandType.StoredProcedure
comm.Parameters.Add("IN_ID", OracleType.Number).Value = intID
comm.Parameters.Add("IN_THING_ONE", OracleType.VarChar, 60).Value = strThingONe
comm.ExecuteNonQuery()
End Using
End Sub
Private Sub saveThingTwo(ByVal conn As OracleConnection, ByVal trans As OracleTransaction)
Using comm As New OracleCommand("Save_Thing_Two_Stored_Procedure", conn, trans)
comm.CommandType = CommandType.StoredProcedure
comm.Parameters.Add("IN_ID", OracleType.Number).Value = intID
comm.Parameters.Add("IN_THING_TWO", OracleType.VarChar, 60).Value = strThingTwo
comm.ExecuteNonQuery()
End Using
End Sub
However, in my current environment we use stored procedures for all of our database interactions. And that being the case it makes using the .Net transactions impossible to use. Now I don't know if the above code would work in a MS-SQL environment, but in an Oracle environment it does not. The best reason I can figure it is because Oracle treats a stored procedure as a transaction in and of it self. So when you get to the end of the stored procedure it does a full commit automatically...hence nothing to rollback. So I had to combine all 4 procedures into one massive one and use Oracle transaction statements to make sure everything is done at once.
Part 2: Oracle Transactions
There are a lot of articles out there about Oracle transactions, but I found that most of them had all the information that I needed separated out across multiple pages. So here it is all together.
As I mentioned above an Oracle stored procedure does a commit on successful completion of the procedure. But you can also explicitly tell it to do the commit any time you want to during its execution by calling the "commit;" command. You can also rollback at any point if you want to just by calling the "rollback;" command. If you put the two together you have the beginning of a nice way to ensure an atomic transaction.
The only other new part of the stored procedure you must add is a an exception clause. This as you may guess works much like a try/catch clause in VB. You can either catch specific Oracle errors by calling them by name, or you can do a catch all and just handle all of them the same way, which is what I did below. The only thing I would warn you about though, is that if you are having trouble with your procedure and are still testing I wouldn't put the exception clause in there quite yet. Oracle will catch and handle the error and end cleanly. You will not know that your code is bombing. It took me a while to figure out why it was performing a rollback and exiting clean.
So here is the code for a transaction in Oracle:
PROCEDURE SAVE_TWO_THINGS(
IN_THING_ONE IN VARCHAR2,
IN_THING_TWO IN VARCHAR2
)
IS
BEGIN
INSERT INTO TABLEONE
(ACTIVE, THING_DESC)
VALUES
('Y', IN_THING_ONE);
INSERT INTO TABLETWO
(ACTIVE, THING_DESC)
VALUES
('N', IN_THING_TWO);
COMMIT;
EXCEPTION WHEN OTHERS THEN ROLLBACK;
END;
Note that the 'OTHERS' keyword in my exception clause is where you would catch a specific oracle error, you can string together as many of those as you want to, but I would recommend using the 'OTHERS' as a kind of catch all at the end if you want to make sure you catch everything.
Summary: Unless you are using inline SQL to connect to your Oracle database then you're really not going to be able to use .Net transactions. So you are going to have to use the Oracle transaction. Many would argue that I should have done that from the beginning since Oracle is such a powerhouse that doing processing there isn't a big deal so any atomic transaction should be tried to be done in Oracle anyway, but I really think it is up to the programmer. It really is personal preferences.
Labels: .NET, Oracle, SQL
posted by Tom Becker at
|
Monday, July 7, 2008
Cheat Sheets
I was just going through some old files and found a couple of cheat sheets that I made for the interns at my last job. And I just thought that I'd share them with you all.
The first one is just some basic
.Net 2.0 information (Word). Function calls, loops, conditionals and the like.
The other one is base
SQL and T-SQL (Word). Selects, updates, loops, and conditional. It is pretty basic although it does have some 'GROUP BY' and 'HAVING' clauses in there.
I don't know how much use they would be except for beginners, but it could be nice to put on your cube wall for easy reference.
Labels: .NET, SQL
posted by Tom Becker at
|
