dynamic queries with FreeMarker

[Eduardo M. Cavalcanti]

Hi,

I'm not managing to make dynamic queries work.

I have setup this:

object OrBroker {
private lazy val ods = {
// ... retrieve some parameters
val ods = new OracleDataSource();
// ... set oracle datasource here
ods
}

private lazy val builder = new BrokerBuilder(ods) with
FreeMarkerSupport

def build: Broker = builder.build
}

//then later in the code:

def lancsByCpf2(cpf: String): IndexedSeq[String] = {

val pcpf = if (cpf == null) "" else cpf

val lancsByCPF = Token("""
select some_field from lancamentos where id_tipo_credito = 1
<#if cpf?exists>
and cpf = :cpf
<#if>""", 'lancsByCPF)

val broker = OrBroker.build

broker.readOnly() { ssn =>
ssn.selectAll(lancsByCPF, "cpf"-> pcpf)
}
}

On execution I receive an exception like freemarker is not working. Am
I missing something? Are there Orbroker/FreeMarker examples that I can
take a look into?
Thank you.


The exception:

Message: java.sql.SQLException: Parâmetro IN ou OUT ausente do
índice:: 2
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
113)
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
147)

oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:
1748)

oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:
3410)

oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:
3460)
org.orbroker.QueryStatement$class.preparedQuery(QueryStatement.scala:
14)
org.orbroker.QueryStatement$class.query(QueryStatement.scala:43)
org.orbroker.Broker$$anon$1.query(Broker.scala:35)
org.orbroker.Queryable$class.queryFromSelect(Queryable.scala:13)
org.orbroker.ReadOnly.queryFromSelect(ReadOnly.scala:10)
org.orbroker.Queryable$class.select(Queryable.scala:31)
org.orbroker.ReadOnly.select(ReadOnly.scala:10)
org.orbroker.Queryable$class.selectAll(Queryable.scala:66)
org.orbroker.ReadOnly.selectAll(ReadOnly.scala:10)

[Nils Kilden-Pedersen]

On Tue, Sep 20, 2011 at 4:21 PM, Eduardo M. Cavalcanti <eduardo.c...@eversystems.com.br> wrote:

       def lancsByCpf2(cpf: String): IndexedSeq[String] = {

               val pcpf = if (cpf == null) "" else cpf

               val lancsByCPF = Token("""
               select some_field from lancamentos where id_tipo_credito = 1
               <#if cpf?exists>
                 and cpf = :cpf
               <#if>""", 'lancsByCPF)

Not sure if this is the problem, but the closing <#if> is missing the /. Should be </#if>.

[Eduardo M. Cavalcanti]

Thanks for help.
I have corrected it , but the error is the same.
"freemarker" does not appear in the stack trace, so I am suposing its function  isn't even being activated. What triggers its use? Just creating a BrokerBuilder with FreeMarkerSupport?
I've tried to use orbroker 3.2rc1. I can't compile my code with it in my environment (eclipse + scala ide plugin 2 beta 10, scala 2.9.1).
3.2 will dump the dynamically generated SQL? How does it work?
Can you provide the source code for 3.2rc1 so I can compile it in Scala 2.9.1?
Thanks.

[Nils Kilden-Pedersen]

Oh, I remember now. FreeMarker/Velocity is not available for statements that are provided at runtime (as opposed to registered with the BrokerConfig/Builder). This is because you can just construct the SQL in Scala then, no need for a template language.

[Eduardo M. Cavalcanti]

Thanks for the clarification, Nils.