topic-34173

Posts: 1789

Joined: Tue Oct 11, 2005 05:01 PM

Parse de consulta sql
Posted: Tue Jun 06, 2017 10:56 PM

Estimados, alguien ha desarrollado un parse para consultas sql?
el unico que he encontrado es en la clase tdolphin de daniel, lo he reducido un poco, y agregado unas pocas lineas, pero
es mi intencion que de una consulta sql, extraer las tablas usadas, las columnas solicitadas, las condiciones, ordenes, etc.
me gustaria mejorarla lo mas posible, permitiendo consultas complejas, ya que actualmente permite consultas simples.

Code (fw): Select all Collapse

METHOD Parse( cQuery ) CLASS TMySQLQuery2
   LOCAL aToken, cItem, cSelect, cTables, cFind, nFind
   LOCAL cCol, lJoin, cTemp
   LOCAL aCommands := { "SELECT",;
                        "WHERE" ,;
                        "GROUP" ,;
                        "HAVING",;
                        "LIMIT" ,;
                        "ORDER" ,;
                        "FROM"  ,;
                        "DESC"  ,;
                        "ASC"   ,;
                        "CALL"   }

   aToken := HB_ATokens( cQuery, " " )

   FOR EACH cItem IN aToken
      IF AScan( aCommands, {| cCommand | cCommand == Upper( cItem ) } ) > 0
         cItem := "|" + cItem
      ENDIF
   NEXT

   cQuery := ""

   FOR EACH cItem IN aToken
      cQuery += cItem + " "
   NEXT

   cQuery := SubStr( AllTrim( cQuery ), 2 )

   aToken := HB_ATokens( cQuery, "|" )

   FOR EACH cItem IN aToken

      cFind := Upper( SubStr( cItem, 1, At( " ", cItem ) - 1 ) )

      IF !Empty( cFind )
         nFind := AScan( aCommands, cFind )
      ELSE
         EXIT
      ENDIF

      SWITCH nFind
      CASE 1 //"SELECT"
         cSelect := AllTrim( SubStr( cItem, 8 ) )
         lJoin   := FALSE
         cTemp   := ""

         ::aColumns := {}

         FOR EACH cCol IN ArrayFromSQLString( cSelect )
            IF lJoin
               IF ")" $ cCol
                  cTemp += cCol
                  AAdd( ::aColumns, cTemp )
                  lJoin := FALSE
               ELSE
                  cTemp += cCol + ","
               ENDIF
            ELSE
               cTemp := ""
               IF "(" $ cCol
                  cTemp := cCol + ","
                  lJoin := TRUE
               ELSE
                  AAdd( ::aColumns, cCol )
               ENDIF
            ENDIF
         NEXT
         EXIT

      CASE 2 //"WHERE"
         IF Empty( ::cWhere )
            ::cWhere := AllTrim( SubStr( cItem, 7 ) )
         ENDIF
         EXIT

      CASE 3 //"GROUP"
         IF Empty( ::cGroup )
            ::cGroup := AllTrim( SubStr( cItem, 10 ) )
         ENDIF
         EXIT

      CASE 4 //"HAVING"
         IF Empty( ::cHaving )
            ::cHaving := AllTrim( SubStr( cItem, 8 ) )
         ENDIF
         EXIT

      CASE 5 //"LIMIT"
         IF Empty( ::cLimit )
            ::cLimit := AllTrim( SubStr( cItem, 7 ) )
         ENDIF
         IF Val( ::cLimit ) <= 1
            ::cLimit := ""
         ENDIF
         EXIT

      CASE 6 //"ORDER"
         IF Empty( ::cOrder )
            ::cOrder := AllTrim( SubStr( cItem, 10 ) )
         ENDIF
         EXIT

      CASE 7 //"FROM"
         IF Empty( ::aTables )
            cTables   := AllTrim( SubStr( cItem, 6 ) )
            ::aTables := ArrayFromSQLString( cTables )
         ENDIF
         EXIT

      CASE 8 //"DESC"
         IF !Empty( ::cOrder )
            ::cOrder += " DESC"
         ENDIF
         EXIT

      CASE 9 //"ASC"
         IF !Empty( ::cOrder )
            ::cOrder += " ASC"
         ENDIF
         EXIT

      ENDSWITCH

   NEXT

RETURN NIL

Salu2

Carlos Vargas

Desde Managua, Nicaragua (CA)

FiveTech Support Forums

Continue the discussion