Thanks. But in mysql we need call suported on sql view :-)
We can wait. Caveman On 6/28/11, jason.p.picker...@gmail.com <jason.p.picker...@gmail.com> wrote: > In fact Sql view supports calling stored procedures through the 'Select * > from my_stored_procedure' syntax which is supported on postgres. > > Sent from my HTC > > ----- Reply message ----- > From: "Hieu Dang Duy" <hieu.hispviet...@gmail.com> > Date: Mon, Jun 27, 2011 23:02 > Subject: [Dhis2-devs] Sql view does not excute my SQL > To: "Orvalho Augusto" <orvaq...@gmail.com> > Cc: "Jason Pickering" <jason.p.picker...@gmail.com>, "DHIS 2 developers" > <dhis2-devs@lists.launchpad.net> > > > Hello, > > In fact, SqlView just allow us to write a query where the SELECT keyword at > the beginning as I presented in the previous mail and belongs to the > resource tables (7 ones) to serve your purpose on querying data. If one of > them deleted/regenerated then the view, which are linked to, would be > recreated for updating in data. > > On the regard issue of security, so SqlView does not support in calling a > store-procedure untill now. > > On Mon, Jun 27, 2011 at 11:49 PM, Orvalho Augusto <orvaq...@gmail.com>wrote: > >> Under MySQL there is one problem. In mysql we call a stored procedure like >> this: >> call storedprocedure(); >> >> And this SQLview just allow SELECT as the first word. Any ideias? >> >> Caveman >> >> >> On Mon, Jun 27, 2011 at 1:33 PM, Orvalho Augusto >> <orvaq...@gmail.com>wrote: >> >>> Really thanks for this advice! >>> >>> Caveman >>> >>> >>> On 6/27/11, Jason Pickering <jason.p.picker...@gmail.com> wrote: >>> > It is important to keep in mind conceptually why this SQL view >>> functionality >>> > is there. DHIS2 destroys certain tables during some processes, such as >>> the >>> > resource table generation process. If you have any tables which the a >>> > particular view depends on, the database will prevent DHIS2 from >>> dropping >>> > these tables. The resource generation process will fail (usually >>> silently >>> > from within DHIS, but will show up in the logs). So, in general, you >>> should >>> > not use any views which are linked directly to DHIS database tables. >>> > >>> > Use of a stored procedure however will allow DHIS2 to delete a table, >>> even >>> > if the SP depends on it, and you could use the "SELECT * FROM >>> > my_stored_procedure" to allow (power) users/admins to execute the >>> > materialization of the SP (if you have more thatn 255 characters in the >>> > view, or actually need an SP for some other reason). >>> > >>> > Regards, >>> > Jason >>> > >>> > >>> > On Mon, Jun 27, 2011 at 5:34 AM, Hieu Dang Duy >>> > <hieu.hispviet...@gmail.com>wrote: >>> > >>> >> Yes, actually, the system will take your view's name be a name of >>> created >>> >> one in your db. But there is a validation on checking the given name >>> (from >>> >> input). If there is any special symple/characters then they will be >>> remove >>> >> before view created. Sorry for this inconvenient. >>> >> >>> >> >>> >> On Mon, Jun 27, 2011 at 3:28 PM, Orvalho Augusto >>> >> <orvaq...@gmail.com>wrote: >>> >> >>> >>> I know now why the Querry does not show UP: >>> >>> >>> >>> I gave the name "Missings_from_old_system" under DHIS. And DHIS >>> >>> created the following view _view_Missingsfromoldsystem". So the "_" >>> >>> is >>> >>> eaten! >>> >>> >>> >>> I reacreated with another name without "_" and it is working. >>> >>> >>> >>> Caveman >>> >>> >>> >>> >>> >>> On 6/27/11, Orvalho Augusto <orvaq...@gmail.com> wrote: >>> >>> > Thanks for the reply and I am so sorry to disturb you guys for my >>> >>> > ignorance. >>> >>> > >>> >>> > The thing is when I go from your the option 2 I get this: >>> >>> > "enter a value between 1 and 255 characters long." >>> >>> > That query has more than 255 characters. I did not explain clearly >>> on >>> >>> > first time. >>> >>> > >>> >>> > So I have no choice. >>> >>> > >>> >>> > And indeed I place select * from v_omissos under SQL statement. I >>> >>> > execute it and it does not show up! >>> >>> > >>> >>> > Caveman >>> >>> > >>> >>> > >>> >>> > On 6/27/11, Hieu Dang Duy <hieu.hispviet...@gmail.com> wrote: >>> >>> >> Hello, >>> >>> >> >>> >>> >> "Sql View" is a functinality which allows the user (admin/mod) to >>> >>> create >>> >>> >> a >>> >>> >> view without interact inside DBMS directly. One more important >>> point >>> >>> >> is >>> >>> >> that >>> >>> >> you cannot use any keyword such as "SELECT INTO, UPDATE, DELETE, >>> >>> >> ALTER" >>> >>> >> excepting "SELECT" in your query only. Btw, I would like to >>> >>> >> present >>> to >>> >>> >> you >>> >>> >> how to use this one for creating/using your own view. >>> >>> >> >>> >>> >> It's one of two ways to do this as following on: >>> >>> >> >>> >>> >> No.1: After you created your own view like v_omissos in your >>> database >>> >>> >> then >>> >>> >> go to the GUI of "Sql View" and creating another view with this >>> query >>> >>> >> statement "select * from v_omissos". Next clicking on the "Execute >>> >>> query" >>> >>> >> button which is corresponding to create a new view. Then clicking >>> on >>> >>> >> "View" >>> >>> >> will see the result. >>> >>> >> >>> >>> >> No.2: Copying your main query as below and then save it with name >>> like >>> >>> >> "omissos" >>> >>> >> >>> >>> >> select '999' AS `OLDMISSCODE`, count(0) AS `contagem` from >>> `datavalue` >>> >>> >> where >>> >>> >> (`datavalue`.`value` = '999') >>> >>> >> union all >>> >>> >> select '9999' AS `OLDMISSCODE`,count(0) AS `count(*)` from >>> `datavalue` >>> >>> >> where >>> >>> >> (`datavalue`.`value` = '9999') >>> >>> >> union all select 'empty' AS `OLDMISSCODE`,count(0) AS `count(*)` >>> from >>> >>> >> `datavalue` where (trim(`datavalue`.`value`) = '') >>> >>> >> union all select 'NULL -- Sent from my mobile device _______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
