Problems reusing query

[lespaul]

has anyone run into problems reusing a query? I have a button that performs several updates. Instead of having a query for each update I clear the SQL, add the SQL, execute the query then clear the SQL, add different SQL and execute. I haven't had any issues with using this technique in the past. However, recently I have found that some queries get skipped! Not all the time, just sometimes. For example, I have the following process:

procedure TfrmPanelRelease.btnProcessReleaseClick(Sender: TObject);
var
strPanelID : string;
begin
  strPanelID := cbPanelList.Items[cbPanelList.ItemIndex];
  with qryGetPanels do
  begin
    SQL.Clear;
    SQL.Add('SELECT * FROM JMPTRIAL WHERE PANELID = ' + QuotedStr(strPanelID) + ' AND JURYSEL = ''N''');
    Active := True;
    if isempty then
    begin
      [b]SQL.Clear;
      SQL.Add('UPDATE JMPNEWHOUR SET TIMEOUT = ' +
      FormatDateTime('HHMM', dtpReleaseTime.Time) +
      ' WHERE JURNUM IN (SELECT JMPNEWHOUR.JURNUM FROM JMPNEWHOUR INNER JOIN JMPMAIN ON ' +
      'JMPNEWHOUR.JURNUM = JMPMAIN.JURNUM WHERE SERVDAT = ' +
      QuotedStr(frmMain.InfoArray[frmMain.TodayDate]) + ' AND FREE <> ''N'' AND PANELID LIKE ''' + 
      strPanelID + '%'')');
      ExecSQL;
      // above bolded statement executed[/b]
      [b][COLOR=red]SQL.Clear;
      SQL.Add('UPDATE JMPDLYPANL SET AVAILABL = ''N'', HRSRECRD = ''Y'' WHERE PANELID =' + QuotedStr(strPanelID) 
      + ' AND SERVDAT = ' +
      QuotedStr(frmMain.InfoArray[frmMain.TodayDate]));
      ExecSQL;
      frmMain.RecordChanges(frmMain.InfoArray[frmMain.JMSID], 'PANELS', 'SUCCESS:Release ' + strPanelID);
      //above red statements did not executed[/color][/b]
      [b]PrintRollTime(cbPanelList.Items[cbPanelList.ItemIndex], frmMain.InfoArray[frmMain.TodayDate]);
      //bolded statements executed[/b]
    end
    else
    begin
      frmMain.RecordChanges(frmMain.InfoArray[frmMain.JMSID], 'PANELS', 'FAILED:Release ' + strPanelID);

      ShowMessage('Panel ' + strPanelID + ' is assigned to ' +
       FieldbyName('CASPRE').AsString + FieldByName('CASNUM').Asstring +
       ' and the jury has not been selected.  Please select the jury before releasing the panel.');
    end;
  end;
  Close;
end;

Yesterday, when this function was processed in the morning, the red section did not run, but the two bolded areas above and below did! I can tell this because the first update statement results are in the database and the PrintRollTime function prints a report. But the other update statement and insert statement, the results are missing. But when it was run in the afternoon, all processed completed normally.

any ideas?

Thanks,
leslie

 

[ChrisGSchultz]

G'day Leslie,

The only thing I do that is different is to close the query prior to modifying it, then opening it when ready to execute.
viz:

  with mySQL do begin
    close;
    clear;
    add('select * from mysql_file');
    open;
  end;

I know that the ExecSQL should be used for UPDATE, DELETE etc, but its a thought.....

If the ExecSQL above the red area results in an error, the query may close, and the statements below will not execute.
You may have to explicitly open the query before calling those statements.

You could also check your dataset state before and after the operation.


Chris ;-)

 

[LucieLastic]

hi

You don't need to do a .open when using ExecSql - ExecSql is only used when no data is returned and Open is used when a dataset is returned.

Just a thought, but try putting some Application.processmessages to sort out any windows messages which may be pending (eg for the logging, screen messages etc).

I can't think why your query didn't run, I've a feeling the problem is elsewhere, eg table locked or something. It may be wise to emulate this coding in a test app and check it that way.

What db are you using?

lou

 

[lespaul]

the backend is an AS400 (have you heard about the Platypus database thread655-606833? this is it)

I can't replicate the error and my office mate has mentioned some other weird things happening in some of his delphi apps.

Part of the problem is that we recently moved the court to a new much bigger building; at the time of the move we changed our network setup, upgraded Lotus Notes, and upgraded the AS400. So there's no telling what's going on! I just thought if anyone else had experienced something similar, maybe I could narrow down the cause!

thanks anyway!

Leslie