Thread: [q-lang-users] ODBC connection with MS Access
Brought to you by:
agraef
Menu
▾
▴
q-lang-users
|
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: 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: 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: <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: <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: 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: 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: <jir...@bl...> - 2008-01-24 23:40:06
Attachments:
odbc_access.xxx
|
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-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: 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-12 03:23:52
|
Tim Haynes wrote: > 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. Tim, you hit the nail on the head there! :) Access doesn't like a zero precision, not even for NULL parameter values. This is clearly an Access-specific problem, MySQL and PostgreSQL both work fine. I also digged through the ODBC docs, but nowhere does it say that a NULL or empty string parameter is supposed to need a nonzero precision, so I consider this a bug in the Access ODBC driver. Anyway, I worked around the bug by adding an extra dummy byte for both strings and () values, so that the precision value is never zero now. Seems to work fine so far. I'm currently uploading a new release candidate (7.11rc2) so that you can give it a whirl. Jiri, could you please try the Qpad-7.11rc2.msi in testing and see whether it works for you? You can download the msi here: http://sourceforge.net/project/showfiles.php?group_id=96881&package_id=188958&release_id=568981 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: Jiri S. <jir...@bl...> - 2008-02-12 18:02:30
|
Dear Albert, I tried the new release candidate and it works now fine with both NULLs and empty strings in parametrized queries. Many thanks, Jiri Albert Graef napsal(a): > Jiri, could you please try the Qpad-7.11rc2.msi in testing and see > whether it works for you? You can download the msi here: > http://sourceforge.net/project/showfiles.php?group_id=96881&package_id=188958&release_id=568981 > |
|
From: Albert G. <Dr....@t-...> - 2008-02-13 00:05:51
|
Jiri Spitz wrote: > I tried the new release candidate and it works now fine with both NULLs > and empty strings in parametrized queries. Great. I also tested the new version with MySQL and PostgreSQL and the workaround doesn't seem to cause any hiccups there, so I hope that we can consider this solved. I still have to fix up the "menagerie" example (odbc_examp.q), so that it works with Access without any ado, but that will be in the final release. BTW, does anyone here know a _portable_ way to quote column names which are also SQL keywords? (MS SQL doesn't like these.) ANSI SQL mandates double quotes, which works with both Access and PostgreSQL out of the box, but not with MySQL. :( 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-13 00:44:23
|
Albert Graef <Dr....@t-...> writes:
> BTW, does anyone here know a _portable_ way to quote column names which
> are also SQL keywords? (MS SQL doesn't like these.) ANSI SQL mandates
> double quotes, which works with both Access and PostgreSQL out of the
> box, but not with MySQL. :(
SQLGetInfo(SQL_IDENTIFIER_QUOTE_CHAR) is your friend:
| SQL_IDENTIFIER_QUOTE_CHAR(ODBC 1.0)
|
| The character string that is used as the starting and ending
| delimiter of a quoted (delimited) identifier in SQL statements.
| (Identifiers passed as arguments to ODBC functions do not have to be
| quoted.) If the data source does not support quoted identifiers, a
| blank is returned.
|
| This character string can also be used for quoting catalog function
| arguments when the connection attribute SQL_ATTR_METADATA_ID is set
| to SQL_TRUE.
|
| Because the identifier quote character in SQL-92 is the double
| quotation mark ("), a driver that conforms strictly to SQL-92 will
| always return the double quotation mark character.
<http://msdn2.microsoft.com/en-us/library/ms711681(VS.85).aspx>
Beware that, in m$loth terminology, "a blank" means either NULL or a space
character depending what they were smoking at the time.
HTH :)
~Tim
--
<http://spodzone.org.uk/>
|
|
From: Albert G. <Dr....@t-...> - 2008-02-14 01:30:50
|
Tim Haynes wrote: > SQLGetInfo(SQL_IDENTIFIER_QUOTE_CHAR) is your friend: Tim, that was very helpful, thanks a lot. I added a new odbc_getinfo function to the odbc module which provides a direct wrapper of SQLGetInfo(), so that I can now query all metadata. Fixing odbc_examp.q so that it works with different RDBMS should be a piece of cake now! While I'm giving the odbc module a facelift... I'd also like to support user-defined functions written in Q (the sqlite3 interface already provides these and I find them pretty useful). By any chance, do you know whether ODBC provides an API for this kind of stuff? I'm looking for an ODBC function which would allow to register a C callback as a UDF to be called from SQL queries, which takes some SQL values as parameters and returns another SQL value as the result. Googling around didn't turn up anything useful so far. Thanks, 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-14 14:23:40
|
Albert Graef <Dr....@t-...> writes: > Tim Haynes wrote: >> SQLGetInfo(SQL_IDENTIFIER_QUOTE_CHAR) is your friend: > > Tim, that was very helpful, thanks a lot. I added a new odbc_getinfo > function to the odbc module which provides a direct wrapper of > SQLGetInfo(), so that I can now query all metadata. Fixing odbc_examp.q > so that it works with different RDBMS should be a piece of cake now! Cool. The other thing you might find useful is exposing SQLGetTypeInfo() - at least so that folks can see the native type-names for use in create/alter statements. (Does varchar need a max-size? Float or Double? text or long varchar? As for date/time/datetime/timestamp, don't get me started :) > While I'm giving the odbc module a facelift... I'd also like to support > user-defined functions written in Q (the sqlite3 interface already > provides these and I find them pretty useful). By any chance, do you know > whether ODBC provides an API for this kind of stuff? I'm looking for an > ODBC function which would allow to register a C callback as a UDF to be > called from SQL queries, which takes some SQL values as parameters and > returns another SQL value as the result. Googling around didn't turn up > anything useful so far. I'm not aware of any such thing as part of the ODBC spec; quick consultation with one of the gurus here didn't produce any obvious links. I would expect it to be outwith the general scope of ODBC as a portable SQL-passing layer, because it would mandate direct intervention in the SQL in transit as a matter of core compliance, BICBW. ~Tim -- <http://spodzone.org.uk/> |
|
From: Albert G. <Dr....@t-...> - 2008-02-15 00:19:27
|
Tim Haynes wrote: > Cool. The other thing you might find useful is exposing SQLGetTypeInfo() - > at least so that folks can see the native type-names for use in > create/alter statements. Ok, I'll have a look at it. Any other stuff missing in the interface that's badly needed? > I'm not aware of any such thing as part of the ODBC spec; quick > consultation with one of the gurus here didn't produce any obvious links. Doesn't surprise me, actually. On second thought it came to me that "script tie-ins" in SQL statements can only be done on the server, not the client side, both for efficiency and security considerations. So ODBC has no business with that. SQLite is different, because it doesn't have a server. 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: <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: Albert G. <Dr....@t-...> - 2008-02-15 12:12:28
|
Albert Graef wrote: > Tim Haynes wrote: >> Cool. The other thing you might find useful is exposing SQLGetTypeInfo() - >> at least so that folks can see the native type-names for use in >> create/alter statements. > > Ok, I'll have a look at it. This is in cvs now (see odbc_typeinfo). I don't know how much of that information is actually useful or even supported on different ODBC implementations, but the function provides it all -- 19 different fields. ;-) I also added a bunch of symbolic constants (SQL_*) which should be useful when working with this function. (I transcribed these from the unixODBC headers, I hope that they are standardized so that they will work on all ODBC implementations.) I also fixed up the odbc_examp.q script, it now makes use of SQL_IDENTIFIER_QUOTE_CHAR and thus the function which initializes the tables of the sample menagerie database should work with MS Access and other strictly ANSI-compliant RDBMSs. 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-15 14:26:42
|
Albert Graef <Dr....@t-...> writes: > Albert Graef wrote: >> Tim Haynes wrote: >>> Cool. The other thing you might find useful is exposing SQLGetTypeInfo() - >>> at least so that folks can see the native type-names for use in >>> create/alter statements. >> >> Ok, I'll have a look at it. > > This is in cvs now (see odbc_typeinfo). I don't know how much of that > information is actually useful or even supported on different ODBC > implementations, but the function provides it all -- 19 different fields. > ;-) I also added a bunch of symbolic constants (SQL_*) which should be > useful when working with this function. (I transcribed these from the > unixODBC headers, I hope that they are standardized so that they will > work on all ODBC implementations.) Excellent. Yes, they should be reasonably standardized. You can cross-reference against sql*.h in iODBC if really desperate. > I also fixed up the odbc_examp.q script, it now makes use of > SQL_IDENTIFIER_QUOTE_CHAR and thus the function which initializes the > tables of the sample menagerie database should work with MS Access and > other strictly ANSI-compliant RDBMSs. Cool :) ~Tim -- <http://spodzone.org.uk/> |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X