[mdb-dev] NUMERIC / DECIMAL fields

[Jakob E. <jab...@gm...>]

Dear friends,

I finally found out how NUMERIC fields are really stored, so let me share this with you.

Numeric fields are only available starting with Access 2000. Numeric fields can have up to 28 digits (this is called the precision). The decimal point is fixed, and the scale defines how many digits are to the right of the decimal point. Numeric fields always take up 17 bytes of space.

The first byte is a marker that has the high bit set if the number is negative. Then follow 4 empty bytes. Then we have a high four byte word, a middle 4 byte word, and a low 4 byte word. The words are little endian unsigned longs, combining to one 12 byte unsigned integer. (this means that this 12byte integer is stored in mixed endian format)


Attached to this email is a patch that really, really fixes MDB_NUMERIC fields. (The patch is also available at https://github.com/jakob/mdbtools/tree/mdbviewer )


For testing purposes, I've made an access database that contains many different NUMERIC date.
It's available here: http://jabakobob.net/mdbviewer/mdbtools/samples.html






From b573fb8310e5dfb6574ddf1a05e3108d3f43efb4 Mon Sep 17 00:00:00 2001
From: jakob <jab...@gm...>
Date: Wed, 16 Mar 2011 14:24:49 +0100
Subject: [PATCH] Fixed NUMERIC data type.

---
 src/libmdb/data.c  |   26 +++----------------
 src/libmdb/money.c |   70 +++++++++++++++++++++++++++++++++++++---------------
 2 files changed, 54 insertions(+), 42 deletions(-)

diff --git a/src/libmdb/data.c b/src/libmdb/data.c
index 5c31343..315e848 100644
--- a/src/libmdb/data.c
+++ b/src/libmdb/data.c
@@ -27,9 +27,10 @@
 #define OFFSET_MASK 0x1fff
 
 char *mdb_money_to_string(MdbHandle *mdb, int start);
+char *mdb_numeric_to_string(MdbHandle *mdb, int start, int prec, int scale);
+
 static int _mdb_attempt_bind(MdbHandle *mdb, 
 	MdbColumn *col, unsigned char isnull, int offset, int len);
-static char *mdb_num_to_string(MdbHandle *mdb, int start, int datatype, int prec, int scale);
 static char *mdb_date_to_string(MdbHandle *mdb, int start);
 #ifdef MDB_COPY_OLE
 static size_t mdb_copy_ole(MdbHandle *mdb, void *dest, int start, int size);
@@ -222,12 +223,9 @@ int ret;
 			//fprintf(stdout,"len %d size %d\n",len, col->col_size);
 			char *str;
 			if (col->col_type == MDB_NUMERIC) {
-				str = mdb_num_to_string(mdb, start,
-					col->col_type, col->col_prec,
-					col->col_scale);
+				str = mdb_numeric_to_string(mdb, start, col->col_prec, col->col_scale);
 			} else {
-				str = mdb_col_to_string(mdb, mdb->pg_buf, start,
-					col->col_type, len);
+				str = mdb_col_to_string(mdb, mdb->pg_buf, start, col->col_type, len);
 			}
 			strcpy(col->bind_ptr, str);
 			g_free(str);
@@ -703,22 +701,6 @@ static char *mdb_memo_to_string(MdbHandle *mdb, int start, int size)
 		return text;
 	}
 }
-static char *
-mdb_num_to_string(MdbHandle *mdb, int start, int datatype, int prec, int scale)
-{
-	char *text;
-	gint32 l;
-
-	memcpy(&l, mdb->pg_buf+start+13, 4);
-
-	text = (char *) g_malloc(prec+2);
-	sprintf(text, "%0*" G_GINT32_FORMAT, prec, GINT32_FROM_LE(l));
-	if (scale) {
-		memmove(text+prec-scale+1, text+prec-scale, scale+1);
-		text[prec-scale] = '.';
-	}
-	return text;
-}
 
 #if 0
 static int trim_trailing_zeros(char * buff)
diff --git a/src/libmdb/money.c b/src/libmdb/money.c
index 2b0300b..65f6a14 100644
--- a/src/libmdb/money.c
+++ b/src/libmdb/money.c
@@ -24,7 +24,7 @@
 #include "dmalloc.h"
 #endif
 
