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?)
Thanks!
Denis
_________________________________________________________________
Learn more ways to connect with your buddies now
http://go.microsoft.com/?linkid=9734388
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
