[q-lang-users] ODBC connection with MS Access

[<jir...@bl...>]

Dear Albert,

I wrote a simple script for loading data from text files into the MS 
Access database. Running the script I am experiencing quite serious 
problems with inserting data into the database. Trying the following 
testing script nothing is inserted into the database:



import odbc;

def DSN = "DSN=EUROSTAT";

def DB = odbc_connect DSN;

def CreateSQL = "create table data_nrg_101a (unit varchar(50) Not Null, 
indic_en varchar(50) Not Null, product varchar(50) Not Null, " ++
                 "geo varchar(50) Not Null, period varchar(50) Not Null, 
val double, flag varchar(2), " ++
									    // for PostgreSQL should be double precision instead
                 "Constraint data_nrg_101a_pkey Primary Key (unit, 
indic_en, product, geo, period));";

def Data =	[("1000t","100100","2000","eu27","2005a00",636987.0,()),
		("1000t","100100","2000","eu27","2004a00",651506.0,()),
		("1000t","100100","2000","eu27","2003a00",665292.0,()),
		("1000t","100100","2000","eu27","2002a00",665146.0,()),
		("1000t","100100","2000","eu27","2001a00",664399.0,()),
		("1000t","100100","2000","eu27","2000a00",655745.0,()),
		("1000t","100100","2000","eu27","1999a00",661603.0,()),
		("1000t","100100","2000","eu27","1998a00",692062.0,()),
		("1000t","100100","2000","eu27","1997a00",761337.0,()),
		("1000t","100100","2000","eu27","1996a00",791975.0,()),
		("1000t","100100","2000","eu27","1995a00",800038.0,()),
		("1000t","100100","2000","eu27","1994a00",810385.0,()),
		("1000t","100100","2000","eu27","1993a00",854335.0,()),
		("1000t","100100","2000","eu27","1992a00",913403.0,()),
		("1000t","100100","2000","eu27","1991a00",974658.0,()),
		("1000t","100100","2000","eu27","1990a00",1084166.0,())];

createTbl = sql DB CreateSQL ();

insertData = do (sql DB "insert into data_nrg_101a values 
(?,?,?,?,?,?,?);") Data;

//run this
test = createTbl || insertData;


The called sql_exec procedure is not further evaluated.

 From about 2.5 millions of similar records only some 5000 records were 
inserted into the table (always the same records when trying more 
times). I tried also longer records (the time series in one row) and no 
record was inserted. When I hardcoded the insert statements instead of 
parametrized query, the ODBC driver complained about missing parameters 
even there were no questionmarks and NULLs in the text fields were 
transformed into "".

 From curiosity I tried precisely the same scripts against the 
PostgreSQL database and all imports went OK without any errors - both 
short and long records. So it is obvious that this issue is specific to 
the MS Access ODBC driver implementation.

Any hints?

Many thanks.

Jiri Spitz