Archive

Archive for the ‘Oracle’ Category

Querying Oracle using PowerShell

September 1, 2009 4 comments

Yesterday, I wrote up a quick bit of code to query out our SQL Servers.  Initially, I wanted a speedy way to hit, parse, and report back log4net logs in our “server status” scripts.

Well, never one to leave something alone, I started tinkering with Oracle support.  In our enterprise, most of our key systems sit on Oracle and there are SEVERAL opportunities for quick data retrieval routines that could help out in daily work.

Plus, doing an Oracle query in PowerShell beats five minute process of cranking up Oracle SQL Developer for a simple, single query. ๐Ÿ™‚

CODE: The full source of this is available here on codepaste.net.

param (
    [string]$server = “.”,
    [string]$instance = $(throw “a database name is required”),
    [string]$query
)

[System.Reflection.Assembly]::LoadWithPartialName(“System.Data.OracleClient”) | out-null
$connection = new-object system.data.oracleclient.oracleconnection( `
    “Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$server)(PORT=1521)) `
    (CONNECT_DATA=(SERVICE_NAME=$instance)));User Id=USER_ID;Password=PASSWORD;”);

$set = new-object system.data.dataset   

$adapter = new-object system.data.oracleclient.oracledataadapter ($query, $connection)
$adapter.Fill($set)

$table = new-object system.data.datatable
$table = $set.Tables[0]

#return table
$table

I chose to use the OracleClient library for simplicity sake.  I could have used ODP.Net; however, that’d make my scripts FAR less portable.  Since OracleClient isn’t loaded by default in PowerShell, this script loads it.  In addition, I chose to use the TNS-less connection string as I don’t typically keep a ‘tnsnames.ora’ file on my computer.  This further adds to the portability of the script.

Past that and the change from SqlClient to OracleClient, the rest of the code is the same from the prior example.

Dealing With Empty Strings and Nulls

One thing that I did run across that differed between Oracle and Microsoft SQL revolved around how empty strings were dealt with when parsing using PowerShell.

Example:

oq “SELECT * FROM Schools”

ID  NAME        PRINCIPAL_EMAIL_ADDRESS

  —-        ———————–

100 School

102 School

112 School      user3@domain.net

140 School      user1@domain.net

etc.

Now, what if I wanted to just see the schools missing a principal_email_address?  I’d just rewrite my SQL query, right?  Yeah, probably, but for the sake of argument and perhaps some scripting.

oq “SELECT * FROM Schools” | ? { $_.principal_email_address -eq “”}

No results.

What? Why not?  I see two in my last query.  Unfortunately, dealing with “nulls” and empty strings can get a bit tricky when pulling from database data.  With Microsoft SQL, a text-based column (varchar, ntext, etc) seems to handle -eq “” just fine, but Oracle is less than pleased.  @ShayLevy suggested -eq [string]::Empty but that didn’t pull through either. 

From a prior experiment, I also tried -eq $null and was greeted with something very different—it returned all results. Meh.

Randomly, I tried -like $null and it worked. Well, that’s interesting.  So the value isn’t empty in Oracle, but it is “like” a null.  After a bit more digging, I discovered that the real value is –eq [DBNull]::Value.

oq “SELECT * FROM Schools” | ? { $_.principal_email_address -eq [DBNull]::Value }

ID  NAME        PRINCIPAL_EMAIL_ADDRESS

  —-        ———————–

100 School
102 School

It makes sense… but more testing is required to see which is more reliable for a wide variety of data types.  I like the concept of “like null” to simulate “string empty or null”.  Further testing required. ๐Ÿ™‚

 

Advertisements

Configuring Oracle SQL Developer for Windows 7

I’m fully Vista-free now and loving it; however, Oracle SQL Developer has (once again) decided to simply be annoying to configure.

<rant>Yes, I realize Oracle hates Microsoft.  Girls, you’re both pretty—please grow up.</rant>

Anyway, after a bit of hunting, I think I’ve found a good mix for those of us who love SQL Developer for coding and testing, but don’t “use” a lot of the Oracle proprietary junk features that comes with it.

Originally, I was going to include a couple of the configuration files; however, they’re spread out EVERYWHERE and, frankly, I can’t find them all. ๐Ÿ˜ฆ  I also can’t figure out a way to import settings (without blowing my settings away first).

File Paths

As I mentioned before, some of the configuration files are spread out—everywhere.  Here’s where the most common files are located.

sqldeveloper.conf – <sqldeveloper folder>\sqldeveloper\bin\

ide.conf – <sqldeveloper folder>\ide\bin\

