Hello Bill,
Yes ... your right. The problem is not with J but with the ADODB choking with
data (this thing runs on J504).
You see, the way I use ADO is to submit a bunch of SQL statements wrapped with
a "BEGIN TRANS". The problem with this is that all of the tables are related
and there are triggers firing on every update and inserts. So ADO either times
out and quits or just plain throws an error.
I was able to work around this by NOT using transactions and by doing batch
insert/updates of data by 512 rows. :) Btw, 512 was not arbitrary ... I
actually did a lot of server response test (I don’t remember the details
anymore) by cutting data by a progression of 8's. So 512 was the biggest number
of rows in one block that wouldn't bring an OLTP MS-SQL server to its knees. It
works but I felt that it wasn't really "safe".
So with the latest version using J601 ... we actually removed all MS-SQL
interaction and I've created a some C# functions which would read a .NET
DataSet or Table and save it into a J variable. Here is one of them:
/// <summary>
/// Converts data to a multidimensional array that can be imported by
EOE
/// </summary>
public static void dataTableToEOE(DataTable sourceTable, out object
outFields, out object outData)
{
// Variable declaration
int cols = sourceTable.Columns.Count;
object[,] fields = new object[cols, 2];
int rows = sourceTable.Rows.Count;
object[,] data = new object[rows, cols];
int icol = 0;
int irow = 0;
string oType;
string strDateTime;
DateTime dtDateTime;
// Lets create the fieldlist first
foreach (DataColumn myDataColumn in sourceTable.Columns)
{
// Save the field name
fields[icol, 0] = myDataColumn.ColumnName.ToString();
oType = myDataColumn.DataType.ToString();
switch (oType)
{
case "System.Byte":
case "System.Decimal":
case "System.Double":
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.SByte":
case "System.UInt16":
case "System.UInt32":
case "System.UInt64":
fields[icol, 1] = "numeric";
break;
case "System.DateTime":
case "System.TimeSpan":
fields[icol, 1] = "datetime";
break;
case "System.Boolean":
fields[icol, 1] = "boolean";
break;
default:
// Treat everyting else as string
fields[icol, 1] = "text";
break;
}
// Increment counter
icol++;
}
// Now retrieve all row data
foreach (DataRow myDataRow in sourceTable.Rows)
{
// Get the value for each of the data
for (int i = 0; i < myDataRow.ItemArray.Length; i++)
{
oType = fields[i, 1].ToString();
switch (oType)
{
case "numeric":
if (myDataRow[i] == DBNull.Value)
{
data[irow, i] = 0;
}
else
{
data[irow, i] = Convert.ToDouble(myDataRow[i]);
}
break;
case "datetime":
if (myDataRow[i] == DBNull.Value)
{
data[irow, i] = "";
}
else
{
//data[irow, i] =
Convert.ToDateTime(myDataRow[i]).ToString("MM/dd/yyyy HH:mm:ss");
dtDateTime = (DateTime)myDataRow[i];
strDateTime = dtDateTime.Month.ToString() + "/"
+ dtDateTime.Day.ToString() + "/";
strDateTime += dtDateTime.Year.ToString() + " "
+ dtDateTime.Hour.ToString();
strDateTime += ":" +
dtDateTime.Minute.ToString() + ":" + dtDateTime.Second.ToString();
data[irow, i] = strDateTime;
}
break;
case "boolean":
if (myDataRow[i] == DBNull.Value)
{
data[irow, i] = 0;
}
else
{
data[irow, i] = Convert.ToByte(myDataRow[i]);
}
break;
case "text":
if (myDataRow[i] == DBNull.Value)
{
data[irow, i] = "";
}
else
{
data[irow, i] = myDataRow[i];
}
break;
}
}
//increment row counter
irow++;
}
// save and return the data
outFields = fields;
outData = data;
}
What you'll get from the C# code above are 2 arrays, the list of fieldnames and
corresponding datatype and the actual SQL data in a two dimensional matrix.
Here are some actual codes in my project (I'm way too lazy to make a new one):
IN C#:
// STEP 1: Get the size information
sqlCommand = "USE " + this.DatabaseServer + @"
SELECT SIZE_CODE, SIZE_SCHEMA_CODE, SIZE_CODE_DESC, SIZE_SEQ_NO
FROM dbo.ZIO_SIZES
WHERE (IO_Number = '" + ioNumber + "')";
DataTable dtZIOH = (DataTable)this.executeSQL(sqlCommand);
// STEP 2: Send this data to EOE
object outFields, outData;
dataTableToEOE(sourceTable, out outFields, out outData);
jObject.SetB("SIZES_FIELDS", ref outFields)
jObject.SetB("SIZES_DATA", ref outData)
So in your J Session, you can now see your data like so:
SIZES_FIELDS
+----------------+-------+
|Size_Code |text |
+----------------+-------+
|Size_Schema_code|text |
+----------------+-------+
|Size_Code_Desc |text |
+----------------+-------+
|Size_Seq_No |numeric|
+----------------+-------+
SIZES_DATA
+--+----+-----------+-+
|S |M001|Small |1|
+--+----+-----------+-+
|M |M001|Medium |2|
+--+----+-----------+-+
|L |M001|Large |3|
+--+----+-----------+-+
|XL|M001|Extra Large|4|
+--+----+-----------+-+
NB. Pretty print
SIZES_DATA,~ {. "1 SIZES_FIELDS
+---------+----------------+--------------+-----------+
|Size_Code|Size_Schema_code|Size_Code_Desc|Size_Seq_No|
+---------+----------------+--------------+-----------+
|S |M001 |Small |1 |
+---------+----------------+--------------+-----------+
|M |M001 |Medium |2 |
+---------+----------------+--------------+-----------+
|L |M001 |Large |3 |
+---------+----------------+--------------+-----------+
|XL |M001 |Extra Large |4 |
+---------+----------------+--------------+-----------+
NB. Check if we have the correct data types
SIZES_FIELDS,. |: datatype each SIZES_DATA
+----------------+-------+--------+--------+--------+--------+
|Size_Code |text |literal |literal |literal |literal |
+----------------+-------+--------+--------+--------+--------+
|Size_Schema_code|text |literal |literal |literal |literal |
+----------------+-------+--------+--------+--------+--------+
|Size_Code_Desc |text |literal |literal |literal |literal |
+----------------+-------+--------+--------+--------+--------+
|Size_Seq_No |numeric|floating|floating|floating|floating|
+----------------+-------+--------+--------+--------+--------+
It works! I'm happy. :)
r/Alex
P.S.
I've been meaning to post something like this to the WIKI for sometime now ...
hehehe ... I'm just way too lazy. :P
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of bill lam
Sent: Thursday, January 03, 2008 11:50 PM
To: General forum
Subject: Re: [Jgeneral] Successful stories
Alex Rufon wrote:
> 6. The slowest part of the system is not during J calculation but during the
> time J calls ADO to retrieve and write to MS-SQL. We've proven this with the
> new version where J does not connect directly to the DB and instead expect
> array input and output.
I'm not familiar with ADODB, does ADODB has the capacity to do column binding
(not sure ado use this term) ? And IMO the bottleneck is not J interfacing sql
database, but wd oleautomaion interfacing com or dotnet is the real bottleneck.
My experience is that J can insert 10000 rows into sql database within a few
seconds.
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
