ElevateDB: Function isDate()

I am without a doubt spoiled by some of Microsoft SQL Server's built in functions. One such built in function that I have used many times is the isDATE function. This function let's you test whether a data value represents a date.

ElevateDB does not have an isDATE function, so I built one and will show you the process I went through to make one.

First I fired up the EDB Manager and created a new script that I could code and test as I went. I decided to see if I could use a BEGIN ... EXCEPTION ... END block to build this function. According to the documentation

Use these statements to declare a block of statements for execution in a procedure or function with an associated exception block of statements for handling any exceptions that may occur in the block of statements.

My intent was to "force an error" by trying to cast a string as a date and trap for an error if the string did not represent a date. It worked. The only gotcha was this, when I executed the code inside an EDB Manager script window, EDB Manager stopped execution to tell me that there was a conversion error with the line

SET TestDate = CAST(strDate as Date);

ElevateDB Error #1011 An error occurred with the value 12345678 (A conversion error occurred)

I will use the error number 1011 in the exception handling.

I fully expected an error to occur but I didn't know the error number ahead of time. I then clicked continue and the program stopped at the breakpoint I had set for SET X = 1; When I inspected the local variables, I saw that the script had done exactly what I asked. It set the isDATE value to 0 (meaning not a date). When I toggle between commenting one of the SET strDate = ... lines, the script perfoms correctly.

Copy this code into a new SCRIPT window inside EDB Manager and set a break point at the line
SET X = 1;.

Notice the second SET strDate statement has been commented out
--SET strDate = '1959-03-23'; commented out.

You can toggle between commenting out both of these SET strDate statements to see how EDB Manager handles good and bad date data.

SCRIPT
BEGIN
----------------------------------------------------------------
-- X is used set a breakpoint within ED Manager
----------------------------------------------------------------
DECLARE X INTEGER;

DECLARE strDate varchar(20);
DECLARE TestDate DATE;
DECLARE isDATE INTEGER;
DECLARE ErrCode Integer;

SET isDATE = 1;
SET strDate = '12345678';--SET strDate = '1959-03-23';
SET ErrCode = 0;

BEGIN
SET TestDate = CAST(strDate as Date);
EXCEPTION
   SET ErrCode = ERRORCODE();
   IF ERRORCODE()=1011 THEN
     SET isDATE = 0;
   END IF;
END;
SET X = 1;
END

Converting the above script into an actual function is pretty straight forward. From inside EDB Manager open a new SQL window and copy the following code:
br />

CREATE FUNCTION "isDate"  (INOUT "DateStr" VARCHAR(20) COLLATE UNI)
RETURNS INTEGER

BEGIN
DECLARE TestDate DATE;
DECLARE ValidDate INTEGER;

SET DateStr = COALESCE(DateStr,'');
SET ValidDate = 1;

BEGIN
SET TestDate = CAST(DateStr as Date);
EXCEPTION
   IF ERRORCODE()=1011 THEN
     SET ValidDate = 0;
   END IF;
END;

RETURN ValidDate;
END

This ElevateDB isDATE function is not as robust as the MS SQL Server function but it does allow you to test whether the date value passed in represents a date. Enjoy.

Semper Fi,
Gunny Mike

ElevatdDB: Date Math 2

Let me start off by mentioning a couple things. I'm not an expert at ElevateDB by any means and I don't claim to be one either. I purchased ElevateDB three weeks ago on 09/01/2011, and have only been using it for about ten days within that three week period.

I'm sharing my ElevateDB experiences with you as they are happening. So, I'm blogging about what I'm learning along the way. As I get more proficient with ElevateDB I hope to reflect that proficiency in my postings about ElevateDB.

Before I get to the Date Math stuff I need to tell you about the SQL query tool that comes with ElevateDB. It's called ElevateDB Manager. If you are familiar with Microsoft's SQL Server Management Studio or Microsoft's SQL Query Analyzer you should feel pretty comfortable with this tool. As of this writing it does not come with it's own manual. You can learn by doing or reading some of the support forum posts.

All the stuff I have been doing so far has been done from within a script window inside the the ElevateDB Manager. You open a new script window by clicking New|Script.

You can also set what are called "Breakpoints". Breakpoints do exactly what they sound like they do, they break in and stop execution of a script as a specific point. To set a break point within a script you click inside the grey margin next to the line where you want the script execution to stop.

Breakpoints are very cool. They let you see the current value of all the local variable that have been declared within the script at the very spot of the breakpoint. I set the breakpoint at the line
SET X = 1;
which is a do nothing statement purely for the purpose of letting me view the local variables.

