bill lam wrote:
David Mitchell wrote:
Possible Enhancements:
NB. MAXARRAYSIZE=: 2147483647
MAXARRAYSIZE=: 32000 NB. DM 2006 07 27
I had some very peculiar results doing a ddins with about 80000 rows, 6
columns. The sqlbulkoperations was still running after 1.8 million
rows had been added. I had to terminate the J task to stop it. Setting
MAXARRAYSIZE to 32000 fixed them. It's possible a larger number would
work also.
originally it was MAXARRAYSIZE=: 65535
only changed this monthly after I tested it ok for ms access. Furthermore I want
to decommit the use of brow if it become stable for MAXARRAYSIZE=: 2147483647
the problem may due to a bug in ddins or odbc driver sqlbulkoperations, the odbc
data type for MAXARRAYSIZE is SQLUINTEGER (unsigned 32-bit integer).
Which dbms and odbc driver version did you test? If file size is small, you may
email me the database and sample script for further testing.
I am using the MS Access driver Version 4.00.6304.00. The initial DB is empty.
The script is saving the file names, sizes, time stamps and 3 integer values
for all the files on my hard drive. I make two calls to ddins, one with all
directory paths and one with all file names. The directory path add worked
fine, it has 4 columns, paths, time stamps and 2 integer values.
sqlbulkoperations will hang with the standard character format ddins or the
modified boxed format one. Both forms of ddins will work with the smaller
MAXARRAYSIZE.
The following changes allow ddins to work with variable length character
columns passed as a vector of text vectors.
NB. if. -. *./ 2=> [EMAIL PROTECTED]&.>}.x do. errret ISI08 return. end. NB. DM
2006
07 27
(comment out rank check)
case. SQL_CHAR;SQL_VARCHAR do.
if. 32=3!:0 ]0{(of+i){x do. NB. DM 2006 07 27
bx=.,brow&{ >(of+i){x NB. DM 2006 07 27
(bname)=: ,a=. ln&{."1 >brow&{ >(of+i){x
(blname)=: ,.;#&.>bx NB. DM 2006 07 27
else. NB. DM 2006 07 27
(bname)=: ,a=. ln&{."1 brow&{ >(of+i){x
(blname)=: (rows,1)$#{.a
end. NB. DM 2006 07 27
q=. sh;(>:i);SQL_C_CHAR;(vad bname);(#{.a);(<vad blname)
There is performance penalty for box vector, we may be speaking of 100000 or
1000000 rows, and there is no special code for appending box vector, so it did
occur to me of accepting vector of text vectors in ddins.
Your approach does has the advantage of specifying the actual length of each
item, However padding spaces may not be serious as it might appeared because
padding spaces is ignored in comparison, (eg 'A' = 'A ' in sql standard), and
it will not increase database storage size. Both char(n) and varchar(n) occupy
the same amount of space inside a database file, actually size for varchar(n)
may be slightly larger because it need to store its actual length as well.
Thanks for the information, I won't worry about the trailing blanks.
--
All the best,
David
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
