onefish

EasySQL custom function

Discussion created by onefish on Jan 6, 2019
Latest reply on Jan 7, 2019 by badmonkey842

So I had this thought that it would be pretty simple to write a CF to make SQL queries with quoted and dynamic table and field references a bit quicker. I already have a couple CFs that I wrote just for the dynamic references but they still require a Let statement and ….

 

Anyway turns out it was anything but simple due to all the variables and possibilities with ExecuteSQL. And I look at all those CF parameters and I wonder if it's aptly named at all, LOL.

 

But it is actually very simple to use within its limitations. Anyway, I thought I'd chuck it up here for a laugh at least.

 

PS. It uses UniqueValues so 16 or 17 only, although it is only for an error check so could be removed, and it's not recursive.

 

EasySQL ( TabFld1 ; TabFld2 ; TabFld3 ; TabFld4 ; Dist|1| ; FldSep ; RowSep ; WH|1|_GB|2|_HV|3|_Fld_Expr1 ; WGH_Fld_Expr2 ; WGH_Fld_Expr3 ; WGH_Fld_Expr4 ; WGH_Fld_Expr5 ; WGH_Fld_Expr6 ; WGH_Fld_Expr7 )

 

Example: EasySQL ( table::field1 ; table::field2 ; table::field3 ; "" ; "" ; "|" ; "¶" ; 1 ; table::ID ; " =3" ; "" ; "" ; "" ; "" )

 

returns:     field1value|field2value|field3value

                 field1value|field2value|field3value

                 field1value|field2value|field3value

where the value in the ID field = 3.

………………………………………………………………………………..

/* Allows for construction and execution of simple SQL statements. Field and table name references are dynamic so the SQL won't break if tables/fields are renamed. There are 4 parameters for table::field names on the SELECT side of the statement. If more fields are required then use List ( GetFieldName ( table::field ) ; GetFieldName ( table::field ) ; ....) in any of the four parameter spaces to include the desired fields.

The FROM side of the statement is automatic based on table::field references used. The function will allow multiple tables but only using the UNION join, INNER JOIN and LEFT OUTER JOIN are not supported. Accordingly the same quantity of fields must be specified for each table.

There are specified parameter placements for DISTINCT, Field Seperator and Row Seperator. Enter the number 1 in the correct parameter to include DISTINCT in the SQL statement. Wrap the field and row seperators in quotations "" as per normal text constants.

There are 7 parameters to the right of the Row Seperator parameter for including WHERE (1), GROUP BY (2), HAVING (3), field names by reference and expressions. All 7 of these parameters accept any of the above data but there can only be one data type per parameter and they must be in the order required by SQL. Wrap expressions in quotations "" as per normal text constants. SQL arguments that are not explicitly listed here are not built into the CF however they may work by typing them out in the correct sequence in any of the 7 parameter spaces to the right of the Row Seperator parameter.

Unused parameters should contain "". Placing a 0 in parameter 7 (WGH_Fld_Expr7) will return the SQL statement for debugging purposes and it will not be executed.*/

 

