[SOLVED] ScalikeJDBC, raw SQL failing to map or return a valid result set

ScalikeJDBC, raw SQL failing to map or return a valid result set

I posted this to the scalikejdbc user group, and should also cross post to github issues.

I've seen in the docs examples of running raw queries against a table, i'm trying to get list of IndexEntry with the following code, and while the query is executed and returns results in the console, i'm not getting anything back in the map(rs =>... portion.

Relevant code is here - when i run this in intellij's debugger the result as "Vector" size = 0. Thanks for any guidance. i'm doing something wrong, hopefully its a simple oversight.

package company.shared.database.models

import company.shared.database.MySQLConnector
import scalikejdbc._

case class IndexEntry(model:String, indexName: String, fieldName: String)

object IndexSchemaModel extends App  with MySQLConnector {
  implicit val session:DBSession = AutoSession

  def indexMap:List[IndexEntry] = {
    val result = DB readOnly { implicit session =>
      sql"""
         SELECT
          substring_index(t.name,'/',-1) as model,
              i.name AS indexName,
              f.name as tableName
       FROM information_schema.innodb_sys_tables t
       JOIN information_schema.innodb_sys_indexes i USING (table_id)
       JOIN information_schema.innodb_sys_fields f USING (index_id)
       where t.name like "MyDB/%"
      """.map(rs => IndexEntry(
              rs.string(0), 
              rs.string(1), 
              rs.string(2))).list().apply()
    }
  println(result) //always List()
  List(IndexEntry("foo", "bar", "az")) //to match the return type
  }

  override def main(args: Array[String]): Unit = {
    configureDB
    indexMap
  }
}

I have tried scalikejdc's other variants - withSql { queryDSL } and the entire bit as SQL Interpolation with full syntax support. The first and the last always execute against the mysql server, which returns 57 rows (small db), the middle throws an NPE, honestly i'm happy to tackle the middle second. I have an issue somewhere in .map and i've tried to have it just return the map, but it always results in an empty list.

Thanks and hopefully no syntax errors copying into SO.

Oh and FWIW, configureDb just sets a connection pool manually since the DB names and servers can vary wildly between sbt tests, dev, test and prod. Currently that is not my issue or i would see "ConnectionPool('default') not initialized" or similar.

Solution

A bit of embarrassment aside. The user in question did not have the process privilege and therefore would not get any rows back from these tables, as soon as a grant process on . to user@host was added, all this worked fine. Permissions in information_schema are driven by what objects the user in question have access to. Meaning items like ROUTINES and in this case PROCESS etc have to be explicitly called out.