User-Configured Settings – %appdata%\SQL Developer\

I prefer to make my modifications in sqldeveloper.conf; however, a lot of the resources that pop up on Google make them in ide.conf.  Why?  I’m not sure.  sqldeveloper.conf simply CALLS ide.conf.  Meh.

Fixing Memory Consumption on Minimize

I found a reference to JDeveloper (another Java utility) that discussed how JDeveloper (and similarly, SQL Developer) pages everything when you minimize the window.

To fix this, open up sqldeveloper.conf and add the following line:

AddVMOption -Dsun.awt.keepWorkingSetOnMinimize=true

Fixing Aero Basic Theme

Tired of your IDE swapping back to Aero Basic whenever you launch SQL Developer?  Me too.  For now, Oracle reports that SQL Developer doesn’t support the full Aero Theme… or does it?

To enable Aero support (or at least keep it from bouncing back to Aero Basic), open up sqldeveloper.conf and add the following line:

AddVMOption -Dsun.java2d.noddraw=true

The Oracle forums also recommend trying the following line:

AddVMOption -Dsun.java2d.ddoffscreen=false

That option; however, never resolved the issue for me.  Your mileage may vary.

Cleaning Up The UI

The default UI leaves a lot to be desired for Oracle SQL Developer.  Here’s a few UI tips to try out.  These settings are found under Tools > Preferences.

Change the Theme – Environment > Theme. 

I like Experience Blue.  It’s clean, simple, and goes well with Windows 7’s look and feel.

Change Fonts – Code Editor > …

There are quite a few fonts that can be configured.  Here’s what I changed:

Code Insight – Segoe UI, 12
Display – check ‘Enable Text Anti-Aliasing’
Fonts – Consolas, 11
Printing – Consolas, 10

Disable Unnecessary Extensions – Extensions > …

Honestly, I don’t use ANY of the extentions, so I disabled everything as well as unchecking ‘Automatically Check for Updates’.  I’ve noticed that load time for the UI is insanely fast now (well, insanely fast for a Java app on Windows).

Window Locations

The only thing that I can’t figure out how to fix is the window location and placement.  Example: When you open a new worksheet, the results area is not visible (you have to drag that frame up each time).  That annoys me to no end and I can’t find a place to ‘save current window layout’ or similar.  Ideas?

That’s it!

With that, SQL Developer loads up quickly, connects, and displays just fine in Windows 7.

Fluent NHibernate Repository of… integers?

April 21, 2009 Comments off

I’d like to preface this by the fact that this “works” doesn’t mean it “should”.  If there’s a proper way to do this, I’m all ears. ๐Ÿ˜€

I recently needed to do some revamp to an application that queried lookup data from another data system.  The system had a collection of composite keys (age and total score) that returned a percentile score.  Easy enough; however, there are a couple dozen of these tables and I didn’t want to create a couple dozen domain objects/repositories for a SINGLE query.

Typically, the NHibernateRepository* takes a type parameter that matches to the mapped object (and provide the proper return type); however, in this case, I didn’t have a type to return, simply an integer.  So why wouldn’t that work?

public class ScoreRepository : NHibernateRepository<int>, IDisposable

With that in place, I can now add a query into Session:

public int GetConceptPercentile(int age, int total)

{

var result =

       Session.CreateSQLQuery(

“select perc from tblConcept where age = :age and total = :total”

             .SetInt32(“age”, age)

             .SetInt32(“total”, total)

.UniqueResult().ConvertTo<int>();

 

return result;

}

A few more of those, and our test looks like:

[Fact]

public void GetPercentiles_For_Student()

{

using (var repository = new ScoreRepository())

       {

              var languagePercentile =

             repository.GetLanguagePercentile(ageCalc_72months.TotalMonths, 18);

            

var motorPercentile =

             repository.GetMotorPercentile(ageCalc_72months.TotalMonths, 18);

            

var conceptPercentile =

             repository.GetConceptPercentile(ageCalc_72months.TotalMonths, 18);

 

             languagePercentile.ShouldBeEqualTo(12);

             motorPercentile.ShouldBeEqualTo(17);

             conceptPercentile.ShouldBeEqualTo(10);

}

}

Everything “appears” to be working; however, the extraneous methods that each NHibernateRepository includes (Get, GetAll, FindAll, etc) are defunct and just sitting there—very messy.

So is there a better way to use NHibernate/Fluent NHibernate WITHOUT mapping objects—those “lookup tables”?

Adventures with Fluent NHibernate

December 23, 2008 Comments off

Fluent NHibernate LogoOver the past few weeks, I’ve taken our existing framework library and reevaluated how we connect to our student information system (SIS) to pull information.  Almost all of our applications read from SIS for some bit of information or another; however, nothing writes to it.  Changes are only handled through the application itself as the business logic is blackboxed by the vendor.

In the old library, connectivity was a bit piecemeal.  When someone needed something, we wrote a test, wrote the implementation PL/SQL, and slugged in some objects.  It worked, was fairly quick, and provided a standardized mechanism for querying the system (avoiding EVERYONE recreating what constituted a ‘student’).

Unfortunately, maintaining the object relationships to the magic strings of PL/SQL code became tedious (at best) and a change was needed.  I’d been evaluating NHibernate for a couple of projects (to replace LINQ-to-SQL) as Entity Framework still doesn’t have sufficient Oracle ODP support out of the box (without paying for another provider).  NHibernate seemed ideal, but XML configuration files made me cringe.

After a bit of syntax searching, I stumbled upon Fluent NHibernate (FNH)—an API that generates both the configuration and XML mappings and allows a MUCH better refactoring experience.  I’m all for ReSharper friendly!

As you may have read, I managed to work out an Oracle9 persistance configuration for FNH that has worked out quite well.  With that in hand, here’s a summary of how things hooked up.  Any feedback would be greatly appreciated as most of my learning has been, in my opinion, the best kind—trial and error. ๐Ÿ™‚

Also, I’m using various bits from the S#arp Architecture project; however, not everything—remember, I just need readonly access and the amount of noise for all of the libraries was far more than I needed.  I’ve also made several modifications to things such as the PersistentObject and NHibernateSession to include more specific casing required by our organization.

Note: I’m not using the AutoMap features of Fluent NHibernate.  Why?  At this time, our data structure is VERY wonky (vendor controlled data structure, no real rhyme or reason to it, readonly access), so I have a TON of “TheColumnNameIs” and such to map the properties to the oddball data field names.  I am going to hit up the AutoMap features on my next project to better understand them. ๐Ÿ™‚

Part #1 – [Code Here]

[Fact]
public void Fetch_A_Report_By_Id()
{
       IReportRepository stubRepository =
              MockRepository.GenerateStub<IReportRepository>(); 

       Report stubReport =

              MockRepository.GenerateStub<Report>();

 

       stubReport.Id = 140;

           

       stubRepository

              .Expect(x => x.Get(140))

             .Return(stubReport);

 

       var report = stubRepository.Get(140);

 

       report.Id.ShouldBeEqualTo(140);

}

A quick, simple test that looks for a repository and grabs a single entity based on Id. 

At this point, I need an IReportRepository that has a Get method and returns a Report entity.

public interface IReportRepository : INHibernateRepository<Report>

{

}

I could simply call NHibernateRepository<Report>() (or INHibernateRepository<T> in this case) directly; however, I prefer to create entity-based repositories.  I suppose if I had an entity that would NEVER have it’s own specific methods, then I’d reconsider, but I don’t like to mix access methodologies around.

Next, our Repository needs a Report object.  Remember, just the basics.

public class Report : PersistentObject

{

}

Since our Id field comes from the PersistentObject base class, adding it again is unnecessary.

With our Test, IReportRepository, and Report entity objects—we’re now flying green.  At this point, everything is mocked using Rhino Mocks (v3.5).

Passing Test!

Now, let’s try an integration test and hit the database using a bit of test data.

Part #2 – [Code Here]

[Fact]

public void Fetch_A_Report_By_Id_From_Database()

{

       var repository = new ReportRepository();

       var report = repository.Get(140);

       report.Id.ShouldBeEqualTo(140);

}

For this test, we need an implementation of our IReportRepository, ReportRepository:

public class ReportRepository : NHibernateRepository<Report>

{

}

The plumbing of the data connection also needs to be hashed out.  Fluent NHibernate makes configuring NHibernate very easy.

Setting up the Entity Map

Generating the mapping files is as simple as using a couple of virtual methods.  Id, Map, References, etc. will grow to be your friends.

public class ReportMap : ClassMap<Report>

{

       public ReportMap()

       {

              CreateMap();

       }

 

       private void CreateMap()

       {

              WithTable(“Reports”);

              Id(x => x.Id);

       }

}

In a few short lines of code, this has told the Fluent NHibernate API to create a mapping file for the Report entity, to use the Reports table, and to assign the data from the Id column to the Id property of the entity.  Good stuff.

Setting up the Configuration to the Database

Replacing the challenging XML syntax is a VERY useful, fluent configuration API.  I’m using a modified version of Init that reslugs in a default configuration since we’re packaging these configurations for internal use.

public static class ExampleConfiguration

{

       public static Configuration Default

       {

              get { return Production; }

       }

 

       public static Configuration Production

       {

              get

              {

                     var config = new Configuration();

 

              MsSqlConfiguration.MsSql2005

                    .ConnectionString

                        .Database(“ExampleDB”)

                        .Server(“DBServer”)

                        .Username(“DBLogin”)

                        .Password(“DBPassword”)

                        .Create

                    .ConfigureProperties(config);

              return config;

              }

       }

 

       public static void Init(ISessionStorage storage)

       {

              Init(Default, storage);

       }

 

       public static void Init(Configuration configuration,

                               ISessionStorage storage)

       {

              // Any of the maps will do for the assembly mapping.

              NHibernateSession.Init(

                     typeof(ReportMap).Assembly, configuration, storage);

       }

}

Default – Set which of the configuration is the default; called by the overloaded Init method.

Production – Set to the production; you could have Test, AnotherTest, or whatever else your environment needs.  The actual Configuration is using Fluent NHibernate’s PersistanceConfiguration to generate the configuration.

Init(ISessionStorage) – An overloaded Init that calls the default configuration and passes the parameter session storage to NHibernate; this calls …

Init(Configuration, ISessionStorage) – Calls the base NHibernateSession.Init.  This takes an Assembly, the configuration we’re passing it, and the ISessionStorage.

Is there a better way?  If there’s a better way to get the Assembly passed along, I’d LOVE to know.  I’m using this methodology since I keep all of my maps in the same library, but if one found its way into another library, this would break.

Initializing the Configuration

With that configured, I put the Init into the constructor for our test (using xUnit, constructors > [SetUp] :P).

public RepositoryBuildUpExample_2()

{

       ExampleConfiguration.Init(new SimpleSessionStorage());

}

SimpleSessionStorage is a S#arp Architecture snippet for mocking NHibernate; works fantastic.

In a production application, such as an ASP.NET Web site, you would initialize the session in Session_Start or Application_Start and pass along a storage mechanism to save it in .NET session state (or another means).

That’s it—everything’s in place, let’s rerun our test!

Fluent NHibernate Example #2

Excellent.

At this point, further requirements flush out:

  • additional “Get” methods which are added to the ReportRepository,
  • additional properties (columns) for the entity,
  • and, of course, the tests that determine what code is needed.

For the “finished” example, you can download the [code here].

PersistanceConfiguration for Oracle/Fluent NHibernate

December 4, 2008 1 comment

On Tuesday, I mentioned that I created a custom PersistanceConfiguration (based on the template for MS SQL) to connect to Oracle.  I had a few individuals email me, so I went ahead and tossed the code out on Google Code for everyone.

Feel free to download the source here.

The class derives FluentNHibernate’s PersistanceConfiguration<T> and uses the Oracle.DataAccess (ODP) driver. 

To use it, run through just like the MS SQL version.  I did, however, add an OtherOptions property to pass caching, pooling, and other Oracle-specific parameters.  I could have put each to a separate property (and may in the future)—as soon as I can get F-NHibernate fully working. Heh.  Priorities, right?

// Add our Oracle configuration to the NHibernate Config.

var oracleConfiguration = OracleConfiguration.Oracle9

.ConnectionString

      .Server(“server”)

      .Port(1521)

      .Instance(“instance.here”)

      .Username(“user”)

      .Password(“p@$$w0rd”)

      .OtherOptions(“Statement Cache Size=50;”)

      .Create;

oracleConfiguration.ConfigureProperties(config);

config.AddMappingsFromAssembly(Assembly.Load(_mappingAssembly));

Fluent NHibernate With Oracle – An Answer…

December 3, 2008 1 comment

Yesterday, I posted up a real annoying issue with getting Fluent NHibernate working with Oracle.  After dinking most of the evening, I found (as with most things) that the issue had little to do with NHibernate and a great deal to do with my lack of understanding.

Ahh, the joys of learning.

So, here’s where I glitched.

1. The ClassMap needs more than to have public constructors—but must be public itself. 

public class StudentMap : ClassMap<Student>

{

       public StudentMap()

       {

              WithTable(“Students”);

 

              Id(x => x.Id, “pupil_number”)

                     .GeneratedBy.Native();

              Map(x => x.FirstName, “first_name”);

              Map(x => x.LastName, “surname”);

 

       }

}

Whoops.

2. The connection string is touchy.  My original connection string looked like:

@“User Id={0};

Password={1};

Pooling=True;

Enlist=False;

Statement Cache Size=50;

Min Pool Size=10;

Incr Pool Size=5;

Decr Pool Size=2;

Data Source= (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST={2})(PORT={3})))(CONNECT_DATA=(SERVICE_NAME={4}))”

