[Info-Ingres] Stored procedures and ODBC

[Gareth Williams]

Thanks Martin,
 
All,
 
>From any .net tool have you managed to return the return_value of an ingres stored procedure? i.e. value of type returnvalue and not just normal input/ouput parameters.
 
Thanks
 
Gareth
 

________________________________

From: info-ingres-bounces at kettleriverconsulting.com [mailto:info-ingres-bounces at kettleriverconsulting.com] On Behalf Of Martin Bowes
Sent: 07 September 2007 12:33
To: Gareth Williams
Cc: info-ingres at kettleriverconsulting.com
Subject: Re: [Info-Ingres] Stored procedures and ODBC



Hi Gareth,

 

As a general point, NEVER put a commit or rollback in a directly executed database procedure. Down that path lies madness. I suspect the ODBC is simply bitching about this.

 

You should also check the value of iierrornumber on entry to the procedure. Ensure you have all the locks you will need before the procedure really gets going.

 

Martin Bowes

 

From: info-ingres-bounces at kettleriverconsulting.com [mailto:info-ingres-bounces at kettleriverconsulting.com] On Behalf Of Gareth Williams
Sent: 07 September 2007 11:31
To: info-ingres at kettleriverconsulting.com
Subject: [Info-Ingres] Stored procedures and ODBC

 

 

I have this stored procedure...

 

create procedure testit as

begin

update test set col1 = date('now')

rollback;

end

 

When I run this from isql it works fine, when I run it from ODBC it throws an erro, and in the errlog.log it says:

 

E_QE0258_ILLEGAL_XACT_STMT   A commit/abort/rollback was attempted in a context where transaction statements are not allowed.

 

Heres the straight foreward calling code from visual basic .net

 

Dim cnn As New Odbc.OdbcConnection("dsn=imadb")

Dim com As New Odbc.OdbcCommand

com.Connection = cnn

com.CommandType = CommandType.StoredProcedure

com.CommandText = "Execute Procedure testit"

cnn.Open()

com.ExecuteNonQuery()

cnn.Close()

com.Dispose()

cnn.Dispose()

 

So I'm not wrapping the call in any transaction that I'm aware of, is this something to do with auto commit?

 

Cheers

 

Gareth

Cymraeg:- 
Mae'r neges hon yn gyfrinachol.Os nad chi yw'r derbynnydd y bwriedid y neges ar ei gyfer, byddwch mor garedig â rhoi gwybod i'r anfonydd yn ddioed. Dylid ystyried un rhywd datganiadau neu sylwadau a wneir uchod yn rhai personol,ac nid o angen rhaid yn rhai o eiddo Ymddiriedolaeth GIG Bro Morgannwg, nac unrhyw ran gyfansoddol ohoni na chorff cysylltiedig.

Cofiwch fod yn ymwybodol ei bod yn bosibl y bydd disgwyl i Ymddiriedolaeth GIG Bro Morgannwg roi cyhoeddusrwydd i gynnwys unrhyw ebost neu o hebiaeth a dderbynnir, yn unol ag amodau'r Ddeddf Rhyddid Gwybodaeth 2000. I gael mwy o wybodaeth am Ryddid Gwybodaeth, cofiwch gyfeirio at wefan Ymddiriedolaeth GIG Bro Morgannwg ar www.bromor-tr.wales.nhs.uk <http://www.bromor-tr.wales.nhs.uk/> 

English:- 
This message is confidential. If you are not the intended recipient of the message then please notify the sender immediately. Any of the statements or comments made above should be regarded as personal and not necessarily those of Bro Morgannwg NHS Trust, any constituent part or connected body.


Please be aware that, under the terms of the Freedom of Information Act 2000, Bro Morgannwg NHS Trust may be required to make public the content of any emails or correspondence received. For further information on Freedom of Information, please refer to the Bro Morgannwg NHS Trust website at www.bromor-tr.wales.nhs.uk <http://www.bromor-tr.wales.nhs.uk/> .

 


Cymraeg:- 
Mae'r neges hon yn gyfrinachol nad chi yw'r derbynnydd y bwriedid y neges ar ei gyfer, byddwch mor garedig â rhoi gwybod
i'r anfonydd yn ddi-oed. Dylid ystyried un rhywd datganiadau neu sylwadau a wneir uchod yn rhai personol,ac nid o angen rhaid yn rhai o 
eiddo Ymddiriedolaeth GIG Bro Morgannwg, nac unrhyw ran gyfansoddol ohoni na chorff cysylltiedig.

Cofiwch fod yn ymwybodol ei bod yn bosibl y bydd disgwyl i Ymddiriedolaeth GIG Bro Morgannwg roi cyhoeddusrwydd i gynnwys unrhyw ebost neu 
ohebiaeth a dderbynnir, yn unol ag amodau'r Ddeddf Rhyddid Gwybodaeth 2000. I gael mwy o wybodaeth am Ryddid Gwybodaeth, cofiwch gyfeirio 
at wefan Ymddiriedolaeth GIG Bro Morgannwg ar www.bromor-tr.wales.nhs.uk

English:- 
This message is confidential. If you are not the intended recipient of the message then please notify the sender immediately. 
Any of the statements or comments made above should be regarded as personal and not necessarily those of Bro Morgannwg NHS Trust, any 
constituent part or connected body.

Please be aware that, under the terms of the Freedom of Information Act 2000, Bro Morgannwg NHS Trust may be required to make public the 
content of any emails or correspondence received. For further information on Freedom of Information, please refer to the Bro Morgannwg NHS 
Trust website at www.bromor-tr.wales.nhs.uk.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.kettleriverconsulting.com/mailman/private/info-ingres/attachments/20070907/2734b6b7/attachment.html