I Learnt Today…

SELECT PatientId FROM Patients WHERE Surname = NULL  --  THIS IS WRONG
SELECT PatientId FROM Patients WHERE Surname is NULL  --  THIS IS CORRECT

SELECT PatientId FROM Patients WHERE Surname != NULL  --  THIS IS WRONG
SELECT PatientId FROM Patients WHERE Surname is not NULL  --  THIS IS CORRECT

2011-10-07

Timestamp is a value that is incremented by the database whenever an insert or update operation is performed.

Even though the name Timestamp could be a bit misleading, this value has no relevance to a clock related time. This only shows a linear progression of time.

For an example , it is something like your database saying it has been two update or insert commands since your last visit.

You can see this value by referring to @@DBTS  [ select @@DBTS ]

How can this be helpful at all?

Well, in simple terms, this helps to keep track of versions of records.

For an example, assume a scenario where you have to fetch a record from a table, manipulate the data and write it back.

What if the record gets changed (from another database call) after you fetched data? You will manipulate the old data and update the record without knowing somebody has updated the record in between your fetching and updating commands.

How can timestamp help?

You can add a column to your table (you can call it “Version”) and set its data type to TimeStamp. Then whenever you update or insert a record to this table, this column will record the database timestamp after that transaction.

So before writing your manipulated data, you can check if the timestamp value remains the same as what you read at the beginning of the transaction.

Following example demonstrates how timestamp can be used to monitor versions:

The Person table used for this example has a column called “Version” which is of type Timestamp.

First simply query the Version column of the table for the person called “Robert”

After an update to the same record, we will check the version (timestamp) of the same record:

As you can see, the timestamp value for the record has been automatically updated.

Note: Timestamp columns are automatically updated by the database engine so you do not have to specify value when either inserting or updating a row of a table which has a timestamp (version) column.

So to insert a record to a table with a timestamp column simply omit the timestamp column from your insert statement.

i.e. – Person table has following columns [Id, Given_Name, Family_Name, Age, Version]

Insert statement would be:

Insert into Person(Id, Given_Name, Family_Name, Age)
Values(001, "Robert", "Nox", 78);

The database will take care of the timestamp (version) column.

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

06-10-2011

We all get that day when we get an exception complaining about the single quotation or apostrophe that was in our SQL statement.

The best advisable thing to do is to use stored procedures so that all data are passed as parameters. However there are situations we have to use in-line SQL statements and even there are situations where even SQL parameters cannot manage this issue.

For example, if you use the exec method in you stored procedure body to do some dynamic stuff [read more about using exec to generate dynamic queries in stored procedures here ] you will have noticed that even if you pass a string with an apostrophe to an sql parameter it will still throw an exception at you !

So the only way out is to escape this character. Once you instruct the SQL parser to escape the character it will take the apostrophe as part of the string input not part of the command.

How to?

Simply replace your apostrophe / single quotation with two apostrophes / two single quotations .

i.e.
Replace Bob’s world With Bob’‘s world  <- these are two single quotation characters (not one double quotation character)

This can be easily done by using the string.replace method.

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

06-10-2011

Have you ever wanted to write a flexible and dynamic stored procedure that would allow you to send the table name as a parameter?

Have you ever wanted to write a flexible and dynamic stored procedure that would allow you to send only the condition but also the column name you want to include in the where clause?

Well I did. I wanted to create a stored procedure that would take Table name, Criteria column name  and the Target criteria value as parameters and create the SQL query dynamically.

Usually, we have to write our SQL code in the stored procedure body where we cannot treat our sql statement as a string.

The way to achieve this, however, is by using the Exec method provided in SQL.

Exec (execute) allows you to execute a command or a character string that contains Transact-SQL command(s).

Without wasting more time, following is the code I used to achieve my goal:

CREATE PROCEDURE GetRowCountByStringColumn

@TableName nvarchar(50),
@CriteriaColumnName nvarchar(50),
@CriteriaValue nvarchar(150)
AS
BEGIN

