CFQuery timing out

Report · Apr 14, 2011

Does anyone know how to force a new database connection each time you execute cfquery?

I'm connected to an Oracle db, and they have configured a timeout on all db connections to 15 min.

My CF script does the following:

1. Queries db (takes 1 min)

2. (Away from the db), transforms data and writes it to a file. (This takes 30 min)

3. Queries db

I receive error: Error Executing Database Query.

[Macromedia][Oracle JDBC Driver][Oracle]ORA-02396: exceeded maximum idle time, please connect again

In CF Administrator, my Data Source has:

- Limit Connections: unchecked

- Maintained Connections: unchecked

- Max Pooled Statements: 0

- Timeout (min): 4

- Interval (min): 4

- Query Timeout (sec): 0

- Oracle Linked Servers: unchecked

I thought by unchecking Maintained Connections, it should force a new connection on each cfquery.

But, it doesn't, and I still get the error.

I am hoping that I don't have to put a try/catch block around every cfquery in all of my scripts

and loop back around to attempt the cfquery again. That will produce some messy code.

So, is there a way to force cfquery to use a new connection each time?

Or is there a way to connect to the db first (outside of cfquery), and pass the connection handle to cfquery?

Thanks.

myscreenname0345

Report · Apr 15, 2011

Have you looked @ what's happening with the connections @ the Oracle end of things? You should be able to profile this sort of thing. It seems curious that CF reckons it's closing the connection after use, and Oracle ain't closing it. TBH, I'd be more inclined to think CF is doing something wrong than Oracle is, though.

Given the length of time you say processing takes, I'm guessing it's doing a lot of data-crunching. I'd consider moving the entire process to the DB, rather than involving CF in it, to be honest. CF's for generating mark-up for websites, not data processing. Whereas a DB is for data processing. Best tool for the job 'n' all.

--

Adam

Report · Apr 15, 2011

Unfortunately, I don't have admin access to our Oracle instance.

But irregardless, Oracle is keeping the connection alive or else

it wouldn't send back a timeout message.

The processing time is mostly taken up by writing the data to a file.

I really don't think Oracle or CF is doing something wrong. I think I am!

I'm hoping someone will tell me whether I have my CF Administrator settings correct

to cause cfquery to use a new connection each time.

myscreenname0345

Report · Apr 15, 2011

If you don't have access I presume you've got DBAs there... ask them. it is their job, after all! I know sometimes it seems an Oracle DBAs job is to say "no" or "can't" or "my database" every time someone talks to them, but that's not actually true. they are theer to support the users of the DB, and you're one of the users.

Your settings look fine to me. I wouldn't be so quick to dismiss CF (or the underlying JDBC drivers) as doing something wrong.

But don't guess... check. Or get your DBAs to check.

--

Adam

Report · Apr 15, 2011

If the 2nd query does not depend on the creation of the file, you could always switch the order, or use cfthread, or something like that.