Friday, May 7, 2010

OSB performing SQL Insert

I assign the result of this:

fn-bea:execute-sql('oracleXEDS', xs:QName('company'), 'insert into COMPANIES (id, NAME, CREATIONDATE) values (?, ?, ?)', 2, 'Acme', '2010')

to a variable companyResult.


I get this error:

OSB Assign action failed updating variable "companyResult": com.bea.dsp.wrappers.rdb.exceptions.RDBWrapperException: line 1, column 1: {bea-err}RDBW0019: [oracleXEDS]: [insert into COMPANIES (id, NAME, CREATIONDATE) values (?, ?, ?)]: [2, Acme, 2010]: Error executing SQL query. Query result is not tabular (Expected ResultSet, got '1')

In fact the signature is :

fn-bea:execute-sql( $datasource as xs:string, $rowElemName as xs:QName, $sql as xs:string, $param1, ..., $paramk) as element()*

Most likely this means that you can only do SELECT and assign the result to a variable, but if you perform an INSERT don't expect to be able to check how many rows you actually inserted.... there must be a workaround, who knows which one... so, basically, USE fn-bea:execute-sql only to do SELECT.

No comments: