Convert Delphi Function to VBS or Transact SQL

Posted on 2009-04-14
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;
TheRealArray : Array [1..6] Of Char;
TheReal : Real;
Move (Int2, TheRealArray[1], 2);
Move (Int4, TheRealArray[3], 4);
Move (TheRealArray[1], TheReal, 6);
Result := TheReal;

Open in new window

Question by:partnershipdev
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
LVL 22

Expert Comment

ID: 24143336
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?

Author Comment

ID: 24143437
I certainly can but not until the morning .... i will post some in about 8 hours as its midnight now!
LVL 22

Expert Comment

ID: 24143462
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 ;-).
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


Author Comment

ID: 24145309
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
LVL 22

Expert Comment

ID: 24148141
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.

Author Comment

ID: 24148205
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
LVL 22

Expert Comment

ID: 24148812
>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  


Author Comment

ID: 24148884
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.
LVL 22

Expert Comment

ID: 24149046
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. ;-)
LVL 22

Expert Comment

ID: 24149260
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. ;-)

Author Comment

ID: 24149292
Thanks, I am mobile at the moment but will drop you a line later on

LVL 22

Expert Comment

ID: 24149329
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
LVL 28

Accepted Solution

Bill Bach earned 300 total points
ID: 24149342
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
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;

Open in new window

LVL 22

Assisted Solution

8080_Diver earned 200 total points
ID: 24149421
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.

Author Comment

ID: 24149469
Fantastic thank you both v much

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…

696 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