[Info-ingres] Occasional SELECT statement error.

Thu Apr 30 08:27:17 UTC 2020

Hi,

Apart from my mild surprise that you don’t have to prefix target variables
in the select statement with colons that looks pretty straight forward.

Recently we have noticed an increase in odd error messages being generated
by what seem like innocent enough statements.

Typically in some sort of batch context it tosses an error then stops.

If you take the offending statement and run it in isolation it just works -
its been too difficult to put a test case together so we have just moved on.

What is the target DBMS and OS / Hardware?

I’m afraid I’m not going to be able to help much other than to encourage
you to use the flight recorder and wait for the next instance.

Sorry,

Adrian

From: info-ingres-bounces at lists.planetingres.org <info-ingres-bounces at lists.
planetingres.org> On Behalf Of Darren Harvey
Sent: 29 April 2020 23:39
To: info-ingres at lists.planetingres.org
Subject: Re: [Info-ingres] Occasional SELECT statement error.

The Procedure is an OpenRoad procedure, which I have attached.

I have looked at the customers errlog.log file and there is nothing in it
which corresponds with the timing of when the error occurred.

Thanks for all the responses I’ve had.

Cheers

Darren

Darren Harvey

Senior Developer

M +61 400 398 188
P +61 3 9922 5519
Teams or Skype for Business ID darren.harvey at fusion5.com.au
<mailto:darren.harvey at fusion5.com.au>

<https://www.fusion5.com.au?utm_source=F5-Int.Sig&utm_medium=email&utm_campa
ign=AskUs2020/> www.fusion5.com.au
<https://www.linkedin.com/company/fusion5>
<https://www.instagram.com/jemini_hrpayroll_software/>

<http://business.fusion5.com.au/HCM2020?utm_source=F5-Int.Sig&utm_medium=ema
il&utm_campaign=HCM2020Conf>

Fusion5 | Level 27, 570 Bourke Street, Melbourne, 3000, Australia

This email and any attachments are confidential and intended exclusively for
the person to whom the email is addressed. Please see our
<https://www.fusion5.com.au/privacy-policy/> Privacy Policy

Are you interested in Fusion5’s COVID-19 business update?　
Please visit  <https://www.fusion5.com.au/covid-19-update/>
https://www.fusion5.com.au/covid-19-update/

From: Adrian Williamson <adrian.williamson at rationalcommerce.com
<mailto:adrian.williamson at rationalcommerce.com> >
Sent: Wednesday, 29 April 2020 6:26 PM
To: Darren Harvey <Darren.Harvey at fusion5.com.au
<mailto:Darren.Harvey at fusion5.com.au> >; info-ingres at lists.planetingres.org
<mailto:info-ingres at lists.planetingres.org> ; 'Martin Bowes'
<martin.bowes at ndph.ox.ac.uk <mailto:martin.bowes at ndph.ox.ac.uk> >
Subject: RE: [Info-ingres] Occasional SELECT statement error.

Hi Darren

Is that a table returning database procedure or OpenROAD?

If the errlog provides no clues then I‘d be tempted to use PRINTQRY or the
flight recorder to understand what arrived at the database (if it is
OpenROAD).

Can you show the full source of the procedure?

Cheers

Adrian

From: info-ingres-bounces at lists.planetingres.org
<mailto:info-ingres-bounces at lists.planetingres.org>
<info-ingres-bounces at lists.planetingres.org
<mailto:info-ingres-bounces at lists.planetingres.org> > On Behalf Of Darren
Harvey
Sent: 29 April 2020 09:07
To: info-ingres at lists.planetingres.org
<mailto:info-ingres at lists.planetingres.org> ; Martin Bowes
<martin.bowes at ndph.ox.ac.uk <mailto:martin.bowes at ndph.ox.ac.uk> >
Subject: Re: [Info-ingres] Occasional SELECT statement error.

Hi Martin

Thanks for your response, and I had not thought of checking the errlog file,
so I will get it and look for any errors at the time the process is run.

Thanks

Darren

Get Outlook for Android <https://aka.ms/ghei36>

  _____

