Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Won't Fix
    • Affects Version/s: 3.3-RC2
    • Fix Version/s: None
    • Component/s: Generator
    • Labels:
      None
    • Environment:
      machine on Windows XP - used Sybase 15.0.2 Developer Edition, Maven 2.07 and plugin 3.3-RC2

      Description

      used goals torque:sql ; torque:sqlExec

      When the *-schema.xml file contains descriptions on tables and/or columns, the torque:sql goal generates a DDL that contains instructions like
      'COMMENT ON TABLE blahblahblah IS 'some description' or 'COMMENT ON COLUMN blahblah IS...
      Sybase does not support these, so they should be suppressed from the output when Sybase is the target database.

      Another issue - Sybase does not like ';' in the DDL – all it wants to see is LF/CR, and 'go' statements

      1. createdb.vm
        0.9 kB
        Sylvain Benoist
      2. sybase.zip
        6 kB
        Sylvain Benoist

        Activity

        Hide
        Sylvain Benoist added a comment -

        Unless I am mistaken, I believe the changes required are in the templates jar file -

        under sql/base/sybase/

        comment.vm
        drop.vm
        table.vm
        foreignkey.vm

        Show
        Sylvain Benoist added a comment - Unless I am mistaken, I believe the changes required are in the templates jar file - under sql/base/sybase/ comment.vm drop.vm table.vm foreignkey.vm
        Hide
        Thomas Fox added a comment -

        I have removed the "comment on ..." stuff from generated sql for sybase. Can you please get the new templates from svn and check that these templates work (I cannot check myself, I do not have sybase running)

        Please confirm that I have understood the ";" correctly:
        Torque's sqlExec executes the script correctly, however it cannot be fed into the sybase console directly as the sybase console does not like the ";".
        Is this correct ?

        If yes, how does the sybase console tell that a command ends and a new command starts ? Is there a "go" after each statement ?

        The problem with removing the semicolons is that the sqlExec task needs to know when a command ends and when the next command begins. The semicolon is used for this now, and we would need a replacement. Maybe the "go" statement can serve as such.

        Show
        Thomas Fox added a comment - I have removed the "comment on ..." stuff from generated sql for sybase. Can you please get the new templates from svn and check that these templates work (I cannot check myself, I do not have sybase running) Please confirm that I have understood the ";" correctly: Torque's sqlExec executes the script correctly, however it cannot be fed into the sybase console directly as the sybase console does not like the ";". Is this correct ? If yes, how does the sybase console tell that a command ends and a new command starts ? Is there a "go" after each statement ? The problem with removing the semicolons is that the sqlExec task needs to know when a command ends and when the next command begins. The semicolon is used for this now, and we would need a replacement. Maybe the "go" statement can serve as such.
        Hide
        Sylvain Benoist added a comment -

        I have attached the templates as I have modified them - the sqlExec command still does not work ( I get a sqlexception for some obscure reason) but at least the generated sql can be passed on to Sybase via iSQL and it works outside of the maven2 plugin

        'go' is the command delimiter for Sybase, so yes, it acts as a replacement for ';'.
        I have seen that there was a delimiter param on the list of params that the maven 2 plugin takes, so I tried it
        but did not succeed. I also looked at the template override feature which is advertised in the maven1 plugin but the maven2 plugin apparently does not - Tried using templatePath with no luck.

        if there is any ';' in the SQL, Sybase complains and says <<Incorrect syntax near ';'>>

        You can download Sybase 15.0.2 on your machine, install and use it - the developer edition is absolutely free for dev and test purposes. There are downloads for Windows, Linux and many others.

        Show
        Sylvain Benoist added a comment - I have attached the templates as I have modified them - the sqlExec command still does not work ( I get a sqlexception for some obscure reason) but at least the generated sql can be passed on to Sybase via iSQL and it works outside of the maven2 plugin 'go' is the command delimiter for Sybase, so yes, it acts as a replacement for ';'. I have seen that there was a delimiter param on the list of params that the maven 2 plugin takes, so I tried it but did not succeed. I also looked at the template override feature which is advertised in the maven1 plugin but the maven2 plugin apparently does not - Tried using templatePath with no luck. if there is any ';' in the SQL, Sybase complains and says <<Incorrect syntax near ';'>> You can download Sybase 15.0.2 on your machine, install and use it - the developer edition is absolutely free for dev and test purposes. There are downloads for Windows, Linux and many others.
        Hide
        Sylvain Benoist added a comment -

        sql/base/sybase templates

        Show
        Sylvain Benoist added a comment - sql/base/sybase templates
        Hide
        Sylvain Benoist added a comment -

        sql/db-init/sybase/createdb.vm

        Show
        Sylvain Benoist added a comment - sql/db-init/sybase/createdb.vm
        Hide
        Thomas Fox added a comment -

        When I have time and energy, I may install Sybase. However, this may take some time.

        Sylvain, did you test the templates I checked in on Sunday at all ? They should work via SqlExec (SqlExec removes the delimiter ";" before executing the sql command).

        I'd guess the problem with SqlExec and your templates is that SqlExec does not know when a statement ends (it expects a ";" if you use default settings). The original ant task has the setting DelimiterType and Delimiret, which need to be set to line-based and "go" for SqlExec, but I do not know whether these settings can also be set via thwe maven 2 plugins. I will check.

        Show
        Thomas Fox added a comment - When I have time and energy, I may install Sybase. However, this may take some time. Sylvain, did you test the templates I checked in on Sunday at all ? They should work via SqlExec (SqlExec removes the delimiter ";" before executing the sql command). I'd guess the problem with SqlExec and your templates is that SqlExec does not know when a statement ends (it expects a ";" if you use default settings). The original ant task has the setting DelimiterType and Delimiret, which need to be set to line-based and "go" for SqlExec, but I do not know whether these settings can also be set via thwe maven 2 plugins. I will check.
        Hide
        Sylvain Benoist added a comment -

        The templates do work when submitted via SqlExec, thx.

        given that there is a <delimiter> option that is available for configuration, I was wondering if the solution
        would be to parameterize the templates so that instead of having in a template like index.vm, showing an explicit ';' delimiter

        #foreach ($index in $table.Indices)
        CREATE #if($index.Unique)UNIQUE#end INDEX $index.Name ON $table.Name ($index.ColumnList);
        #end

        we could replace the ';' with ($delimiter) ( assuming that $delimiter is made accessible to the velocity template engine) ?

        This way the loop would be closed - otherwise having a delimiter option that only works on the way out is limited.

        Show
        Sylvain Benoist added a comment - The templates do work when submitted via SqlExec, thx. given that there is a <delimiter> option that is available for configuration, I was wondering if the solution would be to parameterize the templates so that instead of having in a template like index.vm, showing an explicit ';' delimiter #foreach ($index in $table.Indices) CREATE #if($index.Unique)UNIQUE#end INDEX $index.Name ON $table.Name ($index.ColumnList); #end we could replace the ';' with ($delimiter) ( assuming that $delimiter is made accessible to the velocity template engine) ? This way the loop would be closed - otherwise having a delimiter option that only works on the way out is limited.
        Hide
        Thomas Fox added a comment -

        It is not planned to support sybase any more in Torque 4

        Show
        Thomas Fox added a comment - It is not planned to support sybase any more in Torque 4

          People

          • Assignee:
            Unassigned
            Reporter:
            Sylvain Benoist
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development