EXEC('SELECT  Count(*) FROM ' + @TableName
+ ' WHERE ' + @CriteriaColumnName + ' = ''' + @CriteriaValue + '''')

END
GO

As you can see the procedure takes three arguments:

TableName
CriteriaColumnName – the name of the column that should be checked in the where clause
CriteriaValue – the value the Criteria Column Name should be checked against

The exec command creates the SQL command (at run time) and executes it.

This helped me to create many dynamic stored procedures for my current project and saved me from having to create stored procedures for each table.

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

2010-04-01

We frequently get to fetch data from the database, update them and then write them back to the database.

Most of the time we only have to write them back as individual records.

How about updating a whole database table in the memory and having to synchronize all changes to the actual data table?

My initial thought was this would be full of complex coding. However, thanks to Microsoft, there’s nothing much to be done at all.

So how are we gonna do this is…

We will use a data adaptor to fill our data table as usual.

The only new thing is the use of a Command Binder.

Command Binder: A command binder is capable to detect changes that have occurred to a table (in the memory) and then automatically generate appropriate SQL statements to save those changes into the actual data table (in the database).

Following is a simple example: Scenario: In my application I had to take a database table name and present data in a data grid and then save all changes made by the user.

What we need: a data table an adaptor a connection a command builder I have defined them at the form level so I can use them across the form from different events.

private DataTable _tbl;

private SqlDataAdapter _adptr;

private SqlConnection _conn;

private SqlCommandBuilder _cbldr;

Step1: Initialize connection and retrieve data from the database

_tbl = new DataTable();
_conn = FetchData.GetOpenConnection();
_adptr = new SqlDataAdapter("Select * from " + DatabaseTableName, _conn);
_cbldr = new SqlCommandBuilder(_adptr);
_adptr.Fill(_tbl);

Step2: Let the user to change data (in here simply bind the table to a grid)

dgMainGrid.DataSource = _tbl;

Step3: Save (synchronize) changes to the actual database table Even though this is a complex process and undoubtedly would take a lot of effort to do manually, Thanks to .net framework all we need is a line of code.

_adptr.Update(_tbl);

Once you call the adaptor to update the table, it will use the command builder attached to it to generate all necessary SQL command building. The database table is now up-to-date!

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

2009-05-11

I have being working on a software application made on .Net and recently my client asked me to use D LINQ instead of SQL.

D LINQ has great benefits loaded! As I started working with DLINQ I started to know that preventing SQL injection is not a headache anymore and misspelled SQL queries will not trouble agian at run time because DLINQ generates all necessary SQL inside the framework!

I chose to use annotations inside the class instead of using separate xml file. Following is a part of the first class I ported to D LINQ.

</p>
_
Public Class Process
_
Public ProcessID As Integer
_
Public BusinessProcessID As String
_
Public ProcessText As String
...
...

ProcessID column is the primary key of my database table tbl_Process.

Important Point: I use database to auto-generate values for the primary key column (integer value incremented by one).

So when I run the application, It gave me this unexpected error:

Cannot insert explicit value for identity column in table ‘tbl_Process’ when IDENTITY_INSERT is set to OFF.

Basically, the IDENTITY_INSERT when using the database to auto generate value for a field but when I ran a SQL insert statement at the database end it worked fine!

After some tough time I found out the solution for this problem!

When we use an auto-generate field in a data table we have to specifically mention it in the matching field in the appropriate class.

The code to state this is:

Syntax:

IsDbGenerated:=True

This has to be added to the annotation added for the specific column like:

</p>
_
Public ProcessID As Integer

Now D LINQ can understand that the field value is auto-generated by the database!

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

2008-12-04

In this article I will discus how to backup a database as a FULL backup or with Transaction Log (Tail Log) backup and how to restore the database, how to recover transactions made after you taking the backup, etc.

Since it is not easy to use and organize images in the post itself I have uploaded a compiled pdf file of this article.

Please download the complete manual from following link:

How to backup and restore a database in Microsoft SQL Server 2005

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

2008-11-24

Among various helpful functions provided by Oracle, Trunc function took my attention today because I had to fix a defect where some one has misunderstood and misused this function.

As the name itself suggests, the trunc function is capable of truncating a value. This function can be used either on a date type value or a numeric value.

Using trunc for numeric values

When it comes to numbers, trunc function can return a number truncated to a certain number of decimal places.

syntax:

trunc( number, [ decimal_places ] )

Example usage:

trunc(125.815)	would return 125
trunc(125.815, 0)	would return 125
trunc(125.815, 1)	would return 125.8
trunc(125.815, 2)	would return 125.81
trunc(125.815, 3)	would return 125.815
trunc(-125.815, 2)	would return -125.81
trunc(125.815, -1)	would return 120
trunc(125.815, -2)	would return 100
trunc(125.815, -3)	would return 0

Menol

Note: The trunc function does not round the values like in round function. It simply truncates the number as instructed.

Using trunc for date values

The trunc function is capable to truncate a date value to a specific unit of measure.

Syntax:

trunc ( date, [ format ] )

Possible values for format parameter:

Unit	Valid format parameters
Year	SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO Year	IYYY, IY, I
Quarter	Q
Month	MONTH, MON, MM, RM
Week	WW
IW	IW
W	W
Day	DDD, DD, J
Start day of the week	DAY, DY, D
Hour	HH, HH12, HH24
Minute	MI

Menol

Example Usage:

trunc(to_date(’22-AUG-03′), ‘YEAR’)	would return ’01-JAN-03′
trunc(to_date(’22-AUG-03′), ‘Q’)	would return ’01-JUL-03′
trunc(to_date(’22-AUG-03′), ‘MONTH’)	would return ’01-AUG-03′
trunc(to_date(’22-AUG-03′), ‘DDD’)	would return ’22-AUG-03′
trunc(to_date(’22-AUG-03′), ‘DAY’)	would return ’17-AUG-03′

Menol

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek