Monday, September 5, 2011

ElevateDB: Stored Procedures Part 2

In ElevateDB: Stored Procedures Part 1 I talked about my first attempt at making a stored procedure in ElevateDB. Well today was an interesting day. I wanted to make it my goal to successfully create a non-trivial Stored Procedure using ElevateDB. I finally did but what a adventure. WTF! I mean WTFO! (I'm military the 'O' stands for Over)

Goal: Develope A Stored Procedure That Creates An Amortization ScheduleI'll share my ElevateDB Amortization Proc with you on an upcoming post.

BTW, I purchased ElevateDB last week and technically it comes with a 30 day money back guarantee. At one point today I was so pissed off I kept thinking in my head "Yeah, if I can't get this to work I'm asking for a refund."

Anyway, one of the "Team Elevate" support forum members responded to one of my posts about how simple it would be to whip this up in MS SQL Server and how difficult it is to do using ElevateDB. He suggested I create a proc using MS SQL and post it to the forum asking others members to help me convert it to ElevateDB parlance. Wow, that sounded like a great idea.

I'm sure there are several other Delphi programmers out there with a Microsoft SQL background looking for an embedded database solution that does not rely on Microsoft and their "Database Driver DLL Hell".

So I spent about 35 minutes whipping up a proc that does this. Here is the link to a the MS SQL Server spAmortizationSchedule I wrote.

After I posted this thing I immediately felt like a high school kid asking a support forum to help him with his homework. I had one forum user respond to my post. He basically asked me "What happens when you try to convert this yourself." Besides him, nothing. He didn't know I felt like I was asking the Internet to do my homework for me.

Anyway, I rolled up my sleeves and started to convert this over to ElevateDB on my own. For the life of me, I could not get passed the mental block I developed over the use of cursors, dynamic SQL and prepared statements. If another person tells me to RTFM I don't know what I'd do.

I have been pouring over the manuals and digging through the code scattered throughout the support forums. Do you think I could find an example similar to what I was trying to accomplish? No!

Take in a few parameters
Create a temp table to store the results
While not done ...
Calculate the values
Insert them into the temp table
Return the record set
Drop the temp table

There is nothing like this. Not in the manuals. Not in the support posts. No where. All I needed was some small little nudge "Hey Gunny, don't do that do this instead. And don't use that use this instead." Did I get that... No. Not Just No But F-No.

Wow, I'm starting to feel better already. That is why I'm blogging about it here.
  • First, so I have a reference I can look back on when I forget how I did something.
  • Secondly, for any other poor soul who happens to stumble across this stuff looking for answers to questions like...I do this in MS SQL how do I do this in ElevateDB.
I love ElevateDB I really do, but until you fully wrap your head around the differences between MS SQL it's frustrating. Who know's maybe I'll have enough shit on this blog I could put it in a book.

This Gunny is worn out. Goodnight y'all.

Semper Fi,
Gunny Mike

< Prev

Next >