dhis2-devs team mailing list archive

[Orvalho Augusto <orvaquim@xxxxxxxxx>]

Thanks.

But in mysql we need call suported on sql view :-)

We can wait.
Caveman

On 6/28/11, jason.p.pickering@xxxxxxxxx <jason.p.pickering@xxxxxxxxx> 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@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

-- 
Sent from my mobile device