q-lang-users Mailing List for Q - Equational Programming Language (Page 8)
Brought to you by:
agraef
Menu
▾
▴
q-lang-users — general discussion, questions, suggestions, etc.
You can subscribe to this list here.
| 2003 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
(1) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2004 |
Jan
(3) |
Feb
(27) |
Mar
|
Apr
(4) |
May
(11) |
Jun
(5) |
Jul
(5) |
Aug
(6) |
Sep
(15) |
Oct
(28) |
Nov
(8) |
Dec
|
| 2005 |
Jan
(9) |
Feb
(5) |
Mar
(10) |
Apr
(43) |
May
(8) |
Jun
(31) |
Jul
(45) |
Aug
(17) |
Sep
(8) |
Oct
(30) |
Nov
(2) |
Dec
(6) |
| 2006 |
Jan
(4) |
Feb
(20) |
Mar
(1) |
Apr
|
May
(92) |
Jun
(179) |
Jul
(26) |
Aug
(65) |
Sep
(36) |
Oct
(38) |
Nov
(44) |
Dec
(68) |
| 2007 |
Jan
(11) |
Feb
(25) |
Mar
(37) |
Apr
(7) |
May
(83) |
Jun
(77) |
Jul
(44) |
Aug
(4) |
Sep
(28) |
Oct
(53) |
Nov
(12) |
Dec
(21) |
| 2008 |
Jan
(66) |
Feb
(45) |
Mar
(30) |
Apr
(50) |
May
(9) |
Jun
(18) |
Jul
(11) |
Aug
(6) |
Sep
(4) |
Oct
|
Nov
|
Dec
|
| 2009 |
Jan
|
Feb
|
Mar
|
Apr
(3) |
May
(2) |
Jun
|
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
| 2010 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
(2) |
Oct
|
Nov
|
Dec
|
|
From: Albert G. <Dr....@t-...> - 2008-02-11 23:58:19
|
Albert Graef wrote:
> Tim Haynes wrote:
>>> ==> 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.
>
> The strange thing is that it doesn't even give back an error there, as
> the relevant code at line 622 in modules/odbc/odbc.c does check for an
> error condition in the return code of SQLBindParameter().
Yes, this is indeed a bug in odbc.c (the SQL statement is freed before
the error code is retrieved). I've fixed this now, so that the proper
error code HY104 is reported. Of course this doesn't solve the original
problem, but at least sql_exec returns a proper odbc_error term now.
--
Dr. Albert Gr"af
Dept. of Music-Informatics, University of Mainz, Germany
Email: Dr....@t-..., ag...@mu...
WWW: http://www.musikinformatik.uni-mainz.de/ag
|
|
From: Albert G. <Dr....@t-...> - 2008-02-07 17:08:55
|
Tim Haynes wrote: > Ah. Is there anything useful I can be doing with debugging that further? Thanks for the offer, but I'm afraid that it'll first take a fairly thorough source review to port Q to 64 bit systems. I have set aside a few weeks starting next month to work on this. Once this is finished, it will surely take a while to fully debug the 64 bit version, any help with that will be appreciated, of course. :) Albert -- Dr. Albert Gr"af Dept. of Music-Informatics, University of Mainz, Germany Email: Dr....@t-..., ag...@mu... WWW: http://www.musikinformatik.uni-mainz.de/ag |
|
From: Tim H. <q...@st...> - 2008-02-07 14:18:14
|
Albert Graef <Dr....@t-...> writes: > Tim Haynes wrote: >> Being slightly crazy, and not having tried this for some time, I thought >> I'd have a shot at building straight from cvs HEAD on amd64 (ubuntu gutsy). > > Q doesn't work on 64 bit yet. This is going to be fixed Real Soon Now. Ah. Is there anything useful I can be doing with debugging that further? ~Tim -- <http://spodzone.org.uk/> |
|
From: Albert G. <Dr....@t-...> - 2008-02-07 13:50:43
|
Tim Haynes wrote: > Being slightly crazy, and not having tried this for some time, I thought > I'd have a shot at building straight from cvs HEAD on amd64 (ubuntu gutsy). Q doesn't work on 64 bit yet. This is going to be fixed Real Soon Now. -- Dr. Albert Gr"af Dept. of Music-Informatics, University of Mainz, Germany Email: Dr....@t-..., ag...@mu... WWW: http://www.musikinformatik.uni-mainz.de/ag |
|
From: Tim H. <q...@st...> - 2008-02-07 12:11:51
|
Hi folks, Being slightly crazy, and not having tried this for some time, I thought I'd have a shot at building straight from cvs HEAD on amd64 (ubuntu gutsy). Well, it now builds, but see the warning and errors on startup: | zsh, sauce 12:06PM piglet/ % q -d | def: error loading module | Warning: 141 unresolved external symbols | ____ | / __ \ Q interpreter version 7.11 (x86_64-unknown-linux-gnu) | / /_/ / Copyright (c) 1991-2008 by Albert Graef | \___\_\ http://q-lang.sourceforge.net | | This software is distributed under the terms of the GNU General Public | License version 2 or later; type `copying' for details. | ! File def, line 77: Value mismatch in definition | -- def SIG_IGN = -1 | -- def SIG_DFL = 0 | -- def SIG_TRP = 1 | -- def SCHED_OTHER = 0 | -- def SCHED_RR = 1 | -- def SCHED_FIFO = 2 | -- def clib::MAIN_THREAD = this_thread | ** 1e+307*1e+307 ==> inf | -- def inf = inf | ** inf-inf ==> nan | -- def nan = nan | -- def i = complex::complex 0 1 | | ==> This is my normal build routine: | #!/bin/zsh | export CFLAGS="-Os -fomit-frame-pointer -I/usr/local/stow/macports/include | -I/usr/local/iODBC/include -I$PWD/readline-4.2/" | | export LDFLAGS="-L/usr/lib -L/usr/local/stow/macports/lib | -L/usr/local/iODBC/lib -L$PWD/readline-4.2/ -L$PWD/readline-4.2/lib" | | [ -x ./configure ] || ./autogen.sh | | ./configure --prefix=/usr/local/stow/q \ | --with-{x,gmp,rl,pthread,curl,gdbm,magick,odbc,xml} --without-octave && | nice make and I installed it into /usr/local/stow/q and stow-ed q into /usr/local/. Hmmm. Any ideas? Cheers, ~Tim -- <http://spodzone.org.uk/> |
|
From: Albert G. <Dr....@t-...> - 2008-01-26 20:18:46
|
I've just released qcalc 1.2. There are no functional changes, I just added the missing examples/world.dat file, fixed the version number in the about dialog, and updated the included Q manual for the forthcoming 7.11 release. Download it here: http://sourceforge.net/project/showfiles.php?group_id=96881&package_id=251927 Albert -- Dr. Albert Gr"af Dept. of Music-Informatics, University of Mainz, Germany Email: Dr....@t-..., ag...@mu... WWW: http://www.musikinformatik.uni-mainz.de/ag |
|
From: Albert G. <Dr....@t-...> - 2008-01-25 08:42:04
|
Jiří Spitz wrote: > It should return an error condition as seen from the ODBC log: Yeah, but as I said sql_exec does check for errors from SQLBindParameter(), so I'm not sure what's happening there. > A zip file is enclosed. The procedure is described in the script. Thanks. I can't say when I get to this, as I'm currently busy with other things. But I'll have a look asap. Thanks for the report. Albert -- Dr. Albert Gr"af Dept. of Music-Informatics, University of Mainz, Germany Email: Dr....@t-..., ag...@mu... WWW: http://www.musikinformatik.uni-mainz.de/ag |
|
From: <jir...@bl...> - 2008-01-24 23:40:06
|
Albert Graef napsal(a):
...
...
...
> AFAICT, there's nothing wrong in that code. Could it be that it's a
> binary incompatibility (i.e., maybe you're running a different ODBC
> version with a different ABI than that what I compiled the ODBC module
> against)? What does odbc_info DB say on your system?
==> odbc_info DB
("EUROSTAT","D:\\working\\EB\\tools_pro_EUROSTAT\\DB_EUROSTAT","ACCESS","04.00.0000","odbcjt32.dll","04.00.6304","03.51","03.52.0000")
...
...
...
> All operations, including sql_exec, check the return value of each and
> every ODBC call where an error can arise, and, if that return value
> indicates an error, the operation will bail out and return the error in
> the form 'odbc_error MSG STATE' where MSG is the error message and state
> is the SQL_ERROR state. At least that's how it's supposed to be, if that
> doesn't happen then it's a bug. But at the call in question, it clearly
> checks for an error condition, so it appears that SQLBindParameter()
> doesn't return one.
It should return an error condition as seen from the ODBC log:
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"
> Maybe you can work around the error for now
I already did.
, and send me a minimal
> script, dataset and Access database that reproduces the bug, along with
> detailed instructions on how to reproduce it (baby steps and all that
> ;-). (Or enter the bug report at the q-lang.sf.net bug tracker, if you
> prefer that.) Then I'll have a look at it asap.
A zip file is enclosed. The procedure is described in the script.
Since the conference does not accept zip files, I renamed it to xxx. To
unpack rename it back to zip.
Many thanks,
Jiri
|
|
From: Albert G. <Dr....@t-...> - 2008-01-24 21:53:28
|
Tim Haynes wrote:
>> ==> 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.
The strange thing is that it doesn't even give back an error there, as
the relevant code at line 622 in modules/odbc/odbc.c does check for an
error condition in the return code of SQLBindParameter().
> I observe from the above URL:
> | An application can set the ParameterValuePtr argument to a null pointer,
> | as long as *StrLen_or_IndPtr is SQL_NULL_DATA or SQL_DATA_AT_EXEC.
Indeed that's what set_arg() does (in the isvoid() branch which checks
for () inputs which represent NULL values; see odbc.c, line 239), and
this routine is called at line 539 in odbc.c to initialize the
parameters before they are handed over to SQLBindParameter().
AFAICT, there's nothing wrong in that code. Could it be that it's a
binary incompatibility (i.e., maybe you're running a different ODBC
version with a different ABI than that what I compiled the ODBC module
against)? What does odbc_info DB say on your system?
> Further lines of investigation: since ODBC provides a bunch of metadata
> about the connection, is there anything Q should be probing (eg using
> SQLGetInfo()) to make decisions and modify its behaviour in advance?
Well, what else can I do than to set up the parameters in precisely the
way described by MS's own documentation?
> I'm not sure how Q can handle SQL_ERROR states; how do you determine if a
> statement succeeded in Q given Q's normal way of dealing with supposed
> failures?
All operations, including sql_exec, check the return value of each and
every ODBC call where an error can arise, and, if that return value
indicates an error, the operation will bail out and return the error in
the form 'odbc_error MSG STATE' where MSG is the error message and state
is the SQL_ERROR state. At least that's how it's supposed to be, if that
doesn't happen then it's a bug. But at the call in question, it clearly
checks for an error condition, so it appears that SQLBindParameter()
doesn't return one.
Maybe you can work around the error for now, and send me a minimal
script, dataset and Access database that reproduces the bug, along with
detailed instructions on how to reproduce it (baby steps and all that
;-). (Or enter the bug report at the q-lang.sf.net bug tracker, if you
prefer that.) Then I'll have a look at it asap.
Albert
--
Dr. Albert Gr"af
Dept. of Music-Informatics, University of Mainz, Germany
Email: Dr....@t-..., ag...@mu...
WWW: http://www.musikinformatik.uni-mainz.de/ag
|
|
From: Tim H. <q...@st...> - 2008-01-24 20:41:10
|
Jiùí Spitz <jir...@bl...> writes: [snip] >> 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 :-) Excellent. [snip] > 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 http://msdn2.microsoft.com/en-us/library/ms710963(VS.85).aspx The error code, HY104, is `Invalid precision or scale value'; `The value specified for the argument ColumnSize or DecimalDigits was outside the range of values supported by the data source for a column of the SQL data type specified by the ParameterType argument'. So what it's saying is when you're binding to a char field, a length of 0 is not permitted. [snip] > ==> 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. I observe from the above URL: | An application can set the ParameterValuePtr argument to a null pointer, | as long as *StrLen_or_IndPtr is SQL_NULL_DATA or SQL_DATA_AT_EXEC. Could that be where things are going wonky? Further lines of investigation: since ODBC provides a bunch of metadata about the connection, is there anything Q should be probing (eg using SQLGetInfo()) to make decisions and modify its behaviour in advance? I'm not sure how Q can handle SQL_ERROR states; how do you determine if a statement succeeded in Q given Q's normal way of dealing with supposed failures? > ==> 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." That's not surprising. Not many RDBMS (*cough*, "and this is Access we're talking about") support `=' comparison on NULL values. Much safer to use "is null" etc in your query. ~Tim -- <http://spodzone.org.uk/> |
|
From: <jir...@bl...> - 2008-01-24 20:40:24
|
Jiri Spitz wrote:
> ==> 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 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."
Sorry for that (one element tuples...), but does not work anyway:
==> sql DB "insert into data_nrg_101a values ('d', 'd', 'd', 'd', 'd',
123, ?)" ("",)
sql_exec <<ODBCHandle>> "insert into data_nrg_101a values ('d', 'd',
'd', 'd', 'd', 123, ?)" ("",)
==> sql DB "insert into data_nrg_101a values ('d', 'd', 'd', 'd', 'd',
123, ?)" ((),)
sql_exec <<ODBCHandle>> "insert into data_nrg_101a values ('d', 'd',
'd', 'd', 'd', 123, ?)" ((),)
Jiri
|
|
From: <jir...@bl...> - 2008-01-24 20:07:20
|
Dear Albert, I described my discoveries in the response to the Tim Haynes' mail. Thanks for your answer. Jiri Albert Graef wrote: > > You mean it doesn't even return an odbc_error term? That's weird. > > Ok, so obviously some ODBC function being called in sql_query returns an > error code. If sql_exec doesn't give you an error code, then I guess > you'll just have to compile your own version of the odbc module and > insert some printf's into the function (it's at line 563 in odbc.c), so > that you can find exactly where it fails. (Watch out for all places with > return's and goto's.) > > That's my guess, too. Unfortunately, I don't have Access on my Windows > box and I'm really snowed under work, so I can't debug it myself right > now. But if you can find in which ODBC call sql_query exactly fails, and > with which error code (if any), then we might be able to figure it out. > > Albert > |
|
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
|
|
From: Albert G. <Dr....@t-...> - 2008-01-24 01:43:58
|
Tim Haynes wrote: [snipped] Yes, first try what Tim suggests, he knows way more about ODBC than I do. :) Albert -- Dr. Albert Gr"af Dept. of Music-Informatics, University of Mainz, Germany Email: Dr....@t-..., ag...@mu... WWW: http://www.musikinformatik.uni-mainz.de/ag |
|
From: Albert G. <Dr....@t-...> - 2008-01-24 01:11:14
|
Jir(í Spitz wrote: > The called sql_exec procedure is not further evaluated. You mean it doesn't even return an odbc_error term? That's weird. Ok, so obviously some ODBC function being called in sql_query returns an error code. If sql_exec doesn't give you an error code, then I guess you'll just have to compile your own version of the odbc module and insert some printf's into the function (it's at line 563 in odbc.c), so that you can find exactly where it fails. (Watch out for all places with return's and goto's.) > 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. That's my guess, too. Unfortunately, I don't have Access on my Windows box and I'm really snowed under work, so I can't debug it myself right now. But if you can find in which ODBC call sql_query exactly fails, and with which error code (if any), then we might be able to figure it out. Albert -- Dr. Albert Gr"af Dept. of Music-Informatics, University of Mainz, Germany Email: Dr....@t-..., ag...@mu... WWW: http://www.musikinformatik.uni-mainz.de/ag |
|
From: Tim H. <q...@st...> - 2008-01-24 00:48:40
|
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/>
|
|
From: <jir...@bl...> - 2008-01-23 23:20:37
|
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
|
|
From: Albert G. <Dr....@t-...> - 2008-01-23 07:32:08
|
Hi, RC1 for Q 7.11 is now available in testing: http://sourceforge.net/project/showfiles.php?group_id=96881&package_id=188958 (Currently it's just a tarball, but I'll upload SUSE RPMs and Windows MSI asap.) Here's what's new in this release: - As suggested by Rob Hubbard and John Cowan, special Unicode characters in strings can now be specified using an escape of the form '\&name;', where name is any of the XML entity names specified here: http://www.w3.org/TR/2007/WD-xml-entity-names-20071214/ - clib now provides a set of additional int/float vector operations, as suggested by John Cowan on the mailing list a while ago. These allow you to treat byte strings as mutable C vectors of signed/unsigned 8/16/32 bit integers or float/double values. Operations to convert between such C vectors and corresponding lists of integers/floating point values are provided as well. - The glob and regex functions were moved back from system into clib. Note that the latter change affects code which does a qualified import of fnmatch/glob or regex et al, so you should check your scripts for that. QCalc is the only addon module affected in the distribution, and I've uploaded a new tarball (qcalc-1.1) for that already. As usual, please let me know if you find any bugs asap. Enjoy. Albert -- Dr. Albert Gr"af Dept. of Music-Informatics, University of Mainz, Germany Email: Dr....@t-..., ag...@mu... WWW: http://www.musikinformatik.uni-mainz.de/ag |
|
From: Albert G. <Dr....@t-...> - 2008-01-23 06:43:48
|
Albert Graef wrote: > John Cowan wrote: >> 1) Move the regex function (and possibly the low-level regex functions as well) >> back into qlib.c. Regular expressions are defined by Posix but aren't >> in any way system-dependent. > > Yes, in hindsight it seems much more convenient to have this in the > prelude. So I'm all for this change, but it's going to break existing > code. :( Does anyone object to this? This is in cvs now as well. Actually I moved both regex et al and fnmatch/glob back into clib. Note that this will break existing code doing a qualified import of these routines. I'm ready to release 7.11 RC1 now, stay tuned. -- Dr. Albert Gr"af Dept. of Music-Informatics, University of Mainz, Germany Email: Dr....@t-..., ag...@mu... WWW: http://www.musikinformatik.uni-mainz.de/ag |
|
From: Albert G. <Dr....@t-...> - 2008-01-22 12:53:32
|
Albert Graef wrote:
> I just need to add a function to replace a slice of a byte
> string in-place.
Ok, this is in cvs now. Examples:
==> put_uint32 B (-2) (uint32_vect [90..94])
()
==> uint32_list B
[92,93,94,103,104,105,106,107,108,109,110]
==> uint32_list $ get_uint32 B (-2,3)
[92,93,94,103]
==> uint32_list $ get_uint32 B (8,100)
[108,109,110]
Note that these operations are all safe in that indices are
automatically confined to stay within the bounds of the target vector.
Here's how you can manipulate a string represented as a byte string in a
given character encoding:
==> def S = bytestr ("Hello world!","latin1")
==> put_uint8 S 3 $ bytestr ("äöü","latin1"); bstr S
()
"Heläöüworld!"
Albert
--
Dr. Albert Gr"af
Dept. of Music-Informatics, University of Mainz, Germany
Email: Dr....@t-..., ag...@mu...
WWW: http://www.musikinformatik.uni-mainz.de/ag
|
|
From: Albert G. <Dr....@t-...> - 2008-01-22 10:11:14
|
Rob Hubbard wrote: > Perhaps there's some argument for the user getting some indication (a > warning?) that Q "fall back" functions are being used. That's not really a viable solution, as having fallback equations to handle special cases of an operation whose core is defined as an external is perfectly legitimate. The only way to make that work would be to have some kind of "not supposed to be extended" markup in extern declarations, which goes against Q's philosophy to allow extensions a.k.a. overloading of any operation, even the builtins. Albert -- Dr. Albert Gr"af Dept. of Music-Informatics, University of Mainz, Germany Email: Dr....@t-..., ag...@mu... WWW: http://www.musikinformatik.uni-mainz.de/ag |
|
From: Rob H. <hub...@gm...> - 2008-01-22 09:51:25
|
> John Cowan wrote: > > I think it better for the code to break than to suddenly run 20 or 200 > > times slower because you excluded clib for some reason. I think (2) would be a bad idea. Thus, I agree with Albert here. Perhaps there's some argument for the user getting some indication (a warning?) that Q "fall back" functions are being used. But as removal of clib would be deliberate, perhaps there's little need for that. Albert Graef wrote: > However, if that's a real showstopper for anyone, then I could add a > --with(out)-clib option to the configury. Opinions? And/or --with(out)-qfallback ?? My opinion: these are worth doing only if very easy. Rob. |
|
From: Rob H. <hub...@gm...> - 2008-01-22 09:38:27
|
> John Cowan wrote: > > The current structuring of chapters 10, 11, and 12 has more to do > > with where a function is defined and what language it is written in > > (Q or C) than with what it does, so the user ends up having to search > > all three chapters to find things. The Q documentation is excellent. However, I agree that some restructuring based on functionality (rather than implementation or location) would be helpful; so I agree. As it happens, I am now sufficiently familiar with the Q documentation that I can usually find what I'm looking for relatively quickly. However, what John described was certainly a stumbling block for me when I was new to Q. Albert Graef wrote: > That's true, but your proposed reorganization is a lot of work. ;-) I'll > do it eventually, hopefully in time for the 8.0 release. I for one appreciate the difficulty of writing good documentation. I've spent at least twice as long on my documentation as on my Q code, in part as I'm still a LaTeX novice... [I'm still not 100% happy with it.] But, any additional work on the Q documentation will be appreciated. Thanks, Rob. |
|
From: Albert G. <Dr....@t-...> - 2008-01-22 08:51:45
|
John Cowan wrote: > I think it better for the code to break than to suddenly run 20 or 200 > times slower because you excluded clib for some reason. I don't agree with that. If you're running without clib, it's a conscious decision, and most users who want to do that for some reason will probably prefer the rest of the standard library to just keep working without having to jump through a lot of hoops. > After all, code will break if you exclude other standard modules, too. That's true, but the rest of the stdlib has been specifically designed to not depend on clib. (Well, that's not 100% true any more, but AFAICS it's just a single function from clib which is extended in rational.q, and you can easily comment that one out.) The rationale behind this is, if someone wants to port Q to some exotic Gizmo XYZ device which doesn't have a full POSIX interface and thus clib might not even compile there for some reason, then you still have the option to make a minimal Q implementation work there, without much ado. I think that this is important enough to justify the cost of having a little cruft in the library (it's just a few definitions anyway). However, if that's a real showstopper for anyone, then I could add a --with(out)-clib option to the configury. Opinions? Albert -- Dr. Albert Gr"af Dept. of Music-Informatics, University of Mainz, Germany Email: Dr....@t-..., ag...@mu... WWW: http://www.musikinformatik.uni-mainz.de/ag |
|
From: John C. <co...@cc...> - 2008-01-22 02:26:59
|
Albert Graef scripsit: > > 2) Move the Q functions that are overridden by clib functions (documented > > in 12.20) into the examples. Currently they are loaded but then never used. > > Hmm, these functions are there so that the library doesn't break if you > want/need to remove clib from the prelude. That should still be > possible, although I haven't checked it for a while. So I'd rather keep > them where they are. Any specific reason why you want them removed, > other than tidyness? > I think it better for the code to break than to suddenly run 20 or 200 times slower because you excluded clib for some reason. After all, code will break if you exclude other standard modules, too. -- John Cowan co...@cc... http://ccil.org/~cowan Promises become binding when there is a meeting of the minds and consideration is exchanged. So it was at King's Bench in common law England; so it was under the common law in the American colonies; so it was through more than two centuries of jurisprudence in this country; and so it is today. --Specht v. Netscape |
38 messages has been excluded from this view by a project administrator.