I'm metioning these items because that is how I tested and viewed the results of the date math code I'm about to share with you.

How many times have you had to use a Start Date and End Date within your SQL queries? How many times have you had to set the Start Date equal to the first day of the current month and the End Date to the last day of the current month? How about the first and last days of the previous month, or the following month.

I'm about to show you a simple, sure fire way to set these dates so they work everytime, no matter what. You don't need to worry about going backwards or forwards a year. You don't need to worry whether there are 30 or 31 days in a month. You don't need to worry about February having 28 or 29 days. It's that simple.

Just copy and past this code into an ElevateDB Script. Set a breakpoint at the line mentioned above and your all set. There's only one line that needs to be inserted into the bottom two examples that makes them different from the first example. I've colored them red.

How to set the StartDate and EndDate for the Current Month

SCRIPT
BEGIN
----------------------------------------------------------------
-- This snippet of code sets the StartDate and EndDate to the 
-- first day  and last day of the current month
----------------------------------------------------------------
DECLARE Today     DATE;
DECLARE YYYY      INTEGER;
DECLARE MM        INTEGER;
DECLARE DateStr   VARCHAR(10);
DECLARE WorkDate  DATE;
DECLARE StartDate DATE;
DECLARE EndDate   DATE;

----------------------------------------------------------------
-- X is used set a breakpoint within ED Manager
----------------------------------------------------------------
DECLARE X INTEGER;

----------------------------------------------------------------
-- Get the current Year and Month
----------------------------------------------------------------
SET Today = Current_Date;
SET YYYY  = EXTRACT(YEAR FROM Today);
SET MM    = EXTRACT(Month FROM Today);

----------------------------------------------------------------
-- Set the WorkDate equal to the first day of the current month
-- by building a date string from the extracted date parts
-- Example: YYYY-MM-01
-- WHERE YYYY is the extracted Year
-- AND   MM   is the extracted Month
-- Make sure to pad any month less than 10 with a leading zero
----------------------------------------------------------------
SET DateStr = '';
SET DateStr = DateStr + CAST(YYYY as VARCHAR);
SET DateStr = DateStr + '-';
IF MM < 10 THEN
  SET DateStr = DateStr + '0';
END IF;
SET DateStr = DateStr + CAST(MM as VARCHAR);
SET DateStr = DateStr + '-01';
SET WorkDate  = CAST(DateStr as DATE);

----------------------------------------------------------------
-- Set StartDate equal to the first day of the month and create
-- the EndDate by performing two simple date math operations
-- 1. Add one Month to the WorkDate
-- 2. Subtract one Day from the WorkDate
----------------------------------------------------------------
SET StartDate = WorkDate;
SET WorkDate  = WorkDate + Interval  '1' MONTH;
SET WorkDate  = WorkDate + Interval '-1' DAY;
SET EndDate   = WorkDate;

SET X = 1;
END

How to set the StartDate and EndDate for the Previous Month

SCRIPT
BEGIN
----------------------------------------------------------------
-- This snippet of code sets the StartDate and EndDate to the 
-- first day  and last day of the previous month
----------------------------------------------------------------
DECLARE Today     DATE;
DECLARE YYYY      INTEGER;
DECLARE MM        INTEGER;
DECLARE DateStr   VARCHAR(10);
DECLARE WorkDate  DATE;
DECLARE StartDate DATE;
DECLARE EndDate   DATE;

----------------------------------------------------------------
-- X is used set a breakpoint within ED Manager
----------------------------------------------------------------
DECLARE X INTEGER;

----------------------------------------------------------------
-- Get the current Year and Month
----------------------------------------------------------------
SET Today = Current_Date;
SET YYYY  = EXTRACT(YEAR FROM Today);
SET MM    = EXTRACT(Month FROM Today);

----------------------------------------------------------------
-- Set the WorkDate equal to the first day of the current month
-- by building a date string from the extracted date parts
-- Example: YYYY-MM-01
-- WHERE YYYY is the extracted Year
-- AND   MM   is the extracted Month
-- Make sure to pad any month less than 10 with a leading zero
----------------------------------------------------------------
SET DateStr = '';
SET DateStr = DateStr + CAST(YYYY as VARCHAR);
SET DateStr = DateStr + '-';
IF MM < 10 THEN
  SET DateStr = DateStr + '0';
