"Drop table if exists [tablename]" in scalalike JDBC

[akshay patil]

Is there a way to run the following command  "Drop table if exists [tablename]" in scalalike JDBC. I did not find any documentation and also was producing errors if I use it.

Enter code here...

import scalikejdbc._

java.sql.DriverManager.registerDriver(new Driver())
ConnectionPool.singleton("jdbc:mysql://localhost/table", "user", "password")
DB.autoCommit { implicit s =>
  sql"""
    DROP TABLE IF EXISTS table1
    CREATE TABLE table1(
       id serial NOT NULL PRIMARY KEY,
       name VARCHAR(64)
    )
  """.execute.apply()
}

[Kazuhiro Sera]

You've forgotten to append a semicolon at the end of the drop statement.

-Kaz

> --
> You received this message because you are subscribed to the Google Groups
> "ScalikeJDBC Users Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to scalikejdbc-users...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[akshay patil]

Sorry should have posted "semi colon", I was doing some trial and errors and missed it.

Enter code here...

DB.autoCommit { implicit s =>
  sql"""

    DROP TABLE IF EXISTS apikey;
    CREATE TABLE apikey(

       id serial NOT NULL PRIMARY KEY,
       name VARCHAR(64)
    )
  """.execute.apply()
}

Error - 

[main] ERROR s.StatementExecutor$$anon$1 - SQL execution failed (Reason:
 You have an error in your SQL syntax; check the manual that corresponds
 to your MySQL server version for the right syntax to use near 'CREATE 
TABLE apikey(

           id serial NOT NULL PRIMARY KEY,

           name ' at line 2):

   DROP TABLE IF EXISTS apikey; CREATE TABLE apikey( id serial NOT NULL PRIMARY KEY, name VARCHAR(64) )

I do get the same error even for the following statement

Enter code here...

DB.autoCommit { implicit s =>
  sql"""

    DROP TABLE apikey;
    CREATE TABLE apikey(

       id serial NOT NULL PRIMARY KEY,
       name VARCHAR(64)
    )
  """.execute.apply()
}

It ScalikeJDBC does not like "IF" keyword at all. And it does not like the semicolon as the separator

[Kazuhiro Sera]

This seems not to be a ScalikeJDBC issue.

Your JDBC driver doesn't accept multiple statements in single #execute
invocation. Separate the queries like this:

sql"""drop table apikey""".execute.apply()
sql"""create table apikey(...)""".execute.apply()

-Kaz

[akshay patil]

Thanks for the reply. That's what I am doing right now.
Also, do you know if "DROP TABLE IF EXISTS [tablename]" is supported. Because I think "IF" keyword seems to not exist at all. Is that correct?

[Kazuhiro Sera]

MySQL supports DROP TABLE IF EXISTS:

http://dev.mysql.com/doc/refman/5.7/en/drop-table.html

-Kaz

[akshay patil]

Arrgghh.. Sorry for that silly question. Got confused and thought that mysql statements/keywords are something ScalikeJdbc provides. After importing scalikeJdbc, my IDE was not able to recognize "IF" as a SQL keyword and not autocompleting it, while it  recognized all others like DROP, CREATE, EXISTS etc. hence the confusion.

Thanks for the prompt replies. Appreciate it.