ColinH
Posts: 15
|
| Posted: 12/11/2006, 7:50 AM |
|
Hi
Can I add a SQL statement that does 3 different things to an event. If so what is the format?
For example, here is what I have so far...
Set Connection = New clsDBConnection1
Connection.Open
SQL ="update osm_prev_mnt_gmains" & set next_sched_date=start_date+freq; &
update osm_prev_mnt_gmains &
set create_wo=1 where sysdate>next_sched_date; &
update osm_prev_mnt_gmains &
set reschedule_reqd=1 where freq>0 and sysdate>next_sched_date; &
commit
Connection.Execute(SQL)
Thanks, Colin
|
 |
 |
Edd
Posts: 547
|
| Posted: 12/11/2006, 1:44 PM |
|
Colin,
It might work, but it is not practical as you can't test if a transaction has failed.
Stick a "Connection.Execute(SQL)" - it will not change much to the performance of the system.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
ColinH
Posts: 15
|
| Posted: 12/12/2006, 7:40 AM |
|
Thanks, Edd.
I got it to work - custom code used is shown below.
Questions I have for everyone are:
1. Is there a better way to do this? (Although many small steps is probably better for my brain)
2. Is there a way to send an event to the server to execute a SQL procedure a la - sqplplus user/password@connection @schedule_updater.sql
Working custom code - open for your comments to help me improve:
' Write your own code here.
Dim SQL
Dim Connection
Dim ErrorMessage
if OSM_PREV_MNT_GMAINS.Errors.Count > 0 Then Exit Function
Set Connection = New clsDBConnection1
Connection.Open
' First set the next_sched_date
SQL ="update osm_prev_mnt_gmains set next_sched_date=start_date+freq where PIPE_ID=" & OSM_PREV_MNT_GMAINS.PIPE_ID.Value
Connection.Execute(SQL)
'ErrorMessage = CCProcessError(Connection)
'Response.Write SQL & " " & ErrorMessage
'Response.end
Connection.Close
Set Connection = New clsDBConnection1
Connection.Open
' Then set the WO Required flag
SQL ="update osm_prev_mnt_gmains set create_wo=1 where sysdate>next_sched_date AND PIPE_ID=" & OSM_PREV_MNT_GMAINS.PIPE_ID.Value
Connection.Execute(SQL)
'ErrorMessage = CCProcessError(Connection)
'Response.Write SQL & " " & ErrorMessage
'Response.end
Connection.Close
Set Connection = New clsDBConnection1
Connection.Open
' Then set the Reschedule Required flag
SQL ="update osm_prev_mnt_gmains set reschedule_reqd=1 where freq>0 and sysdate>next_sched_date AND PIPE_ID=" & OSM_PREV_MNT_GMAINS.PIPE_ID.Value
Connection.Execute(SQL)
'ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
'Response.Write SQL & " " & ErrorMessage
'Response.end
On Error Goto 0
' -------------------------
'End Custom Code
|
|
|
|
Edd
Posts: 547
|
| Posted: 12/12/2006, 2:15 PM |
|
Colin
I suggest the following - you don't need to open and close the connection.
You also could have made this a function passing the connection and SQL if you wanted.
' Write your own code here.
Dim SQL
Dim Connection
Dim ErrorMessage
if OSM_PREV_MNT_GMAINS.Errors.Count > 0 Then Exit Function
Set Connection = New clsDBConnection1
Connection.Open
' First set the next_sched_date
SQL ="update osm_prev_mnt_gmains set next_sched_date=start_date+freq where PIPE_ID=" & OSM_PREV_MNT_GMAINS.PIPE_ID.Value
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
If ErrorMessage > "" Then
Connection.Close
Set Connection = Nothing
OSM_PREV_MNT_GMAINS.Errors.AddError SQL & " " & ErrorMessage
Exit Function
End If
' Then set the WO Required flag
SQL ="update osm_prev_mnt_gmains set create_wo=1 where sysdate>next_sched_date AND PIPE_ID=" & OSM_PREV_MNT_GMAINS.PIPE_ID.Value
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
If ErrorMessage > "" Then
Connection.Close
Set Connection = Nothing
OSM_PREV_MNT_GMAINS.Errors.AddError SQL & " " & ErrorMessage
Exit Function
End If
' Then set the Reschedule Required flag
SQL ="update osm_prev_mnt_gmains set reschedule_reqd=1 where freq>0 and sysdate>next_sched_date AND PIPE_ID=" & OSM_PREV_MNT_GMAINS.PIPE_ID.Value
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
If ErrorMessage > "" Then
Connection.Close
Set Connection = Nothing
OSM_PREV_MNT_GMAINS.Errors.AddError SQL & " " & ErrorMessage
Exit Function
End If
Connection.Close
Set Connection = Nothing
On Error Goto 0
' -------------------------
'End Custom Code
Regards
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
