sql stored procedure return values

[pparas]

Hi,
I am calling a SQL stored procedure to add records to a database. The request stores records but what I need in response is the unique tag value which is assigned to records when they are saved by the stored procedure. Using a DSCallback method on the saveData call returns a response record with a good status but no return data. A subsequent fetch, within the execute method of the saveData, does not return the saved record with the unique tag. I believe the return from the request on the stored procedure only indicates that the call was submitted successfully instead of indicating that the call has completed successfully. Even though the record is being stored successfully there is no way to synchronize with its creation. Is there anyway to either get a return value from the stored procedure or to synchronize on the completion of the stored procedure? I have been unable to find an example of this anywhere.

Thank you for your help.

SmartGWTEEPower-2.3

[Isomorphic]

Make sure your stored procedure returns a row, because that's what the SmartGWT server can accept and deliver back to the client. If you have only a singular status value to return, return a row containing that status value.

Also, be sure to post all the requested details next time.

[pparas]

Thank you for your reply, We're still having problems so I'll try and describe what we're dong more fully. I can not cut and paste from my development environment so I'll type in our sample. (please excuse typos..)

test.java ---------------------
...
mainForm.setDataSource("test_tab") ;
ds_req.setOperationId("addRow") ;

TestItem text_str = new textItem("TEXT_STR", "Text Str") ;
FormItem idItem = new FormItem() ;
idItem.setName("ID") ;
idItem.setTitle("Id") ;

mainForm.setItems(text_str, idItem) ;

IButton saveB = new IButton("Save") ;
saveB.addClickHandler(new ClickHandler() {
public void onClick(ClickEvent event) {
mainForm.saveData(new DSCallback(){
public void execute(DSResponse response, Object rawData,
DSRequest request) {
}
}, ds_req) ;
}) ;

test_tab.ds.xml -------------------------

<DataSource ID="test_tab", serverType="sql" dbName="mydb" tableName="TEST_TAB>
<fields>
<field name="ID" type=number" title="Id" primaryKey=true>
......
</field
<field name="TEXT_STR" type="text" length="100 title="Text Str">
.....
</field>
</fields>
<operationBindings>
<operationBinding operationType="add" operationId="addRow" >
<customSQL>call create_row($values.TEXT_STR></customSQL>
</operationBinding>
</operationBindings>



pl/sql file------------------

create table test_tab
(id NUMBER,
text_str VARCHAR2(100)) ;

-- PL/SQL stored procedure (function)
CREATE OR REPLACE FUNCTION create_row(user_txt VARCHAR2)
RETURN test_tab%ROWTYPE
IS
c test_tab%ROWTYPE;
s_val NUMBER;
my_id NUMBER;
BEGIN
select new_id_seq.NEXTVAL INTO s_val FROM dual;
my_id:= to _char(SYSDATE,'YYYYMMDDHH24MISS') ||
trim(to_char(s_val,'000')) ;
INSERT INTO test_tab(id, text_str)
VALUES (my)id, user_txt) ;
SELECT * INTO c FROM test_tab WHERE id=my_id;
RETURN c
END create_row;


Returns SQLException: ORA-06576: not a valid function of procedure name

at oracle.jdbc.driver.DatabaseError.throwSqlException<DatabaseError.java:112>
oracle.jdbc.driver.T4CTTIoer.processError<T2CTTIoer.java:3331>
oracle.jdbc.driver.T2C80all.receive<T2C80all.java:743>
and so on....


The stored procedure in this case is a function. When we replace the function with a procedure call and use a fetch after the save we successfully save and fetch the data. It's using functions that seems to be our problem.

we're running PowerEdition Deployment(build 2010-10-05)

Thank you for your help.

[Isomorphic]

PL-SQL is not an area where we have vast expertise, however, this article suggests that the most appropriate tool to use here is a stored procedure, not a stored function.

After the procedure saves, you'll see SmartGWT automatically try to re-select the row that has just been added or updated, based on the primary key.

[pparas]

Thanks for the response,
I am quite surprised that there is no way for my stored procedure (function) to return rows which will be processed as return records. When I run a procedure (not a function) from an input form the new record is not returned automatically as you suggested, I have to fetch it. Yet when I execute a normal (default) add the new record is automatically returned. My DBA believes that what is necessary from a SQL perspective to make the function work is to be able to add an "INTO" clause to the customSQL so I would have <customSQL>call create_row($values.TEXT_STR) INTO outValues</customSQL> for example. However I don't believe this capability exists..... There is no equivilent of "outValue" defined that I can find. In your original response you indicated to "return a row" so I was hoping that somehow return data was being handled by the SQLDriver but when the function is called ORACLE recognizes that there is no function that matches the call because the function that is defined must assign an output so we get the function not defined error. Anyhow, if you discover any way around this we'd love to use it. Our DBA does not have to use PL/SQL. Otherwise I'll assume that customSQL can ONLY call procedures not functions.
Thank you for your help.

[Isomorphic]

Possibly this has nothing to do with functions vs procedures. For operationType "fetch" your <customSQL> can return rows because that's what "fetch" means and SELECT statements return rows. For other operationType, your <customSQL> is not expected to return any rows, because INSERT, UPDATE and DELETE SQL statements do not.

You can see in the server-side logs the attempt to re-select whatever row you have modified. If it's not working, look over the logs, and if you don't understand why, post them along with other required details.

Another option - see operationBinding.cacheSyncOperation - this allows you to explicitly define another operationBinding to use to re-selected modified data after a change occurs. So you could use an operationBinding with a <customSQL> that doesn't return anything for your update/add operation and specify a second operationBinding with <customSQL> that returns the modified data.

[pparas]

Thanks for the response,
I modified the above example to use fetch operations instead of add (save) in the java and xml files and still got the same "not a valid function of procedure name" error from Oracle.... The console log shows the call with the correct parameters.

Created new operationalBindings to test the cacheSyncOperation idea and that did work so at least this gives me a server side fix for returning the values I need. It turns out this only works on a direct add to the database. When the cacheSync is on the customSQL operationBinding my the log indicates that no rows were updated when the stored procedure is called and the cacheSync is not called. I'm assuming this is a cause and effect? Since the driver doesn't think an update occurred it doesn't execute the cacheSync? Right now it looks like my only choice in this is to call a stored procedure followed by a query/fetch. I'm still not sure there won't be timing issues with this because its hard to tell if the SQL Driver returns before the customSQL statements have completed the update.

[jan_matusiewicz]

wrong call syntax

When you call
<customSQL>call create_row($values.TEXT_STR>)</customSQL> Oracle seeks for procedure, you should use something like that
<customSQL>call $dummy := create_row($values.TEXT_STR>)</customSQL>
to call a function

[Blama]

Hi Jan,

do you have a working example of a Stored Function call where you can use the returned value in SmartClient server code?

I did not manage to get the value and was only able to call a function via "<customSQL>SELECT func() from DUAL</customSQL>" from my .ds.xml.

Thank you & Best regards
Blama