mdbtools-dev

Re: [mdb-dev] libmdb memory corruption (double free)

[Alex M. <al...@no...>]

On 25.03.2011 18:57, Jakob Egger wrote:
> There's a memory overflow bug in the Memo-to-string function (character set conversion) somewhere, I fixed it but I still need to prepare it for github. Maybe I'll have time to sort through my changes on the weekend. Does your database by any chance contain large Memo fields? One workaround could be to compile MDB Tools with a larger bind size (I think the default is 10000, try using 200000). The bind size is declared in mdbtools.h, I think.

Jakob,

thanks for that hint. You're right, the database contains some large 
memo fields. After increasing the bind size and rebuilding mdb-export 
(as well as my PHP script making use of the mdbtools binding) runs 
through - great!

However, i understand that this is a temporary fix? Would be great to 
get access to the patch you mentioned...

> I'd be happy to test your mdb file if you send it to me.

I'll send it to you in private mail.

thanks!

Alex

Re: [mdb-dev] libmdb memory corruption (double free)

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

There's a memory overflow bug in the Memo-to-string function (character set conversion) somewhere, I fixed it but I still need to prepare it for github. Maybe I'll have time to sort through my changes on the weekend. Does your database by any chance contain large Memo fields? One workaround could be to compile MDB Tools with a larger bind size (I think the default is 10000, try using 200000). The bind size is declared in mdbtools.h, I think.

I'd be happy to test your mdb file if you send it to me.

Best regards,
Jakob Egger


PS: I find these memory-management-related bugs much easier to debug using valgrind rather than gdb.

On 25.03.2011, at 10:37, Alex Mayrhofer wrote:

> 
> Hello,
> 
> I'm using mdbtools to export an Access database on a Linux machine. 
> Unfortunately, mdb-export crashes with memory corruption when processing 
> the database (after a couple of rows). A trace is included below - 
> unfortunately my gdb knowledge is not good enough to get around the heap 
> corruption, however, glibc provided a reasonable backtrace (included below).
> 
> The problem seems to occur in mdb_col_to_string() - i've included what i 
> have below.
> 
> I can't post the mdb file itself to the list due to copyright issues, 
> but i think i can email it to someone privately. I really appreciate any 
> help, since this is a show-stopper for me at the moment - i'm very 
> willing to debug more..
> 
> thanks,
> 
> Alex Mayrhofer
> 
> Backtrace:
> 
> *** glibc detected *** mdb-export: double free or corruption (out): 
> 0x09d8cac8 ***
> ======= Backtrace: =========
> /lib/i686/cmov/libc.so.6[0xb75d5764]
> /lib/i686/cmov/libc.so.6(cfree+0x96)[0xb75d7966]
> /usr/lib/libglib-2.0.so.0(g_free+0x36)[0xb7704446]
> /usr/lib/libmdb.so.1(mdb_col_to_string+0x1ef)[0xb777e52f]
> /usr/lib/libmdb.so.1[0xb777ebda]
> /usr/lib/libmdb.so.1(mdb_read_row+0x228)[0xb777eef8]
> /usr/lib/libmdb.so.1(mdb_fetch_row+0x9d)[0xb777efed]
> mdb-export[0x8049207]
> /lib/i686/cmov/libc.so.6(__libc_start_main+0xe5)[0xb757d455]
> mdb-export[0x8048a61]
> ======= Memory map: ========
> 08048000-0804a000 r-xp 00000000 08:01 95513      /usr/bin/mdb-export
> 0804a000-0804b000 rw-p 00001000 08:01 95513      /usr/bin/mdb-export
> 09d7e000-09d9f000 rw-p 09d7e000 00:00 0          [heap]
> b6700000-b6721000 rw-p b6700000 00:00 0
> b6721000-b6800000 ---p b6721000 00:00 0
> b68e9000-b68f5000 r-xp 00000000 08:01 32451      /lib/libgcc_s.so.1
> b68f5000-b68f6000 rw-p 0000b000 08:01 32451      /lib/libgcc_s.so.1
> b68fd000-b753e000 rw-p b68fd000 00:00 0
> b753e000-b7566000 r-xp 00000000 08:01 93844      /usr/lib/libpcre.so.3.12.1
> b7566000-b7567000 rw-p 00027000 08:01 93844      /usr/lib/libpcre.so.3.12.1
> b7567000-b76bc000 r-xp 00000000 08:01 41015      /lib/i686/cmov/libc-2.7.so
> b76bc000-b76bd000 r--p 00155000 08:01 41015      /lib/i686/cmov/libc-2.7.so
> b76bd000-b76bf000 rw-p 00156000 08:01 41015      /lib/i686/cmov/libc-2.7.so
> b76bf000-b76c3000 rw-p b76bf000 00:00 0
> b76c3000-b7777000 r-xp 00000000 08:01 96016 
> /usr/lib/libglib-2.0.so.0.1600.6
> b7777000-b7778000 rw-p 000b4000 08:01 96016 
> /usr/lib/libglib-2.0.so.0.1600.6
> b7778000-b7787000 r-xp 00000000 08:01 89764      /usr/lib/libmdb.so.1.0.0
> b7787000-b7788000 rw-p 0000e000 08:01 89764      /usr/lib/libmdb.so.1.0.0
> b778e000-b7791000 rw-p b778e000 00:00 0
> b7791000-b7792000 r-xp b7791000 00:00 0          [vdso]
> b7792000-b77ac000 r-xp 00000000 08:01 35153      /lib/ld-2.7.so
> b77ac000-b77ae000 rw-p 0001a000 08:01 35153      /lib/ld-2.7.so
> bfe66000-bfe7b000 rw-p bffea000 00:00 0          [stack]
> Aborted
> 
> 
> ------------------------------------------------------------------------------
> Enable your software for Intel(R) Active Management Technology to meet the
> growing manageability and security demands of your customers. Businesses
> are taking advantage of Intel(R) vPro (TM) technology - will your software 
> be a part of the solution? Download the Intel(R) Manageability Checker 
> today! http://p.sf.net/sfu/intel-dev2devmar
> _______________________________________________
> mdbtools-dev mailing list
> mdb...@li...
> https://lists.sourceforge.net/lists/listinfo/mdbtools-dev

[mdb-dev] libmdb memory corruption (double free)

[Alex M. <al...@no...>]

Hello,

I'm using mdbtools to export an Access database on a Linux machine. 
Unfortunately, mdb-export crashes with memory corruption when processing 
the database (after a couple of rows). A trace is included below - 
unfortunately my gdb knowledge is not good enough to get around the heap 
corruption, however, glibc provided a reasonable backtrace (included below).

The problem seems to occur in mdb_col_to_string() - i've included what i 
have below.

I can't post the mdb file itself to the list due to copyright issues, 
but i think i can email it to someone privately. I really appreciate any 
help, since this is a show-stopper for me at the moment - i'm very 
willing to debug more..

thanks,

Alex Mayrhofer

Backtrace:

