Sybase DBMS DDL errors

• Key: TORQUE-104
• Type: Bug
• Status: Open
• Priority: Blocker
• Assignee: Unassigned
• Reporter: Sylvain Benoist
• Component/s: Generator
• Affects Version/s: 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

Comments

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

[Thomas Fischer]

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.

[Sylvain Benoist]

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.

[Sylvain Benoist]

sql/base/sybase templates

[Sylvain Benoist]

sql/db-init/sybase/createdb.vm

[Thomas Fischer]

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.

[Sylvain Benoist]

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.