Solved

Convert Delphi Function to VBS or Transact SQL

Posted on 2009-04-14
15
1,439 Views
Last Modified: 2013-12-25
We have a product (exchequer finance) that stores its data in a pervasive (bTrieve) database.  As part of our business intelligence development cylce I have a requirement to import the data from the pervasive database to MSSQL.  I have achieved this using SSIS without issue however am now facing a totally unforseen issue!

The pervasive database engine is based on DDF files that have a number of limitations, one such limitation is in its handling of numeric data.  In order to work around this the application developer split all financial values between two fields, Field_1 and Field_2 and then uses a function stored in a DLL to recombine the data to make a single currency field.

the application developer has kindly provided a reference to this function in the help file as thus -

Due to the data structures used by Exchequer most of the floating point numbers are not directly accessible using DDFs. To overcome this problem we have split the numbers into 2 fields, a 2-Byte Integer and a 4-Byte Integer, these are indicated by a '_1' and a '_2' after a shared field name. We have provided a function EntConvertInts in EntFuncs.Dll to convert these to a standard Double type which can be used in most programs.

Additionally they have provided a comparable delphi function definition that should work.

my challenge is I am not a delphi developer and I need something in VB or even better transact SQL that will do the same thing as the below code extract.

H E L P !
Function EntConvertInts ( Const Int2 : SmallInt; Const Int4 : LongInt) : Double; StdCall;

 

Var

TheRealArray : Array [1..6] Of Char;

TheReal : Real;

Begin

Move (Int2, TheRealArray[1], 2);

Move (Int4, TheRealArray[3], 4);

Move (TheRealArray[1], TheReal, 6);

 

Result := TheReal;

End

Open in new window

0
Comment
Question by:partnershipdev
  • 8
  • 6
15 Comments
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
If I understand you correctly, you have a _1 column that has a porion of the currency amount and a second, _2 column that has a second portion of the currency amount and this is somehow magically transformed via a DDL function into your currency value.
I tried executing the Delphi function that you were provided with "12" and "3456" as the values for the _1 and _2 entries and I got a value that was extremely small and vaery non-currency looking.  So my first question is, can you provide some examples of the values that you see and the results you expect.
My second question is, can you get the DDL function code provided to you?
My final question is, do you know what the _1 and _2 values represent with regard to the currency?
0
 

Author Comment

by:partnershipdev
Comment Utility
I certainly can but not until the morning .... i will post some in about 8 hours as its midnight now!
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
I fully understand that response! ;-)
Hmmm, it is 18:00 local where I am so you must be, as I gathered from the terminology, in the UK.  
Have a good night's sleep and I will check for your response when I get up to head for work tomorrow (about non, your time ;-).
 
0
 

Author Comment

by:partnershipdev
Comment Utility
Good morning, As promised the below is a selection of results I have by using the delphi function inside the DLL.


thNetValue_1      thNetValue_2      realValue
-18292.00      74417438.00      2118.97
-4981.00      794736721.00      1402.96
31629.00      829009428.00      5677.21
5261.00      907757486.00      5827.41
15756.00      1965545226.00      3922.49
10638.00      864194396.00      11488.64
26251.00      1952867942.00      1955.20
143.00      162660352.00      17625.00
139.00      932708352.00      1468.75
142.00      1856765952.00      15275.00
141.00      1702756352.00      7343.75
-28783.00      1012291010.00      96428.67
143.00      162660352.00      17625.00
143.00      162660352.00      17625.00
-28783.00      1012291010.00      96428.67
-23411.00      1723088240.00      7382.53
-23411.00      1723088240.00      7382.53
139.00      932708352.00      1468.75
139.00      932708352.00      1468.75
138.00      788529152.00      700.00
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
There has got to be more to the DLL than the one function.  In the first place, the thNetValue_1 and thNetValue_2 values you show are not integers, which is what the function is expecting to receive.  In the second place, I created a Quick And Dirty app in Delphi to execute that function and, for the first two thNetValue_1 and thNetValue_2 values, I get a result of "2.00449412758495E-307" if I enter them in that order and a result of "2.11381969177272E-307" if I reverse the order.
What you really need is the DDL for the function from the database.  I might be able to interpret that and come up with a T-SQL equivalent.  
Either that or you need to get the rest of the DLL code so that I can follow the logic through the rest of it because that one function is not appearing to provide the "realValue" results that you are showing . . . it's like there is a piece missing from the puzzle.
0
 

