 |
tessellated
Beginner
Joined: 19 Feb 2005
Posts: 12
|
 Posted: Sat Feb 19, 2005 4:52 am
Inserting text into a string that has special chars
|
Ok. I am building sql statements inside zmud that are to be sent to a 3rd party database but am encountering strangeness with zmud's string functions when manipulating parameters that contain special chars such as an underscore or a comma.
I have built a test alias called "tq" that takes as input a couple of parameters. You'll see that the alias looks overly complicated but that is because I have been forced through trial and error to implement lots of what I would have hoped would be unecessary code.
tq alias as follows:
=========================
Sql_1 = "SELECT ObjectID,ObjectString,IDDate FROM Objects WHERE "
Sql_1 = %concat( @Sql_1, "(ObjectString LIKE '%")
#IF (%pos( "_", %1)) {Sql_1 = %format( "&s&s", @Sql_1, %string( %replace( %1, "'", "''")))} {
Sql_1 = @Sql_1 %replace( %1, "'", "''")
Sql_1 = %delete( @Sql_1, %len( @Sql_1) - %len( %replace( %1, "'", "''")), 1)
}
Sql_1 = %concat( @Sql_1, "%' ")
#show Sql_1 = @Sql_1
Sql_2 = "OR MudZone LIKE '%"
#IF (%pos( "_", %2)) {Sql_2 = %format( "&s&s", @Sql_2, %string( %replace( %2, "'", "''")))} {
Sql_2 = @Sql_2 %replace( %2, "'", "''")
Sql_2 = %delete( @Sql_2, %len( @Sql_2) - %len( %replace( %2, "'", "''")), 1)
}
Sql_2 = %concat( @Sql_2, "%' ")
#show Sql_2 = @Sql_2
Sql = @Sql_1 @Sql_2
#show Sql = @Sql
=======================
Still reading? :P
Ok, so if tq was behaving AS I WANT the output would be:
Sql_1 = SELECT ObjectID,ObjectString,IDDate FROM Objects WHERE (ObjectString LIKE '%dark_blue%'
Sql_2 = OR MudZone LIKE '%red%'
Sql = SELECT ObjectID,ObjectString,IDDate FROM Objects WHERE (ObjectString LIKE '%dark_blue%' OR MudZone LIKE '%red%'
BUT the real output is:
Sql_1 = SELECT ObjectID,ObjectString,IDDate FROM Objects WHERE (ObjectString LIKE '%dark_blue%'
Sql_2 = OR MudZone LIKE '%red%'
Sql = SELECT ObjectID,ObjectString,IDDate FROM Objects WHERE (ObjectString LIKE '%' OR MudZone LIKE '%red%' |
|
|
|
|
Vijilante
SubAdmin
Joined: 18 Nov 2001
Posts: 5182
|
Posted: Sat Feb 19, 2005 5:36 am
|
The problem you are having is really 3 different issues. Here is a working version:
| Code: |
Sql_1 = %concat("SELECT ObjectID,ObjectString,IDDate FROM Objects WHERE (ObjectString Like '%",%replace("%1","'", "''"),"%' ")
#show Sql_1 = %expand(@Sql_1,1)
Sql_2 = %concat("OR MudZone LIKE '%",%replace("%2", "'", "''"),"%' ")
#show Sql_2 = %expand(@Sql_2,1)
Sql = %concat(%expand(@Sql_1,1),%expand(@Sql_2,1))
#show Sql = %expand(@Sql,1) |
First is lack of delimeters around %1 and %2 in your %replaces. These references are expanded early. The exact process of which is a direct substitution. As your alias is taken from storage and passed for parsing those references are replaced, they should nearly always be enclosed in something that the parser can recognize to protect against bad inputs.
Next is recursive expansion. The normal operating mode for variable and other references is to expand the reference until no further expansion can be found. This is controlled through the use of the %expand function. You will see in in the one I produced all the variable references are enclosed with this function and the expansion level is set to 1 so only the variables contents are returned and not further expansion is preformed.
Finally the use of implicit concataination. An example of this from your original: Sql_1 = @Sql_1 %replace( %1, "'", "''"); here the results of the %replace are implicitly concatted onto the results of @Sql_1, then assigned into Sql_1. This results in the extra space you had to %delete. I find the explicit use of %concat to be less prone to unintended results, in this particular case the extra space was undesired. The correct way to write it using the implicit version to eliminate that space is: Sql_1 = @{Sql_1}%replace( %1, "'", "''"). |
|
_________________
The only good questions are the ones we have never answered before.
Search the Forums |
 |
|
|
tessellated
Beginner
Joined: 19 Feb 2005
Posts: 12
|
Posted: Sat Feb 19, 2005 6:23 am
|
Awesome, Vijilante. Thank you so much for the very informed and quick reply!
|
|
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Sat Feb 19, 2005 7:11 am
|
sorry misread
|
|
|
|
|
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
