I've got an upload utility I'm using to allow the client to upload a
txt file which could go into 1 of 33 different tables.. the reports
are structured so differently it's impossible to put them all into one
table.
After uploading the first document (304 rows), I try to upload another
this one has 294. It gets to about 190-200 rows before crapping out on
me.
Cold Fusion reports "maximum opened cursors exceeded".
I did some research and checked with our DBA.. Our cursor limit is set at 1500.
I've looked over my code and cant see an issue with it.
I uploaded 2 documents each with 1 row without a problem.
I've uploaded 1 document with 800+ rows without a problem.
I've uploaded a document of 1600 rows with no problem. It's real odd, when
I get the Error, if I click on the link to take me back to the page
and try again.. it works.. but then if I follow it with another file..
it bombs out on me.
Has anyone seen this before and can anyone suggest an idea.
I'd like to ask for an increase of the cursor limit as a last resort.
<code>
<cfset isProblem = "no">
<cftransaction action="begin">
<cftry>
<cfquery name="getSweepID" datasource="#application.config.getDSN()#">
SELECT seq_sweep_id.nextval AS id FROM DUAL
</cfquery>
<!--- insert main record --->
<cfquery name="sweepInsert" datasource="#application.config.getDSN()#">
INSERT INTO SWEEP_MAIN
(
SWEEP_ID, PHONE_ID, ACTIONUSER, ACTIONDATE, CATEGORY_ID
)
VALUES
(
<cfqueryparam value="#getSweepId.id#"
cfsqltype="cf_sql_numeric">,
<cfqueryparam value="#form.phoneid#"
cfsqltype="cf_sql_numeric">,
<cfqueryparam value="#session.user.emplid#"
cfsqltype="cf_sql_varchar">,
sysdate,
<cfqueryparam value="#form.category_id#"
cfsqltype="cf_sql_numeric">
)
</cfquery>
<!--- get table name --->
<cfquery name="getTable" datasource="#application.config.getDSN()#">
SELECT sweep_table AS tbl_name FROM CATEGORY_SWEEP
WHERE category_id = <cfqueryparam value="#form.category_id#"
cfsqltype="cf_sql_numeric">
</cfquery>
<!--- get table columns --->
<cfquery name="getCols" datasource="#application.config.getDSN()#">
SELECT collate(t.column_name) as columns
FROM all_tab_columns t
WHERE t.table_name= '#getTable.tbl_name#'
ORDER BY column_id
</cfquery>
<!--- loop over record and insert into table --->
<cfset strLen = ListLen(getCols.columns,',')>
<cfset counter = 1>
<cfoutput>Please Wait Loading...<br/></cfoutput>
<cffile action="read"
file="#cffile.serverDirectory#\#cffile.serverFile#"
variable="fileData">
<cfloop index="record" list="#fileData#" delimiters="#chr(10)##chr(13)#">
<cfif counter gt 1>
<cfquery name="qryInsert"
datasource="#application.config.getDSN()#">
INSERT INTO #getTable.tbl_name# (#getCols.columns#)
VALUES
(
<cfqueryparam value="#getSweepId.id#"
cfsqltype="cf_sql_numeric">,
<cfloop from="1" to="#strLen-2#" index="i">
<cfoutput>'#replace(evaluate("listgetat(record,#i#,chr(9))"),'"','',"all")#'</cfoutput>,
</cfloop>
'#evaluate("listgetat(record,#strLen-1#,chr(9))")#'
)
</cfquery>
</cfif>
<cfset counter = counter + 1>
</cfloop>
<CFCATCH type="Any">
<cfoutput>
<h3>Oops, There's been an error</h3>
Please try again, if the problem persists <br/>
notify the administrator<br/>
Message :#CFCATCH.message#<p>
Type :#CFCATCH.type#<p>
<cfset isProblem = "yes">
<p<i>Rolling back the attempted transaction</i></p>
<cftransaction action="ROLLBACK" />
<cfquery name="delFile"
datasource="#application.config.getDSN()#">
DELETE FROM DOCUMENTS WHERE DOCID = <cfqueryparam
cfsqltype="cf_sql_numeric" value="#docid#">
</cfquery>
</cfoutput>
</CFCATCH>
</cftry>
<cfif isproblem eq "no">
<cftransaction action="COMMIT" />
<script language="Javascript">
<!--
alert("Finished: <cfoutput>#counter-2#</cfoutput> rows added");
location.href="../upload/"
-->
</script>
</cfif>
</cftransaction>
</cfif>
</code>
--
Auxilium meum a Domino
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183943
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
