Втр, 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 ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm
