Convert Delphi Function to VBS or Transact SQL
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 !
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;
EndASKER
I certainly can but not until the morning .... i will post some in about 8 hours as its midnight now!
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 ;-).
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 ;-).
ASKER
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
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
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.
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.
ASKER
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
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
>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
>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
ASKER
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
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
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
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
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. ;-)
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. ;-)
ASKER
Thanks, I am mobile at the moment but will drop you a line later on
Dan
Dan
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fantastic thank you both v much
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?