mdb-queries seems not to export table append queries ("INSERT INTO ... SELECT ..." )

[thr27]

It seems mdb-queries does not export the full sql statement if the query is used to insert data into a table

INSERT INTO new-table
SELECT column FROM old-table

Only the SELECT part is exported.

Is there an option to also export the INSERT INTO ... part ?

[rhurlin]

Hi @thr27,
Do you habe an example database and how exactly do you run your command? (mdb-queries ...)

[thr27]

Hi @rhurlin

Attached is a small sample database.

There are the table source and dest and a query (Query) which selects data from source and inserts it into dest

INSERT INTO dest
SELECT col1 AS col1
FROM source;

If I run mdb-queries, the output is only the SELECT of the query:

/mnt/data # mdb-queries Sample.accdb Query
SELECT col1 FROM [source]

Sample.zip

[rhurlin]

Hi @thr27,
Sorry for the late answer, we are very busy at work. And thanks for the data and description.

Your database Sample.accdb seems to be of version ACE12 (via mdb-ver Sample.accdb). Don't know, if this is fully supported by mdbtools v1.0?

I think you are right and it seems that the code in mdb-queries.c can only see some parts of the query. It does not recognize AS col1 within SELECT nor the previous line with INSERT INTO dest. The output we see is from mdb-queries.c:226:

fprintf(stdout,"SELECT%s %s FROM %s %s\n",sql_predicate,sql_columns,sql_tables,sql_sorting);

I have the impression that the code in mdb-queries.c lines 156-162 does not include the entire structure of this part of the Sample.accdb file. If I look in the database file with a hex editor, there is an entry 'LvExtra' between entries 'Flag' and 'Name1', starting at hex address 414B, which is not taken into account. I cannot judge whether this is relevant, but perhaps the entire parsing process is inadequate?

As I am neither a C/C++ coder nor have any knowledge of Access database structures, I am unfortunately unable to help at this point, sorry.