[Jprogramming] bulk insert with ODBC (into MySQL db)

[bill lam]

Втр, 15 Июн 2010, Denis Samson писал(а):
> 
> Hi!  
>      I'm having lots of fun porting an old, huge J application still running on J5.03 (!) and using a SQLite database.  The goal is 1- to get it to use MySQL 5.x instead through an ODBC connection, then 2- move the whole thing to J6.02...
> 
>      Conversion is a snap until I try a bulkinsert (we routinely insert tens of thousand rows), where I hit a speed bump:  I try to bind parameters "by column":
>      SQLSetStmtAttr(sh, SQL_ATTR_PARAM_BIND_TYPE, SQL_PARAM_BIND_BY_COLUMN, 0)
> (fine) and send, say, 1000 elements per column at a time:
>      SQLSetStmtAttr(sh, SQL_ATTR_PARAMSET_SIZE, 1000, 0)
> 
> Fine again, except the MySQL driver essentially tells me "OK, but I'd prefer doing it one element at a time". (one BulkInsert per row :-P )... and my inserts would need about 10 days to complete. 
> 
> So I look around a bit... and realize a few things: 
>      First, the DLL binding & call take an awful lot of time (in J5, at least!) to execute anything, even trivial: that's around .04 seconds for something not even involving the server! 
>      Second, there's a known bug (#48310) in the current MySQL Connector/ODBC 5.1.6 that explains the driver response. It's corrected in 5.1.7, which is not released yet, and nobody on Oracle/Sun would bother to answer my requests about when it will be...
> 
>      Wondering how anyone can use an ODBC connection to MySQL if there's no way to do a bulk insert... I'm certainly not the only guy trying to insert more than one row at a time! 
> 
> There must be something somewhere...
>      And, bingo! there's a <ddins> verb in J602\system\packages\odbc\odbc.ijs that was not in the j503 version of the file...  
>      But I couldn't find it used anywhere, just referenced in system\main\dd.ijs, which is the list of "data driver utilities"
>      Anyway, I extract the verb from the source file, retrofit it to J5 (x. y. etc.) , but notice it behaves a bit strangely:
>      - uses SQLExecDirect before binding or setting the column data;
>      - using getallcolinfo, which gives info about *result* columns, and we're *inserting*;
>      - then using SQLBulkOperations.  (rather than simply SQLExecute, - there might be a good reason, but I haven't gone through the whole documentation yet) (and, AFAICS, there seem to be multiple ways to get things done with ODBC!)(it's not a committee-designed beast for no reason!)
> 
>      I started fiddling with the code, trying to change as little as I need so I can switch to "standard" J6 without having to carry my own version of ddins, but it's starting to look pretty hopeless...
> 
>      So I'd be a very happy camper if anyone could answer the following:
> 
> 1- Does ddins work?  (and 1.1- why does it overtake varchars so max size?)
> 
> 2- Has anyone been using it, with MySQL or any other database?
> 
> 3- Does anyone ever bulkinserts into MySQL or anywhere else (and if so, how?)

1. it works for me

1.1 I'm lazy. 

2. it tested ok with ms-access, ms sqlsvr, firebird

3. I don't know, you better ask in their forum (but it seems you have
   already done that).

I suggest you first verify ddins working properly ms-access or ms sqlsvr. 
If that doesn't work, try again with J602 and use the original odbc package.

Sorry I don't use mysql and cannot help with mysql specific questions.

-- 
regards,
====================================================
GPG key 1024D/4434BAB3 2008-08-24
gpg --keyserver subkeys.pgp.net --recv-keys 4434BAB3