dhis2-devs team mailing list archive

[Orvalho Augusto <orvaquim@xxxxxxxxx>]

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 <orvaquim@xxxxxxxxx> wrote:

> Really thanks for this advice!
>
> Caveman
>
>
> On 6/27/11, Jason Pickering <jason.p.pickering@xxxxxxxxx> 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.hispvietnam@xxxxxxxxx>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
> >> <orvaquim@xxxxxxxxx>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 <orvaquim@xxxxxxxxx> 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.hispvietnam@xxxxxxxxx> 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' AS `OLDMISSCODE`,count(0) AS `count(*)` from
> >>> >> `datavalue` where isnull(`datavalue`.`value`) union all
> >>> >> select 'NA from R conversion' AS `OLDMISSCODE`,count(0) AS
> `count(*)`
> >>> >> from
> >>> >> `datavalue` where (`datavalue`.`value` = 'NA')
> >>> >>
> >>> >> Next to see your result you must click on "Execute query" button
> first
> >>> >> and
> >>> >> then see by "View" button.
> >>> >>
> >>> >> *Notes*: The second way is the best choise for your case because we
> >>> don't
> >>> >> want to waste of capacity in memory of our database. If you are
> >>> >> mention
> >>> >> in
> >>> >> the first way then you will realize that there are two views which
> are
> >>> >> created in the action.
> >>> >>
> >>> >> Hope you get it now :)
> >>> >>
> >>> >> On Sat, Jun 25, 2011 at 2:43 PM, Orvalho Augusto <
> orvaquim@xxxxxxxxx>
> >>> >> wrote:
> >>> >>
> >>> >>> I have this simply query:
> >>> >>> --
> >>> >>> select * from v_omissos
> >>> >>> --
> >>> >>>
> >>> >>> The v_omissos is a view for:
> >>> >>> CREATE OR REPLACE VIEW v_omissos AS
> >>> >>> 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' AS `OLDMISSCODE`,count(0) AS `count(*)`
> from
> >>> >>> `datavalue` where isnull(`datavalue`.`value`) union all
> >>> >>> select 'NA from R conversion' AS `OLDMISSCODE`,count(0) AS
> `count(*)`
> >>> >>> from
> >>> >>> `datavalue` where (`datavalue`.`value` = 'NA')
> >>> >>>
> >>> >>> That query has results directly on mysql but DHIS 2 claims
> >>> >>> "Please execute query to create View table before viewing".
> Actually
> >>> >>> I
> >>> >>> have
> >>> >>> executed it.
> >>> >>>
> >>> >>> Nothing apears on the logs.
> >>> >>>
> >>> >>> Caveman
> >>> >>>
> >>> >>>
> >>> >>>
> >>> >>>
> >>> >>>
> >>> >>> _______________________________________________
> >>> >>> Mailing list: https://launchpad.net/~dhis2-devs
> >>> >>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> >>> >>> Unsubscribe : https://launchpad.net/~dhis2-devs
> >>> >>> More help   : https://help.launchpad.net/ListHelp
> >>> >>>
> >>> >>>
> >>> >>
> >>> >>
> >>> >> --
> >>> >> Good heath !
> >>> >>
> >>> >
> >>> >
> >>> > --
> >>> > Databases, Data Analysis and
> >>> > OpenSource Software Consultant
> >>> > CENFOSS (www.cenfoss.co.mz)
> >>> > Fundacao Manhica/CISM (www.manhica.org)
> >>> > email: orvaquim@xxxxxxxxxxxxx
> >>> > cell: +258846031265
> >>> > cell: +258828810980
> >>> >
> >>>
> >>
> >>
> >>
> >> --
> >> Good heath !
> >>
> >>
> >> _______________________________________________
> >> Mailing list: https://launchpad.net/~dhis2-devs
> >> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> >> Unsubscribe : https://launchpad.net/~dhis2-devs
> >> More help   : https://help.launchpad.net/ListHelp
> >>
> >>
> >
>