Is there an alternative to using ( ’ ) in pub:execSQL? Here’s an example, the $dbSQL has the following informaiton -
insert into table_A ( field_1, field_2, field _3 )
values ( ‘%value one%’, ‘%value two%’, ‘%value 3%’)
An error will occur if a user types something like “I can’t use this word or don’t becuase it causes an error”. If values one, two or three contains ( ’ ) in it, it kills the SQL. Is there a way around this? Thanks.
There is an alternative. When using the execSQL service
Set the $dbSQL to insert into table_A ( field_1, field_2, field _3 ) values ( ?,?,?)
and then use the input $dbParamNames stringlist to specify the names of the three input variables. Also use $dbParamTypes stringlist to specify their datatypes.
When you execute the service it will stuff the first parameter in the first ? slot, the second in the second and thr third in the third slot. Datatypes are also converted properly. so you dont run into these kinds of problems.
The alternative is there using WM $execSQL replacemet (?,?,?) $dbParamNames adn $dbParamTypes. However, if your data containe somedatatype like Date forexample and its not in correct database format you have to transform it before calling execSQL! In this case you can validtae and transform any data going in to be valid format. This is not limitation of WM but a rule of SQL and Dtabase