Please click here for information around the SA Corona Virus.
New Year
Happy 2011 to all!
A new year, and new beginnings. There are a lot of things I want to change in my life, but as with all new years resolutions, we’ll see how it goes. 😉
Posted in General
Leave a comment
Recursive CTE's
First, the problem: You have a table containing some kind of a hierarchy using parent IDs. You have to write some SQL to return all of this data in the hierarchy with each parent and then the child records.
One way of doing this would have been to use a loop, starting at the top of the hierarchy, working your way down. SQL Server 2008 provides a new kind of Common Table Expression (CTE), called a recursive CTE. It consists of two parts:
- An anchor query, which is the source of the recursion, along with a UNION ALL statement and a second query, with recurses across the anchor query
- An outer select that references the routine and specifies the number of recursion levels
Table Create and Sample Data:
create table Employee
{
EmployeeID int,
ManagerID int,
FullName varchar(50)
}
go
insert Employee values(0, 1, 'CEO')
insert Employee values(1, 2, 'Marketing Director')
insert Employee values(1, 3, 'IT Director')
insert Employee values(2, 4, 'Marketing Manager')
insert Employee values(4, 5, 'Sales Analyst 1')
insert Employee values(4, 6, 'Sales Analyst 2')
insert Employee values(3, 7, 'Systems Manager')
insert Employee values(3, 8, 'Hardware Manager')
insert Employee values(7, 9, 'Developer')
insert Employee values(7, 10, 'Tester')
insert Employee values(8, 11, 'Technician')
insert Employee values(8, 12, 'Telephone Specialist')
go
The CTE:
with EmployeeCTE as
(
-- The Anchor Query
select emp.EmployeeID,
emp.ManagerID,
'' as 'Manager',
emp.FullName
from Employe emp with (nolock)
where ManagerID = 0
UNION ALL
select emp.EmployeeID,
emp.ManagerID,
cte.FullName as 'Manager',
emp.FullName
from EmployeeCTE cte
join
Employee emp with (nolock) on emp.ManagerID = cte.EmployeeID
)
select *
from EmployeeCTE
C# Dll's and Delphi
Today I had to create a C# Dll to be called from a Delphi 4 application. The only way of doing it is to register the Dll for COM interop. Its not that hard to do, but I was missing a few attributes on the class and the interface.
A few things are needed to start off:
- All needed Classes, Interfaces and Methods need to be public
- Assembly needs to be signed with a Strong Name
- Classes and Interfaces needs to have Guid’s
Easiest way to do it is to add all the needed methods to an interface and make your class implement the interface.
Guid’s are generated by:
guidgen.exe
The Interface:
// IRandom.cs
using System.Runtime.InteropServices;
[Guid("Axxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"),
ComVisible(true)]
public interface IRandom
{
[DispId(1), ComVisible(true)]
int GetRandomNumber(int seed);
// next methods get DispId(2), DispId(3), etc...
}
The Class:
// RandomDll.cs
using System.Runtime.InteropServices;
[Guid("Bxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"),
ClassInterface(ClassInterfaceType.None),
ComVisible(true)]
public class RandomDll: IRandom
{
#region IRandom Members
[ComVisible(true)]
public int GetRandomNumber(int seed)
{
Random rand = new Random(seed);
return rand.Next();
}
#endregion
}
Sign the Assembly by generating a new key pair and adding it to the project. Next, click Properties on the project, go to the “Signing” tab and tick “Sign the assembly”. Then select the new key file with you generate.
Generate a key file:
sn.exe -k KeyPair.key
Last but not least, you’ll have to click Properties on the project, go to the “Build” tab and tick “Register for COM interop”. Now when you build, Visual Studio will export the type library for the Dll and register it as a COM object for you.
Now to use this Dll in Delphi you need to import the type library. Click Project->Import Type Library, select your .tlb and click Create Unit. Add the generated unit and ComObj to your uses list and reference the class via the interface.
uses ComObj; procedure TForm1.Button1Click(Sender: TObject) var interfaceRef: IRandom; result: Integer; begin interfaceRef := CreateComObject(CLASS_RandomDll_TLB) as IRandom; result := interfaceRef.GetRandomNumber(10); label1.Caption := IntToStr(result); end;
If all went well you’ll be able to call managed code from Delphi now!
Microsoft Certification 70-433: MS SQL 2008 Database Development
A while ago my client decided to switch over from using Sybase ASE to MS SQL Server 2008. Having not used MS SQL for the past couple of years, I decided to go write the certification exam as a way to force myself to read up on MS SQL Server (i haven’t used it since 2000). Unfortunately only the technology specialist (TS) training kits are out, so getting the certified IT professional (MCITP) will have to wait for now.
There are two different routes you can go, either developer or database administrator, I decided to go for the developer one first. I will recommend the exam to anyone looking to get to know the features in 2008, but already know their SQL very good. I must add that it was one of the hardest certification exams I have done so far. The official kit training kit does not go into too much detail.
Once I get time (and start using them), I will post a couple of articles about the new features.
Next on the radar, 70-432 TS: Microsoft SQL Server 2008, Installation and Maintenance. If you are interested in any of the SQL Server certifications, have a look here.
Posted in Microfost Certifications, SQL
Leave a comment
Up and Running
The blog is up and running! I still need to sort out the theme and add a few things though…
Posted in General
Comments Off on Up and Running