-#define MAXPRECISION 19
+#define MAX_NUMERIC_PRECISION 28
 /* 
 ** these routines are copied from the freetds project which does something
 ** very similiar
@@ -43,41 +43,70 @@ static char *array_to_string(unsigned char *array, int unsigned scale, int neg);
  */
 char *mdb_money_to_string(MdbHandle *mdb, int start)
 {
-	const int num_bytes = 8;
+	int num_bytes=8, prec=19, scale=4;
 	int i;
 	int neg=0;
-	unsigned char multiplier[MAXPRECISION], temp[MAXPRECISION];
-	unsigned char product[MAXPRECISION];
-	unsigned char money[num_bytes];
+	unsigned char multiplier[MAX_NUMERIC_PRECISION], temp[MAX_NUMERIC_PRECISION];
+	unsigned char product[MAX_NUMERIC_PRECISION];
+	unsigned char bytes[num_bytes];
 
-	memset(multiplier,0,MAXPRECISION);
-	memset(product,0,MAXPRECISION);
+	memset(multiplier,0,MAX_NUMERIC_PRECISION);
+	memset(product,0,MAX_NUMERIC_PRECISION);
 	multiplier[0]=1;
-	memcpy(money, mdb->pg_buf + start, num_bytes);
+	memcpy(bytes, mdb->pg_buf + start, num_bytes);
 
 	/* Perform two's complement for negative numbers */
-	if (money[7] & 0x80) {
+	if (bytes[num_bytes-1] & 0x80) {
 		neg = 1;
 		for (i=0;i<num_bytes;i++) {
-			money[i] = ~money[i];
+			bytes[i] = ~bytes[i];
 		}
 		for (i=0; i<num_bytes; i++) {
-			money[i] ++;
-			if (money[i]!=0) break;
+			bytes[i] ++;
+			if (bytes[i]!=0) break;
 		}
 	}
 
 	for (i=0;i<num_bytes;i++) {
 		/* product += multiplier * current byte */
-		multiply_byte(product, money[i], multiplier);
+		multiply_byte(product, bytes[i], multiplier);
 
 		/* multiplier = multiplier * 256 */
-		memcpy(temp, multiplier, MAXPRECISION);
-		memset(multiplier,0,MAXPRECISION);
+		memcpy(temp, multiplier, MAX_NUMERIC_PRECISION);
+		memset(multiplier, 0, MAX_NUMERIC_PRECISION);
 		multiply_byte(multiplier, 256, temp);
 	}
-	return array_to_string(product, 4, neg);
+	return array_to_string(product, scale, neg);
+
+}
+
+char *mdb_numeric_to_string(MdbHandle *mdb, int start, int prec, int scale) {
+	int num_bytes = 16;
+	int i;
+	int neg=0;
+	unsigned char multiplier[MAX_NUMERIC_PRECISION], temp[MAX_NUMERIC_PRECISION];
+	unsigned char product[MAX_NUMERIC_PRECISION];
+	unsigned char bytes[num_bytes];
+
+	memset(multiplier,0,MAX_NUMERIC_PRECISION);
+	memset(product,0,MAX_NUMERIC_PRECISION);
+	multiplier[0]=1;
+	memcpy(bytes, mdb->pg_buf + start + 1, num_bytes);
+
+	/* Perform two's complement for negative numbers */
+	if (mdb->pg_buf[start] & 0x80) neg = 1;
+	for (i=0;i<num_bytes;i++) {
+		/* product += multiplier * current byte */
+		multiply_byte(product, bytes[12-4*(i/4)+i%4], multiplier);
+
+		/* multiplier = multiplier * 256 */
+		memcpy(temp, multiplier, MAX_NUMERIC_PRECISION);
+		memset(multiplier, 0, MAX_NUMERIC_PRECISION);
+		multiply_byte(multiplier, 256, temp);
+	}
+	return array_to_string(product, scale, neg);
 }
+
 static int multiply_byte(unsigned char *product, int num, unsigned char *multiplier)
 {
 	unsigned char number[3];
@@ -87,7 +116,7 @@ static int multiply_byte(unsigned char *product, int num, unsigned char *multipl
 	number[1]=(num/10)%10;
 	number[2]=(num/100)%10;
 
-	for (i=0;i<MAXPRECISION;i++) {
+	for (i=0;i<MAX_NUMERIC_PRECISION;i++) {
 		if (multiplier[i] == 0) continue;
 		for (j=0;j<3;j++) {
 			if (number[j] == 0) continue;
@@ -101,7 +130,7 @@ static int do_carry(unsigned char *product)
 {
 	unsigned int j;
 
-	for (j=0;j<MAXPRECISION-1;j++) {
+	for (j=0;j<MAX_NUMERIC_PRECISION-1;j++) {
 		if (product[j]>9) {
 			product[j+1]+=product[j]/10;
 			product[j]=product[j]%10;
@@ -117,9 +146,10 @@ static char *array_to_string(unsigned char *array, unsigned int scale, int neg)
 	char *s;
 	unsigned int top, i, j=0;
 	
-	for (top=MAXPRECISION;(top>0) && (top-1>scale) && !array[top-1];top--);
+	for (top=MAX_NUMERIC_PRECISION;(top>0) && (top-1>scale) && !array[top-1];top--);
 
-	s = (char *) g_malloc(22);
+	/* allocate enough space for all digits + minus sign + decimal point + trailing NULL byte */
+	s = (char *) g_malloc(MAX_NUMERIC_PRECISION+3);
 
 	if (neg)
 		s[j++] = '-';
-- 
1.7.2.3+GitX