DBPROC : Getting Back Result-set --> BUG ?

Danny Tramnitzke Thu, 12 Jun 2003 05:45:27 -0700

Hi all

It might be, that there is a BUG with DBPROC :

To solve the problem of getting back a result set from the dbproc (see former message), I have made a work-around:


create dbproc prog_one (IN input char(150)) RETURNS CURSOR AS
VAR   outstr char(150);
        stmt char(150);

SET outstr = REPLACE (input, '�', 'Ae');

create table TEMP.resultset (outstr varchar(250)) ;
    stmt = 'INSERT into TEMP.resultset values(';
stmt = stmt || ' ' ||  outstr  || ' ' || ')';
  execute stmt ;
     $CURSOR = 'HOTEL_CURSOR';
DECLARE :$CURSOR CURSOR FOR
SELECT outstr FROM TEMP.resultset ;

As you see, I create a table with column 'outstr' and INSERT the varibale 'outstr' ...

BUT :

It's not possible to INSTERT that char variable into the TEMP.Table, THOUGH it IS possible to INSERT Numbers (like : CHR(ABTNR) , ABTNR is int ) ! I have aktivated the dbproc with : call prog_one ('�')

As an example, I changed the dbproc a bit :

create dbproc prog_one (IN input char(150)) RETURNS CURSOR AS
VAR   stmt char(150);
     ABTNR int;
     my_v varchar(255);

SET my_v ='4';
SET ABTNR = 4 ;
   create table TEMP.resultset (outstr varchar(250)) ;
      stmt = 'INSERT into TEMP.resultset values(';
stmt = stmt || ' ' ||  my_v  || ' ' || ')';
  execute stmt ;

$CURSOR = 'HOTEL_CURSOR';

DECLARE :$CURSOR CURSOR FOR
SELECT outstr FROM TEMP.resultset ;

Now, there will be INSERTed the varchar variable my_v = '4' , but not my_v = 'h' , though the Table Column is a Varchar Type. I also could insert the ABTNR.

So, why I couldn't INSERT a char variable, or just a varchar letter, but integer and varchar numbers ??? Could it be a BUG ?

Best regards,
Danny

Am Thu, 12 Jun 2003 12:02:13 +0200 hat Danny Tramnitzke <[EMAIL PROTECTED]> geschrieben:

Many Thanks !

But there is still a problem :

I have to get back a result-table, so I use "RETURNS CURSOR" .

Like I the sapdb Docu, I the dbproc looks like this :
create dbproc prog_one (IN input char(150)) RETURNS CURSOR AS
VAR   outstr char(150);
SET outstr = REPLACE (input, '�', 'Ae');
$CURSOR = 'HOTEL_CURSOR';
DECLARE :$CURSOR CURSOR FOR
SELECT outstr FROM domain.columns where rowno <= 1 ;
So, you see, that I want to get back a result-set with one field, which contains the 'outstr' variable. (needed for a javabean, lying upper...) But I haven't found out yet, how I could Select a variable, because then, Error comes :

Unknown Column Name

I guess, it's only a tiny problem, but the Docu says to Selected Column (select_column) :

Syntax

<select_column> ::= <table_columns> | <derived_column> | <rowno_column> | <stamp_column>

<table_columns> ::= * | <table_name>.* | <reference_name>.* <derived_column> ::= <expression> [ [AS] <result_column_name>] | <result_column_name> = <expression> <rowno_column> ::= ROWNO [<result_column_name>] | <result_column_name> = ROWNO <stamp_column> ::= STAMP [<result_column_name>] | <result_column_name> = STAMP

<result_column_name> ::= <identifier>

So, where is a '<variable_column>' or something like that?
Best regards,
Danny

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general

DBPROC : Getting Back Result-set --> BUG ?

Reply via email to