We turned off 'maintain connections' for the DSN for this app and
bamm! workie works!
Doug
On Thu, 11 Nov 2004 07:53:39 -0600, Greg Morphis <[EMAIL PROTECTED]> wrote:
> Well maybe the problem is with Oracle but the root of it isnt Oracle..
> it's something with the way CF builds those queries and loads those
> files.
>
>
>
>
> On Thu, 11 Nov 2004 07:48:11 -0600, Greg Morphis <[EMAIL PROTECTED]> wrote:
> > The problem isnt with Oracle.. I wrote 2 non-dynamic queries and
> > uploaded several documents in a row. So it has to be within the query
> > and the building of the dynamic query structure.
> >
> >
> >
> >
> > On Thu, 11 Nov 2004 07:07:20 -0600, Greg Morphis <[EMAIL PROTECTED]> wrote:
> > > 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
> > >
> >
> >
> > --
> > Auxilium meum a Domino
> >
>
> --
> 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:183948
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=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