Unfortunately, that doesn’t seem to work with the non-ODP driver (aka: the Microsoft driver).  Statement Cache Size and the Pooling Size are unknown parameters.  That’s TOTALLY understandable and is easy to fix.

For now, I can simplify this to:

@“User Id={0};

Password={1};

Pooling=True;

Data Source= (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST={2})(PORT={3})))(CONNECT_DATA=(SERVICE_NAME={4}))”

I’ll work out the ODP drivers as a next step..

That’s it!

With those two changes, my tests pass without any problems AND return the correct information from our database:

Passing Fluent NHibernate Test

Fluent NHibernate With Oracle – A Question…

December 2, 2008 3 comments

Answer found!  Read more here.

After success with standard NHibernate, I quickly became enraptured with the cleaner Fluent NHibernate API.  No more XML; however, I have had VERY limited (read: none) success getting this working against Oracle—especially against our crazy Oracle environment (of which I have no control and readonly access).

Here’s what I have:

A basic ClassMap of a Student object.

class StudentMap : ClassMap<Student>

{

       public StudentMap()

       {

              WithTable(“Students”);

 

              Id(x => x.Id, “pupil_number”)

                     .GeneratedBy.Native();

              Map(x => x.FirstName, “first_name”);

              Map(x => x.LastName, “surname”);

 

       }

}

