SQLite near error
Posted: Tue Oct 15, 2013 2:36 am
Hello,
SQLite near error due to single quote "'" that contains in text streams (strings). Example:
Danny's Files
Côte d'Ivoire
French words, etc.
I modified the file sql1.prg from <c>:\hmg\samples\sql\sqlite that contains the function c2sql() as follows:
The side effect is that it slows down the whole process most especially in batch mode. I have commented the lines that I've added (//--). If the variable "value" happens to be a memo, definitely it will be WWW (world wide wait) process.
Well it can be done from the calling function but by convention it must be processed from within the c2sql().
FYI.
Regards,
Danny
SQLite near error due to single quote "'" that contains in text streams (strings). Example:
Danny's Files
Côte d'Ivoire
French words, etc.
I modified the file sql1.prg from <c>:\hmg\samples\sql\sqlite that contains the function c2sql() as follows:
Code: Select all
function C2SQL(Value)
local cValue := ""
local ii := 0 //--> added local variable
local cTmp := "" //--> added local variable
local cFormatoDaData := set(4)
do case
case Valtype(Value) == "N"
cValue := AllTrim(Str(Value))
case Valtype(Value) == "D"
if !Empty(Value)
cdate := dtos(value)
cValue := "'"+substr(cDate,1,4)+"-"+substr(cDate,5,2)+"-"+substr(cDate,7,2)+"'"
else
cValue := "''"
endif
case Valtype(Value) $ "CM"
IF Empty( Value)
cValue="''"
ELSE
//-- fixed escaping single quote. Like Pascal Programming add an immediate "'"
//-- to the single quote that precedes it. I have no choice but to use
//-- brute force on this rather using rat() and at()
for ii := 1 to len( value )
if substr( value, ii, 1 ) == "'"
cTmp += substr( value, ii, 1 ) + "'"
else
cTmp += substr( value, ii, 1 )
endif
next
value := cTmp
cValue := "'" + value+ "'"
ENDIF
case Valtype(Value) == "L"
cValue := AllTrim(Str(iif(Value == .F., 0, 1)))
otherwise
cValue := "''" // NOTE: Here we lose values we cannot convert
endcase
Well it can be done from the calling function but by convention it must be processed from within the c2sql().
FYI.
Regards,
Danny