Announcement

Collapse
No announcement yet.

Macro for a SQLite function

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Macro for a SQLite function

    Is there a way to replace a SQLite function with a PowerBASIC Macro?
    Also trying to avoid any concatenations.
    sql = "select Rowid ,LTRIM(Printf('%.2f',price*.01),'0') as Amount from parts limit 1"

    Could use stringbuilder or JOIN$ an array of columns, but that wouldn't make the code easier to read.

    This works, but looking for a better way.
    sql = "select Rowid,@MONEY@ AS Amount from parts limit 1"
    REPLACE "@MONEY@" WITH "ltrim(printf('%.2f',price*.01),'0')" IN sql
    https://duckduckgo.com instead of google

  • #2
    I just use line continuations to make things easier to read:

    Code:
    sql = "select " & _
                 "Rowid, " & _
                  LTRIM(Printf('%.2f',price*.01),'0') & " As Amount, " & _
                 "Partnum, " & _
                 "Partname " & _
                 "from parts " & _
                  "limit 1"

    Comment


    • #3
      That's probably the way to go.
      Thank you!
      https://duckduckgo.com instead of google

      Comment


      • #4
        NB that LTRIM is not PowerBASIC's LTRIM function but SQLite's own ltrim() function.

        To directly answer the question, yes, something like this?

        Code:
        #compile exe
        #dim all
        #debug display
        macro mcolumns = "Rowid ,LTRIM(Printf('%.2f',price*.01),'0') as Amount"
        function pbmain () as long
            local s as string
            s = "select " + $crlf + mcolumns + $crlf + "from parts" + $crlf + "limit 1"
            ? s
        end function

        Comment


        • #5
          That looks like a MACRO isn't needed.
          $m = "Rowid ,LTRIM(Printf('%.2f',price*.01),'0') as Amount"
          https://duckduckgo.com instead of google

          Comment


          • #6
            Yes, but isn't that (using a macro) what you asked for? ;-)

            Comment


            • #7
              I gave up on the MACRO idea and just went for readability

              Code:
              FUNCTION PBMAIN AS LONG
               LOCAL sql      AS STRING
               LOCAL sArray() AS STRING
               sb = CLASS "StringBuilderA"
              
               b "CREATE View Greater AS
               b "Distinct PlayerNo
               b "From Penalties"
               b "Where Amount >
               b " (Select AVG(Amount)
               b "  From Penalties
               b "  Where PlayerNo In
               b "    (Select PlayerNo
               b "     From Matches"
               b "     Where TeamNo = 2));"
               ? sb.string
               GetRecordSet sb.string,sArray(),$TAB
               ? JOIN$(sArray(),$CR)      
              END FUNCTION
              
              SUB b(s AS STRING)    'make easier to read
               sb.add s " "  +$CR
              END SUB
              https://duckduckgo.com instead of google

              Comment


              • #8
                There is an ease-of-maintenance argument for storing queries in GLOBAL STRINGS or as #DEFINEd values, to avoid duplication, or more accurately, not-quite-duplication, in code.

                Also for using macros to substitute alternatives into SELECT statements. For example, different ORDER BY clauses.

                Comment


                • #9
                  ...so rather than embedding the SELECT statement "inline", a macro could be used, for example:

                  Code:
                  #compile exe
                  #dim all
                  #debug display
                  
                  macro function MCREATE_VIEW_GREATER ( n )
                  
                      macrotemp s
                      dim s as string
                      s = "CREATE View Greater AS" + _
                      "Distinct PlayerNo " + _
                      "From Penalties " + _
                      "Where Amount >" + _
                      " (Select AVG(Amount)" + _
                      "  From Penalties" + _
                      "  Where PlayerNo In" + _
                      "    (Select PlayerNo" + _
                      "     From Matches" + _
                      "     Where TeamNo = " + n + "));"
                  end macro = s
                  
                  function pbmain as long
                      local s as string
                  
                      s = MCREATE_VIEW_GREATER ("2")
                      ? s
                  end function

                  Comment


                  • #10
                    This makes the function easy to use with my original question, but does use concatenation.
                    It assumes money is saved as pennies.
                    Code:
                    MACRO money(column_name)= "LTRIM(Printf('%.2f'," + column_name + "*.01),'0')"
                    
                    FUNCTION PBMAIN AS LONG
                     sql$ = "select rowid," + money("MyColumn") + " from parts limit 1"
                     ? sql$
                    END FUNCTION
                    https://duckduckgo.com instead of google

                    Comment


                    • #11
                      MACRO money(column_name)= CHR$("LTRIM(Printf('%.2f',",column_name,"*.01),'0')")

                      Storing money as INTEGER pennies eliminates rounding problems and is probably more efficient
                      1 = .01 100 = 1.00

                      Truncates leading 0 so 60 becomes .60 not 0.60
                      Eliminates concatenation by using $CHR instead of + signs


                      https://duckduckgo.com instead of google

                      Comment

                      Working...
                      X