Calling procedure using infix not working.

[thunderkid]

Version: 1.2.1 Module: quill-async Database: mysql

I'm trying to call a stored procedure using infix.

    def myProcToyOdd = quote {
      (id: String) => infix"""call Try1()""".as[Query[Int]]
    }

    def procToyOdd() = run(myProcToyOdd("welsh"))

Expected behavior

Runs. (As a test if I replace the call Try1() with, eg, an UPDATE sql statement, it runs fine. And call Try1() runs fine from the sql console.)

Actual behavior

Returns the following error:

scala> 01:38:31.298 DEBUG [] [connection-handler][mysql-connection-1] - Channel became active
01:38:31.454 ERROR [] c.g.m.async.db.mysql.MySQLConnection - Received an error message -> ErrorMessage(1064,#42000,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 '()) x' at line 1)

[mosyp]

As said in documentation http://getquill.io/#extending-quill-infix infix is introduced to extends non-supported by quill features. To run native sql, please use execute* methods on context.

@thunderkid Feel free to reopen the issue if this is not working for you :)

[thunderkid]

Is there any documentation on the execute* methods? I assume that to run a procedure that returns something I'd need to use executeActionReturning, but there are no docs on the meaning of the extractor or returningColumn args.

[mosyp]

@thunderkid there's not yet documentation, see https://github.com/getquill/quill/issues/1017.

[mosyp]

@thunderkid however it seems like calling stored procedure requires connection.prepareCall see this. Hence non of execute* methods does not provide such approach. I'd recommend to extend your jdbc context and use withConnection method to pull jdbc connection from pool and use it depending on your needs (calling prepareCall etc.)

[thunderkid]

@mentegy Thanks. But that looks quite involved. I thought calling a stored procedure would be a fairly common use case.

[dalmat36]

@thunderkid Did you have any luck calling stored procedure using Quill?

[thunderkid]

@dalmat36 No, I didn't. I actually switched back from Quill to Slick because of that. It made me rather nervous that something I'd consider pretty mainstream wasn't a standard feature in Quill. That was a while back, so perhaps things have changed by now.

[fwbrasil]

Reopening. We should provide an API for this, even though it doesn't seem a common use case

[stondo]

Any updates on this please?

[stondo]

In case anyone is interested I was eventually able to call a stored procedure on a MySql like database:

  override def clean(): Task[Option[Int]] = {
    val rawStoredProcedure = quote {
      sql"""select dolt_clean('--dry-run')""".pure.as[Int]
    }

    run(rawStoredProcedure).map(Option.apply)
  }