Snippets

APG SQL Cleaner for dataflex.

Created by Sean Bamforth last modified
SQLCleaner.pkg
Raw 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
//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

Comments (0)

HTTPS SSH

You can clone a snippet to your computer for local editing. Learn more.