August 02, 2007
Essential SQL Server Date Function
This is really useful:
Essential SQL Server Date, Time and DateTime Functions
I've always find out strange to work with date and time in SQL Server, it just feel so ... unnatural. The aforementioned link list the important date & time function in SQL Sever. Goodness :)
Posted by vhadiant at 08:57 PM | Comments (0) | TrackBack
February 06, 2006
CodeStriker with Visual Source Safe
I'm checking out the latest version of CodeStriker to see if it's suitable for my current project. It's a really good, no frills, but working really well code review tool. I've used it extensively before, David - CodeStriker's author - and I used to work at the same company. Of course, CodeStriker back then was vastly different with the current incarnation.
Setting it up in my Dell 610 work laptop is painless. I already have IIS and MS SQL Server setup, Perl was the only missing component. The default Perl package from Active State is missing 2 mandatory packages that CodeStriker wants. PPM pretty much solved this problem.
CodeStriker's documentation is good, although you have to at least know what you're doing to be able to get CodeStriker up and running. I guess that's fair enough, I don't think CodeStriker is geared toward the "for dummies" crowd.
The user interface has changed a lot since the early days of CodeStriker and it uses [gasp]database[/gasp] to store its topic. I remember back then David was adamant that he's not going to add DB support :) Enough user requests must have changed his stand on this.
CodeStriker was never meant to be used with Source Safe, so to create the "topic" (the code review) you have to use the ssdiff.pl script. This script is working good enough but there's one problem.
ssdiff.pl wants you to supply the VSS project name. That's fair enough, but most of our developers don't work across area of the system. In fact most of us work in our little components deep inside the source trunk. For example, the project that we are working is $/MyProject. This project has really deep tree structure, many directories from the top project tree. To run ssdiff.pl from the top directory will take too long. I want to be able to cd to my directory and run ssdiff.pl from that directory. Unfortunately I have to modify the parameter to ssdiff.pl to reflect the project that I'm currently on.
This sucks, so I modified ssdiff.pl to supply a "working directory" (a Source Safe concept) and automatically parse the current directory and append it to the working directory to create the project.
For example: the top project is "$/MyProject" and the "working directory" of this project is in C:\Company\Current\Code. Thus when I cd to the directory "C:\Company\Current\Code\DataAccess\Component1\Code", it will automatically parse this and create a string "$/MyProject/DataAccess/Component1/Code" as the VSS project. This modification is very simple even for me who don't know anything about perl.
Add this in the configuration section:
my $working_dir = 'C:/Company/Code';
my $base_vss = '$';
Add this after the "use strict"; line
use Cwd;
my $dir = getcwd;
my $clean_vss_project = substr($dir, length($working_dir));
my $vss_project = $base_vss . $clean_vss_project;
$ss_project = $vss_project;
And that's it. When you cd to "C:\Company\Current\Code\DataAccess\Component1\Code" and run ssdiff.pl > topic txt it will actually run
ssdiff.pl "$/MyProject/DataAccess/Component1/Code" > topic.txt
One caveat: If you are installing codestriker in Windows 2003 and you found out that you can't run your Perl script, you must enable the "Web Service Extension" for Perl CGI scripts. Open IIS control panel to do this. By default Windows will prohibit the execution.
Posted by vhadiant at 07:34 PM | Comments (0) | TrackBack
October 19, 2005
Oracle BLOB/CLOB array problem
Try as I might I couldn't get .NET to work correctly with CLOB/BLOB array in Oracle. I have a stored proc that really needs a BLOB array as a parameter and I keep getting "Internal Error" (couldn't remember the code now). Google doesn't return that many suggestion either. I eneded up had to concatenate all my BLOB together, send another array to indicate the size of each chunk of the BLOB and split it in the stored proc. Thankfully we can do this quite easily using the dbms_lob.read method. It's frustrating really, but I couldn't find a single example that shows CLOB/BLOB with PL/SQL AssociativeArray working nicely with .NET.
Posted by vhadiant at 11:54 PM | Comments (0) | TrackBack
October 13, 2005
Empty array in Oracle and .NET
Looks like Oracle doesn't like you passing an empty array. I've been getting this weird OracleParameter.Value is invalid. The strange thing is checking out Google doesn't give me much, this one kinda describe my problem:
http://forums.oracle.com/forums/message.jspa?messageID=947090
Basically if you have an array in your stored proc as a parameter, you need to make the array size to be at least 1, otherwise the said error will happen. This is really inconvenient since now you have to check whether it's your workaround or it's a real value.
One way to get around this is to have an array size parameter, this will work alright if you are converting DataTable into arrays, 1 parameter can indicate the size of the collection of array you are sending to Oracle. However for a single array send up this can be really annoying.
Posted by vhadiant at 06:46 PM | Comments (0) | TrackBack
August 16, 2005
Oracle's cardinality hint
Whenever you are trying to use in memory array in your SQL statement, Oracle's query optimiser seems to get confused in doing its job. You have to help it by giving the cardinality hint, otherwise a full table scan is guaranteed to happen.
For example you have a type
numtable is table of number
And you populate your internal array like this:
select client_id bulk collect into client_ids
from client where client_type = "XXX";
When you're trying to use the array in this query:
select a.client_name, a.address
from client_detail a,
table(cast(client_ids as numtable )) t
where a.client_id = t.column_value
This looks simple, but regardless how you define the index, Oracle will do a full table scan. Don't really know why. To fix this you need to give it a cardinality hint, doesn't have to be precise but at least some rough estimate how many records you think the array will contain. For example:
select /*+ cardinality (t 8) */ a.client_name, a.address
from client_detail a,
table(cast(client_ids as numtable )) t
where a.client_id = t.column_value
That will make the query peforms heaps better.
Posted by vhadiant at 08:52 PM | Comments (0) | TrackBack
August 11, 2005
Optimistic locking with Oracle 10
New in Oracle 10 is the ORA_ROWSCN pseudo column. This column contains a number which will be automatically incremented upon update.
For example table A
ORA_ROWSCN | ID | DESC
=======================
4300001231 | 10 | Just some desc
4300001231 | 11 | another desc
When you do an update use a simple select statement like this:
update A set DESC = 'New desc' where ID = 10 AND ORA_ROWSCN = 4300001231;
Of course this is assuming that you brought back the ORA_ROWSCN value when you do your select statement. After the update and commit the ORA_ROWSCN value of 4300001231 will be automatically incremented. So how do you know that your update is successful or not? If you are using ADO.NET you should be able to tell how many records are affected and throw an error if it's less than you expected (this is situation it is 1 record), in PL/SQL you check the ROWCOUNT variable like this:
if SQL%ROWCOUNT<=0 then
raise_application_error(-20000, 'Record has been changed by another');
end if;
This way you can do you update safely without locking the row. Oh by default Oracle will not enable rowscn tracking so you have to enable it yourself using the rowdependencies keyword when creating the table, ie:
create table A (...) rowdependencies;
Posted by vhadiant at 10:57 PM | Comments (0) | TrackBack
August 10, 2005
Oracle outer join with multiple fields
I don't know why but I always thought that outer joining with multiple fields are not going to work. So when I had to do one today I decided to give it a test.
Say a simple table
parent
{ p_id number,
account_num varchar2(10),
account_type number
}
and
child
{ c_id number,
account_num varchar2(10),
account_type number
}
and we need to join where both the account_num and account_type is the same if they exist on the child table, if the child record does not exist still include the parent record. The query is simply:
select *
from parent p, child c
where p.account_num = c.account_num(+)
and p.account_type = c.account_type(+)
It's so simple I didn't think it was going to work initially :)
Posted by vhadiant at 11:11 PM | Comments (0) | TrackBack
July 25, 2005
elsif
Took me a while to find this out but elsif is the keyword to do "else if" in PL/SQL grrr ... who designed this thing ...
if i < 5 then
null;
elsif i < 10 then
null;
else
null;
end if;
Posted by vhadiant at 08:51 PM | Comments (0) | TrackBack
July 21, 2005
Oracle exception handling
A good tutorial on PL/SQL exception handling.
Posted by vhadiant at 10:04 PM | Comments (0) | TrackBack
July 20, 2005
Oracle "connect by" "start with"
Oracle's connect by and start with is one very powerful feature that I've just discovered recently. This feature is really useful when you have parent - child relationship in a table, for example this type of layout:
id | parent_id | val
=============
1 | null | 5
2 | 1 | 6
3 | 1 | 7
I also found another good trick that although many of the samples on the Net don't show it, start with clause can be supplied with multiple parameter, making it possible to start with a sub-selected ids.
Posted by vhadiant at 10:13 PM | Comments (0) | TrackBack