Author Comment

by:partnershipdev
Comment Utility
now that is a challenge, all I have is those two values (netval1 and 2) which I import from the pervasive database.  from thier I call an SQL compiled procedure that wraps the dll function and it returns those values.

I think the fact that the netval1 and 2 are showing with 2dp is a red herring caused by the SQL server database structure i created with float datatypes.

I am not sure what more I can provide in this instance as I have given the function as provided by the developers
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
>I am not sure what more I can provide in this instance as I have given the
>function as provided by the developers
The developers have engaged in some numeric magic, as you probably have realized, and have taken advantage of an intimate knowledge of exactly what is happening in the specific version of Delphi they are using, I am afraid.
I have worked with Pervasive BTrieve databases in the past and, frankly, I never ran into a major problem with currency datatypes; however, I also used the recommended BCD datatype (i.e. recommended by Pervasive).
Given the current situation, the suggestion best I can really offer is as follows:
You have, apparently, been able to wrap the Delphi DLL such that you can extract the data and get it into a SQL Server database and you are then able to convert the data to the desired tpe.  (It may not be pretty but it is there.)  You may have to import the data into a staging table, use the wrapped-DLL to transform the 2 columns into the 1 desired column, and then move that transformed data, instead of the 2 columns, into your BI database table.  
As an aside, do you know what version of Delphi your developers use?  (I am assuming that they are developing or have developed something that y'all are using and that it is done in Delphi.)  
Also, if they are manipulating this 2-column data in any way within the BTrieve database using SQL, see if you can get one of those SQL statements.  It should hold a clue as to how they are handling the conversion within BTrieve.  SQL is reasonably standard and, if they are not using some weird quirk in Pervasive SQL, then it should be possible to determine how to create the T-SQL equivalent of what they are doing.  If they are calling some SQL function that they have defined, then that, too, will be apparent and you can then ask for that code so that you can get it translated into T-SQL.
I have to tell you, this is one quirky way to handle the problem that your developers claim to have with the BTrieve database.  BTrieve doeshave a curreny type and it can be more readily translated into a Delphi datatype.
From the Pervasive website (emphasis added):
BCDOverflowException using large Currency values with PDAC.
Problem Description:  
BCDOverflowException using large Currency values with PDAC.  
Entering data into the ''currency'' column near the bounds of the range results in BCDOverflowException . In the Pervasive Control Center, these values work fine.
Problem Environment:Pervasive.SQL V8.5  
Pervasive.SQL V8  
PDAC Pervasive Direct Access Components
Cause of this problem:
Solution Notes:  
Delphi does not offer datatypes sufficient for handling such large currency values accurately.  
 
In order to store and handle very large values in your columns, use float and double datatypes. If you do not use float and double datatypes you will receive inconsistent results with rounding and overflow conditions with very large values from currency data types. The maximum number of digits and values that can be handled in currency data type is -28147497671.0655 to 28147497671.0655  
 

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:partnershipdev
Comment Utility
Diver, I hear where you are coming from however it the product has now been superceded by a father company proposition!  the original product is no longer being developed however it originates from early bTrieve that was later migrated to pervasive sql and as such the functions are old school.

I have traced the pervasive calls and can see that they simply return the two columns to the front end and assume that straight afterwards they must be converting the data internally.

your synopsis on the solution I have in place is correct and although a complete hack from my perspective, it actually does work! albeit slowly on transform.  My challenge moving forwards is we will be totally resident in a 64bit SQL environment soon and at which point my wrapper will fail given it is wrapping an old 16bit dll.
regards,
Dan
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
By the way, I am attaching the Q&D test app that I created that calls the function you provided.  Click on the button and it will convert the two values (_1 and _2 from left to right) and display the results as both a simple FloatToString conversion and a conversion to a format of 20.18 (i.e. something like xx.xxxxxxxxxxxxxxxxxx).
I had to change every one of the file names in the ZIP file so that I could upload it.  They all have an underscore ("_") where the original dot was before the original extension and an extension of ".txt".  This sill web site abviously doesn't deal with programming code well. ;-)

Project1-dof.zip
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
Dan,
OUCH!
the original product is no longer being developed
Does that mean that your original product is now orphaned or that y'all are transitioning off of it?   (Also, if the original product is in 16 bit Delphi or being wrapped in a 16-bit wrapper, that could explain the conversion differences.)
You may want to consider getting your developers to create a new column in the BTrieve table(s) that is of a BTrieve datatype that will allow the data to be properly stored.  
it originates from early bTrieve that was later migrated to pervasive sql and as such the functions are old school
Does this mean that they simply migrated from an old BTrieve database to a newer one and did not modify their tables to take advantage of the newer database's capabilities? :-O  If y'all are going to stay with the BTrieve database on that end of things, you might want to press for them to actually use the right datatypes and, if you are not going to keep the BTrieve back end, then you had best convert off of it before you hit the 64-bit change over! ;-)
However, I am well aware of the "There are decisions you make and those you live with." aspect of our world.
Feel free to contact me any time.  I can be reached at Romeo Delta Whiskey 2 at Golf Victor Tango Charlie dot com.  If you _should_ happen to contact me, I will provide an invite to a professional social network site that you will probably find quite useful. ;-)
0
 

