Raimund Jacob wrote:
>
> i think the subject doesnt surprise anyone here. what makes me write
> this is today:
>
> - i think the CREATE DBPROC documentation is wrong. i states that any
> sql statement can be the body of a stored procedure.
Noone, not even the docu says that ANY sql statement is allowed. There is a
list of sql statements
which are allowed within a dbproc:
<routine_sql_statement> ::=
<close_statement>
| <create_table_temp>
| <declare_cursor_statement>
| <delete_statement>
| <fetch_statement>
| <insert_statement>
| <lock_statement>
| <select_statement>
| <named_select_statement>
| <single_select_statement>
| <select_direct_statement:_searched>
| <select_direct_statement:_positioned>
| <select_ordered_statement:_searched>
| <select_ordered statement:_positioned>
| <subtrans_statement>
| <update_statement>
You did not tell us which statement you wanted to use, but did not find
there
or which one was written there and could not be used.
Please give us a little bit more detail. The list below (allowed keywords)
seems
a little bit too short.
> however only the keywords
> FETCH,CLOSE,WHILE,UPDATE,TRY,SUBTRANS,STOP,SET,SELECT are valid
> after the 'AS'. perhaps the "| <routine_sql_statement>" alternative is
> wrong in the documentation of <statement>. (perhaps my html docs are
> old?).
>
> - what breaks my neck today is this statement:
> create dbproc bla (in oid fixed(30,0),
> in sid fixed(30,0),
> in f3 boolean,
> in f5 float(38),
> in f6 int,
> in f81 fixed(30,0),
> in f82 fixed(30,0))
> as
> try
> insert into raimi.t2_3 (oid,sid,c0) values (:oid,:sid,:f3);
> insert into raimi.t2_5 (oid,sid,c0) values (:oid,:sid,:f5);
> insert into raimi.t2_6 (oid,sid,c0) values (:oid,:sid,:f6);
> insert into raimi.t2_8 (oid,sid,c0,c1) values
> (:oid,:sid,:f81, :f82);
> catch
> if $rc <> 100 then stop ($rc, 'Failed to insert all values');
>
> i want to use it to insert values in multiple tables at once, saving
> some communication overhead (updatable join view not
> applicable because
> i cannot fulfill all the constraints). however, only the
> first insert is
> executed and no error is signaled. i use "xsql" to create and call the
> procedure (yes, i "commit"ed).
>
> - the errormessages are _very_ bad. it doesnt even tell me the
> byte-position of the syntax error. PLEASE improve it, dear SAP people.
>
> does anyone know why only one of the inserts succeeds ?
Mhm, which error is returned after calling this dbproc?
if the first insert runs into trouble (duplicate key or so) the try..catch
has
the task to jump the following statements:
docu:
If an SQL error occurs in the statement list between TRY and CATCH,
the system branches directly to the statement that follows CATCH.
Maybe there is an error in the first insert ?
If we knew more about the resulting error/the sql-statement bringing trouble
we could help much more than we can now.
Elke
SAP Labs Berlin
