RE: dealing with lengthy (time) query sets

Since you don't give any information about your BD, I will give you the
way I would do it with Oracle.

I would put the 'x' records into a QUEUE table. Upon entry into the
queue, a DBMS_JOB would start and process the row. Errors can be
generated to the instance alert log, or notification could be sent to a
management console, etc. In any case, by pushing the row into the QUEUE
table, further processing is asynchronous and your program would not
have a delay.

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: WoodShop [mailto:woodshop2300@xxxxxxxxx]
Sent: Saturday, April 07, 2007 1:03 AM
To: dbi-users@xxxxxxxx
Subject: dealing with lengthy (time) query sets

I currently have script/program that simply takes input lines and does
work on them.
updating tables accordingly and all is good.
however every 'x' input lines i need to call a subroutine that does some
work on the database that takes a while, like 1 or 2 min. however i
can't have the program become unresponsive for that long.

My original idea was to spawn a thread off the subroutine and then it
could go do all the work it likes leaving the main section to continue
on chugging through input. However when I tried this i got errors about
"""handle 3 is owned by thread 61f010 not current thread 11deb70
(handles can't be shared between threads and your driver may need a
CLONE method added) """

So i gave up on that and figured i do it with fork, however there are
apparently issues with keeping the database connection associated with
that.

My last resort which i know works is to make another script that makes
its own database connection does its work and dies. I would run this
script when i need to by making exec "..." calls and passing my
parameters via the command line args. I'v done this before and i know it
works, but it just seems ugly and hacked together and i can't help but
think there must be a better way, i can't be the only person that has to
deal with situations like this..

--
------------------------------------
Alex Anagnos
Student: Northern Michigan University
woodshop2300@xxxxxxxxx
------------------------------------


This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.

.

Relevant Pages

  • Re: Database/Email Script
    ... PHP programmer during the interview. ... Still they have given me a script ... //With the database connection open, I start to insert the data from ... //This sets the query to a variable for easy calling ...
    (comp.lang.php)
  • Re: Is this group dying?
    ... application domain for the language is defined... ... I guess a script object could represent a task, ... single-producer/consumer unbounded wait-free queue which uses no ... The dispatcher queue schedules the tasks to ...
    (comp.programming.threads)
  • Error Sending To MSMQ queue from a Clustered SQL Server
    ... I had this simple task to do: "To have a trigger that sends a message to a queue under certain circunstances...". ... But when I try to migrate this solution to a clustered environment ... I tried to migrate this solution to a similar cluster environment, but with MSMQ installed on cluster instead of locally and still get the same error. ... The weard thing is that if I execute a DTS with an ActiveX Script Task which does the same as the script above. ...
    (microsoft.public.sqlserver.programming)
  • Re: MF Server rts32 does not run in AIX 5.3L batch queue
    ... I can't redirect output to /dev/null as the program running under the ... configuration of the queue device for the job queue. ... > If your script has a command like ...
    (comp.lang.cobol)
  • Weird TNS error with DBI::Oracle 1.21 & oracle 11G
    ... Database connection not made: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor at ./testdbi.pl line 9. ... This script works correctly on our old server running 8.1.6. ... The Oracle environment variables are set properly; sqlplus works just fine when invoked using the 'user'@'tnsname_of_server' login, another indication that TNSNames is working. ... sub PrintHeader { ...
    (perl.dbi.users)