Author Comment

by:partnershipdev
Comment Utility
Thanks, I am mobile at the moment but will drop you a line later on

Dan
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
Dan,
If you do it before 24:00 GMT, you can also send it to my real name (without the space) at SWBC dot com.
Ralph Wilson
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 300 total points
Comment Utility
Here's a better explanation:

This developer was unhappy with the 4-byte floating point value, and didn't want to use up an entire 8-byte double floating point value, so they decided to implement a custom data type, the 6-byte float, which *IS* a supported data type in Turbo PASCAL.  This 6-byte float is NOT a native Pervasive PSQL data type, and therefore the SQL engine has no way of interpretting it.

The two fields that you are seeing must therefore be interpretted as a 6-byte binary value.  See the original source code where they are moving the bytes into a common field, then moving the result into the 6-byte REAL value?  This means that you must interpret the entire field as a 6-byte float.

Here is how this data ios physically stored:

The old Turbo Pascal "Real" type is a 6-byte floating point quantity. The format is
MSB                                               LSB
SFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF EEEEEEEE
S : sign
F : mantissa (implicit leading "1" bit)
E : exponent

if 0 < E <= 255 then Value = (-1)^S * 2^(E -129) * (1.F)
if E = 0 then Value = 0

Now, I don't have a translator in VB or TSQL, but I do have a C function that does the trick.  If you know C, then perhaps you can create your own translator from this.
typedef unsigned char pasreal[6];

typedef unsigned char bassngl[4];

typedef unsigned char basdble[8];

typedef union {

          double        value;

          unsigned char byte[8];

        } IEEEdouble;
 

double pasrealtodouble(pasreal OldNum);

double bassngltodouble(bassngl OldNum);

double basdbletodouble(basdble OldNum);
 

void doubletopasreal(pasreal *New, double Old);

void doubletobassngl(bassngl *New, double Old);

void doubletobasdble(basdble *New, double Old);
 

/*  converts TP 6-byte real to IEEE 8-byte real  */
 

double pasrealtodouble(pasreal OldNum) {
 

  IEEEdouble NewNum;

  char       Sign;

  int        Exp;

  int        X;
 

  for(X = 0; X < 2; X++)

    NewNum.byte[X] = 0x00;
 

  Sign = OldNum[5] & 0x80;

  Exp = OldNum[0] - 0x81 + 0x3FF;

  NewNum.byte[6] = (Exp << 4);

  NewNum.byte[7] = (Exp >> 4) | Sign;
 

  for(X = 5; X > 1; X--) {

    OldNum[X] <<= 1;

    OldNum[X] |= OldNum[X-1] >> 7;

  }

  OldNum[1] <<= 1;
 

  for(X = 6; X >= 2; X--) {

    NewNum.byte[X] |= OldNum[X-1] >> 4;

    NewNum.byte[X-1] =  OldNum[X-1] << 4;

  }
 

  return(NewNum.value);

}

Open in new window

0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 200 total points
Comment Utility
Bill,
Thank you for the additional information.  
The reference to not wanting to consume a whole 8 bytes and opting, instead, for a 6-byte representation is exactly how we got into the Y2K mess. ;-)  
The reference to old Turbo Pascal provided an insight and, as I suspected, it is an issue of the representation of the data that is the heart of the problem.  Since I am working with Delphi 6, I am not working with the same internal data representations that the "old Turbo Pascal" used.  However, given the new information, I think I can create an equivalent function in C# and, if y'all are on SQL Server 2005/2008, that can be turned into a UDF for the database.  
I'll get back to y'all.
 
0
 

Author Comment

by:partnershipdev
Comment Utility
Fantastic thank you both v much
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now