dhis2-devs team mailing list archive

["jason.p.pickering@xxxxxxxxx" <jason.p.pickering@xxxxxxxxx>]

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.hispvietnam@xxxxxxxxx>
Date: Mon, Jun 27, 2011 23:02
Subject: [Dhis2-devs] Sql view does not excute my SQL
To: "Orvalho Augusto" <orvaquim@xxxxxxxxx>
Cc: "Jason Pickering" <jason.p.pickering@xxxxxxxxx>, "DHIS 2 developers" <dhis2-devs@xxxxxxxxxxxxxxxxxxx>


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