*** glibc detected *** mdb-export: double free or corruption (out): 
0x09d8cac8 ***
======= Backtrace: =========
/lib/i686/cmov/libc.so.6[0xb75d5764]
/lib/i686/cmov/libc.so.6(cfree+0x96)[0xb75d7966]
/usr/lib/libglib-2.0.so.0(g_free+0x36)[0xb7704446]
/usr/lib/libmdb.so.1(mdb_col_to_string+0x1ef)[0xb777e52f]
/usr/lib/libmdb.so.1[0xb777ebda]
/usr/lib/libmdb.so.1(mdb_read_row+0x228)[0xb777eef8]
/usr/lib/libmdb.so.1(mdb_fetch_row+0x9d)[0xb777efed]
mdb-export[0x8049207]
/lib/i686/cmov/libc.so.6(__libc_start_main+0xe5)[0xb757d455]
mdb-export[0x8048a61]
======= Memory map: ========
08048000-0804a000 r-xp 00000000 08:01 95513      /usr/bin/mdb-export
0804a000-0804b000 rw-p 00001000 08:01 95513      /usr/bin/mdb-export
09d7e000-09d9f000 rw-p 09d7e000 00:00 0          [heap]
b6700000-b6721000 rw-p b6700000 00:00 0
b6721000-b6800000 ---p b6721000 00:00 0
b68e9000-b68f5000 r-xp 00000000 08:01 32451      /lib/libgcc_s.so.1
b68f5000-b68f6000 rw-p 0000b000 08:01 32451      /lib/libgcc_s.so.1
b68fd000-b753e000 rw-p b68fd000 00:00 0
b753e000-b7566000 r-xp 00000000 08:01 93844      /usr/lib/libpcre.so.3.12.1
b7566000-b7567000 rw-p 00027000 08:01 93844      /usr/lib/libpcre.so.3.12.1
b7567000-b76bc000 r-xp 00000000 08:01 41015      /lib/i686/cmov/libc-2.7.so
b76bc000-b76bd000 r--p 00155000 08:01 41015      /lib/i686/cmov/libc-2.7.so
b76bd000-b76bf000 rw-p 00156000 08:01 41015      /lib/i686/cmov/libc-2.7.so
b76bf000-b76c3000 rw-p b76bf000 00:00 0
b76c3000-b7777000 r-xp 00000000 08:01 96016 
/usr/lib/libglib-2.0.so.0.1600.6
b7777000-b7778000 rw-p 000b4000 08:01 96016 
/usr/lib/libglib-2.0.so.0.1600.6
b7778000-b7787000 r-xp 00000000 08:01 89764      /usr/lib/libmdb.so.1.0.0
b7787000-b7788000 rw-p 0000e000 08:01 89764      /usr/lib/libmdb.so.1.0.0
b778e000-b7791000 rw-p b778e000 00:00 0
b7791000-b7792000 r-xp b7791000 00:00 0          [vdso]
b7792000-b77ac000 r-xp 00000000 08:01 35153      /lib/ld-2.7.so
b77ac000-b77ae000 rw-p 0001a000 08:01 35153      /lib/ld-2.7.so
bfe66000-bfe7b000 rw-p bffea000 00:00 0          [stack]
Aborted

[mdb-dev] Opening database on two laptops.

[Grant <voi...@gm...>]

Hi everyone,

I recently installed another laptop for my point of sale software
accessing a mdb file on a Windows 7 machine.

The following is strace output from the terminal php script I run on
the working laptop and subsequently on the non working.

stat64("/mnt/invoicingaid/wny.mdb", {st_mode=S_IFREG|S_ISGID|0767,
st_size=2691072, ...}) = 0
open("/mnt/invoicingaid/wny.mdb", O_RDONLY) = 4
fstat64(4, {st_mode=S_IFREG|S_ISGID|0767, st_size=2691072, ...}) = 0

-

stat64("/mnt/invoicingaid/wny.mdb", {st_mode=S_IFREG|0755,
st_size=2691072, ...}) = 0
write(2, "Can't alloc filename\n", 21)  = 21

All the permissions are set correctly and as others have said, I can
copy the database from the samba share and open it on the local file
system, but unlike on the first laptop I can not open it directly from
the live samba share.

There is something I have missed in the latest version of mdb-tools or
the latest version of php that enables me to open the file directly
from the samba share in the former but not the latter.

Please help. :)

[mdb-dev] Recent patches

[Brian B. <br...@br...>]

Hi All,

Sorry for my lag.  All the patches posted to the list recently should
now be applied to my repository.  Please let me know if I missed any.

Regards,

Brian

[mdb-dev] "Error expanding embedded variable" making all in odbc

[Dale S. <dal...@sh...>]

Hi, I'm stuck trying to compile the mdbtools ODBC driver manager from the
github master branch (downloaded as a gz.tar yesterday) on a FreeBSD 7.3
system. I did some experimenting with the 0.5 release in the FreeBSD ports,
but need ODBC. It seems development moved to Github from Sourceforge several
years ago, and it seemed a good idea to use the latest and greatest rather
than trying to get the 0.6pre1 release from Sourceforge working (which
reports seemed to indicate I would still need to update from CVS). So, using
the github download and following INSTALL:

 

========================================

 

stumbo# ./autogen.sh

. <snip, no errors or warnings>

Now type `make' to compile MDBTools.

stumbo# ./configure --with-unixodbc=/usr/local

. <snip, no errors or warnings>

stumbo# make

Making all in src

Making all in libmdb

. <snip, no errors or warnings>

Making all in odbc

Error expanding embedded variable.

*** Error code 1

 

Stop in /usr/home/dev/temp/brianb-mdbtools-ff251e7/src.

*** Error code 1

 

Stop in /usr/home/dev/temp/brianb-mdbtools-ff251e7.

stumbo#

 

========================================

 

>From a FreeBSD problem report discussing trying to get the ODBC driver
compiled with the 0.5 release and recommending moving to 0.6pre1
(http://www.freebsd.org/cgi/query-pr.cgi?pr=85586
<http://www.freebsd.org/cgi/query-pr.cgi?pr=85586&cat> &cat=), it seems this
may be a known problem at least on FreeBSD systems. I'm hoping it's an
obvious one though, with an equally obvious solution, as I'm pretty much
still a noob at nix systems programming.

 

My system configuration is pretty much vanilla FreeBSD 7.3:

 

stumbo# uname -a

FreeBSD stumbo.ghostlytrio.local 7.3-RELEASE FreeBSD 7.3-RELEASE #0: Sun Mar
21 06:15:01 UTC 2010
ro...@wa...:/usr/obj/usr/src/sys/GENERIC  i386

stumbo# pkg_info | grep libtool

libtool-2.2.6b      Generic shared library support script

stumbo# pkg_info | grep automake

automake-1.10.1     GNU Standards-compliant Makefile generator (1.10)

automake-wrapper-20071109 Wrapper script for GNU automake

stumbo# pkg_info | grep autoconf

autoconf-2.62       Automatically configure source code on many Un*x
platforms

autoconf-wrapper-20071109 Wrapper script for GNU autoconf

stumbo#

 

Can anyone offer a pithy and quick solution?

 

Thanks,
Dale

 

[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

Re: [mdb-dev] Sanitizing table/column names

[Dmitry N. <luc...@ma...>]

Hi, Nirgal!

Some others difficults has in the process to import scheme in MySQL DB.
This errors produce situation to ignore first command of mysql like as "DROP
DATABASE IF EXIST" and as result INSERT dates into old table.
The 2 lines of comment in header of scheme formed in src/libmdb/backend.c
has "-------------------------------------------------------------------------------", wich not ignored
by mysql. I propose replace there one symbol to space, as "-- ---------------------------"
This lines will not reproduce the warnings in import scheme to MySQL
Attach the small patch do this: mdbtools-mysql-warning.patch

My conf: Mandriva 2010 x86_64, mysql-5.1.42-0.5mdv2010.0 mdbtools-0.6pre1-git-2011-02-21 with
format-security and odbctypes patches.

Nirgal Vourgère <con...@ni...> wrote:
> On Monday 07 March 2011 16:57:09 Dmitry Nikitin wrote:
> > > > 2) need to fix the procedure of sanitize (-S) the dump of MDB files. You
> > > already changes dates to utf-8 codepage, and the more "dummy"
> > > > MS windows users and "admins" used the non ASCII symbols for names of tables
> > > and columns in the MDB-files, like as cyrilic :(
> > > > May be the procedure of sanitize can replace the symbols in names as
> > > transliterate (need check the match of names and length?)
> > > > Now, the replaced only spaces in names as "_", and .... the other
> > > non-english symbols taking "____________________________" as name of table.
> > 
> > > Sanitize is really buggy.
> > > I would recommand removing it from the code.
> > > All backends should now handle the non ascii characters in table/colum names
> > > correctly, quoting them.
> > > Do you really need -S?
> > 
> > I think- Yes, "-S" its good idea for package!!! (non-ASCII symbols in variables, names of tables and other is bad practise in developing.
> > I tested JET4 file with ciryllic symbols, spaces, " ' " and others :((( in names of tables, fields.
> 
> As you pointed out, sanitizing is really tricky.
> I tested a BIG5 encoded jet3 database, and just like you, ended up with conflicts because I had a lot of columns with the same name "__"!
> What do you suggest we should do? Rename every thing into table1, table2, table3...?? Remember we now export relationships. I 
> suspect we might export relationships on linked tables in the future. Renaming these will really be messy.
> I do agree having ideograms as column names, or even spaces is not very fun.
> But in my experience, export does work with these -- I tested postgres backend only I'm afraid.
> *I think fixing database schemas is beyond the scope of mdbtools.*
> mdbtools will convert your poorly design access schema into a poorly design postgres schema for example. It will keep spaces, cyrilic, ideogram or whatever. It will produce a working unicode database. You just need to properly quote your 
> names in your queries, that's all. I've read mysql uses ` character.
> IMHO, you should use another tool if you want to rename your columns.

