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

[Albert G. <Dr....@t-...>]

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