sqlUpdate's statement doesn't appear to accept a variable argument

 Hi,

testString variable reference needs to be prefixed with "$"

<sqlUpdate name="dataSource"
            statement="$testString"
            outputvariable="result" />

Thanks for the quick response:

I've  made the following change:

        <sqlUpdate name="dataSource"
            statement="$testString"
            outputvariable="result" />

and run the mashup again with the following results:

Error execution Mashup Script : org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [$testString]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "$" : EMML Line # 30 : EMML Line # : 6

Which presto version are you using ?

Also, can u try using template and check the behavior...i.e.,

         <variable name="testString" type="string"></variable>

        <template expr="update usersearchprefs set userdn = 'admin',sortfield = 'what?', numrecords = 50 where userdn = 'admin'" outputvariable="testString"/>

        <sqlUpdate statement="$testString" name="mydb" />

I'm using Version 2.7.0

I've attached the mashup that uses the template and returns the following error:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [$testString]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "$" : EMML Line # 35 : EMML Line # : 6

 Hi,

my bad, dynamic variable ($variable) support in sqlUpdate is available only in yet-to-be released next version of Presto.

In 2.7, only static statements with named parameters are supported. 

 To clarify named params,

you could do something like...

<mashup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    xsi:schemaLocation="http://www.jackbe.com/2008-03-01/EMMLSchema../src/schemas/EMMLSpec.xsd"

    xmlns="http://www.jackbe.com/2008-03-01/EMMLSchema" xmlns:macro="http://www.jackbe.com/2008-03-01/EMMLMacro"

    name="TestIfExistsUpdateElseInsert">

    <operation name="invoke">

        <datasource name="dataSource" url="jdbc:hsqldb:hsql://localhost:9001" username="sa" password="" />

        <input name="numRecords" type="number" default="50" />

        <input name="sortField" type="string" default="what?" />

        <input name="DN" type="string" default="admin" />

        <input name="newDN" type="string" default="newadmin" />

        <output name="result" type="string" default="" />

    <variables>

    <variable name="testString" type="string"></variable>

    </variables>

        <sqlUpdate name="dataSource"

            statement="update usersearchprefs set userdn = :newDN, sortfield = :sortField,  numrecords = 50 where userdn = :DN "  outputvariable="result" />

    </operation>

</mashup>                                                                                                                                                                                                    

Hi Raj,

I've tried using a bound parameter in the sqlUpdate statement attribute as suggested but that doesn't appear to work either.

Here's the error message I get:

373 ERROR [com.jackbe.jbp.jems.moe.runtime.JEMSDesignerImpl] - <Error execution Mashup Script : org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [DROP TABLE ?]; nested exception is java.sql.SQLException: Incorrect syntax near '@P0'. : EMML Line # 126 : EMML Line # : 6>

and this was from the emml statement:

                <sqlUpdate name="$dsName" statement="DROP TABLE :table"
                    outputvariable="sqlCmdResult" />

where the table variable holds the actual table name.

Any thoughts?

Cheers, Innes

Hi mashers,

Just confirming that in Presto 3.0, the <sqlUpdate> statement now supports dynamic variables via a <template> assigned variable. 

For example:

...

<variable name="dbTable" type="string" default="myTable" />

<variable name="colNames" type="string" default="mycol1,mcol2" />

<variable name="varBindings" type="string" default=":mycol1,:mycol2" />

...

<template expr="INSERT INTO {$dbTable} ({$colNames}) VALUES ({$varBindings})" outputvariable="sqlStmt" />

<sqlUpdate name="myDataSrc" statement="$sqlStmt" outputvariable="sqlRC"/>

...

now works as it did in Presto 2.7 for the <sql> statement.

Thanks to everyone at JackBe for including this in 3.0 as promised. You have just saved me a whole lot of hours and coding headaches on a major SQL database population project I'm working on.

Cheers everyone,

Mash on!!

Innes (NZ)

 That's great to know Innes !