mySQL Stored Procedure

Applications Grid / Reports
#1

Hi,

Could anybody post a simple example which shows how to use a mySQL stored procedure to populate the data in a grid application in ScriptCase?

I have created a few stored procedures which execute fine in mySQL workbench and also via phpmyadmin but am not having any joy getting them to work in a test grid app.

Any help would be appreciated.

Thanks,

Mike

#2

Re: mySQL Stored Procedure

I use Stored Procs to update data on the server side … not sure what you are trying to achieve by using a stored proc to send data back to a client grid. Can you be more specific?

Regards,
Scott.

#3

Re: mySQL Stored Procedure

Hi Scott,

Essentially we need to perform some quite complex calculations in SQL. We’ve created a sample sql script which runs fine and returns the results we require. Many of the calculations are based upon user supplied data entered on a prior control form. It made sense to me to create a stored procedure out of the script and pass in the various parameters and then populate our grid with the result set.

If this is not the correct approach I’m open to any other suggestions.

Thanks,

Mike

#4

Re: mySQL Stored Procedure

You may have to contact support on this. My thoughts are …

SC supports the calling of SP’s to execute on the server, but has no knowledge of retrieving the data back from the server once the proc is executed. You would need a callback to let the client (grid) know that the server was done and waiting for data to be sent back to populate the grid.

Regards,
Scott.

#5

Re: mySQL Stored Procedure

How abt getting the SP to update a table(dummy or temp if required) and then have SC redirect it to a separate application that fetches data from this temp table?

The records from temp table can be deleted in the beginning of same SP if required to load new data everytime it is called.

#6

Re: mySQL Stored Procedure

That may work but it seems a bit sideways. The trick is … when to call the redirect. You would not want to load a blank grid.
Ideally you would issue an AJAX call and wait for the response … then call the app to load the table/view.

http://www.prototypejs.org/api/ajax/request

I am not sure SC has this ability. Support may have a better answer as to how their code works with callbacks.

Perhaps support is aware of a technique that I do not know about.

Regards,
Scott.