Yes, I use sed in shell script to automatic rename the badlly names in scheme and dump. But it's need more time for testing and make
good scheme to rename.
I propouse (as temporary) leave in the code sanitize for replace the scaces by underscore symbol and should comment the code, wich replace others (non literal-numerical) symbols. So, in future will continued develop the procedure to replace non-ASCII symbols in the transliterated. May be, create two arrays of letters ASCII and matched for character transliteration, such as in Ukrainian:
"Ніргал Вургере" => "nirgal_vourgere"

Good Luck, Dmitry

Re: [mdb-dev] Deferrable on foreign keys in exported schema

[Peter R. <qwe...@gm...>]

Nirgal Vourgère <contact_mdbtools <at> nirgal.com> writes:

> In example http://www.nirgal.com/mdbtools/debian.squeeze/mdb2pgsql script,
> foreign keys and indexes are output on standard output 3, so they can be
> processed at the end. Importing the indexes at the end really speed up the
> process.
> 
> You might find there usefull ideas there, such as support for tables with
> quotes and slashes in names, support for dates before 1000AD, sequences
> initialisations... On the other end, you probably don't want to add OIDS...

Thanks Nirgal,

That looks like it took some effort!  My shell scripting isn't up to that level,
so I'd never have come up with that sort of thing on my own.  We only want to
have a read-only Postgres copy of the Access database, we'll never be inserting
data after import, so deferrable is not necessary, and your solution will work
fine.

Thanks again,

Peter

Re: [mdb-dev] Deferrable on foreign keys in exported schema

[Nirgal V. <con...@ni...>]

On Tuesday 08 March 2011 10:58:44 Peter Russell wrote:
> I'm using mdb-tools to import an Access database into Postgres, one problem
> though is that loading data into an exported schema is more difficult than it
> could be.  It would help if the generated constraints were deferrable, so that
> the data can be loaded in in any order.
> (...)

In example http://www.nirgal.com/mdbtools/debian.squeeze/mdb2pgsql script, foreign keys and indexes are output on standard output 3, so they can be processed at the end. Importing the indexes at the end really speed up the process.

You might find there usefull ideas there, such as support for tables with quotes and slashes in names, support for dates before 1000AD, sequences initialisations... On the other end, you probably don't want to add OIDS...

Cheers

Re: [mdb-dev] [patch] Re: -Werror=format-security

[Dmitry N. <luc...@ma...>]

Hi! Nirgal, 

> > > Attached is a patch. Does it help?
> > Probe the patch "g_warning.diff", but have'nt success, give next errors:
> > (...)
> 
> Hello Dimitry
> 
> Attached is another more general patch about printf(string_that_may_contain_percent) problems.
> It replaces g_warning.diff
> Project now compiles OK here with gcc option -Werror=format-security
> Can you test it?

Yes, tested and process of compilation passed without errors.
Thanks for quickly respond.

[mdb-dev] Deferrable on foreign keys in exported schema

[Peter R. <qwe...@gm...>]

Hi list,

I'm using mdb-tools to import an Access database into Postgres, one problem
though is that loading data into an exported schema is more difficult than it
could be.  It would help if the generated constraints were deferrable, so that
the data can be loaded in in any order.

Thanks

Peter Russell

diff --git a/src/libmdb/backend.c b/src/libmdb/backend.c
index 655303c..48830cd 100644
--- a/src/libmdb/backend.c
+++ b/src/libmdb/backend.c
@@ -643,7 +643,7 @@ mdb_get_relationships(MdbHandle *mdb, const gchar
*namespace, const char* tablen
                                " REFERENCES ", quoted_table_2, "(",
quoted_column_2, ")",
                                (grbit & 0x00000100) ? " ON UPDATE CASCADE" : "",
                                (grbit & 0x00001000) ? " ON DELETE CASCADE" : "",
-                               ";\n", NULL);
+                               " DEFERRABLE", ";\n", NULL);
 
                        break;
                }

[mdb-dev] Re: Sanitizing table/column names

[Nirgal V. <con...@ni...>]

