Re: [q-lang-users] ODBC connection with MS Access
Brought to you by:
agraef
Menu
▾
▴
Re: [q-lang-users] ODBC connection with MS Access
|
From: <jir...@bl...> - 2008-01-24 20:02:14
|
Tim Haynes wrote:
> 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.
I am sure this was correct.
> Several angles of attack:
>
> a) Can you establish a pattern of rows that works or not?
Using your hint g) it was very easy. The problem is caused really by
those NULLs.
>
> b) could be locking with Access - is this thing shared / multi-user?
Not shared, not opened even in Access in parallel.
> 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)
Yes :-)
> 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?
It seems it does not have sense to try now.
>
> e) how about only creating the indexes and/or primary keys after bulk
> inserts, since regenerating those per row will slow things down hugely?
I actually do. My task has several similar tables and several
dictionaries bound with data tables. I create both primary and foreign
keys after importing the data.
> f) are you not getting errors to console while this happens?
No error messages, simply SQL_EXEC is returned unevaluated.
> 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.
I did and here is the interesting part of the log:
Q2.tmp ODBC_MS_ e24-e28 ENTER SQLBindParameter
HSTMT 00E22240
UWORD 7
SWORD 1 <SQL_PARAM_INPUT>
SWORD 99 <SQL_C_DEFAULT>
SWORD 1 <SQL_CHAR>
SQLULEN 0
SWORD 0
PTR 0x00000000
SQLLEN 0
SQLLEN * 0x003EE28C
Q2.tmp ODBC_MS_ e24-e28 EXIT SQLBindParameter with return code -1
(SQL_ERROR)
HSTMT 00E22240
UWORD 7
SWORD 1 <SQL_PARAM_INPUT>
SWORD 99 <SQL_C_DEFAULT>
SWORD 1 <SQL_CHAR>
SQLULEN 0
SWORD 0
PTR 0x00000000
SQLLEN 0
SQLLEN * 0x003EE28C
DIAG [HY104] [Microsoft][Ovladač ODBC pro Microsoft Access]Neplatná
hodnota přesnosti. (98)
// means "Invalid value of precision"
Q2.tmp ODBC_MS_ e24-e28 ENTER SQLFreeStmt
HSTMT 00E22240
UWORD 3 <SQL_RESET_PARAMS>
Q2.tmp ODBC_MS_ e24-e28 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 00E22240
UWORD 3 <SQL_RESET_PARAMS>
Q2.tmp ODBC_MS_ e24-e28 ENTER SQLFreeStmt
HSTMT 00E22240
UWORD 0 <SQL_CLOSE>
Q2.tmp ODBC_MS_ e24-e28 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 00E22240
UWORD 0 <SQL_CLOSE>
Q2.tmp ODBC_MS_ e24-e28 ENTER SQLGetDiagRecW
SQLSMALLINT 3
SQLHANDLE 00E22240
SQLSMALLINT 1
SQLWCHAR * 0x00229C54 (NYI)
SQLINTEGER * 0x00229DC8
SQLWCHAR * 0x00E21938 (NYI)
SQLSMALLINT 300
SQLSMALLINT * 0x00229DB8
Q2.tmp ODBC_MS_ e24-e28 EXIT SQLGetDiagRecW with return code 100
(SQL_NO_DATA_FOUND)
SQLSMALLINT 3
SQLHANDLE 00E22240
SQLSMALLINT 1
SQLWCHAR * 0x00229C54 (NYI)
SQLINTEGER * 0x00229DC8
SQLWCHAR * 0x00E21938 (NYI)
SQLSMALLINT 300
SQLSMALLINT * 0x00229DB8
Q2.tmp ODBC_MS_ e24-e28 ENTER SQLGetDiagRecW
SQLSMALLINT 2
SQLHANDLE 00E21830
SQLSMALLINT 1
SQLWCHAR * 0x00229C54 (NYI)
SQLINTEGER * 0x00229DC8
SQLWCHAR * 0x00E21938 (NYI)
SQLSMALLINT 300
SQLSMALLINT * 0x00229DB8
Q2.tmp ODBC_MS_ e24-e28 EXIT SQLGetDiagRecW with return code 100
(SQL_NO_DATA_FOUND)
SQLSMALLINT 2
SQLHANDLE 00E21830
SQLSMALLINT 1
SQLWCHAR * 0x00229C54 (NYI)
SQLINTEGER * 0x00229DC8
SQLWCHAR * 0x00E21938 (NYI)
SQLSMALLINT 300
SQLSMALLINT * 0x00229DB8
There are three errors per insert but none of them is reported back into
the Q interpreter.
I add some conversation with the Q interpreter:
==> sql DB "insert into data_nrg_101a values ('a', 'a', 'a', 'a', 'a',
NULL, NULL)" ()
1
This works as expected.
==> sql DB "insert into data_nrg_101a values ('b', 'b', 'b', 'b', 'b',
?, ?)" ((), ())
sql_exec <<ODBCHandle>> "insert into data_nrg_101a values ('b', 'b',
'b', 'b', 'b', ?, ?)" ((),())
This does not work, no error message.
==> sql DB "insert into data_nrg_101a values ('b', 'b', 'b', 'b', 'b',
123, '')" ()
1
Empty strings in SQL statement are accepted as expected.
==> sql DB "insert into data_nrg_101a values ('c', 'c', 'c', 'c', 'c',
123, ?)" ("")
sql_exec <<ODBCHandle>> "insert into data_nrg_101a values ('c', 'c',
'c', 'c', 'c', 123, ?)" ""
Empty strings in parameters are not accepted, no error message.
==> sql DB "select * from data_nrg_101a where isnull(flag)" ()
[("unit","indic_en","product","geo","period","val","flag"),("1000t","100100","2000","eu27","2003a00",665292.0,()),("a","b","c","d","e",(),()),("a","a","a","a","a",(),())]
IsNull() in SQL statement works as expected, NULLs are returned as ().
==> sql DB "select * from data_nrg_101a where flag = ?" (())
odbc_error "[Microsoft][Ovladač ODBC pro Microsoft Access]Nesprávná
hodnota pole COUNT. " "07002"
Selecting by NULL in parameter does not work. The error message means
"Invalid value of the field COUNT."
(By the way inserting duplicate record doesn´t produce the error message.)
From the shown above I see:
- () is not properly converted into Access' NULL representation
- error messages from the ODBC driver are not sometimes propagated back
to the Q interpreter.
For now I know what happens and how to workaround. I am not experienced
in low level programming but I'll see whether I can help to correct this
in the interface.
Many thanks for your hint.
Jiri
|
