dBASE Web Applications - Help with WebClass.cc

Author

Help with WebClass.cc

Claus Mygind

2005-12-29, 8:23 pm

I am trying to use MySQL database tables with my dBase web apps.

WebClass.cc has been a great help in developing web applications.
It's reusability has be so great that you almost take some functions for
granted.

However, (and I don't know if this is specific to MySQL) on occasion some
dBase commands fail and you have to use the more specific sql command. Such
is the case with the .save( ). I have twice done something to the MySQL
table, where any record entered via my web app became non-updateable and you
get this nasty error message: "Database Engine Error: Couldn't perform the
edit because another user changed the record."

After much research and testing and input previously given by Ivar, I have
narrowed down the problem to this line:

rf.parent.save() // save row

which is found in the "Function loadFieldsFromArray(
)" of WebClass.cc.

This save command works fine for inserting a new record and also for editing
records that I imported via append from my dBase tables. But not for
records that I have added via my web apps.

The workaround is roughly as follows:

q.requestlive = false
q.active = true
q.sql = [update master set name = 'Mr. Claussen' where id = 'CLAU3001']
q.requery( )

The problem of course is you loose all that functionality of the webClass
where the rowset fields are updated and you have to construct the "set
field1 = variable1, field2=variable2" portion of the update command
yourself. Another issue I will have to figure out is how to deal witht the
various field types as they will have to be constructed appropriatetly.

Below is the function as it appears in the webClass with the workaround I
just mentioned. Could someone help me re-write this function to incorporate
the update command instead of the save command?

Function loadFieldsFromArray(
rowsetFields,bAppend
)

bAppend = iif(empty(bAppend),f
alse,true)

rf = rowsetFields // Create private instance of param

////// If bAppend, make new row
if bAppend
rf.parent.beginAppend()
endif

////// Establish first key
cKey = this.firstKey

///// Traverse the array
for n = 1 to this.count()

if type('rf[cKey]') = 'O' // if field exists

with (rf[ckey]) // with (field)

if type = "LOGICAL" // convert text to type and store

value = iif(upper(this[cKey]) ='TRUE' or ;
upper(this[cKey]) = 'Y',true,false)

elseif type = "NUMERIC" or ;
type = "INTEGER" or ;
type = "DOUBLE" or ;
type = "FLOAT"

value = val(this[cKey])

elseif type = "DATE"

value = ctod(this[cKey])

elseif type = "AUTOINC" or ;
type = 'BINARY' or ;
type = 'TIMESTAMP' or ;
type = 'OLE'

// do nothing!! Autoincrement is automatic
// and the others not supported in HTML

else // Char or memo

value = trim(this[cKey])

endif

endwith

endif
////// Go to next key
cKey = this.nextKey(cKey)

next

if not rf.parent.endOfSet // if not end of set

if bAppend //test if this is an add or edit.
rf.parent.save() // save row
else
q.requestlive = false
q.active = true
//need a better way to construct the line below
//so that it is variable
q.sql = [update master set name = 'Mr. Claussen' where id =
'CLAU3001']
q.requery()
endif
endif
return true

Claus Mygind

2005-12-30, 8:23 pm

Ok I was able to modify WebClass.cc by adding a few parameters ( see example
below ).

Now I just have one question how to deal with single and double quotes in
the string. If you delimit each variable with either type of quote and one
of those show up in the string it will cause an error. So how to format the
"Update" so it will not be a problem (ie: escape the quotes)?

q.sql = [update ]+cTableName+[ set ]+cSet+[ where ]+cWhere+[]

If there is a better way to build the parameter string for the update like
using
the : I would like to see how that would be formatted?

example of WebClass.cc fix

1. calling app passes the name of the table, the key field(s) name(s) and
record key value:

oCGI. loadFieldsFromArray(
q.rowset.fields, false, "master", "ID", nKey)

2. WebClass.cc
//function receives extra parameters cTableName, cTableKey and cKeyValue
Function loadFieldsFromArray(
rowsetFields,bAppend
,cTableName,
cTableKey,cKeyValue)

// local variables are created to build the update string
local cSet, cWhere
//cSet is initalized with a blank to test for first addition to it.
cSet = " "
cWhere = cTableKey+" = '"+cKeyValue+"'"

//In the loop that Traverse the array we capture the
//name and value of the array variable by variable type
for n = 1 to this.count()

//here are two examples where we
//capture both name of variable cKey and its value
elseif type = "DATE"
value = ctod(this[cKey])
//update cSet variable string
cSet += iif(not empty(cSet),',
','')+cKey+'='+iif(e
mpty(value), 0,value)

else // Char or memo
value = trim(this[cKey])
// update character string
cSet += iif(not empty(cSet),', ','')+cKey+'="'+value+'"'
endif

////// Go to next key
cKey = this.nextKey(cKey)

next

//test if this is add or update
if bAppend
rf.parent.save() // save row
else

/*
Use update instead of save for edited records.
*/
q.requestlive = false
q.active = true
//need to know how to escape single and double qoutes
// in case they should appear in the cSet string
q.sql = [update ]+cTableName+[ set ]+cSet+[ where ]+cWhere+[]
q.requery()
q.active = false
endif

Claus Mygind

2006-01-03, 9:23 am

The final piece of the puzzle how to escape the ' . By adding an
escapeQuote( ) function to the WebClass.cc I was able to customize it to
MySQL tables.

In the loadFieldsFromArray(
) function I added "class::escapeQuote( )"

else // Char or memo
value = trim(this[cKey])
cSet += iif(not empty(cSet),", ","")+;
cKey+"='"+;
iif(empty(value),val
ue,class::escapeQuot
e(value))+;
"'"
endif

Then I simply added this function to add a two single quotes (an escape
sequence for quote in MySQL

Function escapeQuote(cStr)
local cRetStr, nLen, cChar, i
//skip search if ' is not contained in string
if "'" $ cStr
cRetStr = ""
nLen = len(cStr)
for i = 1 to nLen
cChar = substr(cStr, i, 1)
if cChar = "'"
cRetStr += "''"
else
cRetStr += cChar
endif
next i
else
cRetStr = cStr
endif
return cRetStr

Sponsored Links