END IF;
SET DateStr = DateStr + CAST(MM as VARCHAR);
SET DateStr = DateStr + '-01';
SET WorkDate  = CAST(DateStr as DATE);

----------------------------------------------------------------
-- For Previous month subtract one month from WorkDate
-- Set StartDate equal to the first day of the month and create
-- the EndDate by performing two simple date math operations
-- 1. Add one Month to the WorkDate
-- 2. Subtract one Day from the WorkDate
----------------------------------------------------------------
SET WorkDate  = WorkDate + Interval '-1' Month;
SET StartDate = WorkDate;
SET WorkDate  = WorkDate + Interval  '1' MONTH;
SET WorkDate  = WorkDate + Interval '-1' DAY;
SET EndDate   = WorkDate;

SET X = 1;
END

How to set the StartDate and EndDate for the Following Month

SCRIPT
BEGIN
----------------------------------------------------------------
-- This snippet of code sets the StartDate and EndDate to the 
-- first day  and last day of the following month
----------------------------------------------------------------
DECLARE Today     DATE;
DECLARE YYYY      INTEGER;
DECLARE MM        INTEGER;
DECLARE DateStr   VARCHAR(10);
DECLARE WorkDate  DATE;
DECLARE StartDate DATE;
DECLARE EndDate   DATE;

----------------------------------------------------------------
-- X is used set a breakpoint within ED Manager
----------------------------------------------------------------
DECLARE X INTEGER;

----------------------------------------------------------------
-- Get the current Year and Month
----------------------------------------------------------------
SET Today = Current_Date;
SET YYYY  = EXTRACT(YEAR FROM Today);
SET MM    = EXTRACT(Month FROM Today);

----------------------------------------------------------------
-- Set the WorkDate equal to the first day of the current month
-- by building a date string from the extracted date parts
-- Example: YYYY-MM-01
-- WHERE YYYY is the extracted Year
-- AND   MM   is the extracted Month
-- Make sure to pad any month less than 10 with a leading zero
----------------------------------------------------------------
SET DateStr = '';
SET DateStr = DateStr + CAST(YYYY as VARCHAR);
SET DateStr = DateStr + '-';
IF MM < 10 THEN
  SET DateStr = DateStr + '0';
END IF;
SET DateStr = DateStr + CAST(MM as VARCHAR);
SET DateStr = DateStr + '-01';
SET WorkDate  = CAST(DateStr as DATE);

----------------------------------------------------------------
-- For Following month add one month to WorkDate
-- Set StartDate equal to the first day of the month and create
-- the EndDate by performing two simple date math operations
-- 1. Add one Month to the WorkDate
-- 2. Subtract one Day from the WorkDate
----------------------------------------------------------------
SET WorkDate  = WorkDate + Interval '1' Month;
SET StartDate = WorkDate;
SET WorkDate  = WorkDate + Interval  '1' MONTH;
SET WorkDate  = WorkDate + Interval '-1' DAY;
SET EndDate   = WorkDate;

SET X = 1;
END

Enjoy,
Gunny Mike

ElevateDB: Stored Procedures Part 5

In the last post of this series ElevateDB: Stored Procedures Part 4 I told you I would share with you the optimized version of the stored procedure written by Tim Young from ElevateSoft and I will. However, I need to point out that the monthly payment was calculated differently between the Microsoft SQL code and the ElevateDB code.

I made sure I declared the APR input parameter as decimal(19,4) in both Microsft SQL and ElevateDB. Here are those declarations along with the appropriate code snippets:

-----------------------------------------------------------------
-- Microsoft SQL
-----------------------------------------------------------------
@APR decimal(19,4) = 3.25
DECLARE @Payment decimal(19,2)
SET @Payment = ROUND(@Principal * ((@APR/1200)/(1- EXP((@Months*-1) * LOG(1 + (@APR/1200))))),2)

Payment = 1370.20 (this is correct)

