Manish Pansiniya’s Blog

Due to requirement of one of my project, i have developed SQL Query formatter in a quick way. It is long way back to 2006. Now, i am thinking that why not to share with the people. So i am sharing it right now.  There are still some problem in the formatter. I will soon post this project to CodePlex to enhance it . So please try it and let me know how can we make it big .

Let me know your feedback!!!

There are many realtime scenario where we need to change the transaction behavior. It is as follows:

• I am updating one row in a Begin Tran and Commit section. I have issued Begin Tran as well as the Update statement. Commit is not yet issued – because I want to perform some more actions in the same transactions on some other tables. If some other user wants to read this row that I have updated but not yet committed, what should happen?
• Consider another scenario. I start a transaction. I calculate a total of a field based upon all rows in a table. Now, I need to add a new record in another table which contains this total. Now, can the original table be changed by some other user after I calculate the total? In which case, there could be a mismatch. Do you want to take such chances?
• Another scenario. I am working on some transaction table between a range of keys within a transaction—say 10 and 20. There were only 5 records when I read the range – 10, 12, 14, 16, 20. Now I am working on other things in the transaction. Before I could commit the transaction, someone added another row with a key value of 11. Now, my base assumption about what records I read between 10 and 20 and further work upon them itself is wrong. Problem!!! Is it not?
• I start reading a long table. It is not a transaction at all. But other users want to refer to that table for updating some fields in specific rows. The query takes 20 minutes to read all the rows. What happens to other users who are trying to update the rows? Do they wait for 20 minutes? Or they are allowed to update the rows even when the rows are being read in a large query? What if the query was used to generate a summary report containing grand totals? The total would be wrong because after the summation started, some rows have changed. Some of these rows could have changed after the summation occurred. What’s to be done now?

In this case, we can use Transaction Isolation. Please go to below link for more information.

http://www.expresscomputeronline.com/20040426/techspace01.shtml

we were working with subsonic and MySQL database. Suddenly there was one error pops up and that is

{“Input string was not in a correct format.”}

I tried many things but could not find the issue. At last I put the pdb file of SubSonic.dll and debug the issue. The issue is in the inbuild column name. Subsonic use Audit fields (CreatedBy, CreatedOn, ModifiedBy and ModifiedOn ). So if any one of these fields present in your table and if it is not varchar type then zoooop…you will get this wierd exception . So to resolve the issue. just rename the field to another name.

If we are using SQL Server, generally we use TOP 10 to return top 10 rows of the table.

But in mysql it doesn’t work. We need to use following query to return top x result.

SELECT * FROM test.product p limit 10;

it returns top 10 records from product table. You can also define offset from where you want top 10 record. It is as follows:

SELECT * FROM test.product p limit 10 offset 10;

it returns first 10 records from 10th record.