• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerServer
  • PowerServer2022, PB2022-1878, Oracle Stored procedure used for retrieving data

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved PowerServer2022, PB2022-1878, Oracle Stored procedure used for retrieving data

  1. Issue
0
Votes
Undo
  1. Kari Paukku
    Kari Paukku
  2. PowerServer
  3. Sunday, 25 September 2022 12:51 PM UTC

Hi,

We have a working PB2022 application and when used as client/server it works fine as 32/64 bit. The database is Oracle19.

When deployd to PowerServer environment, the stored procedur used in DW's to retrieve data give an error. regardled of the procedure, the error is always the same, ORA-006550:line 2, column x, where x is different.

A dw where the data is retrieved from the database using SQL works fine.

To test this I created a simple stored procedure

CREATE PROCEDURE PB_TEST(RecCur IN OUT SYS_REFCURSOR)
IS
BEGIN

OPEN RecCur FOR
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS T FROM DUAL;

END;

and a DW that uses that procedure. Then retrieved the data using the following code

int rc
dw_test.SetTransObject(SQLCA)
rc = dw_test.Retrieve()

MessageBox("rc",rc)
MessageBox("SQLErrText", SQLCA.SQLErrText)


As client/server it worked ok. When doing the same using PowerServer, it returnned the ORA-006550 error



The error message comes before the MessageBox("SQLErrText", SQLCA.SQLErrText), which return null string. The rc, coming from PB, returns -1.

Am I missing some configuration settiong or what might cause this behaviour or how to fix this?

Thanks,
Kari

PB 2022 Powerclient Update and Download Features
Powerserver 2022 webapi hosted in Azure's containe...
Responses (4)
David Xiong @Appeon
David Xiong @Appeon Accepted Answer Pending Moderation
  1. Monday, 26 September 2022 09:39 AM UTC
  2. PowerServer
  3. # 1

Hi Kari,

We see your reported ticket in our Support Portal. We will follow up with you there.

 

Currently, you can temporarily work it around as follows:

Change:

CREATE PROCEDURE PB_TEST(RecCur IN OUT SYS_REFCURSOR)

IS

BEGIN

OPEN RecCur FOR

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS T FROM DUAL;

END;

 

To:

CREATE PROCEDURE PB_TEST(RecCur OUT SYS_REFCURSOR)

IS

BEGIN

OPEN RecCur FOR

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS T FROM DUAL;

END; 

 

Regards,

David

Comment
Kari Paukku
  1. Kari Paukku
  2. Monday, 26 September 2022 10:08 AM UTC
David,

thanks, that did the trick!



When one stops to think, these select types of procedures don't require the "IN" parameter (although I recall, perhaps falsely, that at one point in time (PB5?) it was required).



Kari





  1. Helpful
There are no comments made yet.
Kari Paukku
Kari Paukku Accepted Answer Pending Moderation
  1. Sunday, 25 September 2022 18:07 PM UTC
  2. PowerServer
  3. # 2

Hi,
this is what I can see when doing a "build and deploy"
I did see the "error" tab when I started this thing but I fixed all those errors (e.g. duplicate DW objects)

 

and plain "deploy"



and in the application object I have this

What else can I check?

Kari

Comment
Load more comments
Kari Paukku
  1. Kari Paukku
  2. Sunday, 25 September 2022 18:59 PM UTC
Ok, thanks, I'll try that.

Kari
  1. Helpful
Kari Paukku
  1. Kari Paukku
  2. Monday, 26 September 2022 02:07 AM UTC
I tested the dw in PB/SnapDeveloper, and it works ok; returns the current time as a string. From that, I conclude that the error comes from inside of PowerServer, at the point the retrieve is issued.



I have raised a ticket with Appeon, so let's see if they can find a solution and the reason why it happens.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Monday, 26 September 2022 02:27 AM UTC
Hi Kari;

Thanks for trying this with SnapDevelop. Please open a Support Ticket for this issue.

Regards... Chris
  1. Helpful
There are no comments made yet.
Kari Paukku
Kari Paukku Accepted Answer Pending Moderation
  1. Sunday, 25 September 2022 16:44 PM UTC
  2. PowerServer
  3. # 3

Chris,

I didn't recieve any errors the project complied and deployd without any issue what I can tell.

I'll checked out the unsupported items, but did'n spot anything that would apply. 
My example is as simple it can get, so if that doesn't work in PS, then no other stored procedure will work (which I find difficult to believe).

I did some other tests, e.g. added a RAISE_APPLICATION_ERROR just before opening the cursor. That didn't change things, I did get the same error message so it has to do somtehing in how the datawindow and the stored procedure gets transformed to the PS.

This is the code, I think, the DW in question and to me that looks reasonable correct.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Runtime.Serialization;
using SnapObjects.Data;
using DWNet.Data;
using Newtonsoft.Json;
using System.Collections;

namespace ReSys
{
[DataWindow("d_test", DwStyle.Default)]
[DwProcedure("RESYS.PB_TEST")]
public class D_Test
{
[DwColumn("t")]
public string T { get; set; }

}

}

Thanks,
kari

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Sunday, 25 September 2022 17:03 PM UTC
If you don't set the PS Errors / Warnings checkbox on in the App Painter, then you will not get see a "PowerServer" tab in the output area. If there is no tab visible during a full build or deployment, then this setting is off. Thus you will not get any PS DML feedback on potential problems.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Sunday, 25 September 2022 16:29 PM UTC
  2. PowerServer
  3. # 4

Hi Kari;

    If the Application Object is set to log PS compile errors ... During the PS deployment / compilation, did you receive any PS errors? 

  Did you read the PS documentation about possible DML support / refactoring that may be required?

https://docs.appeon.com/ps2022/unsupported_features_guide.html

HTH 

Regards ... Chris 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.