A basic Student object.

public class Student

{

       private Student() { }

 

       public virtual int Id { get; set; }

       public virtual string FirstName { get; set; }

       public virtual string LastName { get; set; }

}

Note: My constructor is private because this SHOULD be a readonly object.  Setting it public doesn’t seem to matter either. ๐Ÿ˜ฆ

A basic data provider.

public class SISDataProvider

{

       public ISession Session;

 

       public SISDataProvider(ISession session)

       {

              Session = session;

       }

 

       public T GetById<T>(int id)

       {

              Session.Flush();

              return Session.Get<T>(id);

       }

}

No save functionality—again, we just have readonly access to this database.

A basic session manager.

public class SISSessionManager

{

       private readonly string _mappingAssembly;

       private readonly ISessionFactory _sessionFactory;

 

       public SISSessionManager()

       {

              _mappingAssembly = “SIS.Domain”;

              _sessionFactory = GetSessionFactory();

       }

 

       public ISession GetSession()

       {

              return _sessionFactory.OpenSession();

       }

 

       private ISessionFactory GetSessionFactory()

       {

              var config = new Configuration();

 

              var configuration = OracleConfiguration.Oracle9

                .ConnectionString

                    .Server(“server”)

                    .Port(1521)

                    .Instance(“instance.name”)

                    .Username(“user”)

                    .Password(“password”)

                    .Create

                .ConfigureProperties(config);

 

              configuration.AddMappingsFromAssembly(Assembly.Load(_mappingAssembly));

 

              return configuration.BuildSessionFactory();

       }

}

The OracleConfiguration is based on a FluentNhibernate.Cfg.PersistanceConfiguration that I created that calls the OracleClientDriver (not the ODP; that’s OracleDataClientDriver—I’m simply wanting to get the architecture working for now and I’ll dink with increasing performance with ODP as the next step).

Finally, the test.

public class creating_a_student

{

       private SISDataProvider _provider;

       private SISSessionManager _sessionFactory;

 

       public creating_a_student()

       {

              _sessionFactory = new SISSessionManager();

              _provider = new SISDataProvider(_sessionFactory.GetSession());

       }

 

       [Fact]

       public void with_a_valid_student_id_selects_the_correct_student()

       {

             var sut = _provider.GetById<Student>(12345);

             sut.Should_Not_Be_Null();

             sut.Id.Should_Be_Equal_To(12345);

       }

}

Unfortunately, all that and I get an error:

creating_a_student.with_a_valid_student_id_selects_the_correct_student : Failed

XunitException: NHibernate.MappingException : No persister for: SIS.Domain.Student

After some debugging, the Sis.Domain.Student is being added to the Mapping collection, but I’m not sure if that’s the problem or not.  A Google of “no persister” pulls up a few NHibernate hits for not having the XML files set to Embedded Resource, which isn’t my problem here—is it?  I thought Fluent NHibernate escaped XMLHell.

I’d appreciate any guidance on this—I’m stumped.