Last time in
ElevateDB: Stored Procedures Part 3 I showed you how I went about creating an Amortization schedule that works with Microsoft SQL Server. This took me all of about 35 minutes.
You may be wondering why I'm writing about SQL code in a Delphi blog and also why I'm writing about Microsoft SQL if this blog series is entitle "ElevateDB: Stored Procedures Part XX".
Well, the answer is simple... I upgraded from Delphi 5 Enterprise to Delphi 2010 Professional. That in itself is a big jump. Furthermore, I want to create a Delphi database applications that uses an embedded database, and in this case I have chosen to use ElevateDB.
So, my goal is to create a very simple Delphi program that prints out an Amortization Schedule using Rave Reports that pulls the data from a database.
Because I'm blogging about how I am going about doing this, sort of creating a Programming Documentary or "Progumentary", wow I just made up a new word. See how this works.
Do I know how to use Rave Reports that comes with Delphi 2010? No, not yet. But I know if the data is in a database table I can more easily create that Rave Report.
Have I created the Delphi 2010 VCL form for gathering the data inputs needed? No, but I know I can do this and that part is coming later.
Do I know how to get the data into an ElevateDB database using a stored procedure? No. But I do know how to get the data into a MS SQL Database using a stored procedure and that is where this
Pro-gu-mentary is right now.
ElevateDB comes with an ElevateDB Manager, which is a tool similar to Microsoft SQL Server Management Studio. It allows you to create databases, database objects and gives you a very robust query analyzer for performing all the necessary databse tasks.
I have decided to put all the code logic for creating this amortization schedule inside a stored procedure. This allows me to develop a stand alone module that I can test and measure outside of Delphi. I asked a question on SO about using a proc verus keeping the code in Delphi and received a few interesting responses. (
Link to SO Question)
Because I know I can wire up a few simple controls on a Delphi VCL form and make "One" call to an ElevateDB stored procedure this is time well spent and code that will be used when the time comes.
So, after 6 plus hours of trying to get a similar stored procedure working in ElevateDB here is what I came up with:
----------------------------------------------------------------
-- SQL Amortization Schedule
-- Copyright 2011 © By Michael J. Riley
-- www.zilchworks.com
----------------------------------------------------------------
CREATE PROCEDURE "spAmortizationSchedule"
(
IN "StartDate" DATE,
IN "Principal" DECIMAL(19,2),
IN "APR" DECIMAL(19,4),
IN "Months" INTEGER
)
BEGIN
----------------------------------------------------------------
-- VARIABLE DECLARATIONS USED FOR PROCESSING
----------------------------------------------------------------
DECLARE Payment decimal(19,2);
DECLARE PaymentLast decimal(19,2);
DECLARE PmtNumber int ;
DECLARE PmtDate date ;
DECLARE BalanceStart decimal(19,2);
DECLARE PmtInterest decimal(19,2);
DECLARE PmtPrincipal decimal(19,2);
DECLARE BalanceEnd decimal(19,2);
DECLARE SQLStatement Statement;
DECLARE Result CURSOR WITH RETURN FOR Stmt;
/*
----------------------------------------------------------------
-- TEST FOR NULL INPUTS ADD AT SOME POINT
-- PSEUDO-CODE UNTIL I FIGURE OUT HOW TO DO THIS IN ELEVATDB
----------------------------------------------------------------
IF StartDate IS NULL THEN SET STARTDATE = Current_Date;
IF Principal IS NULL THEN SET Principal = 190000;
IF APR IS NULL THEN SET APR = 3.25;
IF Months IS NULL THEN SET Months = 180;
*/
----------------------------------------------------------------
-- EMPTY TABLE FOR NEW USE
----------------------------------------------------------------
EXECUTE IMMEDIATE 'EMPTY TABLE "TempAmortization"';
----------------------------------------------------------------
-- CALCULATE MONTHLY PAYMENT BASED ON INPUT PARAMETERS
-- THIS LINE IS LONG AND MAY WRAP MAKING IT HARD TO READ
----------------------------------------------------------------
SET Payment = ROUND(Principal * ((APR/1200)/(1- EXP((Months*-1) * LN(1 + (APR/1200))))) to 2);
----------------------------------------------------------------
-- INITALIZE VARIABLES BEFORE THE LOOP STARTS
----------------------------------------------------------------
SET PmtNumber = 0 ;
SET BalanceEnd = Principal ;
SET PmtDate = StartDate + INTERVAL '-1' MONTH;
----------------------------------------------------------------
-- PERFORM CALCULATIONS FOR ALL BUT LAST MONTH
-- PmtDate column commented out. Need to get the proper syntax.
----------------------------------------------------------------
WHILE PmtNumber < Months -1
DO
SET PmtNumber = PmtNumber +1;
SET BalanceStart = BalanceEnd;
SET PmtDate = PmtDate + INTERVAL '1' MONTH;
SET PmtInterest = ROUND(BalanceStart * (APR/1200) to 2);
SET PmtPrincipal = Payment - PmtInterest;
SET BalanceEnd = BalanceStart - PmtPrincipal;
EXECUTE IMMEDIATE '
INSERT INTO TempAmortization
(
PmtNumber ,
-- PmtDate ,
PmtAmount ,
BalanceStart ,
PmtPrincipal ,
PmtInterest ,
BalanceEnd
)
VALUES
(
' + CAST(PmtNumber as varchar(25)) + ' ,
-- ''' + CAST(PmtDate as varchar(25)) + ''' ,
' + CAST(Payment as varchar(25)) + ' ,
' + CAST(BalanceStart as varchar(25)) + ' ,
' + CAST(PmtPrincipal as varchar(25)) + ' ,
' + CAST(PmtInterest as varchar(25)) + ' ,
' + CAST(BalanceEnd as varchar(25)) + '
)
';
END WHILE;
----------------------------------------------------------------
-- PERFORM CALCULATIONS FOR THE LAST MONTH AND
-- PmtDate column commented out. Need to get the proper syntax.
----------------------------------------------------------------
SET PmtNumber = PmtNumber +1;
SET BalanceStart = BalanceEnd;
SET PmtDate = PmtDate + INTERVAL '1' MONTH;
SET PmtInterest = ROUND(BalanceStart * (APR/1200) to 2);
SET PaymentLast = BalanceStart + PmtInterest;
SET PmtPrincipal = BalanceStart;
SET BalanceEnd = BalanceStart + PmtInterest - PaymentLast;
EXECUTE IMMEDIATE '
INSERT INTO TempAmortization
(
PmtNumber ,
--PmtDate ,
PmtAmount ,
BalanceStart ,
PmtPrincipal ,
PmtInterest ,
BalanceEnd
)
VALUES
(
' + CAST(PmtNumber as varchar(25)) + ' ,
--''' + CAST(PmtDate as varchar(25)) + ''' ,
' + CAST(PaymentLast as varchar(25)) + ' ,
' + CAST(BalanceStart as varchar(25)) + ' ,
' + CAST(PmtPrincipal as varchar(25)) + ' ,
' + CAST(PmtInterest as varchar(25)) + ' ,
' + CAST(BalanceEnd as varchar(25)) + '
)
';
----------------------------------------------------------------
-- RETURN RESULTS FROM TEMPORARY TABLE
----------------------------------------------------------------
PREPARE Stmt FROM
'
SELECT
PmtNumber ,
PmtDate ,
PmtAmount ,
BalanceStart ,
PmtPrincipal ,
PmtInterest ,
BalanceEnd
FROM TempAmortization
';
OPEN Result;
END
VERSION 1.00!
----------------------------------------------------------------
It's crude, it works, except for the
PmtDate column but at least I knew enough to comment out just that one portion. There are some things I had to do differently from the original MS SSQL Stored Proc:
- Had to skip error trapping the input parameters becaue I don't know how to set default values
- Had to used to use a real table instead of a temp table because I kept getting errors saying the temp table already exists for this session, bla bla bla
- Had to comment out the Payment Date column because I could not figure out the proper syntax even thought I thought I had worked that out with the ElevateDB Date Math stuff. I'll have to revisit ElevateDB Date Math at a later time
A day or two later Tim Young, the guy who created ElevateDB posted this on the support forum...
Michael,
<< I'm new to ElevateDB coming from a Microsoft SQL background. Roy suggested I whip up an example of a Microsoft Stored procedure that I'd like to get converted over to ElevateDB and post it to the forum. >>
I know that you've already got this done, but here's an optimal version for EDB:
CODE GOES HERE
(I'll share his code in a future post)
How cool is that. I got the guy who created ElevateDB to whip up an
optimized version of the stored proc I needed. The proc I wrote in MS SQL is filled with little nuggets that can be used over and over again in other stored procs. There's alot going on in there. For example:
- Use of defaults for input paramters
- Use of temp table for storing temporary data
- A date math routine that increments a date by one month
- Use of a While loop
- Selection of the final result set
That's it for now. Gunny Out!
Semper Fi,
Gunny Mike
