Access Violation in nxServer using StoredProcedure

I have the following very simple stored procedure:

DROP ROUTINE IF EXISTS "GetMyRecord";

CREATE PROCEDURE "GetMyRecord" ()
READS SQL DATA -- needs either read-access
--MODIFIES SQL DATA -- or write access
begin
select * from MyRecord;
END


MyRecord is a view.

If I call this SP from EnterpriseManager I get the results I expect.
When I go to Delphi and drop a TnxStoredProc and set the Database and
the StoresProcName and try to execute or open the stored proc I get
the following error message. This occurs at runtime, or at design
time if I set ActiveDesigntime to TRUE.

---------------------------
Error
---------------------------
NexusDB: nxStoredProc1: Query execution failed:
Unexpected exception object raised: [EAccessViolation] Access
violation at address 006C72F7 in module 'nxServer.Exe'. Read of
address 00000039 [$3CA1/15521].
---------------------------
OK
---------------------------


To make sure I had all other settings correct, I called another SP
that inserts data for MyRecord (adds data to multiple tables), all
worked as expected.

This is my first attempt to return a result set using a Select. Am I
totally missing something?

Mack

Mack wrote:

> I have the following very simple stored procedure:
>
> DROP ROUTINE IF EXISTS "GetMyRecord";
>
> CREATE PROCEDURE "GetMyRecord" ()
> READS SQL DATA -- needs either read-access
> --MODIFIES SQL DATA -- or write access
> begin
> select * from MyRecord;
> END
>
>
> MyRecord is a view.
>
> If I call this SP from EnterpriseManager I get the results I expect.
> When I go to Delphi and drop a TnxStoredProc and set the Database and
> the StoresProcName and try to execute or open the stored proc I get
> the following error message. This occurs at runtime, or at design
> time if I set ActiveDesigntime to TRUE.
>
> ---------------------------
> Error
> ---------------------------
> NexusDB: nxStoredProc1: Query execution failed:
> Unexpected exception object raised: [EAccessViolation] Access
> violation at address 006C72F7 in module 'nxServer.Exe'. Read of
> address 00000039 [$3CA1/15521].
> ---------------------------
> OK
> ---------------------------
>
>
> To make sure I had all other settings correct, I called another SP
> that inserts data for MyRecord (adds data to multiple tables), all
> worked as expected.
>
> This is my first attempt to return a result set using a Select. Am I
> totally missing something?
>
> Mack

I am not sure you can update a view. I think they are read only.

--

I am not updating the view. I am simply perfoming a view. Views
should be able to be updated as long as it can return a live result
set.

Mack

On 24 May 2006 11:29:48 +0100, "David Guest" <dpguest@bestweb.net>
wrote:

>Mack wrote:
>
>> I have the following very simple stored procedure:
>>
>> DROP ROUTINE IF EXISTS "GetMyRecord";
>>
>> CREATE PROCEDURE "GetMyRecord" ()
>> READS SQL DATA -- needs either read-access
>> --MODIFIES SQL DATA -- or write access
>> begin
>> select * from MyRecord;
>> END
>>
>>
>> MyRecord is a view.
>>
>> If I call this SP from EnterpriseManager I get the results I expect.
>> When I go to Delphi and drop a TnxStoredProc and set the Database and
>> the StoresProcName and try to execute or open the stored proc I get
>> the following error message. This occurs at runtime, or at design
>> time if I set ActiveDesigntime to TRUE.
>>
>> ---------------------------
>> Error
>> ---------------------------
>> NexusDB: nxStoredProc1: Query execution failed:
>> Unexpected exception object raised: [EAccessViolation] Access
>> violation at address 006C72F7 in module 'nxServer.Exe'. Read of
>> address 00000039 [$3CA1/15521].
>> ---------------------------
>> OK
>> ---------------------------
>>
>>
>> To make sure I had all other settings correct, I called another SP
>> that inserts data for MyRecord (adds data to multiple tables), all
>> worked as expected.
>>
>> This is my first attempt to return a result set using a Select. Am I
>> totally missing something?
>>
>> Mack
>
>I am not sure you can update a view. I think they are read only.

Make that, "performing a select..."

Discussion in IRC - Hannes is investigating and will report findings
here.

Temporary fix is to use a TnxQuery to call the StoredProc.

> I am not updating the view. I am simply perfoming a view. Views
> should be able to be updated as long as it can return a live result
> set.

Views don't return live result sets.

--

Eivind Bakkestuen
Nexus Database Systems

Mack wrote:

> Discussion in IRC - Hannes is investigating and will report findings
> here.
>
> Temporary fix is to use a TnxQuery to call the StoredProc.

Issue# 2065

--

Hannes Danzl [NexusDB Developer]
Newsgroup archive at http://www.tamaracka.com/search.htm