#1543 ✓invalid
Ngure Nyaga

Unable to create stored procedures using Play 2 Evolutions

Reported by Ngure Nyaga | June 1st, 2012 @ 10:17 AM

Framework version: Play 2.0.1
Platform you're using: OpenJDK 1.7.0_03 64 bit on Ubuntu 12.04, Postgresql 9.1

Reproduction steps:

Any useful stored procedure / trigger function etc will include semicolons. Play Evolutions appear to split the SQL in the stored procedure using semicolons. A stored procedure definition that passes through PgAdmin III or psql fails when it is applied via Play 2 evolutions.

Details:

This is a sample of a definition that passes via PgAdmin III but fails via the Play 2 evolution:

    CREATE OR REPLACE FUNCTION check_master_org() RETURNS TRIGGER AS '
    DECLARE
       master_org_count numeric;
    BEGIN
    SELECT count(id) FROM organization WHERE organization_type = ''MASTER'' INTO master_org_count;
    IF (TG_OP = ''UPDATE'') AND (new.organization_type = ''MASTER'') AND (old.organization_type <> ''MASTER'' AND master_org_count > 0) THEN
      RAISE EXCEPTION ''<relevant error message>'';
    END IF;
    
    IF (TG_OP = ''INSERT'') AND (master_org_count > 0 AND new.organization_type = ''MASTER'') THEN
      RAISE EXCEPTION ''<relevant error message>'';
    END IF;
    END;
    ' LANGUAGE plpgsql;

The slightly funny escaped quotes ( '' ) are there because I have not used a dollar quoted string literal for the function body.

This issue has been reported before ( #997 ) but marked as resolved. Looking at https://github.com/playframework/Play20/blob/master/framework/src/p... , ( line ~ 220 )I'd say its not resolved yet.

Comments and changes to this ticket

  • Nicolas Leroux

    Nicolas Leroux July 3rd, 2012 @ 07:47 PM

    • State changed from “new” to “invalid”

    This is the wrong project.

  • Dirceu Semighini Filho

    Dirceu Semighini Filho November 1st, 2012 @ 03:59 PM

    This issue is happening to me when I try to create a procedure in the postgresql:

    create or replace function completetransfer(group_id bigint) returns integer as '
    declare
    to_acc_id bigint;
    amount numeric(10,2);
    has_balance boolean;
    begin

    select accountid into to_acc_id from accounttx where groupid=group_id and status_bitmap!=64 and deposit>0;
    select deposit into amount from accounttx where groupid=group_id and status_bitmap!=64 and deposit>0;
    if amount >0 then
        update braspay_accountbalances set balance=balance+amount where accountid=to_acc_id;
        update accounttx set status_bitmap=64 where accountid=to_acc_id and groupid=group_id;
        return 0;
    else return 1;
    end if;
    

    end; '
    LANGUAGE plpgsql STRICT VOLATILE;

    the error seems to be caused by
    s.sql.split(";").map(_.trim).foreach {

    line 232 of
    https://github.com/playframework/Play20/blob/master/framework/src/p...

    Is there a chance for this to be fixed?

  • rch850

    rch850 February 6th, 2013 @ 02:10 PM

    http://www.playframework.com/documentation/2.1.0/Evolutions says

    Play splits your .sql files into a series of semicolon-delimited statements
    before executing them one-by-one against the database. So if you need to use
    a semicolon within a statement, escape it by entering ;; instead of ;.
    For example, INSERT INTO punctuation(name, character) VALUES ('semicolon', ';;');.
    

    So we have to use double semicolons...

  • Sharon Bailey

    Sharon Bailey July 12th, 2018 @ 11:06 AM

    I just hate these kind of coding because i never learn these kind of coding. We may be trying that we are not good in the field of computer because essayontime.com reviews is impotant in the field of computer. So you may be trying to learn and may be you are good in your work.

  • Hard Work

    Hard Work September 7th, 2018 @ 06:45 AM

    Today morning I was unable to get synchronized my internet settings in windows 10 suddenly my friend asked me to try out http://syncsettingswindows10.com this website which seems helpful to me and will also provide all useful information.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2>Play framework</h2>

Play makes it easier to build Web applications with Java. It is a clean alternative to bloated Enterprise Java stacks. It focuses on developer productivity and targets RESTful architectures. Learn more on the <a href="http://www.playframework.org">http://www.playframework.org</a> website.<br><br>

<h2>Source code is hosted on github</h2>Check out our repository at <a href="http://github.com/playframework/play">http://github.com/playframework/play</a><br><br>

<h2>Contributing, creating a patch</h2> Please read the <a href="http://play.lighthouseapp.com/projects/57987/contributor-guide">contributor guide</a><br><br>

<h2>Reporting Security Vulnerabilities</h2> Since all bug reports are public, please report any security vulnerability directly to <em>guillaume dot bort at gmail dot com</em>.<br><br>

<h2>Creating a bug report</h2> Bug reports are incredibly helpful, so take time to report bugs and request features in our ticket tracker. We’re always grateful for patches to Play’s code. Indeed, bug reports with attached patches will get fixed far quickly than those without any.<br><br>

Please include as much relevant information as possible including the exact framework version you're using and a code snippet that reproduces the problem.<br><br>

Don't have too much expectations. Unless the bug is really a serious "everything is broken" thing, you're creating a ticket to start a discussion. Having a patch (or a branch on Github we can pull from) is better, but then again we'll only pull high quality branches that make sense to be in the core of Play.

Pages