Escape Clauses

[Lonnie]

Hi everyone,
I had the issue of hyphens in some field data that was causing issues my app. I found the code below to resolve this as well as quotes, returns and tabs and a few others but at the bottom is my attempt to escape a set of parentheses. Obviously, it didn't work. Any way to escape a set of () in field data like: teamname = CHICAGO GRAY SOX (IL)

put tScoresArray[count][team1name] into ttTeam1
put scrubData(ttTeam1) into ttTeam1 -- remove troublesome characters

function scrubData pVar
put whatKindOfVariableIsIt?(pVar) into theType
switch theType
case "string"
# Escape commas
replace comma with "" in pVar
# Escape quotes
replace "'" with "''" in pVar
replace quote with quote & quote in pVar
# Escape tab and return characters
replace return with "*RETURN*" in pVar
replace tab with "*TAB*" in pVar
# NEED Escape for ( and )
replace "(" with "*(*" in pVar
replace ")" with "*)*" in pVar
break
end switch
return pVar
end scrubData

function whatKindOfVariableIsIt? pVar
if pVar is a boolean then return "boolean"
if pVar is an integer then return "integer"
if pVar is a number then return "number"
if pVar is a point then return "point"
if pVar is a rect then return "rect"
if pVar is a date then return "date"
if pVar is a color then return "color"
return "string"
end whatKindOfVariableIsIt?

Thanks,
Lonnie

[Thierry]

.... is my attempt to escape a set of parentheses.
Obviously, it didn't work.
Any way to escape a set of () in field data like: teamname = CHICAGO GRAY SOX (IL)

Hi Lonnie,

I did try your script without any changes and it works well here.
Don't really understand what is not working.
Maybe because of a particular input text?

Feel free to send me some text where it doesn't work for you;
here or in private.

Regards,

Thierry

[dunbarx]

Looks like it should work to me as well.

But your function does not remove, say, "(", it just replaces it with "*(*". This still leaves that paren in place. Don't you want to:

Code: Select all

replace "(" with empty in...

Or perhaps some unTypable character that does no harm?

Also, please place your handler samples within the code tabs "</>" as I just did. Makes it much easier to read and work with.

Craig

[Lonnie]

The actual project is a softball tournament manager app. In this section I'm using game results to calculate each team's tournament standings. Once scores are posted I use a repeat loop to go through a list of games and the results.with the fields .. team1name, runs1, team2name, runs2. Within this loop I have another repeat loop to lookup each of these teams by name in another table that has one team per record and I post the win or loss, their total score is incremented, as well as their opponents score and if they were the winner another field stores the total differential or difference between the winner's score and the looser's score. These last few field values are used to determine their standings in the event of a tie.

I'm able to run this portion of the script without an error being generated. And was, as well, prior to using the scrubdata function. At first try the sores were properly updated if a team name didn't have a hyphen or parentheses in the team name. Then I added the scrubdata function and the teams with hyphens posted correctly but the teams with parentheses did not, no crash, just no results. I added the "(" and ")" to the last parts of scrubdata and still no score updates are posted to the teams with () in the teamname field. I know I could just add a field for state, which is what is contained in the (), but that would be a solution not a resolution to the challenge!

I started this project just to learn as much as I could about LiveCode. I'm not creating this at anyone's request so it's free to anyone interested. I wrote it originally in Visual FoxPro which I started using in the early 80's ... yes I'm in my 70's, so techniques learned come slow and don't stick around for long anymore!!

I tried to attach the stack, the background image and the database for anyone that wanted to play around with what I have so far but it wouldn't let me. So I copied the files to my Dropbox so if interested shoot me an email to lonnie dot hardesty at gmail dot com and I'll send you access codes.

I also have a MySQL database online that by changing the value of gUseServerDB variable in the login button script from false to true you access the server database and tables. The most challenging is yet to come. I need to be able to create a tournament schedule, convert it to an online HTML table, and as well post a pdf format report online for participants to download. Anyone wants to join in let me know!! See t-mgr dot com for more details. Click on Tournaments and Builder. Our league is mauiseniorsoftball dot com.

Thanks again for your help and support ... great system (totally lacking with VFP ... that's a big part of the "why" to switch)

Lonnie

[dunbarx]

Lonnie.

Since you have control over the data, name, score, whatever, you can parse the various parts by using an unusual character or character string. Then set the itemDel to isolate the several parts, and it will not matter at all what odd chars are present in the actual items.

But much better, prepare your data so that you don't need to jump through hoops to massage it into a usable form at all.

But in any case you now know you can delete whatever troublesome chars you might be forced to accept.

Craig

[Lonnie]

Thanks gang,

Ended up using placeholders so whatever was in the trn.team1name field would go into the standings.teamname.

------------------------------------------------------------------------------------------
ttTeam1 is temporary text memvar populated in the outside repeat loop along with the game results
then in the inside repeat loop I pick insert those game results into the standings table ...

put revQueryDatabase(gConID, "SELECT * FROM standings WHERE teamname = :1","ttTeam1") into theStdgsCursor
-- use the above cursor, theStdgsCursor, to create an array
ConvertSQLCursorToArray theStdgsCursor, theStdgsArray

put "UPDATE standings SET" && \
"teamname = :1," && \
"wins = :2," && \
"losses = :3," && \
"totdiff = :4," && \
"totscore = :5," && \
"oppscore = :6" && \
"WHERE teamname = :1;" into tSQL

put ttTeam1 into tTeamArray[1]
put tiWins1 into tTeamArray[2]
put tilosses1 into tTeamArray[3]
put tiTotDiff1 into tTeamArray[4]
put tiTotScore1 into tTeamArray[5]
put tiOppScore1 into tTeamArray[6]

dbUpdateData tSQL, tTeamArray -- calls revExecuteSQL gConID, pSQL to execute the UPDATE
revCloseCursor theStdgsCursor
------------------------------------------------------------------------------------------

I also read that this method also protects against SQL injection attacks which I don't fully understand but hope I'm protected.

Thanks again everyone,

Lonnie

[dunbarx]

Lonnie.

put tiWins1 into tTeamArray[2]

A suggestion. Name your keys(pseudo):

Code: Select all

 put tiWins1 into tTeamArray[TeamName][NumberOfWins]

or if tTeamArray is already slotted for a single team:

Code: Select all

 put tiWins1 into tTeamArray[NumberOfWins]

But one array(first option) can hold all the teams and all their data if you want.

Craig