[prev in list] [next in list] [prev in thread] [next in thread] 

List:       dbi-dev
Subject:    RE: Array Binding (an ODBC example)
From:       martin () easysoft ! com
Date:       2001-08-10 8:47:01
[Download RAW message or body]


On 10-Aug-2001 Neil Lunn wrote:
> 
> 
>>-----Original Message-----
>>From: martin@easysoft.com [mailto:martin@easysoft.com]
>>Sent: Thursday, August 09, 2001 7:16 PM
>>To: dbi-dev@perl.org
>>Subject: Array Binding (an ODBC example)
>>
>>
>>Just in case it helps the current discussion.
>>
>>In ODBC binding arrays can be used on insert operations and selects.
>>Using bound arrays is MUCH faster (I can provide timings if anyone
>>needs them).
> 
> Just a short clarification. Otherwise the information is helpful.
> Is the binding of arrays accepted in ODBC v2 or only in v3 compliant
> drivers?

ODBC 2.0 only has SQL_BIND_TYPE and SQL_ROWSET_SIZE which can be used with
SQLExtendedFetch to retrieve arrays of bound columns.

I have seen references later in this list to checking the drivers ODBC version
to see if array bound parameters can be supported. Please don't go in this
direction. This is similar to what happend in PHP and it is a disaster (cannot
stress that enough). You only have to call SQLGetInfo(SQL_DRIVER_ODBC_VER) on
MS Access and it returns "03.51" (this is for odbcjt32.dll at version
04.00.4204) and yet it does not support array bound parameters.
 
DBD::ODBC changed some time back to distribute with a driver manager (iODBC,
although the version is old now and I personally use the unixODBC driver manager
instead - http://www.unixodbc.org).

Around that time SQLDataSources calls were added to ODBC.c which is an ODBC
driver manager function and not ODBC driver functions. So, as far as I can see
DBD::ODBC already needs a driver manager and the driver manager is responsible
for mapping ODBC 2.0 apps to ODBC 3.0 drivers and vice versa (although in the
case of array bound parameters the driver manager does not particularly help).

If you call SQLSetStmtAttr(SQL_ATTR_PARAMSET_SIZE) from an ODBC 2
application (which DBD::ODBC is) through a driver manager to say MS Access
(which is ODBC 3.51 on my machine) you will get S1C00 Optional feature not
implemented. Retrieving SQL_ATTR_PARAMSET_SIZE after the above returns 1. If
you pick an ODBC 2.0 driver and make the same call the driver manager should
return S1092 Option type out of range (the actual states depend on the ODBC
version of your app - the driver manager will convert them to what you expect).

If an ODBC driver does not support array bound parameters I guess DBD::ODBC
should do the multiple calls to SQLExecute/SQLFetch moving through the arrays -
in that way people writing perl using DBD::ODBC do not have to worry about a
driver supporting arrays or not.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development

[prev in list] [next in list] [prev in thread] [next in thread]