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@gmail.comwrote:
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@gmail.comwrote:
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).
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@gmail.comwrote:
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.
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!
“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
On 6/28/11, jason.p.pickering@gmail.com <jason.p.pickering@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.hispvietnam@gmail.com>
Date: Mon, Jun 27, 2011 23:02
Subject: [Dhis2-devs] Sql view does not excute my SQL
To: "Orvalho Augusto" <orvaquim@gmail.com>
Cc: "Jason Pickering" <jason.p.pickering@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 <orvaquim@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 >> <orvaquim@gmail.com>wrote:
Really thanks for this advice!
Caveman
On 6/27/11, Jason Pickering <jason.p.pickering@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.hispvietnam@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 >>> >> <orvaquim@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 <orvaquim@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.hispvietnam@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