-----------------------------------------------------------------
-- ElevateDB (APR = 3.25 although it's not shown)
-----------------------------------------------------------------
IN "APR" DECIMAL(19,4), 
DECLARE Payment decimal(19,2);
SET Payment = ROUND(Principal * ((APR/1200)/(1- EXP((Months*-1) * LN(1 + (APR/1200))))) to 2);

Payment = 1369.26 (this is incorrect)

So, I decided to investigate why the two payments were different. Upon close examination I found the following:

Even though both database have APR defined as decimal(19,4)...

Microsoft SQL calculates @APR/1200 as 0.002708333
ElevateDB calculates APR/1200 as 0.0027

I won't speculate why Microsoft's calculations seem to ignore the decimal place rules. I do know that ElevateDB does enforce the decimal place rules. So, armed with this bit of information, I changed ElevateDB to use the Float datatype for APR,

-----------------------------------------------------------------
-- ElevateDB Revisited (APR = 3.25 although it's not shown)
-----------------------------------------------------------------
IN "APR" float,
DECLARE Payment decimal(19,2);
SET Payment = ROUND(Principal * ((APR/1200)/(1- EXP((Months*-1) * LN(1 + (APR/1200))))) to 2);

Payment = 1370.20 (this is now correct)

So, if you intend to do calculations where the decimal places need to go beyond 4 places when using ElevateDB make sure to use the float datatype.

As promised, here is the optimized amortization schedule that Tim Young produced from the example Microsoft SQL Code i posted to his forum. There's a lot you can learn about how ElevateDB works from this code snippet, Enjoy!

----------------------------------------------------------------
-- SQL Amortization Schedule
-- Copyright 2011 © By Michael J. Riley
-- www.zilchworks.com
-- Created by Tim Young - ElevateSoft
-- www.elevatesoft.com
----------------------------------------------------------------
CREATE PROCEDURE "spAmortizationSchedule02" (
INOUT "StartDate" DATE, 
INOUT "Principal" DECIMAL(19,4), 
INOUT "APR" FLOAT, 
INOUT "Months" INTEGER)
BEGIN

----------------------------------------------------------------
-- VARIABLE DECLARATIONS USED FOR PROCESSING
----------------------------------------------------------------

DECLARE InsertStmt STATEMENT;
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR WITH RETURN FOR ResultStmt;

DECLARE Payment      DECIMAL(19,4);
DECLARE PaymentLast  DECIMAL(19,4);
DECLARE PmtNumber    INTEGER;
DECLARE PmtDate      DATE;
DECLARE BalanceStart DECIMAL(19,4);
DECLARE PmtInterest  DECIMAL(19,4);
DECLARE PmtPrincipal DECIMAL(19,4);
DECLARE BalanceEnd   DECIMAL(19,4);

----------------------------------------------------------------
-- INPUT PARAMETERS WITH DEFAULT VALUES
----------------------------------------------------------------

SET StartDate = COALESCE(StartDate, CURRENT_DATE());
SET Principal = COALESCE(Principal, 195000);
SET APR = COALESCE(APR, 3.25);
SET Months = COALESCE(Months, 180);

----------------------------------------------------------------
-- TEMP TABLE TO HOLD AMORTIZATION OUTPUT
----------------------------------------------------------------

PREPARE InfoStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE Name=?';
OPEN InfoCursor USING 'TempAmortization02';
IF ROWCOUNT(InfoCursor) > 0 THEN
  EXECUTE IMMEDIATE 'EMPTY TABLE TempAmortization02';
ELSE
  EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE TempAmortization02
                    (
                    PmtNumber    INTEGER       ,
                    PmtDate      DATE          ,
                    PmtAmount    DECIMAL(19,4) ,
                    BalanceStart DECIMAL(19,4) ,
                    PmtPrincipal DECIMAL(19,4) ,
                    PmtInterest  DECIMAL(19,4) ,
                    BalanceEnd   DECIMAL(19,4)
                    )';
END IF;

----------------------------------------------------------------
-- CALCULATE MONTHLY PAYMENT BASED ON INPUT PARAMETERS
-- This line may wrap and be hard to read
----------------------------------------------------------------
SET Payment = ROUND(Principal * ((APR/1200)/(1- EXP((Months*-1) *
             LOG(1 + (APR/1200))))) to 2);

----------------------------------------------------------------
-- INITALIZE VARIABLES BEFORE THE LOOP STARTS
----------------------------------------------------------------
SET PmtNumber  = 0;
SET BalanceEnd = Principal;

PREPARE InsertStmt FROM 'INSERT INTO TempAmortization02
                       (
                       PmtNumber     ,
                       PmtDate       ,
                       PmtAmount     ,
                       BalanceStart  ,
                       PmtPrincipal  ,
                       PmtInterest   ,
                       BalanceEnd
                       )
                       VALUES (?,?,?,?,?,?,?)';

----------------------------------------------------------------
-- PERFORM CALCULATIONS FOR ALL BUT LAST MONTH AND
-- STORE RESULTS IN TEMPORARY TABLE
-- 
-- MICROSOFT SQL DATEADD(,,
-- ELEVATEDB SQL  + INTERVAL '' 
----------------------------------------------------------------
WHILE PmtNumber < Months -1 DO

SET PmtNumber    = PmtNumber + 1;
SET BalanceStart = BalanceEnd;
SET PmtDate      = (StartDate + CAST(PmtNumber-1 AS INTERVAL MONTH));
SET PmtInterest  = ROUND(BalanceStart *(APR/1200) to 2);
SET PmtPrincipal = Payment - PmtInterest;
SET BalanceEnd   = BalanceStart - PmtPrincipal;

EXECUTE InsertStmt USING PmtNumber     ,
                         PmtDate       ,
                         Payment       ,
                         BalanceStart  ,
                         PmtPrincipal  ,
                         PmtInterest   ,
                         BalanceEnd;

END WHILE;

----------------------------------------------------------------
-- PERFORM CALCULATIONS FOR THE LAST MONTH AND
-- STORE RESULTS IN TEMPORARY TABLE
----------------------------------------------------------------
SET PmtNumber    = PmtNumber +1;
SET BalanceStart = BalanceEnd;
SET PmtDate      = (StartDate + CAST(PmtNumber-1 AS INTERVAL MONTH));
SET PmtInterest  = ROUND(BalanceStart *(APR/1200) ,2);
SET PaymentLast  = BalanceStart + PmtInterest;
SET PmtPrincipal = BalanceStart;
SET BalanceEnd   = BalanceStart + PmtInterest - PaymentLast;

EXECUTE InsertStmt USING PmtNumber     ,
                        PmtDate       ,
                        PaymentLast   ,
                        BalanceStart  ,
                        PmtPrincipal  ,
                        PmtInterest   ,
                        BalanceEnd;

----------------------------------------------------------------
-- RETURN RESULTS FROM TEMPORARY TABLE
----------------------------------------------------------------

PREPARE ResultStmt FROM 'SELECT
                       PmtNumber     ,
                       PmtDate       ,
                       PmtAmount     ,
                       BalanceStart  ,
                       PmtPrincipal  ,
                       PmtInterest   ,
                       BalanceEnd
                       FROM TempAmortization02';
OPEN ResultCursor;

END

VERSION 1.00!
----------------------------------------------------------------

Semper Fi,
Gunny Mike

< Prev

ElevateDB: Stored Procedures Part 4

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

< Prev

Next >

ElevateDB: Stored Procedures Part 3

In my last ElevateDB Stored Procedure post I went on a rant about how frustrating my stored procedure journey became. Well, today is a new day and I feel better. A few more posts were added to the support thread I started with one really good one by the Tim Young the creator of ElevateDB.

The stored proc I was attempting to create is a fairly simple "Amortization Schedule". So here is how you go about building an Amortization Schedule from scratch.

Because I'm an old school programmer here's my very simple method of deciding how to do just about every programming task:

Gunny's Rules

• 1. Identify the output
• 2. Identify the inputs
• 3. Ask ====> Are there enough inputs to create that output?

If the answer is NO there are not enough inputs to create that output, then go get the necessary inputs. It's a waste of time to begin writing code until you have all the inputs. If you spend a little time now ironing out these details you will save tons of time later.

This is really bone simple but it works. Or as I used to say to my Major... "It's Gump Proof" (Yeah I know I'm a Marine but it's still a cool saying. Calling something Gomer Proof doesn't have the same meaning.)

Identify The Output:
A database table that contains payment information for each monthly payment in an Amortization Schedule. To be more specific:

• Payment Number
• Payment Date
• Balance Before Payment
• Payment Amount
• Principal Portion of Payment
• Interest Portion or Payment
• Balance After Payment

Identify The Inputs:

• Payment Start Date
• Loan Amount
• Annual Percentage Rate (APR)
• Length of Loan in Months

Ask ====> Are there enough inputs to create that output?

Answer: No. I need the payment amount.

Decision Time: Okay, there are two choices at this point.

• Ask for this value to be supplied just like the other values
• Calculate the payment based on the existing input values

I decide to calculate the payment. So now I need a loan payment formula. Let's assume I know where to get one of these. The major point is I identified an additional input was needed - A Formula. My list of inputs now looks like this:

Identify The Inputs:

• Payment Start Date
• Loan Amount
• Annual Percentage Rate (APR)
• Length of Loan in Months
• Loan Payment Formula

Ask ====> Are there enough inputs to create that output?

Answer: No. I need two more bits of information.

• Another formula for calculating the Interest Portion of the payment
• A method for advancing the Payment Date by one month

No problem. That formula is pretty straigh forward and most SQL languages support Date Math. I'm good at this point. Let's have another look at the list of inputs.

Identify The Inputs:

• Payment Start Date
• Loan Amount
• Annual Percentage Rate (APR)
• Length of Loan in Months
• Loan Payment Formula
• Interest Payment Formula
• Date Math Routine

Ask ====> Are there enough inputs to create that output?

Answer: Yes

That is Gunny's simple Gump Proof way of gathering requirements. Here is a Microsoft SQL Server version of the stored procedure. Next time I will show you my working but lame attempt at creating the same amortization stored procedure using ElevateDB. Enjoy!

----------------------------------------------------------------
-- SQL Amortization Schedule
-- Copyright 2011 © By Michael J. Riley
-- www.zilchworks.com
----------------------------------------------------------------
CREATE PROCEDURE [dbo].[spAmortizationSchedule] 

----------------------------------------------------------------
-- INPUT PARAMETERS WITH DEFAULT VALUES
----------------------------------------------------------------
@StartDate  datetime      = '2011-09-15' ,
@Principal  decimal(19,2) = 195000       ,
@APR        decimal(19,4) = 3.25         ,
@Months     integer       = 180

AS

----------------------------------------------------------------
-- 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)

----------------------------------------------------------------
-- TEMP TABLE TO HOLD AMORTIZATION OUTPUT
----------------------------------------------------------------
CREATE TABLE #TempAmortization
(
PmtNumber    int           , 
PmtDate      date          , 
PmtAmount    decimal(19,2) , 
BalanceStart decimal(19,2) ,
PmtPrincipal decimal(19,2) ,
PmtInterest  decimal(19,2) ,
BalanceEnd   decimal(19,2) 
)

----------------------------------------------------------------
-- CALCULATE MONTHLY PAYMENT BASED ON INPUT PARAMETERS
-- This line may wrap and be hard to read
----------------------------------------------------------------
SET @Payment = ROUND(@Principal * ((@APR/1200)/(1- EXP((@Months*-1) *
LOG(1 + (@APR/1200))))),2)

----------------------------------------------------------------
-- INITALIZE VARIABLES BEFORE THE LOOP STARTS
----------------------------------------------------------------
SET @PmtNumber  = 0
SET @BalanceEnd = @Principal

----------------------------------------------------------------
-- PERFORM CALCULATIONS FOR ALL BUT LAST MONTH AND
-- STORE RESULTS IN TEMPORARY TABLE
----------------------------------------------------------------
WHILE @PmtNumber < @Months -1
BEGIN
 
 SET @PmtNumber    = @PmtNumber +1
 SET @BalanceStart = @BalanceEnd
 SET @PmtDate      = DATEADD(m, @PmtNumber-1, @StartDate)
 SET @PmtInterest  = ROUND(@BalanceStart *(@APR/1200) ,2)
 SET @PmtPrincipal = @Payment - @PmtInterest
 SET @BalanceEnd   = @BalanceStart - @PmtPrincipal

 INSERT INTO #TempAmortization
 (
 PmtNumber     , 
 PmtDate       , 
 PmtAmount     , 
 BalanceStart  ,
 PmtPrincipal  ,
 PmtInterest   ,
 BalanceEnd    
 )
 VALUES
 (
 @PmtNumber     , 
 @PmtDate       ,  
 @Payment       ,
 @BalanceStart  ,
 @PmtPrincipal  ,
 @PmtInterest   ,
 @BalanceEnd    
 )
 
END

----------------------------------------------------------------
-- PERFORM CALCULATIONS FOR THE LAST MONTH AND
-- STORE RESULTS IN TEMPORARY TABLE
----------------------------------------------------------------
SET @PmtNumber    = @PmtNumber +1
SET @BalanceStart = @BalanceEnd
SET @PmtDate      = DATEADD(m, @PmtNumber-1, @StartDate )
SET @PmtInterest  = ROUND(@BalanceStart *(@APR/1200) ,2)
SET @PaymentLast  = @BalanceStart + @PmtInterest
SET @PmtPrincipal = @BalanceStart
SET @BalanceEnd   = @BalanceStart + @PmtInterest - @PaymentLast

INSERT INTO #TempAmortization
(
PmtNumber     , 
PmtDate       , 
PmtAmount     , 
BalanceStart  ,
PmtPrincipal  ,
PmtInterest   ,
BalanceEnd    
)
VALUES
(
@PmtNumber     , 
@PmtDate       ,  
@PaymentLast   ,
@BalanceStart  ,
@PmtPrincipal  ,
@PmtInterest   ,
@BalanceEnd    
)

----------------------------------------------------------------
-- RETURN RESULTS FROM TEMPORARY TABLE
----------------------------------------------------------------
SELECT  
PmtNumber     , 
PmtDate       , 
PmtAmount     , 
BalanceStart  ,
PmtPrincipal  ,
PmtInterest   ,
BalanceEnd    
FROM #TempAmortization

----------------------------------------------------------------
-- HOUSEKEEPING
----------------------------------------------------------------
DROP TABLE #TempAmortization

Semper Fi, Gunny Mike

< Prev

Next >

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 >

ElevateDB: Select TOP Equivelent

In my previous post ElevateDB: Date Math I discussed how to select internal constants and literals from within the ElevateDB Manager using a dummy table.

Well the ElevateDB boys have spoken and you do not need to create a dummy table to perfom this type of query. You can simply go against the information.tables. (I'll have to read up on these guys).

Anyway here is the suggested way to run the same query without using my dummy table approach:

select 
current_date as today 
from information.tables

This works great except it returns four rows in my environment. So, naturally the next question is how do you limit the results to produce only one row.

From MS SQL Query Analyzer you just use the keyword TOP 1. In EDB it's slightly different:

select 
current_date as today 
from information.tables 
range 1 to 1

Semper Fi
Gunny Mike

ElevateDB: Date Math 1

ElevateDB has just impressed me again the way it handles date math. I don't know if you have ever tried doing SQL Date Math but it can be tricky unless there is built in support.

Often times you need to add a Day or a Month or a Year to a given date. ElevateDB lets you do this and it works well.

I opened up a new SQL Window inside EDB Manager and tried executing the following:

SELECT
Current_Date as Today

I received an Error:

ElevateDB Error #700 An error was found in the statement at line 3 and column 1 (Missing FROM)

So I created a dummy table with one field and one record. Then I modified my original SQL as follows...

SELECT
Current_Date as Today,
FROM dummy

and it works great. Even though I'm not returning any real data from the "dummy" table.

Being inquisitive and wanting to see how well ElevateDB does "Date Math" I tried the following...

SELECT
Current_Date + INTERVAL '-4' DAY AS FourDaysAgo,
Current_Date + INTERVAL '-3' DAY AS ThreeDaysAgo,
Current_Date + INTERVAL '-2' DAY AS TwoDaysAgo,
Current_Date + INTERVAL '-1' DAY AS OneDayAgo,
Current_Date as Today,
Current_Date + INTERVAL '1' DAY AS OneDayFromNow,
Current_Date + INTERVAL '2' DAY AS TwoDaysFromNow,
Current_Date + INTERVAL '3' DAY AS ThreeDaysFromNow,
Current_Date + INTERVAL '4' DAY AS FourDaysFromNow
FROM dummy

and it works AWESOME. I tested going backwards until one day before March 1, 2011 testing leap year. I tested going forward until one day after Feb 28, 2012. I tested December 31st plus 1 Day and Jan 1st minus 1 Day. It's all good.

Now I know that using a dummy table this way probably isn't considered EDB best practices but it did allow me to get the information I was looking for. This method also works for selecting literal values.

I'm waiting to hear back from the ElevateDB boys on the proper way to return this kind of data but until then, I have a working model.

Semper Fi
Gunny Mike

ElevateDB: Stored Procedures Part 1

Okay, so I finally decided to shit or get off the pot and I purchased ElevateDB last week. The GUI tool is great very user friendly. I created a couple tables and populated them. I used the reverse engineer tool to see what that does. It would be nice if the reverse engineer tool would let you pick just one table. (Submited a wish list comment on the support forum)

I'm commited to making ElevateDB work inside my Delphi applications. So, one of the first things I tried doing was creating a simple stored procedure to select all of the data from one of the tables. Easy enough right... wrong.

I've been using stored procedures inside of Microsoft SQL for the past 12 years. So I created what I thought was a pretty straight forward stored procedure:

CREATE PROCEDURE "spSelectStrategies" ()
BEGIN
SELECT
StrategyId,
Strategy,
Hint
FROM tblStrategies
ORDER BY Sort
END

Nope. This gave me the following error:

ElevateDB Error #700 An error was found in the statement at line 4 and column 1 (Expected : but instead found StrategyId)

I sped off to make my first post on ElevateDB's support forum. This is place where users help users. Besides being told my use of a stored procedure was a trivial use of a stored procedure I was given the correct syntax for how an ElevateDB stored procedure should look.

CREATE PROCEDURE "spSelectStrategies" ()
BEGIN
DECLARE procCur CURSOR WITH RETURN FOR procStmt;

PREPARE procStmt FROM 
'
SELECT 
StrategyId,
Strategy,
Hint 
FROM tblStrategies 
ORDER BY Sort
';

OPEN procCur;
END

The whitespace is not necessary, that's all me. I tend to use a lot of white space in my code. I know I'm anal when it comes to certain things. Anyway, it turns out that ALL stored procedures within ElevateDB act on Dynamic SQL. In order to get a dataset returned you need to

DECLARE a CURSOR;
PREPARE a statement;
OPEN the CURSOR

It's going to take me a little while to wrap my SQL Head around to the ElevateDB way of thinking. Stay tuned as I learn more about ElevateDB.

Semper Fi,
Gunny Mike

Next >

Making a ClientDataSet Talk to an MS Access Database 1

I purchased Cary Jensen's book called Delphi in Depth: ClientDataSets a couple weeks ago. It's a great book filled with lots of nuggets. You can only read so much before you need to practice by doing. All of Cary's examples use the DBDEMOS database that comes with Delphi.

So, I have decided to port these examples over so they talk to a MS Access database. I figure this is a great way to learn. For me this is a pain-stakingly slow process, because I want to learn (I mean really learn) how to do this well.

I have just gotten to page 53 of Chapter 3 and decided to blog about what I've done so far.

I substituted the BDE.TTable for a dbGo.TADOTable. I also had to use a dbGo.TADOConnection. The TADOConnection comes with a built in Connection String Builder which works very well.

Anyway, here's where the chicken and the egg thing come into play. In order for this to work you need an MS Access database. So, I created a very simple database using different table names from the DBDEMOS so I can learn how to wire everything up properly.

I set set the TADOConnection.Connected property to true so I can see stuff happening in the IDE. I decided to add a couple more tables to my MS Access database. With my project loaded in the IDE I attempted to open my MS Access database by doubl-clicking on the mdb file... Well, my computer hung. I had to do a hard reboot. Keep this in mind if you go back and forth between Delphi and MS Access.


Another thing that was weird. I tried applying the updates ClientDataSet1.ApplyUpdates(-1); and I got the following error:









So, again Jensen to the rescue. On page 52 he talks about using the ResolveToDataSet property of the DataSetProvider component. I set this property to True and now my updates from with my Delphi application are showing up in the MS Access database.

I'm off to learn some more. I'll share anything that's good to know along the way.

Semper Fi
Gunny Mike

I Took A Little Walk-About - But I'm Back

A lot has been going on inside my head since the last post. Delphi has just released XE2. I have finally decided to use ElevateDB for all my Delphi applications. I needed to shit or get off the pot on making a decision about which database to use.

My apps are not that complicated. They are for single user interaction. I originally thought of using MS Access and then ruled that out. Then I toiled with the idea of using some version of MS SQL Server and after careful reading found out that too much stuff has to be configured on the users machine.

I want a simple install, no complicated hunt and grab driver xxx from location yyy followed by asking some weird little question like "did you want to leave the current setting in place". When you sell a $40 app one sales call chews up the rest of the profit.

Anyway, I pursued the idea of using Firebird 2.5 and spent quite a bit of time learning about all the third party vendors who sell Firebird drivers because my Delphi 2010 Professional doesn't come with a DBExpress driver for Firebird. Along the way I learned about the great Delphi community on Stack Overflow (http://stackoverflow.com/). I also learned how even though Firebird is free, if you want really good help from the people in the know, it will cost you.

So, I said all that to say this... I'm going to use ElevateDB from http://www.elevatesoft.com/. It's 100% written in Delphi. I'm a small ISV and so is Elevate. The documentation is fantastic. I don't have to pay per incident for answers from people in the know. There is an annual maintenance fee but that's to be expected.

If I could point to one thing that pushed me over the edge in my decision... the demo project used a Delphi "Data Module" to consolidate all the db stuff in one location. I never heard of a TDataModule until yesterday and it came from Tim Young at ElevateSoft. Thank you Tim.

How come Wharton, Biorre and all the others pushing Firebird didn't mention this? Answer they are too busy staring in the mirror they didn't see the big picture.

Semper Fi - Gunny Mike