dhis2-devs team mailing list archive

[Hieu Dang Duy <hieu.hispvietnam@xxxxxxxxx>]

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


-- 
Good heath !