On Monday 07 March 2011 16:57:09 Dmitry Nikitin wrote:
> (...)
> > > 2) need to fix the procedure of sanitize (-S) the dump of MDB files. You
> > already changes dates to utf-8 codepage, and the more "dummy"
> > > MS windows users and "admins" used the non ASCII symbols for names of tables
> > and columns in the MDB-files, like as cyrilic :(
> > > May be the procedure of sanitize can replace the symbols in names as
> > transliterate (need check the match of names and length?)
> > > Now, the replaced only spaces in names as "_", and .... the other
> > non-english symbols taking "____________________________" as name of table.
> 
> > Sanitize is really buggy.
> > I would recommand removing it from the code.
> > All backends should now handle the non ascii characters in table/colum names
> > correctly, quoting them.
> > Do you really need -S?
> 
> I think- Yes, "-S" its good idea for package!!! (non-ASCII symbols in variables, names of tables and other is bad practise in developing.
>   
> > If using Jet3, don't forget to set MDB_JET3_CHARSET as described in mdb-schema
> > manual and HACKING file.
> 
> I tested JET4 file with ciryllic symbols, spaces, " ' " and others :((( in names of tables, fields.

As you pointed out, sanitizing is really tricky.
I tested a BIG5 encoded jet3 database, and just like you, ended up with conflicts because I had a lot of columns with the same name "__"!
What do you suggest we should do? Rename every thing into table1, table2, table3...?? Remember we now export relationships. I suspect we might export relationships on linked tables in the future. Renaming these will really be messy.

I do agree having ideograms as column names, or even spaces is not very fun.
But in my experience, export does work with these -- I tested postgres backend only I'm afraid.

*I think fixing database schemas is beyond the scope of mdbtools.*

mdbtools will convert your poorly design access schema into a poorly design postgres schema for example. It will keep spaces, cyrilic, ideogram or whatever. It will produce a working unicode database. You just need to properly quote your 
names in your queries, that's all. I've read mysql uses ` character.

IMHO, you should use another tool if you want to rename your columns.

[mdb-dev] [patch] Re: -Werror=format-security

[Nirgal V. <con...@ni...>]

On Monday 07 March 2011 16:57:09 Dmitry Nikitin wrote:
> > Attached is a patch. Does it help?
> Probe the patch "g_warning.diff", but have'nt success, give next errors:
> (...)

Hello Dimitry

Attached is another more general patch about printf(string_that_may_contain_percent) problems.
It replaces g_warning.diff
Project now compiles OK here with gcc option -Werror=format-security
Can you test it?

Re: [mdb-dev] Proposed changes to HACKING doc

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

My dear fellow hackers,

I found out some more information about the column flags byte.

The special columns used for replication all have the bit 0x10 set. I haven't seen this bit on any other columns. Maybe this marks the column as hidden, or as replication-related. These columns start with "s_" or "Gen_" (the "Gen_" fields are for memo fields)

In Access 2007 and Access 2010, "Complex Columns" (multivalued fields, version history, attachments) always have the flag byte set to exactly 0x07

Hyperlinks (basically memo fields with a special syntax) have the 0x80 bit set. The hyperlinks seem to have the following syntax:
"Link Title#http://example.com/somepage.html#" or "#PAGE.HTM#" (see the old NWind.mdb sample database)

Thus, as far as I know, the known bits of the column bitmask are:
0x01: variable length column
0x02: can be null
0x04: is auto long
0x10: replication related field (or hidden?)
0x40: is auto guid
0x80: hyperlink

I haven't seen the other bits anywhere yet


Best regards,
Jakob Egger



On 28.02.2011, at 09:25, dw wrote:
> 4) From table.c we see that:
>         pcol->is_long_auto = col[fmt->col_flags_offset] & 0x04 ? 1 : 0;
> 	pcol->is_uuid_auto = col[fmt->col_flags_offset] & 0x40 ? 1 : 0;
> My own observations suggest bit 2 indicates whether the column can be null.  
> 
> Thus, the known bits of the column bitmask are:
> 0x01: variable length column
> 0x02: can be null
> 0x04: is auto long
> 0x40: is auto guid
> So, this line from HACKING is insufficient:
> | ???? | 1 byte  | bitmask     | low order bit indicates variable columns |

Re: [mdb-dev] [patch] Re: More patches soon ...

[Dmitry N. <luc...@ma...>]

Hi!

> Attached is a patch. Does it help?
> 

Probe the patch "g_warning.diff", but have'nt success, give next errors:  
$ make
....
gcc -DHAVE_CONFIG_H -I. -I../../include  -I../../include -pthread -DORBIT2=1 -I/usr/include/gtk-2.0 -I/usr/lib/gtk-2.0/include -I/usr/include/atk-1.0 -I/usr/include/cairo -I/usr/include/pango-1.0 -I/usr/include/glib-2.0 -I/usr/lib/glib-2.0/include -I/usr/include/pixman-1 -I/usr/include/freetype2 -I/usr/include/libpng12 -I/usr/include/libglade-2.0 -I/usr/include/libxml2 -I/usr/include/libgnomeui-2.0 -I/usr/include/libart-2.0 -I/usr/include/gconf/2 -I/usr/include/gnome-keyring-1 -I/usr/include/libgnome-2.0 -I/usr/include/libbonoboui-2.0 -I/usr/include/libgnomecanvas-2.0 -I/usr/include/gnome-vfs-2.0 -I/usr/lib/gnome-vfs-2.0/include -I/usr/include/orbit-2.0 -I/usr/include/dbus-1.0 -I/usr/lib/dbus-1.0/include -I/usr/include/libbonobo-2.0 -I/usr/include/bonobo-activation-2.0 -I/usr/include/gail-1.0   -DDATADIR="\"/usr/local/share\"" -DGMDB_ICONDIR="\"/usr/local/share/gmdb/glade/\"" -DGMDB_GLADEDIR="\"/usr/local/share/gmdb/glade/\""    -O2 -g -pipe -Wformat -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -DHAVE_ICONV -DSQL -DHAVE_ICONV -DSQL -MT table_export.o -MD -MP -MF .deps/table_export.Tpo -c -o table_export.o table_export.c
table_export.c: In function ‘gmdb_table_export_button_cb’:
table_export.c:198: error: format not a string literal and no format arguments
table_export.c:204: error: format not a string literal and no format arguments
table_export.c:209: error: format not a string literal and no format arguments
table_export.c:214: error: format not a string literal and no format arguments
make[3]: *** [table_export.o] Error 1
make[3]: Залишаю каталог "/home/luckas/SVN/MDB/mdbtools.git/src/gmdb2"

list of errors lines:
[luckas@wind gmdb2]$ cat -n table_export.c|grep 198
   198                          if (i>0) fprintf(outfile,delimiter);
[luckas@wind gmdb2]$ cat -n table_export.c|grep 204
   204          if (need_headers) fprintf(outfile,lineterm);
[luckas@wind gmdb2]$ cat -n table_export.c|grep 209
   209                          if (i>0) fprintf(outfile,delimiter);
[luckas@wind gmdb2]$ cat -n table_export.c|grep 214
   214                  fprintf(outfile,lineterm);

Previous warnings also presented.

The odbc patch worked also without  CFLAGS="-Werror=format-security"
But with in - give error:
gcc -DHAVE_CONFIG_H -DLOCALEDIR=\"/usr/local/share/locale\" -I. -I../../include  -I../../include -I/usr/include/glib-2.0 -I/usr/lib/glib-2.0/include     -O2 -g -pipe -Wformat -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -DHAVE_ICONV -DSQL -DHAVE_ICONV -DSQL -DUNIXODBC -MT mdb-export.o -MD -MP -MF .deps/mdb-export.Tpo -c -o mdb-export.o mdb-export.c
mdb-export.c: In function ‘main’:
mdb-export.c:208: error: format not a string literal and no format arguments
make[2]: *** [mdb-export.o] Error 1

Others warnings add in the attched file...

> I don't have a git repository. Brian Burns is the author.
> The latest repository is located at https://github.com/brianb/mdbtools.git

Sorry :) - for Brian. 

> > But when I would build the RPM package for my distr (Mandriva 2010), the
> build CFLAGS of:
> > CFLAGS="-O2 -g -pipe -Wformat -Werror=format-security
> -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector
> --param=ssp-buffer-size=4 -DHAVE_ICONV -DSQL"
> > give the errors in sources:
> > main2.c: In function ‘gmdb_about_cb’:
> > main2.c:75: warning: passing argument 1 of ‘gtk_show_about_dialog’ from
> incompatible pointer type
> > /usr/include/gtk-2.0/gtk/gtkaboutdialog.h:67: note: expected ‘struct
> GtkWindow *’ but argument is of type ‘struct GtkWidget *’
> > main2.c: In function ‘gmdb_help_cb’:
> > main2.c:105: error: format not a string literal and no format arguments
> > make[3]: *** [main2.o] Error 1
> > 
> > This errors take the CFLAGS="-Werror=format-security", without this flags
> compiled as is.
> 
> I think it's not the proper way to fix that. It's a very interresting
> warning/error message my compiler doesn't show. A pity.
> 
> I think it's the same problem than using "printf(message)" when you have no
> idea whether the message might contain "%".
> 
> You should use puts(message) or printf("%s", message) in these cases.
> 
> > Also give errors in build mdbtools with unixODBC:
> > My configurations:
> > Mandriva 2010.0, x86_64, mdbtools-git-2011-02-28,
> lib64gtk+2.0_0-devel-2.18.9-1.2mdv2010.0.x86_64,
> lib64unixODBC-devel-2.2.14-8mdv2010.0.x86_64

> > Also I would like propouse a some thinks:
> > 1) need to fix maximum varchar (255) for mysql
> 
> I read limit is 65535 since mysql 5.0.3.
> http://dev.mysql.com/doc/refman/5.0/en/char.html

Oh, yes!  But I think it will be better for compatibillity (max 510 for MSAccess- max 255 utf8 for mysql), 
And somebody have old comp's whit old mysql.  

> > 2) need to fix the procedure of sanitize (-S) the dump of MDB files. You
> already changes dates to utf-8 codepage, and the more "dummy"
> > MS windows users and "admins" used the non ASCII symbols for names of tables
> and columns in the MDB-files, like as cyrilic :(
> > May be the procedure of sanitize can replace the symbols in names as
> transliterate (need check the match of names and length?)
> > Now, the replaced only spaces in names as "_", and .... the other
> non-english symbols taking "____________________________" as name of table.

> Sanitize is really buggy.
> I would recommand removing it from the code.
> All backends should now handle the non ascii characters in table/colum names
> correctly, quoting them.
> Do you really need -S?

I think- Yes, "-S" its good idea for package!!! (non-ASCII symbols in variables, names of tables and other is bad practise in developing.
  
> If using Jet3, don't forget to set MDB_JET3_CHARSET as described in mdb-schema
> manual and HACKING file.

I tested JET4 file with ciryllic symbols, spaces, " ' " and others :((( in names of tables, fields.

Good Luck, Dmitry

[mdb-dev] [patch] Re: More patches soon ...

[Nirgal V. <con...@ni...>]

On Friday 04 March 2011 18:29:22 Dmitry Nikitin wrote:
> Hi, Nirgal Vourgère!
> You job is greate. Thanks.
> I found your git-repository and compiled worked and new functionality mdbtools.

I don't have a git repository. Brian Burns is the author.
The latest repository is located at https://github.com/brianb/mdbtools.git
I occasionaly update some unoffical debian packages at http://www.nirgal.com/mdbtools/

> But when I would build the RPM package for my distr (Mandriva 2010), the build CFLAGS of:
> CFLAGS="-O2 -g -pipe -Wformat -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -DHAVE_ICONV -DSQL"
> give the errors in sources:
> 
> gcc -DHAVE_CONFIG_H -I. -I../../include  -I../../include -DORBIT2=1 -pthread -I/usr/include/gtk-2.0 -I/usr/lib64/gtk-2.0/include -I/usr/include/atk-1.0 -I/usr/include/cairo -I/usr/include/pango-1.0 -I/usr/include/glib-2.0 -I/usr/lib64/glib-2.0/include -I/usr/include/pixman-1 -I/usr/include/freetype2 -I/usr/include/libpng12 -I/usr/include/libglade-2.0 -I/usr/include/libxml2 -I/usr/include/libgnomeui-2.0 -I/usr/include/libart-2.0 -I/usr/include/gconf/2 -I/usr/include/gnome-keyring-1 -I/usr/include/libgnome-2.0 -I/usr/include/libbonoboui-2.0 -I/usr/include/libgnomecanvas-2.0 -I/usr/include/gnome-vfs-2.0 -I/usr/lib64/gnome-vfs-2.0/include -I/usr/include/orbit-2.0 -I/usr/include/dbus-1.0 -I/usr/lib64/dbus-1.0/include -I/usr/include/libbonobo-2.0 -I/usr/include/bonobo-activation-2.0 -I/usr/include/gail-1.0   -DDATADIR="\"/usr/local/share\"" -DGMDB_ICONDIR="\"/usr/local/share/gmdb/glade/\"" -DGMDB_GLADEDIR="\"/usr/local/share/gmdb/glade/\""    -O2 -g -pipe -Wformat -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -DHAVE_ICONV -DSQL -DHAVE_ICONV -DSQL -MT main2.o -MD -MP -MF .deps/main2.Tpo -c -o main2.o main2.c
> main2.c: In function ‘gmdb_about_cb’:
> main2.c:75: warning: passing argument 1 of ‘gtk_show_about_dialog’ from incompatible pointer type
> /usr/include/gtk-2.0/gtk/gtkaboutdialog.h:67: note: expected ‘struct GtkWindow *’ but argument is of type ‘struct GtkWidget *’
> main2.c: In function ‘gmdb_help_cb’:
> main2.c:105: error: format not a string literal and no format arguments
> make[3]: *** [main2.o] Error 1
> 
> This errors take the CFLAGS="-Werror=format-security", without this flags compiled as is.

I think it's not the proper way to fix that. It's a very interresting warning/error message my compiler doesn't show. A pity.

I think it's the same problem than using "printf(message)" when you have no idea whether the message might contain "%".

You should use puts(message) or printf("%s", message) in these cases.

Attached is a patch. Does it help?


> Also give errors in build mdbtools with unixODBC:
> Making all in odbc
> make[2]: Вхожу у каталог "/home/luckas/rpm/BUILD/mdbtools-0.6pre1.patched/src/odbc"
> /bin/sh ../../libtool --tag=CC   --mode=compile gcc -DHAVE_CONFIG_H -I. -I../../include  -I../../include -I/usr/include/glib-2.0 -I/usr/lib64/glib-2.0/include   -I/usr/include   -O2 -g -pipe -Wformat -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -DHAVE_ICONV -DSQL -DHAVE_ICONV -DSQL -DUNIXODBC -MT odbc.lo -MD -MP -MF .deps/odbc.Tpo -c -o odbc.lo odbc.c
> libtool: compile:  gcc -DHAVE_CONFIG_H -I. -I../../include -I../../include -I/usr/include/glib-2.0 -I/usr/lib64/glib-2.0/include -I/usr/include -O2 -g -pipe -Wformat -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -DHAVE_ICONV -DSQL -DHAVE_ICONV -DSQL -DUNIXODBC -MT odbc.lo -MD -MP -MF .deps/odbc.Tpo -c odbc.c  -fPIC -DPIC -o .libs/odbc.o
> odbc.c:223: error: expected declaration specifiers or ‘...’ before ‘SQLROWOFFSET’
> odbc.c:224: error: expected declaration specifiers or ‘...’ before ‘SQLROWSETSIZE’
> odbc.c:220: error: conflicting types for ‘SQLExtendedFetch’
> /usr/include/sqlext.h:1857: note: previous declaration of ‘SQLExtendedFetch’ was here
> odbc.c: In function ‘SQLExtendedFetch’:
> odbc.c:236: error: ‘pcrow’ undeclared (first use in this function)
> odbc.c:236: error: (Each undeclared identifier is reported only once
> odbc.c:236: error: for each function it appears in.)
> odbc.c: At top level:
> odbc.c:1485: error: expected declaration specifiers or ‘...’ before ‘SQLROWCOUNT’
> odbc.c:1482: error: conflicting types for ‘SQLSetStmtOption’
> /usr/include/sql.h:796: note: previous declaration of ‘SQLSetStmtOption’ was here
> make[2]: *** [odbc.lo] Error 1
> 
> My configurations:
> Mandriva 2010.0, x86_64, mdbtools-git-2011-02-28, lib64gtk+2.0_0-devel-2.18.9-1.2mdv2010.0.x86_64, lib64unixODBC-devel-2.2.14-8mdv2010.0.x86_64

Attached is another fix for that one.

> Also I would like propouse a some thinks:
> 1) need to fix maximum varchar (255) for mysql

I read limit is 65535 since mysql 5.0.3.
http://dev.mysql.com/doc/refman/5.0/en/char.html

> 2) need to fix the procedure of sanitize (-S) the dump of MDB files. You already changes dates to utf-8 codepage, and the more "dummy"
> MS windows users and "admins" used the non ASCII symbols for names of tables and columns in the MDB-files, like as cyrilic :(
> May be the procedure of sanitize can replace the symbols in names as transliterate (need check the match of names and length?)
> Now, the replaced only spaces in names as "_", and .... the other non-english symbols taking "____________________________" as name of table.

Sanitize is really buggy.
I would recommand removing it from the code.
All backends should now handle the non ascii characters in table/colum names correctly, quoting them.
Do you really need -S?
If using Jet3, don't forget to set MDB_JET3_CHARSET as described in mdb-schema manual and HACKING file.

[mdb-dev] [patch] Re: Proposed changes to HACKING doc

[Nirgal V. <con...@ni...>]

I tried to make a patch out of all that, since there is some really good stuff, and I believe it's easier for Brian to merge.

I discarded a few things, like the renaming of fields, because it would not match the source code anymore. It's complex enough reading it... HACKING does help.

On Monday 28 February 2011 08:25:17 dw wrote:
> 8) The definitions for index information seem to be wrong.
> (...)
> Lastly
> | ???? | 1 byte | primary_key | 0x01 if this index is primary |
> Should be
> | ???? | 1 byte | index_flags | see MDB_IDX_* flags |

I disagree with that one.
The flags are above, in the jet3 39 bytes index blocks, ot jet 4 52 bytes one.

> So, when walking the list of index definitions, you use num_idx to tell 
> you how many indices there are.  Then for each index, you use 
> index_Cols_index to index into the column definitions table to find the 
> columns for that index.  The reason num_idx and num_idx_col might be 
> different is not because some of the indices are "real" (whatever that 
> was supposed to mean), but simply because more than one index uses the 
> same column list.  I've seen this happen if there is one index that 
> includes nulls, and one that doesn't.

FYI
Type 2 extra "indexes" are the reverse of contraints, probably used to optimized cascade updates.
These ones don't have matching columns definitions blocks.

> I have a re-written mdb_read_indices that (I believe) correctly parses 
> this data if anyone is interested.

It wouldn't hurt to publish that to the list, would it?
I did that several times, and often had bad surprises, like when there are so many indexes that index definitions cover several DB pages...

[mdb-dev] (no subject)

[CCOR58 <cat...@gm...>]

A bit off topic, but can anyone point me to a decent tutorial type site 
for linux sound architecture/setup as well as one for
finding comparable coding for converting msaccess/mdb to OpenOffice Base 
(hsql).

I installed a streaming audio automation control package and sound quit. 
Setup to use alsa and jack. 'notifications' grab sound
and prevent jackd from starting and without jackd I am unable to run the 
streaming app or patch otrher media players to outputs.
OS is openSuSE 11.2 with 1.0 GB RAM and 120 GB HD on HP Pavilion a520n.

I want to be able to use Open Office Base to generate summary reports 
from the streaming app's log data, that I know I can do in access, hence 
the need for the qqq on mdb info.

Thanks, any tips or pointers to decent tutorial site would be 
appreciated. Most forums are debian/ubuntu based and don't really relate 
easily
to SuSE syntax or command ref sets.

Andy

[mdb-dev] More patches soon ...

[Dmitry N. <luc...@ma...>]

Hi, Nirgal Vourgère!

You job is greate. Thanks.
I found your git-repository and compiled worked and new functionality mdbtools.
But when I would build the RPM package for my distr (Mandriva 2010), the build CFLAGS of:
CFLAGS="-O2 -g -pipe -Wformat -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -DHAVE_ICONV -DSQL"
give the errors in sources:

gcc -DHAVE_CONFIG_H -I. -I../../include  -I../../include -DORBIT2=1 -pthread -I/usr/include/gtk-2.0 -I/usr/lib64/gtk-2.0/include -I/usr/include/atk-1.0 -I/usr/include/cairo -I/usr/include/pango-1.0 -I/usr/include/glib-2.0 -I/usr/lib64/glib-2.0/include -I/usr/include/pixman-1 -I/usr/include/freetype2 -I/usr/include/libpng12 -I/usr/include/libglade-2.0 -I/usr/include/libxml2 -I/usr/include/libgnomeui-2.0 -I/usr/include/libart-2.0 -I/usr/include/gconf/2 -I/usr/include/gnome-keyring-1 -I/usr/include/libgnome-2.0 -I/usr/include/libbonoboui-2.0 -I/usr/include/libgnomecanvas-2.0 -I/usr/include/gnome-vfs-2.0 -I/usr/lib64/gnome-vfs-2.0/include -I/usr/include/orbit-2.0 -I/usr/include/dbus-1.0 -I/usr/lib64/dbus-1.0/include -I/usr/include/libbonobo-2.0 -I/usr/include/bonobo-activation-2.0 -I/usr/include/gail-1.0   -DDATADIR="\"/usr/local/share\"" -DGMDB_ICONDIR="\"/usr/local/share/gmdb/glade/\"" -DGMDB_GLADEDIR="\"/usr/local/share/gmdb/glade/\""    -O2 -g -pipe -Wformat -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -DHAVE_ICONV -DSQL -DHAVE_ICONV -DSQL -MT main2.o -MD -MP -MF .deps/main2.Tpo -c -o main2.o main2.c
main2.c: In function ‘gmdb_about_cb’:
main2.c:75: warning: passing argument 1 of ‘gtk_show_about_dialog’ from incompatible pointer type
/usr/include/gtk-2.0/gtk/gtkaboutdialog.h:67: note: expected ‘struct GtkWindow *’ but argument is of type ‘struct GtkWidget *’
main2.c: In function ‘gmdb_help_cb’:
main2.c:105: error: format not a string literal and no format arguments
make[3]: *** [main2.o] Error 1

This errors take the CFLAGS="-Werror=format-security", without this flags compiled as is.

Also give errors in build mdbtools with unixODBC:
Making all in odbc
make[2]: Вхожу у каталог "/home/luckas/rpm/BUILD/mdbtools-0.6pre1.patched/src/odbc"
/bin/sh ../../libtool --tag=CC   --mode=compile gcc -DHAVE_CONFIG_H -I. -I../../include  -I../../include -I/usr/include/glib-2.0 -I/usr/lib64/glib-2.0/include   -I/usr/include   -O2 -g -pipe -Wformat -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -DHAVE_ICONV -DSQL -DHAVE_ICONV -DSQL -DUNIXODBC -MT odbc.lo -MD -MP -MF .deps/odbc.Tpo -c -o odbc.lo odbc.c
libtool: compile:  gcc -DHAVE_CONFIG_H -I. -I../../include -I../../include -I/usr/include/glib-2.0 -I/usr/lib64/glib-2.0/include -I/usr/include -O2 -g -pipe -Wformat -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -DHAVE_ICONV -DSQL -DHAVE_ICONV -DSQL -DUNIXODBC -MT odbc.lo -MD -MP -MF .deps/odbc.Tpo -c odbc.c  -fPIC -DPIC -o .libs/odbc.o
odbc.c:223: error: expected declaration specifiers or ‘...’ before ‘SQLROWOFFSET’
odbc.c:224: error: expected declaration specifiers or ‘...’ before ‘SQLROWSETSIZE’
odbc.c:220: error: conflicting types for ‘SQLExtendedFetch’
/usr/include/sqlext.h:1857: note: previous declaration of ‘SQLExtendedFetch’ was here
odbc.c: In function ‘SQLExtendedFetch’:
odbc.c:236: error: ‘pcrow’ undeclared (first use in this function)
odbc.c:236: error: (Each undeclared identifier is reported only once
odbc.c:236: error: for each function it appears in.)
odbc.c: At top level:
odbc.c:1485: error: expected declaration specifiers or ‘...’ before ‘SQLROWCOUNT’
odbc.c:1482: error: conflicting types for ‘SQLSetStmtOption’
/usr/include/sql.h:796: note: previous declaration of ‘SQLSetStmtOption’ was here
make[2]: *** [odbc.lo] Error 1

My configurations:
Mandriva 2010.0, x86_64, mdbtools-git-2011-02-28, lib64gtk+2.0_0-devel-2.18.9-1.2mdv2010.0.x86_64, lib64unixODBC-devel-2.2.14-8mdv2010.0.x86_64

Also I would like propouse a some thinks:
1) need to fix maximum varchar (255) for mysql
2) need to fix the procedure of sanitize (-S) the dump of MDB files. You already changes dates to utf-8 codepage, and the more "dummy"
MS windows users and "admins" used the non ASCII symbols for names of tables and columns in the MDB-files, like as cyrilic :(
May be the procedure of sanitize can replace the symbols in names as transliterate (need check the match of names and length?)
Now, the replaced only spaces in names as "_", and .... the other non-english symbols taking "____________________________" as name of table.

Good Luck, Dmitry

[mdb-dev] [patch] default boolean values on export

[Nirgal V. <con...@ni...>]

There is a bug in the detection of default value property for booleans. The test whether a default value was already writen is wrong: there might be column properties, but no default value inside. Attached is a fix.
Cheers

Re: [mdb-dev] Proposed changes to HACKING doc

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

On 28.02.2011, at 09:25, dw wrote:

> 3) Per my experimentation, Access 2007 apparently uses Jet v5.  So now the known values for the byte at Offset 0x14 of the Database     Definition Page are:
> 
> Jet 3: 0
> Jet 4: 1
> Jet 5: 2
> 
> Has anyone tried Access 2010?

Access 2010 has a 3 at that byte, and a 1 at the next byte.
I don't know what the 1 means, though. All older file formats have a 0 at that location.

This is relevant because the version is read in like this in file.c (probably wrong):
mdb->f->jet_version = mdb_get_int32(mdb->pg_buf, 0x14);

[mdb-dev] Proposed changes to HACKING doc

[dw <lim...@ya...>]

I have been collecting changes/corrections to the HACKING document.  
Some of these tidbits are apparently already known (since the code uses 
them), but they haven't found their way into the doc.  Some appear to be 
new discoveries I have made.  And some appear to be just plain incorrect 
info in the HACKING doc.  Perhaps the most interesting item is #8.

However, I am very new to MDBTools.  I would appreciate it if some of 
you people who have been digging into these files for years could 
review/critique these observations.  While it makes sense to keep the 
doc as up-to-date as possible, we don't want to get it wrong.

Please confirm/deny any of these 9 assertions that you can.

Thanks.

dw

==============
1) From file.c we learn that:

     /* get the db password located at 0x42 bytes into the file */

So apparently on the Database Definition Page, the 14 bytes starting at 
0x42 are the (encrypted) database password.

==============
2) Also from file.c:

     mdb->f->db_key = mdb_get_int32(mdb->pg_buf, 0x3e);

Apparently the 4 bytes at 0x3e on the Database Definition Page are the 
database key.

==============
3) Per my experimentation, Access 2007 apparently uses Jet v5.  So now 
the known values for the byte at Offset 0x14 of the Database Definition 
Page are:

Jet 3: 0
Jet 4: 1
Jet 5: 2

Has anyone tried Access 2010?

==============
4) From table.c we see that:

         pcol->is_long_auto = col[fmt->col_flags_offset]&  0x04 ? 1 : 0;
	pcol->is_uuid_auto = col[fmt->col_flags_offset]&  0x40 ? 1 : 0;

My own observations suggest bit 2 indicates whether the column can be null.

Thus, the known bits of the column bitmask are:

0x01: variable length column
0x02: can be null
0x04: is auto long
0x40: is auto guid

So, this line from HACKING is insufficient:

| ???? | 1 byte  | bitmask     | low order bit indicates variable columns |

==============
5) Examining the raw data for columns in a v4 database, it looks like 
the first two bytes after the (second) col_num are an LCID.  That would 
explain why there are commonly 0x409 (meaning English).

==============
6) Looking at one of the Windows headers (dbdaoint.h 
<http://www.koders.com/c/fidE758EB80731C033AA2D2015B9A0EAF6F1C2C1ECE.aspx?s=matrix>), 
we see the DataTypeEnum enum has a remarkable correlation with the 
values for column data types.  That suggests that the other values in 
the enum may also apply:

dbdaoint.h:

	dbBoolean	= 1,
	dbByte	= 2,
	dbInteger	= 3,
	dbLong	= 4,
	dbCurrency	= 5,
	dbSingle	= 6,
	dbDouble	= 7,
	dbDate	= 8,
	dbBinary	= 9,
	dbText	= 10,
	dbLongBinary	= 11,
	dbMemo	= 12,
	dbGUID	= 15,
	dbBigInt    = 16,
	dbVarBinary    = 17,
	dbChar    = 18,
	dbNumeric    = 19,
	dbDecimal    = 20,
	dbFloat    = 21,
	dbTime    = 22,
	dbTimeStamp    = 23

==============
7) I believe I have more info about the data at the end of a tabledef:

| ???? | n bytes | ???         |                                          |
| 0xFF | 2 bytes | ???         | End of the tableDef  ?                   |

It appears the struct for this data actually looks like this:

#pragma pack(1)
typedef struct {
     short col_num;
     int arg1;
     int arg2;
} TDef_Unknown;

The first two bytes are either a column number, or 0xffff to indicate 
the end of the list.  Of the > 80 tables I have looked at, only columns 
of type OLE and Memo have entries in this table.  In fact, only OLE and 
Memo fields with a length of 0xffff.  Contra-wise, while not all OLE and 
Memo fields have entries in this list, all the OLE and Memo fields with 
a length of 0xffff have entries in this list.

I haven't yet determined the meaning of arg1 and arg2.  However, I do 
believe my guess about them being ints is correct.

I can tell you that of the 192 TDef_Unknowns that I have found:

- In all 192 cases, arg2 = arg1 + 1
- arg1 and arg2 are all unique values.  ie None of the other 
TDef_Unknown entries anywhere in the db use the same values.
- I don't believe arg1 and arg2 are page numbers (too big), or point to 
primary keys in some database table (can't find any correlation).

Here's some entries to show what I mean:

col_num arg1 arg2 coltype colsize

2 00005c02 00005c03 12 65535
3 00005c04 00005c05 12 65535
4 00005c06 00005c07 12 65535
5 00005c08 00005c09 12 65535
6 00005c0a 00005c0b 12 65535
7 00005c0c 00005c0d 12 65535
8 00005c0e 00005c0f 12 65535
9 00005c10 00005c11 12 65535
10 00005c12 00005c13 12 65535
11 00005c14 00005c15 12 65535
12 00005c16 00005c17 12 65535
14 00005c18 00005c19 12 65535
15 00005c1a 00005c1b 12 65535
16 00005c1c 00005c1d 12 65535
17 00005c1e 00005c1f 12 65535
18 00005c20 00005c21 12 65535
19 00005c22 00005c23 12 65535
10 00006a02 00006a03 12 65535
11 00008402 00008403 12 65535
12 00008404 00008405 12 65535
13 00008406 00008407 12 65535
14 00008408 00008409 12 65535

These records are all from a single table that contains many Memo 
fields.  Notice that while the numbers always increase, they don't 
always do so sequentially.

So, I don't believe arg1 and arg2 are any type of datatype or flag 
fields (doesn't make sense with incrementing fields).  Nor do I think 
they have anything to do with the # of records in a table (I have large 
numbers in TDEF_Unknown for tables with 0 records).

So far, I'm stumped.  But I feel certain enough about the fact they 
these are records, the record lengths, the list terminator and the 
contents of the first bytes to suggest putting them in the doc.

==============
8) The definitions for index information seem to be wrong.

The problem starts with this area of HACKING:

| ???? | 4 bytes | num_idx     | Number of indexes in table               |
| ???? | 4 bytes | num_real_idx| Number of indexes in table (repeat)      |

Which should be:

| ???? | 4 bytes | num_idx     | Number of indexes in table               |
| ???? | 4 bytes | num_idx_col | Number of index column entries           |

Also this:

| ???? | 4 bytes | index_num2  | Number of the index (repeat)             |

should be this

| ???? | 4 bytes | index_cols_index  | Index into index cols list         |

Lastly

| ???? | 1 byte | primary_key | 0x01 if this index is primary |

Should be

| ???? | 1 byte | index_flags | see MDB_IDX_* flags |

Since it can contain any of these flags (from mdbtools.h):

	MDB_IDX_UNIQUE = 0x01,
	MDB_IDX_IGNORENULLS = 0x02,
	MDB_IDX_REQUIRED = 0x08

So, when walking the list of index definitions, you use num_idx to tell 
you how many indices there are.  Then for each index, you use 
index_Cols_index to index into the column definitions table to find the 
columns for that index.  The reason num_idx and num_idx_col might be 
different is not because some of the indices are "real" (whatever that 
was supposed to mean), but simply because more than one index uses the 
same column list.  I've seen this happen if there is one index that 
includes nulls, and one that doesn't.

I have a re-written mdb_read_indices that (I believe) correctly parses 
this data if anyone is interested.

==============
9) The Memo Field Definition is incorrect.  This:

| ???? | 3 bytes | memo_len    | Total length of the memo                 |
| ???? | 1 bytes | bitmask     | See values                               |

should be:

| ???? | 4 bytes | memo_len    | Total length of the memo w/flags    |

Where the top 2 bits are the flags described.  The bitmask *can't* be an 
entire byte long.  OLE fields can hold up to 1gig.  That requires at 
least 30 bits, leaving only 2 bits for flags.  In fact, this quote 
<http://office.microsoft.com/en-us/access-help/access-2007-specifications-HA010030739.aspx> 
from makes me wonder if sometimes 0xC0000000 is ignored?

     Number of characters in a Memo field: 65,535 when entering data through the user interface; 2 gigabytes of character storage when entering data programmatically

Cuz 2gigs is going to take 31 bits

[mdb-dev] Replacement for mdb_ole_read_full

[dw <lim...@ya...>]

So, my first proposed modification is to replace the mdb_ole_read_full 
routine in data.c.  While the current routine does work, it has a number 
of inefficiencies:

- Performs unnecessary memory allocation/memcpy of the Memo Field Definition
- Performs (repeated) unnecessary memcpys
- Performs (repeated) unnecessary reallocs (which also do memcpys)
- Allocates more memory than is needed to hold the data
- Uses an inconsistent memory allocator (malloc vs g_malloc)
- Leaves the col->bind_ptr in an unusable state

My replacement (below) does:

- Zero memcpys
- Zero reallocs
- A single, correctly-sized allocation using g_malloc
- Preserves the Memo Field Definition in col->bind_ptr
- Fully commented

My next proposed modification is more substantive, but this seemed like 
a good place to start.

dw

/*
  * mdb_ole_read_full - reads the entire OLE field
  *
  * mdb - the database pointer
  * col - the column to read
  * size - outputs the size of the buffer returned (may be NULL)
  *
  * returns - the result in a big buffer.  The number of data bytes is
  * returned in the size parameter.  The returned buffer must freed
  * using g_free().
  *
  * On return, col->bind_ptr still points to the 12 byte Memo Field
  * Definition, NOT the data.  This means the OLE field can be
  * re-read if necessary.
  *
  */
void*
mdb_ole_read_full(MdbHandle *mdb, MdbColumn *col, size_t *size)
{
	void *pOldBind;
	unsigned char *result;
	size_t pos, iTotSize;

	// What's the total length of the field?  Drop off flags
	iTotSize = mdb_get_int32(col->bind_ptr, 0)&  0x3fffffff;

	// Allocate room for the entire field.
	result = (unsigned char *)g_malloc(iTotSize);

	// Save the old pointer which points to the 12 byte Memo Field Definition
	pOldBind = col->bind_ptr;

	// mdb_ole_read&  mdb_ole_read_next always write to col->bind_ptr.
	// So we adjust it to point to our full sized buffer
	col->bind_ptr = result;

	// Reads at most 1 data page
	pos = mdb_ole_read(mdb, col, pOldBind, iTotSize);

	// Is there more to read?
	while (pos<  iTotSize)
	{
		// Adjust col->bind_ptr so the next call will write to
		// the appropriate offset in the buffer
		col->bind_ptr = result + pos;

		// Read the next chunk (at most 1 data page).  Passing NULL
		// for the 3rd parameter avoids redundant error checking
		pos += mdb_ole_read_next(mdb, col, NULL);
	}

	// assert pos == iTotSize

	if (size)
		*size = pos;

	// restore the 12 byte Memo Field Definition
	col->bind_ptr = pOldBind;

	return result;
}

[mdb-dev] How do I submit corrections?

[dw <lim...@ya...>]

I have been working with MDBTools for a couple of weeks now.  First of 
all, I want to say thank you to all the people who helped create this 
library.  It has helped me solve a problem I didn't think I was going to 
be able to solve.

That said, there are some imperfections in the library.  Some of the 
things I've seen are simply inefficient.  Some of the things I need are 
just different from what other people have needed.  And some of the 
things I have found (I believe) are just wrong.

I'm not sure what the process is to submit improvements.  I doubt that 
anyone wants me to just say "please fix the so-and-so routine."  That 
seems inefficient, especially when I have already changed the routine 
myself.  Do I just post the actual code corrections as messages to this 
list?

dw