How to execute specific SQL (mysql 5 syntax) from scriptella

[Victor Izverschi]

Hello,
This is my first message to scripptella group.
First of all nice job for all developers who participated in
scriptella development it's a nice tool.

We want to use scriptella on our spring webapp to initialize our
database.
Our ETL config file is the following:

<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
<connection id="mysql" driver="${jdbc.driverClassName}" url="$
{jdbc.url}" user="${jdbc.username}"
password="${jdbc.password}" />
<script connection-id="mysql">
<dialect name="mysql">
<include href="sql-scripts/urssaf_db.sql" encoding="utf-8" />
<include href="sql-scripts/urssaf_upgrade.sql" encoding="utf-8" />
</dialect>
</script>
</etl>

With the folowing properties defined:
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/urssaf_db_lot10?
characterEncoding=UTF-8&characterSetResults=UTF-8
jdbc.username=user
jdbc.password=xxxx

In one of the script with have the folowing query:

DELIMITER $$

DROP PROCEDURE IF EXISTS `upgrade_urssaf_db` $$
CREATE PROCEDURE upgrade_urssaf_db()
BEGIN

declare db_name varchar(256);
select @db_name := database();

--nouvelle colonne cheminTemplateCourrierRIB
IF NOT EXISTS( (SELECT * FROM `information_schema`.`COLUMNS` WHERE
COLUMN_NAME='cheminTemplateCourrierRIB' AND
TABLE_NAME='compte_bancaire' AND TABLE_SCHEMA=@db_name) ) THEN
ALTER TABLE `compte_bancaire` ADD cheminTemplateCourrierRIB
varchar(512) DEFAULT '';
END IF;

qsdqsd

--nouvelle colonne activer
IF NOT EXISTS( (SELECT * FROM `information_schema`.`COLUMNS` WHERE
COLUMN_NAME='activer' AND TABLE_NAME='compte_bancaire' AND
TABLE_SCHEMA=@db_name) ) THEN
ALTER TABLE `compte_bancaire` ADD activer BIT DEFAULT TRUE;
END IF;

END $$

CALL upgrade_urssaf_db() $$

DELIMITER ;

Scriptella gives an error during script execution with message
incorrect syntax.
The scripts executes with no errors from MySQL Query browser on MySQL
5.1 version.

Is there a way to specify the dialect version on which should be used
during script execution?

Thanks for answer.

Regards,
Victor.

[Fyodor Kupolov]

Hi Victor,

Custom delimiters are controlled in Scriptella by using
statement.separator and statement.separator.singleline connection
properties. Originally it was developed for Oracle PL/SQL statement
blocks, but was added as a generic feature, so it is available to all
jdbc connections. It obviously lacks documentation, so thanks for
pointing out this problem, I will at least document this property on
the MySQL driver page. A ticket created to track this issue
http://javaforge.com/issue/25690

Your scripts have to be modified, so that delimiter is specified as a
connection property rather than inline statement. Please note that
delimiter is declared once and cannot be changed, so you probably need
a separate connection for it. Here is a working example:

<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>

<connection id="db_procs" url="...." >
statement.separator=$$
</connection>
<connection id="db" url="..." > <!-- normal connection for ;
separated statements -->
</connection>

<script connection-id="db_procs">
CREATE TABLE Test (ID Integer) $$
INSERT INTO Test VALUES (1)
</script>
<script connection-id="db">
INSERT INTO Test VALUES (2);
INSERT INTO Test VALUES (3);
</script>
</etl>


Additional notes:
- I think you don't need <dialect> feature in your scenario. It was
designed for cross-database scripts, where only very minor parts were
database specific. It's not used very often.
- If your are using Spring, you can also use Scriptella
EtlExecutorBean to declare and run ETLs directly from Spring
application context:
<bean id="dbInit" class="scriptella.driver.spring.EtlExecutorBean">
<property name="configLocation" value="classpath:db/
create.schema.etl.xml" />
<property name="autostart" value="true" /> <!-- If true, ETL is
executed on appCtx initialization -->
</bean>
In the ETL file you can use Spring managed datasources, by
declaring <connection driver="spring" url="beanName"/>

Regards,
Fyodor


On Jan 12, 5:37 pm, Victor Izverschi <izverschi.vic...@gmail.com>
wrote:

[Victor Izverschi]

Hi Fyodor ,
Thanks for pointing out the solution for me, it works better now :)
Have a nice day and thanks again.
Regards,
Victor.