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

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

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