Problem using result set of a stored procedure

[leeyuiwah]

Hi,

I have a stored procedure which can return a result set to my MySQL client

    call repoTreeView('foo')

However, when I tried to use it in a dynamic DataSource, the returned result set is not returned to the SmartGTW client. My code of the Data Source looks like this:

    <operationBindings>
        <operationBinding  
                operationType="fetch"
        >
            <customSQL>call repoTreeView('foo')</customSQL>
        </operationBinding>
    </operationBindings>

According to the manual (here), this should work:

Stored procedures

It is possible to include templated calls to SQL stored procedures in a customSQL clause, for the ultimate in flexibility. ...

...

When calling stored procedures this way, be sure that the <customSQL> operates like a normal SQL statement, so that it can be called via normal JDBC calls. For operationType="fetch", the JDBC API PreparedStatement.executeQuery() is called and expects a ResultSet returned.

Any suggestion on how to address this problem? Thanks!

I am using MySQL as the DB.

My SmartGWT Version: v10.0p_2016-04-19/PowerEdition Deployment (built 2016-04-19)

[Isomorphic]

Evidently, using your SQL statement with JDBC's PreparedStatement.executeQuery() API does not actually return a ResultSet. You may need to adjust your SQL in some way to solve this, we're not sure how (this is a basic Java/SQL issue, not an issue with SmartGWT).

We would recommend searching this forum for other threads on stored procedures to try to figure out what to do.

[leeyuiwah]

But I could see the result set when I was using MySQL WorkBench (an MySQL client). It was only in SmartGWT that the result set was not fed into the data source.

What do you mean when you said " ... does not actually return a ResultSet"

[Isomorphic]

We mean exactly what we said: "Evidently, using your SQL statement with JDBC's PreparedStatement.executeQuery() API does not actually return a ResultSet".

It's not relevant that MySQL WorkBench is able to execute your stored procedure. Your stored procedure needs to work with JDBC (and we cannot change JDBC for you).

[leeyuiwah]

Hi, I found out what went wrong. The argument of the stored procedure cannot be a literal string (e.g. 'foo') but must be a velocity context variable. (I was thinking of doing a quick test hence the literal string.) In other words, after I changed my ds.xml to the following I could get the result set into my datasource.

    <operationBindings>
        <operationBinding   
                operationType="fetch" 
        >
            <customSQL>call repoTreeView($criteria.foo)</customSQL>
        </operationBinding>
    </operationBindings>

Thanks!

[Isomorphic]

If the actual SQL you want to execute is "call repoTreeView('foo')" as shown in your first example, then you can simply put this in your <customSQL> tag. There is no requirement to use a Velocity context variable. This is made very obvious by various examples in the QuickStart and examples shared on these forums, which use literal strings without the need to involve Velocity.

Evidently the sample SQL you initially posted was simply wrong in some way - the argument was incorrect, incorrectly quoted, or you meant to use a variable but did not use the $ prefix or did not use $criteria or $valeus - something like that.

[leeyuiwah]

Hi, I think you are right. This was the first time I tried to use the result sets returned via a stored procedure invoked via a SmartGWT DataSource. And I might have made some stupid mistakes that mis-led myself. Anyway I have got my code to work so all is good now.

Thanks!