maria-developers team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

Obviously I neeed to set another delimiter.  Thanks!

-- Peter

On Wed, Oct 22, 2014 at 12:40 PM, Sergei Golubchik <serg@xxxxxxxxxxx> wrote:

> Hi, Peter!
>
> On Oct 22, Peter Laursen wrote:
> > I tried to comment on this Blog
> > https://blog.mariadb.org/mariadb-10-1-1-compound-statements/, but it
> won't
> > let me use my (corporate) gmail address for authencticaton. So I send
> > this mail instead:
>
> strange. I'll check it out.
>
> > I tried the example provided on Windows (both server and client are Win7
> 64
> > bit) using the command line client shipped with MariaDB 10.1:
> >
> > Enter password: ********
> > Welcome to the MariaDB monitor.  Commands end with ; or \g.
> > Your MariaDB connection id is 5
> > Server version: 10.1.1-MariaDB mariadb.org binary distribution
> >
> > Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
> >
> > Type 'help;' or '\h' for help. Type '\c' to clear the current input
> > statement.
> >
> > MariaDB [(none)]> use test;
> > Database changed
> > MariaDB [test]> IF @have_csv = 'YES' THEN
> >     ->   CREATE TABLE IF NOT EXISTS general_log (
> >     ->     event_time TIMESTAMP(6) NOT NULL,
> >     ->     user_host MEDIUMTEXT NOT NULL,
> >     ->     thread_id BIGINT(21) UNSIGNED NOT NULL,
> >     ->     server_id INTEGER UNSIGNED NOT NULL,
> >     ->     command_type VARCHAR(64) NOT NULL,
> >     ->     argument MEDIUMTEXT NOT NULL
> >     ->   ) engine=CSV CHARACTER SET utf8 comment="General log";
> > ERROR 1064 (42000): You have an error in your SQL syntax; check the
> manual
> > that
> > corresponds to your MariaDB server version for the right syntax to use
> near
> > '' a
> > t line 9
> > MariaDB [test]> END IF;
> >
> > Obviously the SEMICOLON after comment="General log" disturbs.  But this
> is
> > no better:
> ...
> > What is the problem here? Is it someWindows-specific bug/issue? BTW it is
> > the same in SQLyog (compiled with MariaDB C-API)  as in commandline.
>
> Right. It's exactly the same issue as with CREATE PROCEDURE and friends.
> If you need to enter something complex with semicolons inside, you need
> to change the delimiter. Here's an example from the manual:
>
> https://mariadb.com/kb/en/using-compound-statements-outside-of-stored-programs/
>
>   MariaDB [test]> delimiter |
>   MariaDB [test]> if @have_innodb then
>     CREATE TABLE IF NOT EXISTS innodb_index_stats (
>       database_name    VARCHAR(64) NOT NULL,
>       table_name       VARCHAR(64) NOT NULL,
>       index_name       VARCHAR(64) NOT NULL,
>       last_update      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON
> UPDATE CURRENT_TIMESTAMP,
>       stat_name        VARCHAR(64) NOT NULL,
>       stat_value       BIGINT UNSIGNED NOT NULL,
>       sample_size      BIGINT UNSIGNED,
>       stat_description VARCHAR(1024) NOT NULL,
>       PRIMARY KEY (database_name, table_name, index_name, stat_name)
>     ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
> STATS_PERSISTENT=0;
>   end if|
>
> Also note (the manual explains it too) that you could get a warning
> "Unknown
> storage engine", despite the IF. The statement may be not executed, but
> it will be parsed anyway, and the parser can warn about unknown engine.
>
> Regards,
> Sergei
>