Created by
Sean Bamforth
last modified
| //cleaner
Use variant.pkg
Global_Variable Integer ghSQLStringBuilder
Global_Variable Integer ghMaxSQLParamlength
Object oSQLStringBuilder is a cObject
Move (Self) to ghSQLStringBuilder
Function Convert_Boolean Boolean isOK Returns String
If (isOK) Function_Return "True"
Else Function_Return "False"
End_Function
Function to2Chars Integer iInp Returns String
String sRetVal
Move ("000"+(String(iInp))) to sRetVal
Function_Return (right(sRetVal ,2))
End_Function
Function Convert_DateTime DateTime dtInp Returns String
String sReturn
If (String(dtInp)="") Function_Return "'1753-01-01'"
Append sReturn "'" (DateGetYear(dtInp))
Append sReturn "-" (to2Chars(Self,(DateGetMonth(dtInp))))
Append sReturn "-" (to2Chars(Self,(DateGetDay(dtInp))))
Append sReturn "'"
Function_Return sReturn
End_Function
Function Convert_String String sInp Returns String
If (ghMaxSQLParamlength=0) Move 250 to ghMaxSQLParamlength
Move (Trim(sInp)) to sInp
Move (Left(sInp,ghMaxSQLParamlength)) to sInp
Move (Replaces("'",sInp,"''")) to sInp
Move 250 to ghMaxSQLParamlength
Function_Return ("'" + sInp + "'")
End_Function
Function Convert_Number Number nInp Returns String
Function_Return nInp
End_Function
End_Object
Function BuildSQL Global String sCommand Variant sParam1 Variant sParam2 Variant sParam3 Variant sParam4 Variant sParam5 Variant sParam6 Variant sParam7 Returns String
String sParam
Integer iArg
Variant vTest
Integer iType
String sArg
If (num_arguments>10) Error DFERR_WRONG_NUMBER_OF_ARGUMENTS // only allow max of 9 params
Move (Replaces("%%",sCommand,"$%$")) to sCommand // %% is used when you want a single % followed by a 1-9. e.g. "%%1"=%1
For iArg from 2 to Num_Arguments
Move iArg& to vTest
Move (DeRefW(AddressOf(vTest), 0)) to iType
Move (iArg-1) to sArg
If (iType=OLE_VT_Decimal) Move (Convert_Number(ghSQLStringBuilder,vTest)) to sParam
If (iType<=OLE_VT_Cy) Move (Convert_Number(ghSQLStringBuilder,vTest)) to sParam
If (iType=OLE_VT_Bool) Move (Convert_Boolean(ghSQLStringBuilder,vTest)) to sParam
If (iType=OLE_VT_Bstr) Move (Convert_String(ghSQLStringBuilder,vTest)) to sParam
If (iType=OLE_VT_Date) Move (Convert_DateTime(ghSQLStringBuilder,vTest)) to sParam
If (iType=OLE_VT_Bstr) Move (Replaces("'%"+sArg+"'",sCommand,sParam)) to sCommand
If (iType=OLE_VT_Date) Move (Replaces("'%"+sArg+"'",sCommand,sParam)) to sCommand
Move (Replaces("%"+String(iArg-1),sCommand,sParam)) to sCommand
Loop
Move (Replaces("$%$",sCommand,"%")) to sCommand
Function_Return sCommand
End_Function
Function LikeString Global String sFront String sInp String sBack String sEscapeChar Returns String
If (sFront<>"%") Move "" to sFront
If (sBack<>"%") Move "" to sBack
Move (Replaces("%",sInp,sEscapeChar+"%")) to sInp
Move (Replaces("_",sInp,sEscapeChar+"_")) to sInp
Function_Return (sFront+sInp+sBack)
End_Function
|