From: Martin Bowes <martin.bowes at ndph.ox.ac.uk <mailto:martin.bowes at ndph.ox.
ac.uk> >
Sent: Wednesday, April 29, 2020 4:56:59 PM
To: Darren Harvey <Darren.Harvey at fusion5.com.au
<mailto:Darren.Harvey at fusion5.com.au> >; info-ingres at lists.planetingres.org
<mailto:info-ingres at lists.planetingres.org>
<info-ingres at lists.planetingres.org
<mailto:info-ingres at lists.planetingres.org> >
Subject: RE: [Info-ingres] Occasional SELECT statement error.

Hi Darren,

That’s not a lot to go on.

As a guess there is some condition that occurs that allows your procedure to
branch into a rarely executed section of code which contains the syntactic
error that causes this popup. For example a deadlock may occur, your
procedure freaks. In this case you would see some deadlock errors reported
in the errlog.

Have you tried correlating errors in the errlog with the failed execution of
this procedure?

Martin Bowes

From: Darren Harvey <Darren.Harvey at fusion5.com.au
<mailto:Darren.Harvey at fusion5.com.au> >
Sent: 29 April 2020 04:44
To: info-ingres at lists.planetingres.org
<mailto:info-ingres at lists.planetingres.org>
Subject: [Info-ingres] Occasional SELECT statement error.

Hi All

We are getting the following popup message occasionally at customers sites.
The process that calls the procedure that contains the SELECT statement
(screenshot below) is an end of week process, and we have customers who run
the process without the error for lots of weeks, and then one week they get
the error message, and then the next time they run the process there is no
popup error message.

Does anyone have any suggestions/thoughts as to why this problem doesn’t
occur every time the process is run, but is just an occasional problem?

Thanks

Darren

Error Message

SELECT statement in procedure that is getting error.

The ACTIAN Generic Error Code for -3200 is described as follows:

Darren Harvey

Senior Developer

M +61 400 398 188
P +61 3 9922 5519
Teams or Skype for Business ID darren.harvey at fusion5.com.au
<mailto:darren.harvey at fusion5.com.au>

<https://www.fusion5.com.au?utm_source=F5-Int.Sig&utm_medium=email&utm_campa
ign=AskUs2020/> www.fusion5.com.au
<https://www.linkedin.com/company/fusion5>
<https://www.instagram.com/jemini_hrpayroll_software/>

<http://business.fusion5.com.au/HCM2020?utm_source=F5-Int.Sig&utm_medium=ema
il&utm_campaign=HCM2020Conf>

Fusion5 | Level 27, 570 Bourke Street, Melbourne, 3000, Australia

This email and any attachments are confidential and intended exclusively for
the person to whom the email is addressed. Please see our
<https://www.fusion5.com.au/privacy-policy/> Privacy Policy

Are you interested in Fusion5’s COVID-19 business update?　
Please visit  <https://www.fusion5.com.au/covid-19-update/>
https://www.fusion5.com.au/covid-19-update/

Message protected by MailGuard: e-mail anti-virus, anti-spam and content
filtering.
http://www.mailguard.com.au/mg

Report this message as spam
<https://console.mailguard.com.au/ras/1Y224Axk04/692AzJtZ3fqYQws7UzQf14/0.2>

Message protected by MailGuard: e-mail anti-virus, anti-spam and content
filtering.
http://www.mailguard.com.au/mg

Report this message as spam
<https://console.mailguard.com.au/ras/1Y238TVa5Q/43QtGy8T54pRPLTQhucVrJ/0.2>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20200430/7cd590d1/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 974 bytes
Desc: not available
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20200430/7cd590d1/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 3560 bytes
Desc: not available
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20200430/7cd590d1/attachment-0001.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image003.jpg
Type: image/jpeg
Size: 43812 bytes
Desc: not available
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20200430/7cd590d1/attachment.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image004.jpg
Type: image/jpeg
Size: 12462 bytes
Desc: not available
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20200430/7cd590d1/attachment-0001.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image005.jpg
Type: image/jpeg
Size: 26556 bytes
Desc: not available
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20200430/7cd590d1/attachment-0002.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image006.jpg
Type: image/jpeg
Size: 10986 bytes
Desc: not available
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20200430/7cd590d1/attachment-0003.jpg>