Let ( [

f1 = GetFieldName ( TabFld1 ) ;
f2 = GetFieldName ( TabFld2 ) ;
f3 = GetFieldName ( TabFld3 ) ;
f4 = GetFieldName ( TabFld4 ) ;
f1 = If ( PatternCount ( f1 ; "::" ) = 1 ; f1 ; TabFld1 ) ;
f2 = If ( PatternCount ( f2 ; "::" ) = 1 ; f2 ; TabFld2 ) ;
f3 = If ( PatternCount ( f3 ; "::" ) = 1 ; f3 ; TabFld3 ) ;
f4 = If ( PatternCount ( f4 ; "::" ) = 1 ; f4 ; TabFld4 ) ;

d = If ( Dist|1| = 1 ; "DISTINCT " ; "" ) ;
fs = If ( IsEmpty ( FldSep ) ; "|" ; FldSep ) ;
rs = If ( IsEmpty ( RowSep ) ; "¶" ; RowSep ) ;

w1 = GetFieldName ( WH|1|_GB|2|_HV|3|_Fld_Expr1 ) ;
w2 = GetFieldName ( WGH_Fld_Expr2 ) ;
w3 = GetFieldName ( WGH_Fld_Expr3 ) ;
w4 = GetFieldName ( WGH_Fld_Expr4 ) ;
w5 = GetFieldName ( WGH_Fld_Expr5 ) ;
w6 = GetFieldName ( WGH_Fld_Expr6 ) ;
w7 = GetFieldName ( WGH_Fld_Expr7 ) ;
w1 = Case ( PatternCount ( w1 ; "::" ) = 1 ; GetValue ( Substitute ( w1 ; "::" ; "¶" ) ; 2 ) ;
           WH|1|_GB|2|_HV|3|_Fld_Expr1 = 1 ; " WHERE " ;
           WH|1|_GB|2|_HV|3|_Fld_Expr1 = 2 ; " GROUP BY " ;
           WH|1|_GB|2|_HV|3|_Fld_Expr1 = 3 ; " HAVING " ;
           WH|1|_GB|2|_HV|3|_Fld_Expr1 ) ;
w2 = Case ( PatternCount ( w2 ; "::" ) = 1 ; GetValue ( Substitute ( w2 ; "::" ; "¶" ) ; 2 ) ;
           WGH_Fld_Expr2 = 1 ; " WHERE " ;
           WGH_Fld_Expr2 = 2 ; " GROUP BY " ;
           WGH_Fld_Expr2 = 3 ; " HAVING " ;
           WGH_Fld_Expr2 ) ;
w3 = Case ( PatternCount ( w3 ; "::" ) = 1 ; GetValue ( Substitute ( w3 ; "::" ; "¶" ) ; 2 ) ;
           WGH_Fld_Expr3 = 1 ; " WHERE " ;
           WGH_Fld_Expr3 = 2 ; " GROUP BY " ;
           WGH_Fld_Expr3 = 3 ; " HAVING " ;
           WGH_Fld_Expr3 ) ;
w4 = Case ( PatternCount ( w4 ; "::" ) = 1 ; GetValue ( Substitute ( w4 ; "::" ; "¶" ) ; 2 ) ;
           WGH_Fld_Expr4 = 1 ; " WHERE " ;
           WGH_Fld_Expr4 = 2 ; " GROUP BY " ;
           WGH_Fld_Expr4 = 3 ; " HAVING " ;
           WGH_Fld_Expr4 ) ;
w5 = Case ( PatternCount ( w5 ; "::" ) = 1 ; GetValue ( Substitute ( w5 ; "::" ; "¶" ) ; 2 ) ;
           WGH_Fld_Expr5 = 1 ; " WHERE " ;
           WGH_Fld_Expr5 = 2 ; " GROUP BY " ;
           WGH_Fld_Expr5 = 3 ; " HAVING " ;
           WGH_Fld_Expr5 ) ;
w6 = Case ( PatternCount ( w6 ; "::" ) = 1 ; GetValue ( Substitute ( w6 ; "::" ; "¶" ) ; 2 ) ;
           WGH_Fld_Expr6 = 1 ; " WHERE " ;
           WGH_Fld_Expr6 = 2 ; " GROUP BY " ;
           WGH_Fld_Expr6 = 3 ; " HAVING " ;
           WGH_Fld_Expr6 ) ;
w7 = Case ( PatternCount ( w7 ; "::" ) = 1 ; GetValue ( Substitute ( w7 ; "::" ; "¶" ) ; 2 ) ;
           WGH_Fld_Expr7 = 1 ; " WHERE " ;
           WGH_Fld_Expr7 = 2 ; " GROUP BY " ;
           WGH_Fld_Expr7 = 3 ; " HAVING " ;
           WGH_Fld_Expr7 ) ;

f = List ( f1 ; f2 ; f3 ; f4 ) ;
w = w1 & w2 & w3 & w4 & w5 & w6 & w7 ;

s = " SELECT " & d ;
i = ValueCount ( f ) ;
qTab = UniqueValues ( Substitute ( f ; "::" ; "¶::" ) ) ;
qTab = ValueCount ( qTab ) - PatternCount ( qTab ; "::" ) ;
f = If ( i = 1 ; "\"" & f & "\"" ; "List ( \"" & Substitute ( f ; "¶" ; "\" ; \"" ) & "\" )" ) ;
j = "UNION" ;

QA = Case ( i = 1 ; 1 ;

                    qTab = 1 ; 1 ;

                    Mod ( i ; 2 ) ≠ 0 ; 0 ;

                    Substitute ( 10 ^ ( qTab - 1 ) - 1 ; 9 ;

                              "ad = ad + t ;
                              t1 = PatternCount ( " & f & " ; GetValue ( Substitute ( GetValue ( " & f & " ; ad ) ; \"::\" ; \"\¶\" ) ; 1 ) ) ;
                              bl = If ( t / t1 = bl ; bl ; 0 ) ; " ) ) ;

QA = If ( QA = 0 or QA = 1 ; QA ; Evaluate ( "Let ( [ ad = 1 ; t = PatternCount ( " & f & " ; GetValue ( Substitute ( GetValue ( " & f & " ; ad ) ; \"::\" ; \"\¶\" ) ; 1 ) ) ; bl = 1 ; " & Left ( QA ; Length ( QA ) - 2 ) & "] ; bl ) " ) ) ;

lt = Substitute ( 10 ^ i - 1 ; 9 ;
                          "ii = ii + 1 ;
                         cf = Quote ( GetValue ( Substitute ( GetValue ( " & f & " ; ii ) ; \"::\" ; \"\¶\" ) ; 2 ) ) ; 
                          ct = Quote ( GetValue ( Substitute ( GetValue ( " & f & " ; ii ) ; \"::\" ; \"\¶\" ) ; 1 ) ) ;
                          nt = Quote ( GetValue ( Substitute ( GetValue ( " & f & " ; ii + 1 ) ; \"::\" ; \"\¶\" ) ; 1 ) ) ;
                          fq = Case ( IsEmpty ( fq ) and ii < " & i & " and ct = nt ; \"" & s & "\" & cf ;
                         IsEmpty ( fq ) and ii < " & i & " and ct ≠ nt ; \"" & s & "\" & cf & \" FROM \" & ct & \" " & j & s & "\" ;
                         IsEmpty ( fq ) and ii = " & i & " ; \"" & s & "\" & cf & \" FROM \" & ct ;
                         ii < " & i & " and ct = nt and RightWords ( fq ; 1 ) ≠ \"SELECT\" and RightWords ( fq ; 1 ) ≠ \"DISTINCT\" ; fq & \", \" & cf ;
                         ii < " & i & " and ct = nt and ( RightWords ( fq ; 1 ) = \"SELECT\" or RightWords ( fq ; 1 ) = \"DISTINCT\" ) ; fq & \" \" & cf ;
                         ii < " & i & " and ct ≠ nt ; fq & \", \" & cf & \" FROM \" & ct & \" " & j & s & "\" ;
                         ii = " & i & " and RightWords ( fq ; 1 ) ≠ \"SELECT\" and RightWords ( fq ; 1 ) ≠ \"DISTINCT\" ; fq & \", \" & cf & \" FROM \" & ct ;
                         ii = " & i & " and ( RightWords ( fq ; 1 ) = \"SELECT\" or RightWords ( fq ; 1 ) = \"DISTINCT\" ) ; fq & \" \" & cf & \" FROM \" & ct ) ; " ) ;

lt = Evaluate ( "Let ( [ ii = 0 ; fq = \"\" ; " & Left ( lt ; Length ( lt ) - 2 ) & "] ; fq )" ) & w ] ;

 

Case ( QA = 0 ; "ERROR: Multiple tables have been selected and the Qty of fields from each table must be the same" ;
           w7 = 0 ; "ExecuteSQL ( " & Left ( lt ; Length ( lt ) - 1 ) & " ; " & Quote ( fs ) & " ; " & Quote ( rs ) & " )" ;
           ExecuteSQL ( lt ; fs ; rs ) )
)

Outcomes