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

[Tim H. <q...@st...>]

Jiøí Spitz <jir...@bl...> writes:

[snip]
>
> def Data =	[("1000t","100100","2000","eu27","2005a00",636987.0,()),
> 		("1000t","100100","2000","eu27","2004a00",651506.0,()),
[snip]
> 		("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 "".

Interesting. Let's start from Q: you have to be sure that the statements
being executed are of the form

         sql "insert.... values (?, ..)" (parameters,...) ;

in all cases. It seems that the `do' construct and data segment above does
give the right number of parameters per invocation as a tuple, so this
shouldn't be the problem.

(Aside: you can do 
        filter (<>7) $ map (#) Data
to check for rows with non-7 number of fields.)

> 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.

Several angles of attack: 

a) Can you establish a pattern of rows that works or not?

b) could be locking with Access - is this thing shared / multi-user?

c) could it be those () things in the last element of the tuples above? How
do they translate into in SQL in both drivers? (Cf point A above)

d) How about batching them into transaction chunks, or even make the whole
thing one transaction if nobody else is likely to use it a while?

e) how about only creating the indexes and/or primary keys after bulk
inserts, since regenerating those per row will slow things down hugely?

f) are you not getting errors to console while this happens?

g) ODBC driver-manager debug trace log (Control panel / ODBC / Tracing, if
you're stuck with Windoze). Actually this should be the first port of call
as it will answer all points above. Search for failures (SQL_ERROR) and
check subsequent SQLError() info.

The debug trace will also show us what's going off behind the scenes with
that NULL->"" business. The obvious concern is that you're not trying to
shove a NULL into a not-null column. The next consideration is what
data-types Q is presenting for these fields when it binds the parmeters
(probably via SQLBindParameter()).

HTH,

~Tim
-- 
<http://spodzone.org.uk/>