Home » Developer & Programmer » Forms » Execute_query causes hang in db connection
icon13.gif  Execute_query causes hang in db connection [message #333301] Fri, 11 July 2008 03:14 Go to next message
kathrine78
Messages: 6
Registered: July 2008
Location: Norway
Junior Member
Hi Smile

I hope someone here can help me with my problem.
I have trouble with loosing a db connection, everything hangs on initializing the form (all this code is in the form program unit)

I navigate to a block with go_block and execute_query to fill it with contents. However, as I'm trying to fill the block with an image (and for some reason this doesn't work, problems with tiff and the oracle viewer), the entire window freezes Sad

My question is; is there some way to handle this as it is the execute_query that causes the hang?

I've tried:
1) go_block('blockname');
execute_query;
if not form_success then
raise form_trigger_failure;
end if;

2) begin
go_block('blockname');
execute_query;
exception when others then
raise form_trigger_failure;
end;

I just cannot seem to handle rolling back og controlling the datablock filling... Sad
Hope anyone can help me Smile

PS: I've tried rollback also...
Re: Execute_query causes hang in db connection [message #333310 is a reply to message #333301] Fri, 11 July 2008 03:47 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
TIFF format may cause Forms hang.

What happens if you convert TIFF into a JPG (try not to use progressive encoding option), store this JPG image into the database and load it using EXECUTE_QUERY once again? Any improvement?
Re: Execute_query causes hang in db connection [message #333324 is a reply to message #333310] Fri, 11 July 2008 04:31 Go to previous messageGo to next message
kathrine78
Messages: 6
Registered: July 2008
Location: Norway
Junior Member
I know tiff may cause problems, but we usually just open the tiff file in another tool, change it a bit (color depth e.g.) and save, upload it to the db again - and everything works Smile

However, I would like to have my form handle the problem (when this occurs, this happens now and again); maybe by rolling back and just displaying the form without the picture... Just to prevent the form to hang.
Any idea how to control or set timeout or something incase the tiff image cuses hang in execute_query?
Re: Execute_query causes hang in db connection [message #333327 is a reply to message #333324] Fri, 11 July 2008 04:41 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Using a TIMER, perhaps?
Re: Execute_query causes hang in db connection [message #333347 is a reply to message #333327] Fri, 11 July 2008 05:58 Go to previous messageGo to next message
kathrine78
Messages: 6
Registered: July 2008
Location: Norway
Junior Member
But how can I set a timer to timeout an execute_query?
It seems like the db connection is lost as the tiff is not loaded ( I got the dba people here to check my status in the system, and I was not found)...

Thank you for brainstorming with me Smile Kathrine Smile
Re: Execute_query causes hang in db connection [message #333355 is a reply to message #333347] Fri, 11 July 2008 06:39 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Of course ... stupid me; what was I thinking about? Perhaps it's timer for me to exit.

Hm, I'm sorry, but I can't suggest anything else at the moment. Perhaps someone else will be able to help you.
Re: Execute_query causes hang in db connection [message #333360 is a reply to message #333355] Fri, 11 July 2008 06:52 Go to previous messageGo to next message
kathrine78
Messages: 6
Registered: July 2008
Location: Norway
Junior Member
Hehe, thank u anyway Smile

Anyone else? Holla at me Smile
Re: Execute_query causes hang in db connection [message #333671 is a reply to message #333360] Mon, 14 July 2008 02:00 Go to previous messageGo to next message
kathrine78
Messages: 6
Registered: July 2008
Location: Norway
Junior Member
Still hoping someone can help me Smile
Re: Execute_query causes hang in db connection [message #334113 is a reply to message #333671] Tue, 15 July 2008 08:15 Go to previous messageGo to next message
kathrine78
Messages: 6
Registered: July 2008
Location: Norway
Junior Member
Anyone? Confused
Re: Execute_query causes hang in db connection [message #339140 is a reply to message #334113] Wed, 06 August 2008 21:19 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Did you solve your problem? What version of Forms and operating system are you using?

David
Re: Execute_query causes hang in db connection [message #357907 is a reply to message #339140] Fri, 07 November 2008 02:51 Go to previous messageGo to next message
seim
Messages: 3
Registered: November 2008
Junior Member
Hi, I have got the same problem and am just going to solve it for my db module.. After some time of digging on the web, i found only two interesting web sites:

1) http://www.experts-exchange.com/Database/Oracle/Q_10187879.html
"You can always poll the connection using a some kind of a timer mechanism.
For instance, set a thread in you program to check the tcp/ip connection to the host (using ICMP messages - aka ping or even tnsping) every x seconds.
Despite the network overhead, it seems like the only way to achieve what you're asking for(since you can't get it directly from Oracle)."

2) http://www.akadia.com/services/ora_net8_taf.html
"In addition to what gets preserved and lost during a failover, there are some connectivity issues to worry about. If a node goes down, your application may not notice it, and TAF may not be triggered until your application attempts to execute another SQL statement. A hung listener might cause a client to hang during a connection attempt. In that case, the client will never get a chance to attempt a connection to the backup instance. If the primary instance is up, but in an indeterminate state (such as during a startup or shutdown) client connections will fail, but not in a way that causes TAF to be triggered. Using ALTER SESSION to kill a client connection will also prevent TAF from being triggered. "

Now I'll try following:

- make some threads just for periodical tests executing 'SELECT 1 FROM DUAL' and hope, that after more failures the hanging ones will wake up
- make some proxy thread controlling the worker's thread and watching some timeout for killing the worker
Re: Execute_query causes hang in db connection [message #358103 is a reply to message #357907] Sat, 08 November 2008 10:20 Go to previous message
seim
Messages: 3
Registered: November 2008
Junior Member
"- make some threads just for periodical tests executing 'SELECT 1 FROM DUAL' and hope, that after more failures the hanging ones will wake up"

First idea doesn't seem functional. When using SCP (StatelessConnectionPool), SCP generates Connection* objects without any hesitation after connection lost and executing Statements* via this Connection* objects hangs in as much threads as you'll try to use. This situation persists for 30minutes (system TCP settings).

- make some proxy thread controlling the worker's thread and watching some timeout for killing the worker

If you have to write a robust system and need exactly result of a database operation or error (or hint about failure), I thing this is the only way, how to do it with the hanging thread behaviour..n Razz


Another notes:
- if you want to create Environment* and SCP* instances, the connection to DBS must be OK, or you'll get immediately an error
- if the connection became broken, your instances of Env & SCP are no longer usable (after connection restoration yea, but you'll save some timeouts if you don't register and test the global database connection state) => perhaps better would be to release them, a peridically try to get new ones..
Previous Topic: How to overcome this error??? FRM - 92101
Next Topic: combobox
Goto Forum:
  


Current Time: Thu Dec 08 10:22:50 CST 2016

Total time taken to generate the page: 0.13173 seconds