Unique Problem

[aitai]

Hi, All

I have a unique insert problem with the code below.

This code takes a structure from a parsed xml document, and inserts the values the database. The number of records will always be about 3000. Hence, the loop is placed on the inside the query tag to limit the calls to the database to one call (a performance decision, if you will).

The problem is that record insertion always stalls at 455 records (no error is thrown, though)!

If I place the loop outside the query, this solves the problem, but performance takes a hit from 3000 calls to the database.

I would greatly appreciate any insight to this problem-leaving the loop inside the query.

Technical solution-CF5, SQL Server 7.0

<CFOUTPUT>
<CFSET I=1>
<CFSET END=ArrayLen(structDailyCall.DAILYCALLS.CALLRECORD)>
<CFQUERY NAME=&quot;qGet_DailyCalls&quot; DATASOURCE=&quot;#DSN#&quot; DBTYPE=&quot;ODBC&quot; USERNAME=&quot;#UNAME#&quot; PASSWORD=&quot;#PW#&quot;>
<CFLOOP INDEX=&quot;I&quot; FROM=&quot;1&quot; TO=&quot;#END#&quot; STEP=&quot;1&quot;>
insert into DailyCalls
(
CallDateTime,
AnsweredDuration
)
values
(
'#TRIM(DateFormat(StructFind(structDailyCall.DAILYCALLS.CALLRECORD.CALLDATETIME, &quot;VALUE&quot,&quot;mm/dd/yyyy&quot)#
#TRIM(TimeFormat(StructFind(structDailyCall.DAILYCALLS.CALLRECORD.CALLDATETIME, &quot;VALUE&quot,&quot;hh:mm:sstt&quot)#',
#TRIM(StructFind(structDailyCall.DAILYCALLS.CALLRECORD.ANSWEREDDURATION, &quot;VALUE&quot)#
)
<CFSET I=I+1>
</CFLOOP>
</CFQUERY>
</CFOUTPUT>

 

[Sarky78]

how many records are in your array?

could it be that there are only 455 records in the array?

you dn;t need to increment the value of i each time through the loop the loop will take care of that for you -> step=1.

Tony

 

[aitai]

The number of records will always be about 3000 (100% certain). Without the counter, and error is thrown on the first loop after the last record.

Thanks