Update using parameters

If I need to insert into a table, the following works well for text fields where single quotes (apostrophes) may be used:

{

Mstm.SelectSql(new MetastormDefault(),@INSERT INTO YOUR_TABLE

(Column1,Column2)

VALUES (@p1, @p2)",

new QueryParameter("@p1",Local.Variable1),

new QueryParameter("@p2",Local.Variable2)

);

}

Is there a corresponding format for UPDATE? I haven't been able to stumble my way thru the correct syntax - I get a "name" variable error when I try to deploy even though the script validates correctly:

{

Mstm.SelectSql(new MetastormDefault(),@Update YOUR_TABLE set

Column1 = @p1,Column2 = @p2 where Column3 = @p3"

new QueryParameter("@p1",Local.Variable1),

new QueryParameter("@p2",Local.Variable2),

new QueryParameter("@p3", Local.Variable3)

);

}

Thanks for any assistance.

Find more posts tagged with

BPM

Comments

Tony Wheeler

I think you have a syntax problem in your QueryParams. You can break it up a little to be more understandable. This should work:

QueryParameter[] inputs = {
            new QueryParameter("@p1",Local.Var1),
                new QueryParameter("@p2",Local.Var2),
                new QueryParameter("@p3",Local.Var3)
        };

        Mstm.SelectSql(new MetastormDefault(),
            @"Update  YOUR_TABLE set Column1 = @p1,Column2 = @p2 where Column3 = @p3",
            inputs);

Jennifer Methlie

Unfortunately, I get the same deployment error:

This is an invalid value for "name". Valid names must be at least 0 characters, and no more than 121 characters

and the component is the code activity. But the script validated as correct again and there are no entries in the error log.

Tony Wheeler

Sounds like you might have something else going on in your solution. Does the solution validate and deploy with a simple SQL Select statement instead of the update with the same given parameters? Can you cast the native metastorm types that will be coming in off of the Local object to the .Net counterparts (ToString() for TextField, ToNullableDateTime(), for DateTimeField, etc...)

Jennifer Methlie

The solution deploys and works fine using the "Update Row" component and

String.Format("Update YOUR_TABLE set COLUMN1 = '{0}', COLUMN2 = '{2}' where COLUMN3 = '{1}'",
Local.variable1, Local.variable2, Local.variable3 )

but then throws an error if a single quote is used in the fields variable1 or variable2 (They're general text fields and can likely have the user using an apostrophe).

I was just trying to prevent a headache for the user. Thanks!

Jerome_Pearce

You should be able to execute ant SQL in the SelectSql() function.

You should probably use the SQLArg() function to create the QueryParameter object as it is cleaner.

I can see nothing wrong with your Update function call. I suspect the error is caused by something else.

jm_aussie wrote:

If I need to insert into a table, the following works well for text fields where single quotes (apostrophes) may be used:

{

Mstm.SelectSql(new MetastormDefault(),@INSERT INTO YOUR_TABLE

(Column1,Column2)

VALUES (@p1, @p2)",

      new QueryParameter("@p1",Local.Variable1),

      new QueryParameter("@p2",Local.Variable2)

      );

}

Is there a corresponding format for UPDATE? I haven't been able to stumble my way thru the correct syntax - I get a "name" variable error when I try to deploy even though the script validates correctly:

{

Mstm.SelectSql(new MetastormDefault(),@Update YOUR_TABLE set

Column1 = @p1,Column2 = @p2 where Column3 = @p3"

      new QueryParameter("@p1",Local.Variable1),

      new QueryParameter("@p2",Local.Variable2),

      new QueryParameter("@p3", Local.Variable3)

      );

}

Thanks for any assistance.

Jerome_Pearce

Try renaming the Visual Script activity?

Jennifer Methlie

Yes, I'd thought of that but it didn't work either. And I've searched for anything else that might be "name" to no avail. Only deleting the Code Actitivity Command box eliminates the error on deployment.

Jennifer Methlie

Started over and redid the whole section and the parameter works now. Not sure what the difference was but it works. Thanks!