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.
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.
I found some unexpected results with ODBC and ran a simple test. Based on the
results of the test, it looks like varchar storage, at least a currently used
with MS Access ODBC and J, does not remove padding spaces.
The test created two Access databases each with one table with one varchar
column. I inserted 10000 text vectors in each database, the ones in the first
database were 7 characters long, the vectors for the second were padded with
spaces to length 255. The first database was 324K on disk, the second was 5796K.
As far as I can tell, the same thing happens with ddins.
Is there an option to have ODBC or J remove the padding spaces?
---
fee=: 3 : 0
c1=:ddcon 'dsn=test1'
c2=:ddcon 'dsn=test2'
'create table foo (fee varchar)' ddsql c1
'create table foo (fee varchar)' ddsql c2
for_i. i.10000 do.
'insert into foo (fee) values(''Dummy01'')' ddsql c1
('insert into foo (fee) values(''',(255{.'Dummy01'),''')') ddsql c2
end.
dddis c1
dddis c2
)
--
Thanks,
David Mitchell
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
