Re: ODBC bcp

2015-03-12 19:36 GMT+00:00 Richard Hughes <[email protected]>:
> Hi all,
>
> I wanted to be able to bulk copy into SQL Server from an ODBC
> application, so attached is my *work in progress* attempt at making
> this happen, for the purpose of early feedback about the approach.
>

Very interesting project! I thought about sometimes but never had a
practical reason to do it!

> The only other attempt I can find at doing this was [0], which seemed
> extremely hacky and may have required significant code in the user
> application. My attempt tries to do it 'properly' and I've been
> testing it with an application which builds unaltered on both Windows
> and Linux.
>

Yes, I agree too, no change to user application (beside perhaps some
include and obviously tools changed).

> The patch steals a large amount of code from dblib/bcp.c but, in
> order to give myself an achievable goal, it implements *only* bcp in
> from RAM; all of the file I/O stuff has been ripped out. I believe
> this is a very sane and useful subset to have because my application
> does the bcp as one step of a single larger transaction and hence
> needs to use the existing ODBC connection. People wanting the file I/O
> bit probably don't have this constraint and hence can just use dblib
> or freebcp.
>
> A very useful document is [1] which lists the API differences from
> dblib. The most annoying is probably that all errors must go through
> SQLGetDiagRec rather than dblib's error handling, which is what
> necessitated so much copy-and-paste. A future task is to look at how
> this could be rationalized.
>

Yes, coy and paste is not that great. I'm changing actually bcp.c
(dblib) to use tds_convert instead of dbconvert. This could help
moving code to libTDS (the common part) so we can reuse it. I remember
was a nightmare extracting code from either dblib and ctlib in libTDS
I don't want to do it again for dblib/odbc :)

Well... probably using tdserror instead of dbperror could be a way.
libTDS use callbacks and even for normal errors from network if calls
these callbacks. In odbc these callbacks store errors in
statement/connection to be retrieved by SQLGetDiagRec. I noted that
bcp.c (used objdump) calls very few dblib functions (dbconvert,
dbvarylen, dbperror and another I don't remember now) so I don't think
will be much effort to move to libTDS. In bulk.c or another file.

> The HDBC problem should be discussed. Because the driver manager has
> no idea about the bcp APIs, the first parameter that all the functions
> receive is a driver manager HDBC, not a TDS_DBC*.
> SQLGetInfo(SQL_DRIVER_HDBC) can be used to convert, but that means we
> need to find SQLGetInfo. Approaches I've thought of so far are:
>
> 1) dlopen(...,RTLD_NOLOAD) with various hard-coded filenames to try to
> find which driver manager the application is using
>
> 2) dl_iterate_phdr() to look for filenames that look like the driver
> manager [this is what's currently in the patch].
>
> 3) dlsym(RTLD_DEFAULT,...).
>
> 4) Create a gratuitous libtdsodbcbcp.so containing just thunker
> functions whose sole purpose is to get out of the way of the default
> symbol search path so that method (3) works more reliably.
>
> Microsoft's Windows driver uses (1) because they know there's only one
> driver manager. (3) is subject to ordering problems with the symbol
> search, but those problems exist anyway at link time so I'm beginning
> to think that might be best.
>

(3) should be fine as odbc drivers (like our one) should be loaded
with RTLD_LOCAL flag so the SQLGetInfo from our driver is not found
with RTLD_DEFAULT.

Another idea would be to use a sort of "tunnel" using functions like
SQLSetConnectAttr. Practically let's say we want to implement
bcp_collen, we do something like

typedef struct  {
  DBINT cbData;
  INT idxServerCol;
} bcp_collen_params;

RETCODE bcp_collen (
        HDBC hdbc,
        DBINT cbData,
        INT idxServerCol)
{
   bcp_collen_params params = { cbData, idxServerCol };
    return SQLSetConnectAttr(hdbc, SQL_SS_FUNC_BCP_COLLEN, &params,
sizeof(params));
}

and FreeTDS code detect the willing to use bcp_collen and call
tdsodbc_bcp_collen according. This way you solve having to get proper
HDBC (driver one) and resolving tdsodbc_bcp_collen. Consider also that
driver could be unloaded and loaded in another position but this way
is you don't have to cache a pointer to tdsodbc_bcp_collen to make it
faster the call. Yes, you have to marshal and unmarshal the
parameters.

> Richard.
>
> [0] http://lists.ibiblio.org/pipermail/freetds/2005q1/018076.html
> [1] https://msdn.microsoft.com/en-us/library/ms130924.aspx

Frediano