Thunderstone Support Forums

We've got a form we're posting to a vortex page, with several values in it. One of them is a comma separated list of the form variables, such as:

FIELD1,FIELD2,FIELD3,FIELD4

etc.

In the vortex script, we're trying to generate an update statement. Something like this is what we're shooting for (in pseudovortex):


update mytable set FIELD1 = <getvar $FIELD1>, FIELD2 = <getvar $FIELD2>, FIELD3= <getvar $FIELD3> .......


OK, so the syntax there isn't great, but you get the idea.

In a nutshell, what we're doing is posting a string with the fields we're interested in, then looping through that string to build our SQL statement. We just can't seem to get it right, keep hitting problems like some of our fields have single quotes and other bad characters in them, and the update statement chokes on that.

We know it's possible to avoid those kinds of problems by using vortex variables in lieu of actual text values in the update statement, but we can't seem to get it right, and would love some sort of sample, ideally using the posted string/list of field names to help create the statement, then doing some sort of <getvar> magic to pull out the actual field values and put them into the update statement. Just keep in mind that some of those variables will have single quotes and other evil characters in them. Is what we're trying possible?

Does every SQL field name only get set from the Vortex variable of the same name (if it is to be updated)? Eg. Field1 only ever gets assigned from $Field1? (Your pseudoVortex with <getvar> implies another level of indirection, ie. that $Field1 is the name of the ultimate value, not the value itself. I assume that's a typo and you meant <getvar "Field1"> not <getvar $Field1>.)

Then you can do this, assuming $flds is your string containing the list of fields to update (comma-separated):

<$stmt = >
<rex row "[\alnum_]+" $flds> <!-- get each field name in $ret -->
<strfmt "%s, %s=$$%s" $stmt $ret $ret> <!--add ", Fld = $$Fld"-->
<$stmt = $ret>
</rex>
<substr $stmt 2 -1> <!-- strip the leading ", " -->
<strfmt "update mytable set %s" $ret>
<$stmt = $ret>
<SQL $stmt></SQL>

Given <$flds = "Field1,Field7">, this yields $stmt set to:

update mytable set Field1=$Field1, Field7=$Field7

The double-dollar sign is to escape $ until <SQL> evaluates it.

(Note that every possible $FieldN Vortex variable must explicitly be used as $Field1 etc. in the script somewhere, not just indirectly like this.)

Also, you should add a check for $loop = 0 after </rex>, in case there's no fields. Also, each field in the loop should be checked against a master list of valid fields to avoid invalid statements or changing illegal fields.

Thanks Kai, We still are having a problem. There were no syntax errors or anything, but the update did not work. We got only blank values on the fields. Just for your information we are passing in the values as Form hidden variables.
Any ideas??

thanks!!

Check that every variable (not field) that you're getting update values from actually exists in the script. Ie. if $Field29 is a possible value, then the actual variable $Field29 must be used somewhere (even in another function) explicitly. Otherwise it doesn't exist as a variable in the script, and will appear to have no values. (The dynamic reference in the <strfmt>-built statement does not count.)

Make up a dummy private function if you have to:

<A NAME=dummy PRIVATE>
$Field1 $Field2 $Field3 ... $Field29
</A>

There just needs to be an explicit reference in the script to the variables you're using in the UPDATE statement.

Hi, Thanks. That worked. But here is one small problem. Our fields names could be a small subset of some 1400 fields. Is there any way to dynamically execute a statement.
That is, Go through the field list loop and declare them dynamically in the dummy function?

For example, something like this,
here let us say $flds is an array,
<loop $flds>
somehow declare the current element in the array
</loop>

thanks!!

See: <varinfo> , its probably a better way of doing this.

I looked in varinfo. I still could not figure out how to declare the variables (coming from query string or form post) dynamically. Maybe I am not looking in the right place. Sorry!!

Could you help us?
thanks!!

For fields or variables that will be used within <sql> there is no choice but to mention them somewhere in the script as suggested by kai above.