Hi all,
I've got another ADO related issue that I could use some
tips/hints/suggestions. For the record, we're using Gentran:Windows,
version 5.0, patch 9, MSSQL v2005 for the database.
I have the need to store 6 months of invoice related data within Gentran
database to identify possible duplicate invoices. We don't have the space
to store 180 days of invoice data in active interchanges (we archive at 30
days), but we still need to do duplicate checks. So I can't use the
default "duplicate document" check for the partner either as this requires
that the interchanges remain active and within Gentran.
To accomplish the duplicate checks, we came up with using the Gentran
lookup_tb entries to store the invoice number. This table does not
archive or purge with archive so the data would remain.
In setting this up I discovered that Sterling has "streamlined" the
"select" extended rule code so that it responds quickly during
translation. However the "insert" and "update" extended rules use a
different code path during translation that adds a lot of time to each
translation of an incoming invoice. Sterling level 2 support confirmed
this for me. The times we're talking about for a single "insert" extended
rule is 15 or more seconds per invoice. Our main client routinely sends
us 60,000 invoices in a single day. At 15 seconds per invoice, processing
would take hours if not days to complete.
Level 2 support suggested I use a user exit and an ADO object call to the
database to make the insert. So I set up the following code to do this:
At the $810 level, at the "on begin":
obConnection = CreateObject ("ADODB.Connection");
strCnn = "Provider=MSDASQL;" + "DSN=GentranDB_TEST;";
obConnection.Open(strCnn);
If obConnection.State != 1 then
cerror(700,#ODBCerrMsg);
Within the BIG02 (element #0076) there is:
#INV_NBR_LOOKUP = "22_" + #0076;
select Item, Text2 into #EPAY_INVOICE_NBR, #EPAY_DATE
from DivisionLookup where Tablename = "EPAY_INV" and Item = #INV_NBR_LOOKUP;
If exist(#EPAY_INVOICE_NBR) then
begin
cerror(440,#0076);
auditlog(10,AL_PROC,0,#EPAY_INVOICE_NBR,#EPAY_DATE);
end
else
begin
result = obConnection.ExecuteSQL(INSERT INTO DivisionLookup
(PartnerKEY,TableName,Item,Description,Text1,Text2,Text3,Text4)
values ("SYSTEMUSR","EPAY_INV",#INV_NBR_LOOKUP,"","22",#0373,"",""));
end
And finally in the $810 level at the "on-end", there's this:
obConnection.close();
deleteobject(obConnection);
The result? Well, it's working ... sort of. It's flagging any
duplicate invoices and logging the "cerror" just fine. If the invoice
does not exist, it's adding the invoice data to the lookup_tb table just
fine.
But, it's still taking 15 seconds to run and I'm getting the following error:
Other ActiveX error: Exception occurred. Method : ExecuteSQL Field
Rule, field name = 0076
Sterling level 2 has 'washed their hands' of this because it's now a user
exit and they don't support them, so I can't go to them.
>From examining the CPU utilization during translation execution, it would
appear that tx32.exe is actually loading the entire lookup_tb table into
memory, doing the insert, then writing the entire table back out. But I
can't confirm this.. just my observations during translation.
Does anyone have:
1. Any idea what the "exception" error could be that I'm seeing during
the insert command? Any way for me to trap and/or display more details
for the exception?
2. Any idea why it's still taking 15 seconds per ST/SE if an insert is
needed? Has anyone else confirmed that Gentran does in fact load the
entire lookup table into memory every time an 'insert' or 'update' is
executed either using extended rules or an ADO call?
I know this has been rather lengthy, but I just wanted to give you all the
info I could.
My thanks to any and all suggestions how to resolve this. We would like
to get this solved in the next week or two because we have another large
client coming online soon and really need the duplicate invoice check in
place by then.
Thanks, again..
Richard Jones
...
Please use the following Message Identifiers as your subject prefix: <SALES>,
<JOBS>, <LIST>, <TECH>, <MISC>, <EVENT>, <OFF-TOPIC>
Job postings are welcome, but for job postings or requests for work: <JOBS> IS
REQUIRED in the subject line as a prefix.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/EDI-L/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/EDI-L/join
(Yahoo! ID required)
<*> To change settings via email:
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
