[Openroad-users] Ingres and Stored Procedures
Bloomfield, Martin (PSD)
We are investigating the use of Crystal Enterprise Reports to deliver database information to our users via our intranet.
Some of our existing reports have numerous complex select loop queries which call sub procedures for each row retrieved. The consultant we had visit us did not know of any way to handle these select loops within Crystal. He has asked us if Ingres can use "stored procedures" to perform the calculations required for the report. [I'm not sure if he is wanting Ingres to do the calculations as the data is retrieved, or will get Crystal to call the procedure for each record]
I am only aware of Ingres having database procedures triggered by database rules on tables. Does anyone know if Ingres can use "stored procedures" and how we would create them? Alternatively, can anyone who has used Crystal Reports (from a web front-end) to call this type of complex report provide any assistance?
Many thanks for assistance,
Martin Bloomfield.
_______________________
Application Developer & Database Administrator
IT Branch,
Pesticides Safety Directorate,
YORK
Email: martin.b...@psd.defra.gsi.gov.uk
Tel: (01904) 455783 GTN: 5138 5783
Fax: (01904) 455711
Website: www.pesticides.gov.uk
"Department for Environment, Food and Rural Affairs (Defra)
This email and any attachments is intended for the named recipient only. Its unauthorised
use, disclosure, storage or copying is not permitted. If you have received it in error, please
destroy all copies and inform the sender. Whilst this email and associated attachments will
have been checked for known viruses whilst within Defra systems we can accept no
responsibility once it has left our systems. Communications on Defra's computer systems
may be monitored and/or recorded to secure the effective operation of the system and for
other lawful purposes."
Bloomfield, Martin (PSD)
Martin
Bloomfield.
-----Original Message-----
From:
Kent Smith [mailto:ksm...@ipsoinc.com]
Sent: 26 November 2003
15:11
To: Bloomfield, Martin (PSD); 'Openroad-Users'
Cc:
Park, Andy (PSD); Merry, Tracey (PSD)
Subject: RE: [Openroad-users]
Ingres and Stored Procedures
Ingres definitely supports stored procedures. I haven't worked with Crystal enough to know how Crystal would call them, but I expect that it is possible. There are limitations with stored procedures compared with Sybase or Oracle, and depending on the version of Ingres that you are running on your server. Ingres has only recently been able to return an array - it used to be limited to a non-array list of values. This made it difficult to have a SP run all the logic and just return a result set. I believe with Ingres II, this restriction is lifted.You create a procedure the same way you would create on to be fired off by a rule. To execute it, use the sql statement: "execute procedure_name".--Kent==================
Kent Smith
IPSO Incorporated
http://www.ipsoinc.com
Phone: +1-508-877-1401
AIMchat: ipsokent
Gerner, Allen
************************************ This email and any files transmitted with it are proprietary and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of ITT Industries, Inc. The recipient should check this email and any attachments for the presence of viruses. ITT Industries accepts no liability for any damage caused by any virus transmitted by this email. ************************************ |
Kent Smith
Kent Smith
==================
Kent Smith
IPSO Incorporated
http://www.ipsoinc.com
Phone: +1-508-877-1401
AIMchat:
ipsokent
-----Original Message-----
From: openroad-us...@peerlessit.com [mailto:openroad-us...@peerlessit.com]On Behalf Of Gerner, Allen
Sent: Monday, December 01, 2003 9:58 AM
To: Openroad-Users
Subject: [Openroad-users] RE: Ingres and Stored ProceduresHi Martin,I noticed that you said you wanted to make a stored procedure. I wanted to ask if it was possible to make this an extended stored procedure that you could put in a dynamic link library. It would be cool if you could put a trigger in a DLL as well, but I may be way off with that.
-----Original Message-----